Hi quick question, can you somehow access production (remote) D1 outside Worker? Maybe via REST + Au

HHelfo Hi quick question, can you somehow access production (remote) D1 outside Worker?...

V

Vlady•4/18/24, 9:42 AM

Yup https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

HHelfo Hi quick question, can you somehow access production (remote) D1 outside Worker?...

H

Hello, I’m Allie!•4/18/24, 9:42 AM

We generally recommend that you build a Worker to proxy requests to D1. While there is a REST API to interact with a D1 instance, it is generally much slower than a Worker

HHello, I’m Allie!We generally recommend that you build a Worker to proxy requests to D1. While th...

H

HelfoOP•4/18/24, 9:45 AM

I need to make

.csv

.csv

.csv

.csv exports from D1 database and Worker is running "out of resources" while trying to process that data (expected). So my current plan is to make node server that will dump it without any limitations and provide final csv file.

But yeah your suggestion works too.. Worker can work as proxy and node server will slowly paginate data and save them

P

Prisma Gestión Empresarial•4/18/24, 1:30 PM

I got a cron worker that is occasionally getting a

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost.

. The cron executes every minute.

* * * * *

* * * * *

* * * * *

* * * * *. Is it expected to get those errors from time to time? I wasn't really monitoring that worker before so I don't know how long it's been happening, in the last 24 hours it only happened 5 times so it's not a big deal for me either (since the worker execute every minute). Just wondering if there may be something I'm doing wrong that's causing these errors.

MMatt Hamann Is it expected that running `db.prepare('INSERT INTO my_table (id, name) VALUES ...

K

kian•4/18/24, 7:19 PM

Isn't that what RETURNING is for?

K

kian•4/18/24, 7:20 PM

https://www.sqlite.org/lang_returning.html

K

kian•4/18/24, 8:12 PM

iirc Postgres is the same

K

kian•4/18/24, 8:12 PM

postgres=# INSERT INTO foo VALUES ('bar');
INSERT 0 1
postgres=# INSERT INTO foo VALUES ('bar') RETURNING *;
 bar 
-----
 bar
(1 row)

INSERT 0 1

postgres=# INSERT INTO foo VALUES ('bar');
INSERT 0 1
postgres=# INSERT INTO foo VALUES ('bar') RETURNING *;
 bar 
-----
 bar
(1 row)

INSERT 0 1

postgres=# INSERT INTO foo VALUES ('bar');
INSERT 0 1
postgres=# INSERT INTO foo VALUES ('bar') RETURNING *;
 bar 
-----
 bar
(1 row)

INSERT 0 1

postgres=# INSERT INTO foo VALUES ('bar');
INSERT 0 1
postgres=# INSERT INTO foo VALUES ('bar') RETURNING *;
 bar 
-----
 bar
(1 row)

INSERT 0 1

_

_tweak^prone•4/19/24, 7:40 AM

Does D1 have a geometry field ?

大

大熊•4/19/24, 8:49 AM

can i import one d1 database from one cloudflare account to another cloudflare account?

V

Vikash•4/19/24, 8:50 AM

D1 should consider

undefined

undefined

as

null

null

I think, otherwise we have to use

value || null

value || null

value || null

value || null for each field. For example, the description throw an error when

undefined

undefined

 await env.DB.prepare(
    `UPDATE Campaigns SET name = ?, description = ? WHERE campaign_id = ?`
  )
    .bind(
      campaign.name,
      campaign.description || null,
      campaign.campaign_id
    )
    .run();

 await env.DB.prepare(
    `UPDATE Campaigns SET name = ?, description = ? WHERE campaign_id = ?`
  )
    .bind(
      campaign.name,
      campaign.description || null,
      campaign.campaign_id
    )
    .run();

 await env.DB.prepare(
    `UPDATE Campaigns SET name = ?, description = ? WHERE campaign_id = ?`
  )
    .bind(
      campaign.name,
      campaign.description || null,
      campaign.campaign_id
    )
    .run();

 await env.DB.prepare(
    `UPDATE Campaigns SET name = ?, description = ? WHERE campaign_id = ?`
  )
    .bind(
      campaign.name,
      campaign.description || null,
      campaign.campaign_id
    )
    .run();

VVikash D1 should consider `undefined` as `null` I think, otherwise we have to use `val...

_

_tweak^prone•4/19/24, 9:16 AM

that would seem a bit dangerous - its a specific assumption that

undefined

undefined

would have any relation to

null

null

. You would need to handle these specifically yourself.

Also please note,

||

||

operator works on falsy/truthy.

null

null

,

NaN

NaN

NaN

NaN,

,

""

""

and

undefined

undefined

would all end as

null

null

in your example above.

_

_tweak^prone•4/19/24, 9:17 AM

In other words, if the description is

""

""

or

"0"

"0"

"0"

"0"

