has anyone noticed how restrictive sqlite is when it comes to migration scripts? there are barely an

has anyone noticed how restrictive sqlite is when it comes to migration scripts? there are barely any options to alter an existing column. how are people getting by with all these missing features?

squareclamp•3/12/24, 3:44 AM

Hi, I'm looking for help on how to do multi statement inserts in d1, when the inserts are dependent.

Example, I want to perform a transaction to create a postpost and have author inserted in post_authorpost_author. If this is done separately, a post can be without author. I can't use batch API, because I need post_idpost_id for entry in post_authorpost_author.

Is this an alternative that can be achieved with d1?

Ccopper @Max (@rozenmd) thanks for hinting to a hyperdrive channel, who could I ask abou...

Tony Leung•3/12/24, 3:44 AM

Oh that's why my app are suddenly slow-read on low traffic period.

Tony Leung•3/12/24, 3:45 AM

Bro literally had summoned big boss Kenton to answer your question :MeowHeartCloudflare:

Mmatt has anyone noticed how restrictive sqlite is when it comes to migration scripts?...

Tony Leung•3/12/24, 3:50 AM

That's how SQLite works, not D1 fault, you cannot alter a column after they has created. There are two way for you to deal with this:

since sqlite does not restrict you from saving other data type to a typed column, you can just save different data type to it. Like you can store text into number column and no one can stop you
If you want to add foreign key or change column properties like set not null, you have to create a new table with different name with new configuration, insert data from old table into the new one, delete the old one and rename the new table to be the same as the old one.

RRexNormous Right! Re-engineering this in DO, I believe I will: 1. Start DO tx to acquire lo...

squareclamp•3/12/24, 3:52 AM

@RexNormous Did you end up implementing this? Any pointers would be great.

Mmatt has anyone noticed how restrictive sqlite is when it comes to migration scripts?...

Tony Leung•3/12/24, 3:54 AM

If you are not confident about writing the migration yourself (like I am :lul:), then use a db management software, modify the local sqlite db there and copy the generated sql commands into the migration file. I'm using datagrip for that task

Ssquareclamp @RexNormous Did you end up implementing this? Any pointers would be great.

RexNormous•3/12/24, 3:56 AM

Not yet - I’m considering actually not using D1 and instead going with a database that supports transactions instead, possibly Turso since it’s SQLite and porting to D1 later should be easy, or Neon. The main reason for this change is the potential cost of Durable Objects - my fetch could take seconds of latency and AFAIK DO bills on wall time not CPU time.

TTony Leung If you are not confident about writing the migration yourself (like I am :lul:),...

mattOP•3/12/24, 4:05 AM

i found that prisma can auto-generate migration scripts for altering tables via creating new table, copy data, delete old table, rename new... but prisma doesn't run on cloudflare d1 as i understand so I'm just using it for the migrations

then im generating a drizzle schema from the prisma one... and using that in my runtime code

Pranjal•3/12/24, 5:30 AM

Hello everyone! I'm currently working on a project using Drizzle ORM with D1. I'm wondering if there are any tools like phpmyadmin available to visualize D1's database ?

Jokcy•3/12/24, 8:15 AM

Hi team, what the maxium per db size will be when D1 ga? will it still be limited so it's not suitable for application require unlimited data size?

Ccopper drizzle studio?!

Pranjal•3/12/24, 10:24 AM

Even after logging in with wrangler login I get this with drizzle studio
[Warning] It seems like you are trying to access your D1 Database. Please make sure to run 'wrangler login' before using Drizzle Studio to ensure it has your token for making requests to the D1 Database. If you encounter an error related to CLOUDFLARE_TOKEN, please run 'wrangler login' and restart the studio

If I ignore this warning
drizzle studio looks for wrangler.toml in the wrong place

Pranjal•3/12/24, 10:25 AM

Is this https://orm.drizzle.team/docs/rqb not supported for D1 yet ??

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Original message was deleted

Pranjal•3/12/24, 11:14 AM

Thanks a lot!

Original message was deleted

Max (@rozenmd)•3/12/24, 11:29 AM

