There's https://www.sqlite.org/lang_corefunc.html#last_insert_rowid and 'INSERT ... RETURNING ID' ht

SSteved There's https://www.sqlite.org/lang_corefunc.html#last_insert_rowid and 'INSERT ...

G

Gagan Suie•12/31/23, 10:44 PM

Wondering if last_insert_rowid will get wonky if multiple users are inserting concurrently.

G

Gagan Suie•12/31/23, 10:45 PM

Returning I'd might be the go-to then.

GGagan Suie Wondering if last_insert_rowid will get wonky if multiple users are inserting co...

G

Gagan Suie•12/31/23, 10:47 PM

Ah, nevermind, it's session specific.

G

Gagan Suie•12/31/23, 10:47 PM

Thanks! Gona try last_insert_rowid

<

<Alterion.Dev>•1/1/24, 4:50 PM

I'm a little curious why D1 doesn't have a

.get()

.get()

.get()

.get() method to retrieve a single row in the db. Not that it's the end of the world that I have to do .all()[0] but it seems... weird, that I can't just do

db.prepare(blah).bind(somevar).get()

db.prepare(blah).bind(somevar).get()

db.prepare(blah).bind(somevar).get()

db.prepare(blah).bind(somevar).get() ?

<

<Alterion.Dev>•1/1/24, 4:52 PM

like,

const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).get();

// vs
const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).all()[0];

const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).get();

// vs
const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).all()[0];

const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).get();

// vs
const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).all()[0];

const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).get();

