Hey! If I create a db with d1, can I access the database from pgAdmin4 on my laptop to read and writ

Aalvaro-silva Hey! If I create a db with d1, can I access the database from pgAdmin4 on my lap...

I

Isaac McFadyen•12/29/23, 6:17 PM

Unfortunately not unless they specifically say they support D1 which it doesn't look like they do.

I

Isaac McFadyen•12/29/23, 6:17 PM

D1 has it's own API because SQLite doesn't have a standardized version, so they'd need to explicitely support D1.

A

alvaro-silvaOP•12/29/23, 6:17 PM

Thanks for the answer

G

Gagan Suie•12/29/23, 7:52 PM

has anyone used this?

https://marketplace.visualstudio.com/items?itemName=yawarjamal.cf-d1&ssr=false#overview

Cloudflare D1 - Visual Studio Marketplace

Extension for Visual Studio Code - Explore and query Cloudflare D1 databases

GGagan Suie has anyone used this? https://marketplace.visualstudio.com/items?itemName=yawar...

J

JustinNoel•12/29/23, 10:44 PM

No, but I'd recommend you use D1 Console by our very own Isaac McFadyen

https://github.com/isaac-mcfadyen/d1-console

GitHub

GitHub - isaac-mcfadyen/d1-console: A full query console for Cloudf...

A full query console for Cloudflare's D1 database product. - GitHub - isaac-mcfadyen/d1-console: A full query console for Cloudflare's D1 database product.

S

sten•12/30/23, 1:56 AM

Can you only drop one table at a time against D1?

npx wrangler --env dev d1 execute workers-db --command="DROP TABLE account"

npx wrangler --env dev d1 execute workers-db --command="DROP TABLE account"

npx wrangler --env dev d1 execute workers-db --command="DROP TABLE account"

npx wrangler --env dev d1 execute workers-db --command="DROP TABLE account"
I have tried chaining like

account, user, comment

account, user, comment

account, user, comment

account, user, comment but it responds with

near "comment": syntax error at offset 19 [code: 7500]

near "comment": syntax error at offset 19 [code: 7500]

near "comment": syntax error at offset 19 [code: 7500]

near "comment": syntax error at offset 19 [code: 7500]

A

AsyncBanana•12/30/23, 2:15 AM

Is there any way to bind d1 databases to a script dynamically without workers for platforms?

A

AsyncBanana•12/30/23, 2:16 AM

Also, is an eventually consistent mode planned for d1?

G

Gagan Suie•12/30/23, 7:20 AM

Howdy, any way to automatically update the updatedAt timestamp when the row gets updated? Copilot says to use triggers but I'm not sure if D1 supports triggers.

GGagan Suie Howdy, any way to automatically update the updatedAt timestamp when the row gets...

M

Matt Silverlock•12/30/23, 12:08 PM

D1 supports triggers.

C

Cyb3r-Jak3•12/30/23, 4:28 PM

For D1 with pages, you can do pretty much all the same dev locally as workers but use remote dev because pages doesn't support that. I use the standard wrangler file in my pages project for interacting with bindings. Just have to remember to use the dashboard to apply bindings

Oom Aren't the changes and schema I do in local d1 should be published to the produc...

C

Cyb3r-Jak3•12/30/23, 4:47 PM

No, they are two separate databases. You have two run migrations in both

C

Cyb3r-Jak3•12/30/23, 4:49 PM

Once with the

--local

--local

--local

--local flag and once without

S

staticmedia•12/31/23, 12:57 AM

Is the only current way to access a d1 database outside of workers to use a proxy like this - https://github.com/elithrar/http-api-d1-example ?

GitHub

GitHub - elithrar/http-api-d1-example: An example HTTP API for Clou...

An example HTTP API for Cloudflare's D1 database: https://developers.cloudflare.com/d1/

GitHub - elithrar/http-api-d1-example: An example HTTP API for Cloudflare's D1 database: ht...

Sstaticmedia Is the only current way to access a d1 database outside of workers to use a prox...

C

Cyb3r-Jak3•12/31/23, 1:13 AM

Yes. There is the REST API but it is rate limited so better making your own with the worker

MMatt Silverlock D1 supports triggers.

G

Gagan Suie•12/31/23, 8:02 AM

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

  incomplete input [code: 7500]

incomplete input [code: 7500]

  incomplete input [code: 7500]

incomplete input [code: 7500]

CREATE TRIGGER update_channels_updatedAt
AFTER UPDATE ON channels
FOR EACH ROW
BEGIN
    UPDATE channels SET updatedAt = CURRENT_TIMESTAMP WHERE _id = NEW._id;
END;

CREATE TRIGGER update_channels_updatedAt
AFTER UPDATE ON channels
FOR EACH ROW
BEGIN
    UPDATE channels SET updatedAt = CURRENT_TIMESTAMP WHERE _id = NEW._id;
END;

CREATE TRIGGER update_channels_updatedAt
AFTER UPDATE ON channels
FOR EACH ROW
BEGIN
    UPDATE channels SET updatedAt = CURRENT_TIMESTAMP WHERE _id = NEW._id;
END;

CREATE TRIGGER update_channels_updatedAt
AFTER UPDATE ON channels
FOR EACH ROW
BEGIN
    UPDATE channels SET updatedAt = CURRENT_TIMESTAMP WHERE _id = NEW._id;
END;

Sstaticmedia Is the only current way to access a d1 database outside of workers to use a prox...

S

scotto•12/31/23, 2:46 PM

https://github.com/JacobLinCool/d1-manager

GitHub

GitHub - JacobLinCool/d1-manager: D1 Manager is a web UI and API fo...

D1 Manager is a web UI and API for Cloudflare D1, a serverless SQL database. It provides a user-friendly interface for managing databases, tables, and records, as well as an API for performing oper...

Aalvaro-silva Hey! If I create a db with d1, can I access the database from pgAdmin4 on my lap...

R

Rubi•12/31/23, 5:59 PM

I have try this, and no, pgadmin doesnt support D1 and vice versa

R

Rubi•12/31/23, 6:03 PM

anyway, I wonder why https://northwind.d1sql.com/dash taking so long on F12 network tab, all request are up to 350-550ms while my SvelteKit MongoDB only takes 150-200ms for almost similar response

R

Rubi•12/31/23, 6:16 PM

I hope I could tell my user that way

X

xeon06•12/31/23, 6:22 PM

Does anyone know of any project that is attempting local SQLite replication that would work with D1? For instance, https://github.com/vlcn-io/cr-sqlite and https://electric-sql.com/ are both really cool but not inherently compatible with D1

G

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

How do you get the primary key of the row that got inserted?

G

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

Will first() work?

S

Steved•12/31/23, 10:10 PM

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

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.

Hey! If I create a db with d1, can I access the database from pgAdmin4 on my laptop to read and writ

Similar Threads

Similar Threads

Similar Threads