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

^

CChrisBrocklesby Its really important to be able to download a D1 database, with this new TimeTra...

C

Cyb3r-Jak3•10/27/23, 1:50 AM

You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup your database and then download that

D

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

and he beat me to it

CCyb3r-Jak3 You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup yo...

C

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

Hi, Does that work with databases on the new storage subsystem in D1 ?

D

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

queries the DB with select and writes it to an sql file to put it simply

C

ChrisBrocklesby•10/27/23, 2:01 AM

@Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully this becomes native option in the Cloudflare Dashboard (or Wrangler CLI) in the future.

CChrisBrocklesby @Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully ...

C

Cyb3r-Jak3•10/27/23, 2:03 AM

I believe it is planned to be added

C

captain•10/27/23, 4:52 AM

odd thing occuring...that seems new and I can't explain it

C

captain•10/27/23, 4:53 AM

i'm inserting data but on the primary key, if i insert a number like: 9440344, the database saves it as 9440344.0

C

captain•10/27/23, 4:54 AM

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

D

Dahoom•10/27/23, 4:54 AM

probably an sqlite thing, might be saving int as double for some reason?

D

Dahoom•10/27/23, 4:55 AM

check the PRAGMA config

DDahoom probably an sqlite thing, might be saving int as double for some reason?

C

captain•10/27/23, 4:55 AM

whats weird is that i didn't notice

C

captain•10/27/23, 4:55 AM

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

D

Dahoom•10/27/23, 4:57 AM

which column do you get the number from?

D

Dahoom•10/27/23, 4:59 AM

since a lot of it is being saved as text it might be the way js is parsing the values before being inserted into the db

C

captain•10/27/23, 4:59 AM

i'm checking the number in console before it gets entered and not seeing the .0

C

captain•10/27/23, 5:00 AM

and i'm also doing parseInt(key)

DDahoom which column do you get the number from?

C

captain•10/27/23, 5:03 AM

any other ideas?

C

captain•10/27/23, 5:03 AM

i'm dumbfounded on this

C

captain•10/27/23, 5:04 AM

wondering if something changed on the cloudflare side

D

Dahoom•10/27/23, 5:04 AM

order_id is the primary key using text

C

captain•10/27/23, 5:04 AM

yes

C

captain•10/27/23, 5:07 AM

https://stackoverflow.com/questions/23157411/using-text-as-a-primary-key-in-sqlite-table-bad discussion says there shouldn't be any performance issues

Stack Overflow

Using text as a primary key in SQLite table bad?

Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true? And will the rowid be used as the actual primary key in such a case?

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

Similar Threads