Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

D1: Our SQL database

blog.cloudflare.com

Post reply on HN
Print view View original

101–110 of 241 posts

Re: D1: Our SQL database

#101

Post by ranguna »

Cthulhu_ wrote: post #24
ranguna wrote: post #17

This looks amazing! I see cloudflare people are on this post, any chance to compar D1 vs postgres in terms of DB features? Insert ... Returning Stored procedures and triggers Etc etc Would be really helpful to get a comparison like cockroachDB did here https://www.cockroachlabs.com/docs/stable/postgresql-compati... Or even better, a general sql compatibility matrix like this https://www.cockroachlabs.com/docs/stable/…

The announcement - if you read it before posting - says it's sqlite, so that's something you can punch into google. Long story short, don't expect anything fancy. Support for alter table is limited, and concurrency can be an issue.

It is indeed sqlite but it could possibly have modification done or additions added. Please be considerate and think a little more before commenting.

Re: D1: Our SQL database

#102

Post by bmon »

endisneigh wrote: post #70
Quarrelsome wrote: post #67

Earlier quoted context omitted.

they should be using SQLite more often than they are.

Why? What use cases are better with SQLite vs Postgres, MySQL, etc?

If you consider cost... I would imagine a fair few. From the article:

> We will ensure that D1 costs less and performs better than comparable centralized solutions.

Re: D1: Our SQL database

#103

Post by ranguna »

the_duke wrote: post #22
ranguna wrote: post #17

This looks amazing! I see cloudflare people are on this post, any chance to compar D1 vs postgres in terms of DB features? Insert ... Returning Stored procedures and triggers Etc etc Would be really helpful to get a comparison like cockroachDB did here https://www.cockroachlabs.com/docs/stable/postgresql-compati... Or even better, a general sql compatibility matrix like this https://www.cockroachlabs.com/docs/stable/…

Well, it's sqlite... so presumably you will get most of the capabilities sqlite has. RETURNING is covered. Stored procedures are indirectly there by running your own code "next to the database", as mentioned in the post. Which is arguably much nicer than having to use some database specific language, given that you can run WASM on workers.

> Stored procedures are indirectly there by running your own code "next to the database",

"indirectly" is a keyword here, because running code when data is modified potentially won't replace triggers since they'll probably execute outside the running transaction.

Re: D1: Our SQL database

#104

Post by ranguna »

tyingq wrote: post #39
the_duke wrote: post #22

Earlier quoted context omitted.

Well, it's sqlite... so presumably you will get most of the capabilities sqlite has. RETURNING is covered. Stored procedures are indirectly there by running your own code "next to the database", as mentioned in the post. Which is arguably much nicer than having to use some database specific language, given that you can run WASM on workers.

There is a layer on top of Sqlite here, so I imagine it's something less than all the capabilities sqlite has, at least initially. Plus the upsides and downsides from their approach to have a master and read replicas.

Yes was thinking the same. Nice to see some people here actually understood the question, thank you.

Re: D1: Our SQL database

#105

Post by sophacles »

endisneigh wrote: post #70
Quarrelsome wrote: post #67

Earlier quoted context omitted.

they should be using SQLite more often than they are.

Why? What use cases are better with SQLite vs Postgres, MySQL, etc?

Some "pros" that many find appealing:

* Copying the database around its a file copy in sqlite. Each database is it's own single file. (there's also WAL stuff that you get control of)

* No extra service to deploy, manage, and/or optimize. I don't fully agree with the following, but I had a colleague who used to say "If you don't have multiple app servers writing to the db, postgres is a waste of effort".

* embedded means way lower data latency - if the dataset is in the fs cache even lower, no waiting on network transactions.

I've frequently chosen it over PG in cases where I needed basic relational data operations. In one case we ingested a large dataset (a few gbs of measurements) once an hour. Then we did some initial analytics on those measurements and threw the results in the same db file. After that step was done, the data was read only for several other systems and we just copied the db file to each of the systems that needed the data on-demand. A couple of the systems did additional analytics and effectively imported the db file to a different db (one was PG another was a graph db - neo4j). A couple of the systems just used the db file directly. It worked our really well.

Re: D1: Our SQL database

#106

Post by lucasyvas »

mritchie712 wrote: post #100
lucasyvas wrote: post #99

The API for this is currently the only thing I wish I could grok a bit better. It seems like it would be hard to make it work with existing libraries that can access SQLite, which is kind of a shame. I'm thinking of sqlx in Rust (or any other language binding / ORM for that matter), which has compile time schema safety. This is a nice capability, and because this interface seems non-standard (possibly for good reason…

There might be a `env.DB.url` (e.g. the jdbc URL) which you could pass into an existing library.

Interesting thought! Would love to see more details.

Re: D1: Our SQL database

#107

Post by sorenbs »

the_duke wrote: post #35

All this recent hype around sqlite... sqlite is a great embedded database and thanks to use by browsers and on mobile the most used database in the world by orders of magnitude. But it also comes with lots of limitations. * there is no type safety, unless you run with the new strict mode, which comes with some significant drawbacks (eg limited to the handful of primitive types) * very narrow set of column types and o…

Migrations have gotten better recently, but there are still cases where you need to follow the 12 steps very carefully: https://www.sqlite.org/lang_altertable.html#otheralter

Prisma Migrate can automatically generate these steps, removing most of the pain. I'm sure other migration tools can do this as well.

Re: D1: Our SQL database

#109

Post by steaminghams »

vaughan wrote: post #48
the_duke wrote: post #35

All this recent hype around sqlite... sqlite is a great embedded database and thanks to use by browsers and on mobile the most used database in the world by orders of magnitude. But it also comes with lots of limitations. * there is no type safety, unless you run with the new strict mode, which comes with some significant drawbacks (eg limited to the handful of primitive types) * very narrow set of column types and o…

Has anyone tried to write a new modern SQLite?

why do you consider sqlite to not be modern?

all the hip service providers seem to be all over it which would indicate pretty good modernity to me at least.

Re: D1: Our SQL database

#110

Post by adamckay »

endisneigh wrote: post #96
ignoramous wrote: post #90

Earlier quoted context omitted.

It isn't as much as folks who need Postgres features are moving to SQLite just because it is cool, but it is folks who don't want those Postgres features moving to SQLite, because the latter has just enough features they only ever really need .

SQLite made sense as an embedded database on day a desktop or phone because there’s only a single person generally writing to it. The perfect use case. I don’t understand how it will be usable at all in a website with multiple users. Is the idea to make your site to every user gets their own database? How do you stop SQL injection? Once you solve all of these problems aren’t you better off just using Postgres?

> I don’t understand how it will be usable at all in a website with multiple users

With WAL mode enabled the database is locked during writes only, and concurrent writes are queued but you can still perform reads concurrently. If you keep your write transactions small and consider that a lot of apps aren't writing a lot, it can give perfectly good performance for a lot of usecases.

> Is the idea to make your site to every user gets their own database?

You can do... I know of B2B apps that give each billable customer their own database.

> How do you stop SQL injection?

In the exact same way you do in all other flavours of SQL - with parameterized queries.

> Once you solve all of these problems aren’t you better off just using Postgres?

Not necessarily. Postgres gives you a different set of problems and limitations to consider and work around.

Post reply on HN