Migrations are unlikely to be bottlenecked by a bit of network latency. Running the migration will b

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

entries at a time before the server hits me with a

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

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

F

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

I have a hard time distinguishing D1 from other products like turso, except for the obvious CF integration

Fflorent does D1 have the ability to create read replicas? I couldn't fine anything about...

H

Hello, I’m Allie!•3/15/24, 11:55 AM

Not yet

N

natsumesou•3/15/24, 3:04 PM

Does D1 plan to provide features to support cost optimization, such as showing how many rows were scanned by a query?

C

Cyb3r-Jak3•3/15/24, 3:11 PM

It does. There is the meta object on the result object

Nnatsumesou Does D1 plan to provide features to support cost optimization, such as showing h...

D

DaniFoldi•3/15/24, 3:12 PM

Docs: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#return-object
Also analytics and graphql: https://developers.cloudflare.com/d1/observability/metrics-analytics/

N

natsumesou•3/15/24, 3:15 PM

oh sorry, thank you!

Nnatsumesou Does D1 plan to provide features to support cost optimization, such as showing h...

M

Matt SilverlockOP•3/15/24, 3:15 PM

Also: d1-database

MMatt Silverlock Also: https://discord.com/channels/595317990191398933/992060581832032316/1211617...

M

Max (@rozenmd)•3/15/24, 3:17 PM

we also plan on building UI for this, so you won't have to use

wrangler d1 insights

wrangler d1 insights

MMax (@rozenmd)we also plan on building UI for this, so you won't have to use `wrangler d1 insi...

M

Matt SilverlockOP•3/15/24, 3:17 PM

^ this!

Example via

wrangler

wrangler

today:

➜  npx wrangler@latest d1 insights db-enam --sort-by=reads

➜  npx wrangler@latest d1 insights db-enam --sort-by=reads

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

N

natsumesou•3/15/24, 3:21 PM

@Matt @Max (@rozenmd) Its cool! thank you so much.

F

florent•3/15/24, 10:07 PM

D1 can only be queried in a worker or pages environment right? So I couldnt query it from a vercel hosted nextjs app?

Fflorent D1 can only be queried in a worker or pages environment right? So I couldnt quer...

I

itsmatteomanf•3/15/24, 10:12 PM

You can, you need a Worker to be a proxy.

M

matt•3/17/24, 1:17 AM

if using the

/query

/query

endpoint with multiple statements, is the operation atomic?

M

matt•3/17/24, 1:19 AM

is read replication enabled? the top comment at https://news.ycombinator.com/item?id=36208568 suggests it is

thunderbong

Why SQLite is so great for the edge

Hacker News

•

6/6/23, 5:37 AM

Mmatt is read replication enabled? the top comment at https://news.ycombinator.com/ite...

I

Isaac McFadyen•3/17/24, 1:27 AM

Replication is not currently enabled for D1.

Migrations are unlikely to be bottlenecked by a bit of network latency. Running the migration will b

Similar Threads

Similar Threads

Similar Threads