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

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

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

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

3000

3000 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

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

, 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

chars max and if you are sending

params

params

with the request I guess there is a limit of

.

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

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

, 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

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

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/

Original message was deleted

I

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

If you have preset values then it's up to you to make sure they can't send an arbitrary value.

I

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

Just make sure all of the queries they select are safe.

I

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

I.e. don't just parse the query params and use them for filters, instead allow them to select

equals

equals

,

not equals

not equals

, etc.

I

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

Nope, not really, if the query is completely dynamic. Just make sure they can only query allowed columns and only with allowed filters, and then bind the actual filter values using the

.bind()

.bind()

API, and you should be good.

I

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

Keep in mind you can only bind values, not the column names or table names themselves.

I

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

You'll have to manually do string interpolation there.

T

thipperz•3/14/24, 11:26 PM

someone please help me with a query: I'm trying to remove the dot at the end of the values that have it:

wrangler d1 execute database --command "UPDATE tags SET tag = SUBSTR(tag, 1, LENGTH(tag) - 1) WHERE tag LIKE '%.'" --remote

wrangler d1 execute database --command "UPDATE tags SET tag = SUBSTR(tag, 1, LENGTH(tag) - 1) WHERE tag LIKE '%.'" --remote

when I try it, I get

 Internal error in D1 storage caused object to be reset. [code: 7500]

 Internal error in D1 storage caused object to be reset. [code: 7500]

I don't think it is a timeout issue since there are 1000 matching tags

T

Thev•3/15/24, 12:31 AM

Hi everyone, is adding the D1 binding along with the database id into your .toml not a security risk?

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

TThev Hi everyone, is adding the D1 binding along with the database id into your .toml...

C

Chaika•3/15/24, 12:32 AM

In general with CF IDs (Account IDs, Zone IDs, D1 IDs, or any binding in your wrangler.toml) are not secrets or a security risk. Only you/your account can bind to your D1 DB

T

Thev•3/15/24, 12:32 AM

awesome. cheers!

C

Chaika•3/15/24, 12:34 AM

Of course if you have Secrets/api keys/etc, use secrets (https://developers.cloudflare.com/workers/configuration/secrets/), rather then environment variables in your wrangler.toml, but bindings wise all those IDs and such are safe to commit.

EEnduriel Is there some reason I would be unable to turn off foreign key constraints? I sa...

J

jkomyno•3/15/24, 5:49 AM

I stumbled on the same problem with

D1Database

D1Database

using

getPlatformProxy

getPlatformProxy

from the

"wrangler"

"wrangler"

module

F

florent•3/15/24, 11:52 AM

does D1 have the ability to create read replicas? I couldn't fine anything about that in the docs

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

Similar Threads