`cmd` isn't a fan of single quotes.

G

Hi! How is billed the usage of SQL extension RETURNING * on INSERT/UPDATE/DELETE ?
Is the cost of the writing ( 500x to 1000x more expansive that a read cost ) offer the content returned?

K

kianOP•10/25/23, 7:10 AM

Take a look at the returned meta object and see how rows_read and rows_written change

K

kianOP•10/25/23, 7:10 AM

If they don’t change if you add RETURNING, then there is no difference to billing.

K

kianOP•10/25/23, 7:37 AM

Given the breakpoints, I assume you're using

wrangler dev

wrangler dev

K

kianOP•10/25/23, 7:37 AM

That is likely using an older version of

workerd

workerd

which doesn't have the changes merged.

K

kianOP•10/25/23, 7:38 AM

You need at least Wrangler 3.13.2 for the changes to be reflected in local development.

Kkian `cmd` isn't a fan of single quotes.

O

Old Man Maker•10/25/23, 12:15 PM

Thanks @kian, I think you are spot on - powershell appears to be fine, but this was backed into a cmd - great pick up. Cheers

E

EvanPiro•10/25/23, 2:23 PM

I'm not able to delete a table from d1 due to this mysterious error. It's the only table in my database.

Screen_Shot_2023-10-25_at_10.14.14_AM.png

EEvanPiro I'm not able to delete a table from d1 due to this mysterious error. It's the on...

E

EvanPiro•10/25/23, 2:23 PM

The table view also doesn't load

Screen_Shot_2023-10-25_at_10.23.46_AM.png

EEvanPiro The table view also doesn't load

E

EvanPiro•10/25/23, 2:25 PM

And now the issue has vanished

¯\_(ツ)_/¯

EEvanPiro And now the issue has vanished ¯\_(ツ)_/¯

E

EvanPiro•10/25/23, 2:26 PM

probably due to eventual consistency, right?

F

Farhian•10/25/23, 3:38 PM

Is there a D1 portal outage atm?

F

Farhian•10/25/23, 3:38 PM

I tried relogging and still nothing

K

kane•10/25/23, 3:39 PM

#cloudflare-status something wrong going on

F

Farhian•10/25/23, 3:39 PM

thank you @kane

EEvanPiro probably due to eventual consistency, right?

C

Chaika•10/25/23, 3:46 PM

(D1 isn't eventually consistent, no read replicas right now, strongly consistent)

EEvanPiro probably due to eventual consistency, right?

M

Matt Silverlock•10/25/23, 5:49 PM

There is an incident with a number of our APIs (not specific to D1, but affects the dashboard): https://www.cloudflarestatus.com/incidents/s1hkh315y9s9

Cloudflare Dashboard and Cloudflare API service issues

F

Fun Miles•10/25/23, 8:25 PM

Is it possible to get inserted row ids from a batch insert ?

FFun Miles Is it possible to get inserted row ids from a batch insert ?

F

Fun Miles•10/25/23, 8:33 PM

I found the answer: using "RETURNING rowId" at the end of the statement did the job.

J

Jacob Wright•10/25/23, 10:25 PM

I have a couple of questions about migrations. Do they run automatically locally, or remotely? Or do you always run them with the command? And second, is there any way to specify which migrations go to which database? It looks like you create them with a database name, but then any database will list them as a migration for it. Is that an oversight?

G

GuillaumeLakano•10/26/23, 8:50 AM

There is not yet a #d1-feature-suggestions but I would like to suggest somes features:

G

GuillaumeLakano•10/26/23, 8:50 AM

1) To permit in a batch requests list to have a way to continue or stop the batch, based on the previous result. This will prevent multiple roundtrips between the Workers <=> D1, for example you want to execute some requests but first, you need to be sure the session of this user is still alive and also want to update the session:

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1')
        .bind(session)
        .expect({ meta: { changes: 1 } }),
    db.prepare('SELECT * FROM ...'),
    ...
])

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1')
        .bind(session)
        .expect({ meta: { changes: 1 } }),
    db.prepare('SELECT * FROM ...'),
    ...
])

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1')
        .bind(session)
        .expect({ meta: { changes: 1 } }),
    db.prepare('SELECT * FROM ...'),
    ...
])

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1')
        .bind(session)
        .expect({ meta: { changes: 1 } }),
    db.prepare('SELECT * FROM ...'),
    ...
])

G

GuillaumeLakano•10/26/23, 8:50 AM

2) An extension of this idea, still to prevent roundtrips, is to catch a value from a previous result and to uses it in another request of the batch. Imagine the client/browser never known his own id_user, only the session ID, but the id_user is stored in a field of the sesssion table, then you could optimise your roundtrips like this:

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1 RETURNING *')
        .bind(session)
        .expect({ meta: { changes: 1 } })
        .set({ user: 'id_user' }), // Or user: [ 'id_user', 0 ] to indicate a position in the results array
        
    db.prepare('SELECT * FROM users WHERE id_user=?1').bind( db.get('user') )
    ...
])

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1 RETURNING *')
        .bind(session)
        .expect({ meta: { changes: 1 } })
        .set({ user: 'id_user' }), // Or user: [ 'id_user', 0 ] to indicate a position in the results array
        
    db.prepare('SELECT * FROM users WHERE id_user=?1').bind( db.get('user') )
    ...
])

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1 RETURNING *')
        .bind(session)
        .expect({ meta: { changes: 1 } })
        .set({ user: 'id_user' }), // Or user: [ 'id_user', 0 ] to indicate a position in the results array
        
    db.prepare('SELECT * FROM users WHERE id_user=?1').bind( db.get('user') )
    ...
])

