@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?...

T

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.

R

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:),...

M

matt•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

P

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 ?

J

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?!

P

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

P

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

P

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

Thanks a lot!

Original message was deleted

M

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

...yet (though sharding will be necessary)

M

matt•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)

E

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.

S

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

M

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

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

M

matt•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...

M

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

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

M

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?

M

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

M

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?

M

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...

S

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

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

J

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]

  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.

M

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

As of wrangler@3.33.0,

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute and

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler 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.

M

matt•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, ...

M

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

correct

T

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 ...

M

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, ...

M

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.

A

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: [...],
}

{
  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

33

33 entries at a time before the server hits me with a

500 Internal Server Error

500 Internal Server Error

500 Internal Server Error

500 Internal Server Error. When concatinating the SQL query myself I found that I can go up to a query length of

10000

10000 characters. Depending on the length of my input values I can insert more than

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?

J

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

Hi, is there any way of creating a D1 database locally with

wrangler

wrangler

, without needing to be logged in to a Cloudflare Account?

Command:

npx wrangler d1 create database_name

npx wrangler d1 create database_name

npx wrangler d1 create database_name

npx wrangler d1 create database_name

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

Original message was deleted

C

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

If you need to know, it's under

npx wrangler d1 info

npx wrangler d1 info

npx wrangler d1 info

npx 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 ...

M

matt•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 ...

M

matt•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 ...

M

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...

M

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 ...

M

matt•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 ...

M

matt•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 ...

A

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

10000

10000 chars max and if you are sending

params

params

with the request I guess there is a limit of

100

100.

Aaa_ndrej We have a requirement where we need a dedicated server to read specific files an...

A

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

@Matt see my observasions here.

K

keya•3/14/24, 2:21 PM

Maybe, wrangler dev with —remote true?

Original message was deleted

I

Isaac McFadyen•3/14/24, 5:43 PM

D1 has an HTTP API so it's certainly possible, but it would be up to the developers of TablePlus to add it, not the developers of D1

Original message was deleted

I

Isaac McFadyen•3/14/24, 5:57 PM

True, but at that point where does it end, since there are hundreds of such tools out there? Usually it's up to the tools to support the service not the service to support the tools.

N

Nicolas•3/14/24, 7:32 PM

So my applications throws a 500 error when a table doesn't exist against a query, but according to the types,

D1Response

D1Response

D1Response

D1Response is supposed to contain an

error

error

field, but the type is

never

never

? When I console log the error it says the error is

D1_Error

D1_Error

D1_Error

D1_Error, so is there a way I catch this error in a type-safe way so I can determine the error message?

This is my code:

// Check if the user already exists
        const checkUserQuery = 'SELECT Username FROM User WHERE Username = ?';
        const checkUserStmt = env.usersDB.prepare(checkUserQuery);        
        console.log("existing user statement")
        console.log(checkUserStmt)
        let existingUser;
        try {
            existingUser = await checkUserStmt.bind(username).all();
        }
        catch (e)
        {
            console.log("Received Error")
            console.log(e)                        
        }

// Check if the user already exists
        const checkUserQuery = 'SELECT Username FROM User WHERE Username = ?';
        const checkUserStmt = env.usersDB.prepare(checkUserQuery);        
        console.log("existing user statement")
        console.log(checkUserStmt)
        let existingUser;
        try {
            existingUser = await checkUserStmt.bind(username).all();
        }
        catch (e)
        {
            console.log("Received Error")
            console.log(e)                        
        }

// Check if the user already exists
        const checkUserQuery = 'SELECT Username FROM User WHERE Username = ?';
        const checkUserStmt = env.usersDB.prepare(checkUserQuery);        
        console.log("existing user statement")
        console.log(checkUserStmt)
        let existingUser;
        try {
            existingUser = await checkUserStmt.bind(username).all();
        }
        catch (e)
        {
            console.log("Received Error")
            console.log(e)                        
        }

// Check if the user already exists
        const checkUserQuery = 'SELECT Username FROM User WHERE Username = ?';
        const checkUserStmt = env.usersDB.prepare(checkUserQuery);        
        console.log("existing user statement")
        console.log(checkUserStmt)
        let existingUser;
        try {
            existingUser = await checkUserStmt.bind(username).all();
        }
        catch (e)
        {
            console.log("Received Error")
            console.log(e)                        
        }

Original message was deleted

N

Nicolas•3/14/24, 7:49 PM

ok that makes sense, for now i am just doing

const e_error = e as unknown as Error

const e_error = e as unknown as Error

const e_error = e as unknown as Error

const e_error = e as unknown as Error and it works fine

N

Nicolas•3/14/24, 7:50 PM

i feel like never should never be used for a type lol, i can't see a useful case for it, if it's supposed to be reserved for future use it should

any

any

, even

unknown

unknown

is questionable to use lol

U

Unsmart•3/14/24, 8:33 PM

If the variable "e" in the catch is labeled as never that seems like an issue with your typescript and not with D1.

never

never

is very useful as a type to specify that something should never be set

Original message was deleted

I

Isaac McFadyen•3/14/24, 9:34 PM

You can use the D1

.bind()

.bind()

API to bind variables based on JS values.

I

Isaac McFadyen•3/14/24, 9:34 PM

For example, a query would be:

SELECT * FROM table WHERE name = ?

SELECT * FROM table WHERE name = ?

SELECT * FROM table WHERE name = ?

SELECT * FROM table WHERE name = ? and then you can bind a value for

name

name

I

Isaac McFadyen•3/14/24, 9:34 PM

https://developers.cloudflare.com/d1/build-databases/query-databases/

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

Similar Threads