...yet (though sharding will be necessary)

mattOP•3/12/24, 1:11 PM

anyone here working on the drizzle d1 plugin, or is that the drizzle team working on it? it doesn't really work so good right now, it tries using transactions instead of the batch api, the migrator doesn't work, can't access .exec, plus a few other minor issues

MMax (@rozenmd)...yet (though sharding will be necessary)

Epailes•3/12/24, 2:02 PM

Would auto sharding ever be something on the table? Or would that always be up to the developer?

I was doing a thought exercise on how to make something like a simple Tumblr clone on D1 (chronological feed of posts from accounts a user follows, and ability to search posts from all users). It works fine small scale, but the conclusion I came to was just as it scales up it'd get too hacky to keep working on D1.

Shihmin Lee•3/12/24, 2:17 PM

Hey guys, I have some feedback on the D1 UI. When I use the UI and go to page 40 to edit a value and click save, the UI redirects back to page 1. This means if I need to continue editing data around page 40, I have to click 40 times to get back to the area where the data is.

I feel that after clicking save, it would make sense for the UI to stay where it was?

Original message was deleted

mattOP•3/12/24, 2:25 PM

Latest version, I’ve gone through the source code on github too

mattOP•3/12/24, 2:28 PM

Are you using the drizzle migrate, or wrangler migration?

SShihmin Lee Hey guys, I have some feedback on the D1 UI. When I use the UI and go to page 40...

Max (@rozenmd)•3/12/24, 2:48 PM

cheers for the bug report, will add it to our backlog

Matthijs•3/12/24, 4:00 PM

@Max (@rozenmd) quick question, we have raised a request for a limit increase I think 6 weeks ago but never got a reply. What's the best way to get a reply to this request?

Max (@rozenmd)•3/12/24, 4:02 PM

Would appreciate not being pinged directly - if it's a DB size increase, we're aiming for 10GB for GA

Matthijs•3/12/24, 4:02 PM

sure, no problem.
so during beta that won't be possible?

MMatthijs sure, no problem. so during beta that won't be possible?

Max (@rozenmd)•3/12/24, 4:18 PM

at the moment we're only increasing the number of DBs

RRexNormous Not yet - I’m considering actually not using D1 and instead going with a databas...

squareclamp•3/12/24, 4:23 PM

thanks! @Max (@rozenmd) any word on support for transactions before GA?

James•3/12/24, 4:49 PM

I've been seeing this every now and then, I'd say about once per week, on at least one project with a D1 database when deploying via CI:

  binding D1USEAST of type d1 failed to generate. Please try again later or contact support. [code: 10021]

  binding D1USEAST of type d1 failed to generate. Please try again later or contact support. [code: 10021]

If I re-run CI, it generally works fine, but hopefully can be improved in the future.

Max (@rozenmd)•3/12/24, 9:37 PM

As of wrangler@3.33.0, wrangler d1 executewrangler d1 execute and wrangler d1 migrations applywrangler d1 migrations apply now default to using a local database, to match the default behavior of wrangler dev.

It is also now possible to specify one of --local or --remote to explicitly tell wrangler which environment you wish to run your commands against.

mattOP•3/13/24, 5:44 AM

does wrangler use the http rest endpoint for execute / migrations apply? if so, does this mean migrations are slower than running the same queries from a worker?

Mmatt does wrangler use the http rest endpoint for execute / migrations apply? if so, ...

Max (@rozenmd)•3/13/24, 6:33 AM

correct

Tony Leung•3/13/24, 7:13 AM

hi, will D1 have a feature like clustering data? Manually sharding with D1 will make JOIN impossible.

TTony Leung hi, will D1 have a feature like clustering data? Manually sharding with D1 will ...

Max (@rozenmd)•3/13/24, 10:35 AM

eventually, but at the moment you'd need to pull the data into a Worker and do join in JS

Mmatt does wrangler use the http rest endpoint for execute / migrations apply? if so, ...

Matt Silverlock•3/13/24, 12:55 PM