await db.batch([
    db.prepare('UPDATE sessions SET updated=CURRENT_TIMESTAMP WHERE id_session=?1 RETURNING *')
        .bind(session)
        .expect({ meta: { changes: 1 } })
        .set({ user: 'id_user' }), // Or user: [ 'id_user', 0 ] to indicate a position in the results array
        
    db.prepare('SELECT * FROM users WHERE id_user=?1').bind( db.get('user') )
    ...
])

G

GuillaumeLakano•10/26/23, 8:50 AM

3) It's not really a feature, but activate the SQLite configuration flag SQLITE_ENABLE_UPDATE_DELETE_LIMIT to permit to uses LIMIT on INSERT/UPDATE/DELETE, could also permit us to optimise some requests and to add a layer of security.

R

Ravi Kumar Choudhary•10/26/23, 12:36 PM

I am trying to take backup of my D1 database but getting following issue Please guide me if anyone have any solution for this issue?

The request is malformed: Experimental DBs do not support backups yet:
9babc6b2-bdbf-4593-9379-04b468b81d5e (v3) [code: 7400]

RRavi Kumar Choudhary I am trying to take backup of my D1 database but getting following issue Please ...

C

Cyb3r-Jak3•10/26/23, 1:13 PM

Backups aren’t supported in the beta system.

Original message was deleted

M

Matt Silverlock•10/26/23, 1:17 PM

This isn’t true.

RRavi Kumar Choudhary I am trying to take backup of my D1 database but getting following issue Please ...

M

Matt Silverlock•10/26/23, 1:17 PM

https://developers.cloudflare.com/d1/learning/time-travel/

Time Travel and backups · Cloudflare D1 docs

Time Travel is D1’s approach to backups and point-in-time-recovery, and allows you to restore a database to any minute within the last 30 days.

S

Steved•10/26/23, 3:27 PM

D1 feature request: Some option for Unicode collation (specifically non-ASCII case insensitivity)

Could we have the ICU extension enabled?, see https://sqlite.org/src/dir/ext/icu
Sadly, this could affect existing indexes (as discussed at the bottom of the link).
So, another potential option is to implement a custom collation sequence (via create_collation - https://www.sqlite.org/c3ref/create_collation.html ). For example, add a new 'UNICODE_NOCASE' collation that could then be used on columns or specific where clauses with

... COLLATE UNICODE_NOCASE

... COLLATE UNICODE_NOCASE

... COLLATE UNICODE_NOCASE

... COLLATE UNICODE_NOCASE.

I'm currently adding duplicate columns with the data passed through Javascript's toLower(), but that's a PITA.
I understand it is ultimately an Sqlite quirk. However, Sqlite provides the above two options, but consumers of D1 have no control of those extensions/APIs which is why some assistance from D1 would be helpful.

Stanislav Trofimchuk wrote a really good background piece, with lots of discussion and example showing use of custom collation (from Python): https://shallowdepth.online/posts/2022/01/5-ways-to-implement-case-insensitive-search-in-sqlite-with-full-unicode-support/ and a great follow up post specifically on performance: https://shallowdepth.online/posts/2022/01/performance-of-application-defined-functions-and-collations-in-sqlite/

SSteved D1 feature request: Some option for Unicode collation (specifically non-ASCII ca...

D

DaniFoldi•10/26/23, 3:42 PM

I second this, internationalized case insensitivity would be useful, instead of keeping a second column where all characters are ascii-mapped in advance so case doesn't matter

S

switz•10/26/23, 7:55 PM

is there a max file size for import/execute?

S

switz•10/26/23, 7:55 PM

getting

  Cannot create a string longer than 0x1fffffe8 characters

  Cannot create a string longer than 0x1fffffe8 characters

  Cannot create a string longer than 0x1fffffe8 characters

  Cannot create a string longer than 0x1fffffe8 characters

on a 1gb database

U

Unsmart•10/26/23, 8:01 PM

The limits are documented here: https://developers.cloudflare.com/d1/platform/limits/

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public beta. Join the #d1-beta channel in the Cloudflare Developer Discord or subscribe to D1’s public …

S

switz•10/26/23, 8:22 PM

ah so I'm hitting

> Maximum SQL statement length

> Maximum SQL statement length

> Maximum SQL statement length

> Maximum SQL statement length?

S

switz•10/26/23, 8:23 PM

anyone have a nice cli command to split the requests into 100kb chunks?

Sswitz ah so I'm hitting `> Maximum SQL statement length`?

O

omar4289•10/26/23, 9:49 PM

Just wondering what request do you make that goes beyond 100kb

S

switz•10/26/23, 9:49 PM

I'm trying to import a sqlite db that has 1gb of data into d1

S

switz•10/26/23, 9:50 PM

each sql statement is just a few bytes, but the whole file is 1gb

D

Dahoom•10/26/23, 9:50 PM

ask gippity to write a python script that batches the sql file into ~99kb chunks

S

switz•10/26/23, 9:52 PM

oh just realized free dbs are limited to 500mb, so may have to come up with another solution unless I want to pay

S

switz•10/26/23, 9:52 PM

this is not mission critical, will have to re-think

Sswitz oh just realized free dbs are limited to 500mb, so may have to come up with anot...

O

omar4289•10/26/23, 10:12 PM

Yeah, also if you have indexes, that might take some space too, and now we are only limited to 2gb per db, so not even sure you could migrate it all

C

ChrisBrocklesby•10/27/23, 1:49 AM

Its really important to be able to download a D1 database, with this new TimeTravel feature the ability has seemed to disappear... This seems like vendor lock-in... Any tricks or workarounds to download a large D1 database ?

DDahoom nora's project should be linked in the docs

D

Dahoom•10/27/23, 1:50 AM

^

Similar Threads

Similar Threads

Similar Threads