Cloudflare Developers•2y ago

I'm surprised that Miniflare doesn't show it.

kianOP•12/29/23, 1:55 AM

That should probably be fixed/improved so you don't need a 2nd DB, or to tinker with a production DB.

Kkian I'm surprised that Miniflare doesn't show it.

Chris•12/29/23, 1:56 AM

This is what I see in the

meta

meta

from a query locally:

{
  "served_by": "miniflare.db",
  "duration": 1,
  "changes": 0,
  "last_row_id": 0,
  "changed_db": false,
  "size_after": 28672
}

{
  "served_by": "miniflare.db",
  "duration": 1,
  "changes": 0,
  "last_row_id": 0,
  "changed_db": false,
  "size_after": 28672
}

kianOP•12/29/23, 2:03 AM

Yeah, I can see they're not included. I suspect it needs updating to use the counters that were made available by https://github.com/cloudflare/workerd/pull/979, which I assume D1 itself uses.

kianOP•12/29/23, 2:05 AM

I guess there's two issues here:

kianOP•12/29/23, 2:06 AM

The documentation needs updating to talk about, probably just under Definitions, that temporary tables incur reads/writes. It might be worth a page/section dedicated to optimising for cost, which is covered here and there by the 'use indexes' sections but not as far as temporary tables.

Miniflare needs updating to match the

meta

meta

object that D1 itself returns.

jacky•12/29/23, 3:24 AM

Question: After the beta phase, will the database size limit be removed, or is there an option to lift the size restriction by making a payment?

Unsmart•12/29/23, 3:25 AM

There will still be size limits per database and you will need to use sharping to grow it larger. There’s also an account storage limit that you’d just request an increase on an as needed basis

sSr•12/29/23, 4:09 AM

can you guys compare sqllight with duckdb too https://duckdb.org/ duckdb might be a better option for D1!

DuckDB

An in-process SQL OLAP database management system

DuckDB is an in-process SQL OLAP database management system. Simple, feature-rich, fast & open source.

CChris This is what I see in the `meta` from a query locally: ```{ "served_by": "mini...

kianOP•12/29/23, 9:20 AM

It's only two lines to add in the

rows_written

rows_written

and

rows_read

rows_read

into Miniflare so I've opened an issue for it.

SsSr can you guys compare sqllight with duckdb too https://duckdb.org/ duckdb might ...

Isaac McFadyen•12/29/23, 12:37 PM

D1 has already had a lot of development work put into it, including integrating it into the Cloudflare runtime, so I highly doubt they'd switch at this point

Isaac McFadyen•12/29/23, 12:37 PM

Also an OLAP database has very different goals than a transactional database

CChris 🦆 I think I'm answering my own question here, but thought I'd share my rubber-d...

Henry•12/29/23, 12:44 PM

Really useful message. I knew my D1 database was missing an index on a few new queries I "forgot", or rather was too lazy, to add yet. I added the index without any changes to the worker that runs the queries and you can definitely see where it took effect and how big of an effect it had. It made me think of how expensive this would be and how a simple index drops costs. It would have cost me over $240,000 as in really over 240 thousand dollars a month just missing an index on a new SELECT query I added to my worker if I left it to run like that. After the index? Under 200$ a month outside free/included usage. That really is a simple way to bankrupt yourself if you aren't paying attention

Aflina•12/29/23, 3:14 PM

Hi! I need to have a counter that will increment by client requests and it can happen that multiple clients try to increment the same attribute at the same time. Can I use D1 or should I stick with DO? Thanks!

AAflina Hi! I need to have a counter that will increment by client requests and it can h...

Isaac McFadyen•12/29/23, 3:45 PM

D1 is strongly consistent so yes, it will work.

Isaac McFadyen•12/29/23, 3:46 PM

Just make sure to increment in the same SQL query as the fetch (like

UPDATE foo SET counter = counter + 1

UPDATE foo SET counter = counter + 1

) rather than fetching and then adding 1 in your code.

Isaac McFadyen•12/29/23, 3:46 PM

In the future there will be read replica support, but it should still theoretically work since all writes will be propagated back to the master which has the latest writes.

PPepper I was just using javascript and the api to test it out. This isn't the actual ta...

JustinNoel•12/29/23, 4:08 PM

Definitely use bindings as suggested. Manually inserting values like this can lead to SQL Injection Attacks.

alvaro-silva•12/29/23, 6:07 PM

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

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

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.

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.

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

Thanks for the answer

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...

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.

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"

I have tried chaining like

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]

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

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

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

Also, is an eventually consistent mode planned for d1?

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...

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

D1 supports triggers.

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...

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

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

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

Once with the

--local

--local

flag and once without

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...

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.

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]

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...

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...

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

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

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

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

I hope I could tell my user that way

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

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

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

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

Will first() work?

I'm surprised that Miniflare doesn't show it.

Similar Threads