Migrations are unlikely to be bottlenecked by a bit of network latency. Running the migration will be the slowest part. It’s rarely “hot path” in a user facing request.

aa_ndrej•3/13/24, 5:42 PM

We have a requirement where we need a dedicated server to read specific files and then update a D1 database. I found the D1 REST (HTTP) API to insert values into D1 from outside a worker environment.

However:
I can only insert a small batch at a time.

{
  sql: 'INSERT INTO "table" ("a", "b", "c") VALUES (?, ?, ?)',
  params: [...],
}

{
  sql: 'INSERT INTO "table" ("a", "b", "c") VALUES (?, ?, ?)',
  params: [...],
}

With that I can insert up to 3333 entries at a time before the server hits me with a 500 Internal Server Error500 Internal Server Error. When concatinating the SQL query myself I found that I can go up to a query length of 1000010000 characters. Depending on the length of my input values I can insert more than 30003000 entries at a time with this method. This could be however a security risk as I understand.

Is there a more secure yet fast way to insert a lot of values into D1 from outside a worker environment?

jkomyno•3/13/24, 6:16 PM

Hi, is there any way of creating a D1 database locally with wranglerwrangler, without needing to be logged in to a Cloudflare Account?

Command: npx wrangler d1 create database_namenpx wrangler d1 create database_name

Context: I need to spawn a local D1 database from Rust, for integration test purposes.

Original message was deleted

Chaika•3/13/24, 7:09 PM

If you need to know, it's under npx wrangler d1 infonpx wrangler d1 info, but yea would be nice in dash

Jjkomyno Hi, is there any way of creating a D1 database locally with `wrangler`, without ...

mattOP•3/14/24, 12:20 AM

You can use miniflare

MMatt Silverlock Migrations are unlikely to be bottlenecked by a bit of network latency. Running ...

mattOP•3/14/24, 2:38 AM

i might be crazy... (im sure I am) but i deploy the worker & db migration together like this:

deploy the new worker script, including the ID of the latest migration
incoming requests check real db value against ID, and will retry db operations until the value matches
while some incoming requests are being held this way, the db migration is performed

this is why the speed of the migration is important for me, and I only can do this because there's only ever max 1 db operation per incoming request. this way I can make breaking changes to the DB schema without having to do the traditional widen-narrow multi-deployment approach

Jjkomyno Hi, is there any way of creating a D1 database locally with `wrangler`, without ...

Matt Silverlock•3/14/24, 10:51 AM

https://developers.cloudflare.com/d1/configuration/local-development/#test-programmatically

Cloudflare Docs

Local development · Cloudflare D1 docs

D1 has fully-featured support for local development, running the same version of D1 as Cloudflare runs globally. Local development uses Wrangler, the …

Mmatt i might be crazy... (im sure I am) but i deploy the worker & db migration togeth...

Matt Silverlock•3/14/24, 10:53 AM

Right, but we’re talking about 30ms (typical case to D1) vs 250ms (typical case to HTTP API) - not seconds or more. This isn’t to say we don’t want to make the API faster - we do! - but I want to put it in context here.

MMatt Silverlock Right, but we’re talking about 30ms (typical case to D1) vs 250ms (typical case ...

mattOP•3/14/24, 10:56 AM

Got it, thanks - I thought I read it was up to 30 seconds via teh REST API, not sure where I got that from

MMatt Silverlock Right, but we’re talking about 30ms (typical case to D1) vs 250ms (typical case ...

mattOP•3/14/24, 1:41 PM

Does the http api support batching? If not the migration could take a while for each round trip eg. Read latest migration ID, apply missing migrations and update migration table after each success. I think for 2 migrations that’s 5 round trips?

Mmatt Does the http api support batching? If not the migration could take a while for ...

aa_ndrej•3/14/24, 1:49 PM

You can send several SQL statements delimeted by ;; to the HTTP API if that works for you, but there are limits. Query string can be 1000010000 chars max and if you are sending paramsparams with the request I guess there is a limit of 100100.

has anyone noticed how restrictive sqlite is when it comes to migration scripts? there are barely an

Similar Threads

Similar Threads

Similar Threads