your SQL would save them as
```
null
```
```
null
```
.

__tweak^prone that would seem a bit dangerous - its a specific assumption that `undefined` wou...

V

Vikash•4/19/24, 9:19 AM

That's expected, as

description

description

is optional fields, so I need to insert

null

null

if no value provided.

Otherwise D1 throws an error

VVikash That's expected, as `description` is optional fields, so I need to insert `null`...

_

_tweak^prone•4/19/24, 9:19 AM

you misunderstand.

the string

"0"

"0"

"0"

"0" is not "no value"

V

Vikash•4/19/24, 9:20 AM

I am using

||

||

on string type fields only

_

_tweak^prone•4/19/24, 9:20 AM

"0" == false

"0" == false

"0" == false

"0" == false, therefore

"0" || null

"0" || null

"0" || null

"0" || null is

null

null

V

Vikash•4/19/24, 9:20 AM

on Boolean type, I am using like this

campaign.is_enabled ? 1 : 0

campaign.is_enabled ? 1 : 0

campaign.is_enabled ? 1 : 0

campaign.is_enabled ? 1 : 0

_

_tweak^prone•4/19/24, 9:21 AM

You should read up on truthy / falsy

V

Vikash•4/19/24, 9:21 AM

I will, thanks. Any quick way to change string types to null?

_

_tweak^prone•4/19/24, 9:21 AM

If "description" is just a zero ("0") as text, you will save "null" in the database.

VVikash I will, thanks. Any quick way to change string types to null?

_

_tweak^prone•4/19/24, 9:23 AM

campaign.description === undefined ? null : campaign.description

campaign.description === undefined ? null : campaign.description

campaign.description === undefined ? null : campaign.description

campaign.description === undefined ? null : campaign.description

or make a wrapper function

function bindWrap( ...args)

function bindWrap( ...args)

function bindWrap( ...args)

function bindWrap( ...args)

that does it for you

__tweak^prone `campaign.description === undefined ? null : campaign.description` or make a wr...

V

Vikash•4/19/24, 9:24 AM

Awesome, thanks.

L

luna•4/19/24, 9:26 AM

i want to take my remote db and use it locally, is there a wrangler command for this? i couldnt find anything in the docs.

Lluna i want to take my remote db and use it locally, is there a wrangler command for ...

_

_tweak^prone•4/19/24, 9:36 AM

add --remote and you can use the remote database in your dev env

__tweak^prone add --remote and you can use the remote database in your dev env

L

luna•4/19/24, 9:51 AM

what if im using next js?

Lluna what if im using next js? 🤔

_

_tweak^prone•4/19/24, 10:29 AM

here you go

https://community.cloudflare.com/t/nextjs-pages-application-with-d1-bindings/595958

A

arsengoian•4/19/24, 5:40 PM

Hi everyone. Strings are truncated of any whitespace characters while inserting or updating columns of any type (varchar, binary). Is this intended behaviour? How do I insert a string that ends with a space? I can run the same in an sqlite3 database and the space is preserved.

H

Hannes•4/19/24, 7:48 PM

Hi, when exporting a D1 Database with

wrangler d1 export

wrangler d1 export

, the SQL file cannot be imported again using

wrangler d1 execute --file ...

wrangler d1 execute --file ...

wrangler d1 execute --file ...

wrangler d1 execute --file ..., because in the sql the tables are being created in the wrong order. That results in logs of

no such table

no such table

no such table

no such table error messages because

CONSTRAINT FOREIGN KEYS

CONSTRAINT FOREIGN KEYS

CONSTRAINT FOREIGN KEYS

CONSTRAINT FOREIGN KEYS come before the respective

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE...
Is that a bug?

Ggeelen can you send me the chunk of SQL that shows the statements in the wrong order?

H

Hannes•4/19/24, 8:58 PM

Sure, thanks for looking into this. I'll send you a DM. Or should I create a GitHub Issue?

__tweak^prone `campaign.description === undefined ? null : campaign.description` or make a wr...

森

森@VerseLink (RW)•4/20/24, 2:34 AM

wouldn't

campaign.description ?? null

campaign.description ?? null

campaign.description ?? null

campaign.description ?? null work the same but more concise and easy to read?

Y

yellott•4/20/24, 6:02 AM

Hi there, is there any recommended workflow on D1 migration to reduce/eliminate downtime? Maybe something CF specific, wasn't able to find anything related in the docs.

O

oof2win2•4/20/24, 4:43 PM

add a cronjob and an expiry field, every cronjob execution just delete the expired ones

O

oof2win2•4/20/24, 4:43 PM

ideally with an index on the expired field

O

oof2win2•4/20/24, 4:43 PM

since you dont want to go over all sessions to delete expired ones

VVitamin Protein One question can I use cloudflare d1 with the django app ? The whole documenta...

H

Hello, I’m Allie!•4/20/24, 5:44 PM