// vs
const table = this.#db
  .prepare(
    "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?;",
  ).bind(this.#name).all()[0];

K

kian•1/1/24, 5:34 PM

.first()

.first()

.first()

.first()

K

kian•1/1/24, 5:34 PM

https://developers.cloudflare.com/d1/platform/client-api/#await-stmtfirstcolumn

R

Rabid•1/1/24, 8:06 PM

Any way to create a DB if it doesn't already exist via CLI wrangler? I am scared of hitting the 2GB limit, so I need to create around 140 dbs to shard my data

R

Rabid•1/1/24, 8:08 PM

Making a wow classic armory appliation via pages: https://atlasforge.gg/wow-classic/armory/us/crusader-strike/Rhyis and want to start storing basic data about characters in a D1 DB, so I can show level and item level leaderboards. I want to shard the data per wow server, and wow classic has 141 realms. Doing all that manually would be painful.

RRabid Any way to create a DB if it doesn't already exist via CLI wrangler? I am scare...

E

EdFletcher•1/1/24, 8:54 PM

Yup just write a script to call

wrangler d1 create

wrangler d1 create

wrangler d1 create

wrangler d1 create for each one https://developers.cloudflare.com/workers/wrangler/commands/#create

Commands - Wrangler · Cloudflare Workers docs

Create, develop, and deploy your Cloudflare Workers with Wrangler commands.

EEdFletcher Yup just write a script to call `wrangler d1 create` for each one https://develo...

R

Rabid•1/1/24, 8:55 PM

Ah, thanks. Any way to bind them to a pages function?

RRabid Ah, thanks. Any way to bind them to a pages function?

E

EdFletcher•1/1/24, 8:57 PM

Hmm that part I'm not sure of as I don't use Pages functions... there is a PATCH REST API to alter worker bindings, maybe similar exists for pages?

C

chientrm.com•1/1/24, 9:01 PM

Do we have

INSERT IGNORE

INSERT IGNORE

INSERT IGNORE

INSERT IGNORE?

RRabid Ah, thanks. Any way to bind them to a pages function?

C

Cyb3r-Jak3•1/1/24, 9:25 PM

You can use the REST API to both create the D1 databases and bind them to a pages project

Kkian `.first()`

<

<Alterion.Dev>•1/1/24, 10:28 PM

That's... interesting. am I just used to all the other libraries just using .get() ? Any specific technical reason this is different here, along with the .bind() being required?

<

<Alterion.Dev>•1/1/24, 10:32 PM

no I mean, you need to do db.prepare(query).bind(params).all() , for example. But other sqlite modules basically all do

db.prepare(query).all(params)

db.prepare(query).all(params)

db.prepare(query).all(params)

db.prepare(query).all(params).

<

<Alterion.Dev>•1/1/24, 10:33 PM

The fact that it's

.first()

.first()

.first()

.first() instead of

.get()

.get()

.get()

.get() is a separate "why would this be different" issue. Like, I don't want to sound rude, it just feels like there's no legitimate reason for these things to not use a more standardised sqlite API

K

kian•1/1/24, 10:37 PM

db.prepare(query).all()

db.prepare(query).all()

db.prepare(query).all()

db.prepare(query).all() is valid.

K

kian•1/1/24, 10:37 PM

Oh, I see what you mean.

<

<Alterion.Dev>•1/1/24, 11:13 PM

I will certainly not argue that returning the first column of a result is wrong - absolutely not!

<

<Alterion.Dev>•1/1/24, 11:13 PM

My question is why doesn't D1 follow the known convention of all the other npm sqlite wrappers that I know of, essentially

<

<Alterion.Dev>•1/1/24, 11:14 PM

having first() and get() would make this easier to move existing code to D1

<

<Alterion.Dev>•1/1/24, 11:15 PM

And removing .bind() intermediary in order to directly support binding in the execution call (run(), all(), get(), exec()) would also make it a lot easier

CCyb3r-Jak3 You can use the REST API to both create the D1 databases and bind them to a page...

B

bennycondon•1/2/24, 3:49 AM

Are there any tutorial or example docs on how we can set this up?

R

Rabid•1/2/24, 1:35 PM

Is it possible to get D1 query metrics via a wrangler or dashboard query?

R

Rabid•1/2/24, 1:35 PM

Running

npx wrangler d1 execute wow-classic-us-seasonal --command "SELECT * FROM characters ORDER BY pvp_rank DESC LIMIT 5"

npx wrangler d1 execute wow-classic-us-seasonal --command "SELECT * FROM characters ORDER BY pvp_rank DESC LIMIT 5"

npx wrangler d1 execute wow-classic-us-seasonal --command "SELECT * FROM characters ORDER BY pvp_rank DESC LIMIT 5"

npx wrangler d1 execute wow-classic-us-seasonal --command "SELECT * FROM characters ORDER BY pvp_rank DESC LIMIT 5"

And want to see rows_read metrics

K

kian•1/2/24, 2:00 PM

You can see it in the network response in devtools if you use the dashboard

Kkian You can see it in the network response in devtools if you use the dashboard

R

Rabid•1/2/24, 2:13 PM

Perfect, thank you!

M

Matt Silverlock•1/2/24, 2:46 PM

You can pass

--json

--json

--json

--json to wrangler.

M

Matt Silverlock•1/2/24, 2:48 PM

➜  wrangler d1 execute db-enam --command "PRAGMA table_list" --json | jq '.[].meta'
{
  "duration": 0.1513,
  "changes": 0,
  "last_row_id": 0,
  "changed_db": false,
  "size_after": 1832153088,
  "rows_read": 0,
  "rows_written": 0
}

➜  wrangler d1 execute db-enam --command "PRAGMA table_list" --json | jq '.[].meta'
{
  "duration": 0.1513,
  "changes": 0,
  "last_row_id": 0,
  "changed_db": false,
  "size_after": 1832153088,
  "rows_read": 0,
  "rows_written": 0
}

➜  wrangler d1 execute db-enam --command "PRAGMA table_list" --json | jq '.[].meta'
{
  "duration": 0.1513,
  "changes": 0,
  "last_row_id": 0,
  "changed_db": false,
  "size_after": 1832153088,
  "rows_read": 0,
  "rows_written": 0
}

➜  wrangler d1 execute db-enam --command "PRAGMA table_list" --json | jq '.[].meta'
{
  "duration": 0.1513,
  "changes": 0,
  "last_row_id": 0,
  "changed_db": false,
  "size_after": 1832153088,
  "rows_read": 0,
  "rows_written": 0
}

GGagan Suie how do you create triggers in D1? i cant get mine to work. it says ` incomplete...

G

Gagan Suie•1/2/24, 9:22 PM

anyone got any suggestions for this?

G

Gagan Suie•1/3/24, 5:10 AM

Just curious, the results wouldn't contain the data since run() is being used instead of all(), correct?

W

WcaleNieWolny•1/3/24, 5:29 AM

Hello, I tried to migrate a large amount of data from postgres into d1 and I got

please wait and consider throttling your request speed [code: 971]

please wait and consider throttling your request speed [code: 971]

please wait and consider throttling your request speed [code: 971]

please wait and consider throttling your request speed [code: 971]. What would be the best way to create a large amount (100k rows) in d1?

WWcaleNieWolny Hello, I tried to migrate a large amount of data from postgres into d1 and I got...

M

Matt Silverlock•1/3/24, 6:38 AM

How are you attempting to migrate? You should batch your inserts - there’s no reason you can’t insert 10k+ rows per statement, or more, depending on overall width.

W

WcaleNieWolny•1/3/24, 7:46 AM

What do you mean by batch my statements?

W

WcaleNieWolny•1/3/24, 7:47 AM

I use the http api and I do 1 call per row

W

WcaleNieWolny•1/3/24, 7:50 AM

How should I batch this?

WWcaleNieWolny How should I batch this?

M

Matt Silverlock•1/3/24, 8:35 AM

Pass multiple statements in one API call, separated by a semicolon. You’re being rate limited by the HTTP API, not D1 itself.

MMatt Silverlock Pass multiple statements in one API call, separated by a semicolon. You’re being...

W

WcaleNieWolny•1/3/24, 8:45 AM

Oh, I did not know that. Thanks ^^

Original message was deleted

M

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

it's a bug, if you want data, use .all()

MMatt Silverlock Pass multiple statements in one API call, separated by a semicolon. You’re being...

W

WcaleNieWolny•1/3/24, 1:57 PM

Error: A prepared SQL statement must contain only one statement.

Error: A prepared SQL statement must contain only one statement.

Error: A prepared SQL statement must contain only one statement.

Error: A prepared SQL statement must contain only one statement.

W

WcaleNieWolny•1/3/24, 2:14 PM

https://gist.github.com/WcaleNieWolny/3510515be7145411d3b3cc93ccd0c433https://gist.github.com/WcaleNieWolny/3510515be7145411d3b3cc93ccd0c433

W

WcaleNieWolny•1/3/24, 2:14 PM

Essentially I read data from postgrs -> convert it into an insert statement and then I try to execute this in d1

W

WcaleNieWolny•1/3/24, 2:15 PM

the problem is that I get rate limited - it was suggested I "Pass multiple statements in one API call, separated by a semicolon" - that did not work as I use a prepared statement

WWcaleNieWolny https://gist.github.com/WcaleNieWolny/3510515be7145411d3b3cc93ccd0c433https://gi...

I

Isaac McFadyen•1/3/24, 2:15 PM

We're not going to be able to debug with that unfortunately, that won't show us the actual SQL being executed.

W

WcaleNieWolny•1/3/24, 2:16 PM

W

WcaleNieWolny•1/3/24, 2:16 PM

here is the sql being executed

I

Isaac McFadyen•1/3/24, 2:16 PM

So the first thing is that D1 doesn't support BEGIN TRANSACTION

There's https://www.sqlite.org/lang_corefunc.html#last_insert_rowid and 'INSERT ... RETURNING ID' ht

Similar Threads

Similar Threads

Similar Threads