Live data from Hacker News

D1: Our SQL database

blog.cloudflare.com

91–100 of 241 posts

Re: D1: Our SQL database

#91
post #73

wow SQLite getting a lot of love these days https://tailscale.com/blog/database-for-2022 https://fly.io/blog/all-in-on-sqlite-litestream https://blog.cloudflare.com/introducing-d1

With the mileage (and attention) those new products are getting out of using SQLite, I think Richard Hipp deserves a lot more acknowledgement for creating such an amazing piece of software.

New products getting a lot of mileage out of sqlite is old-hat at this point. It one of those rare evergreen techs - pay attention for a while and this latest round of attention will die down for 6-12 months then someone else will start another round of "look how cool sqlite is".

At least that's been my observation since I started coming around here.

Re: D1: Our SQL database

#92

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…

What types are missing from strict that you need?

[deleted]

Re: D1: Our SQL database

#94

Any current or planned support for existing ORMs, such as Prisma or TypeOrm? Also, I wonder how hard it will be to migrate existing PostgreSQL databases and SQL statements. Of course, I understand if Cloudflare is focused on greenfield applications.

We are definitely interested in ORMs. Want to make it easy to use. I hope someone creates the next Rails using Workers. And having other models on top of our SQL offerings will be important. Get in contact and let us know what you'd like.

> I hope someone creates the next Rails using Workers

I too am eagerly waiting for a good serverless nodejs framework that is "batteries included". I've deployed on Lambda using the "Serverless Framework" but once your app grows to a certain size everything starts to fall apart and you lose some of the magic. Unfortunately, most of the things that advertise themselves as serverless/lambda/worker nodejs frameworks are monoliths and/or an existing monolith framework that "supports" lambda (with a billion asterisks after that). There is absolutely nothing wrong with monolith frameworks, I love them, but just not for lambda, I want to deploy a single endpoint as a single function (or as a cron, or queue listener, etc), not all of my code for every function (you hit size limits quick with this method).

I want express/nestjs/etc-type routes that I define with code or annotations that result in /only/ that function (endpoint) being bundled up and deployed. I ended up rolling my own "framework" on top of Serverless Framework (uses serverless.ts config file that scans my directories for a special file that defines the routes defined in that directory) but Serverless Framework is pretty shaky ground. Their documentation is a mess, Serverless Components appears dead, and they seem to be busy with their own "cloud" so I don't know how much longer I can keep building on top of them.

When it works it's like magic but there are a ton of walls you run headfirst into: Cloud formation entity limits, package size limits, typescript/bundling support, clear disregard for medium/large projects ("Just use multiple services", this leads to a terrible dev experience), and long deploy times.

I wish CF Workers had been out when I first started building my current project, I might have gone in that direction instead, I still might.

Re: D1: Our SQL database

#95
Unless I missed it by skimming, where are the deets? Is this strongly or eventually consistent? What are max table sizes, and do they become partitioned? Are there cross partition joins?

Re: D1: Our SQL database

#96

Have any of the problems that led people to use Postgres instead of SQLite actually been solved? Are we doomed to repeat the same mistakes? Also, any plans to support PATCH x-update-range so SQLite can be used entirely in the browser via SQLite.js? Can someone enlighten me with the types of use cases this would be better for vs say Postgres?

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?

Re: D1: Our SQL database

#97
post #82

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…

* the big one for me: very limited migration support, requiring quite a lot of ceremony for common tasks (eg rewriting a whole table and swapping it out) I don't know where this idea of having to swap a whole table in SQLite came from, but it simply isn't true. Over the last 13 years I have upgraded production HashBackup databases at customer sites a total of 35 times without rewriting and swapping out tables by usin…

For a long time sqlite did not have DROP COLUMN and RENAME COLUMN support, which are both pretty essential.

I'm embarrassed to admit that I didn't realize RENAME COLUMN was actually added in 3.25, almost four years ago.

DROP COLUMN was only just added last year in 3.35.

I'm surprised a database schema lasted 9/12 years without ever renaming or dropping a column.

This changes things! But even now, ALTER TABLE is not transactional. So especially with many concurrent readers there can definitely be situations where you'd still want to rewrite.

Re: D1: Our SQL database

#98
post #88

Earlier quoted context omitted.

D1 does not throw away consistency. It’s built on top of Durable Objects which is globally strongly consistent.

Just clarifying - D1 without read replicas is strongly consistent. If you add read replicas, those can have replication lag and will not be strongly consistent. Disclaimer: I work at Cloudflare :)

Thanks for the clarification, that is what I would expect.

Does SQLite support some kind of monotonic transaction id that can be used as a cache coherency key? Say a client writes a new record to the database which returns `{"result": "ok", "transaction_id": 123}`, then to ensure that subsequent read requests are coherent they provide a header that checks that the read replica has transaction_id >= 123 and either waits for replication before serving or fails the request. (Perhaps a good use for the embedded worker?)

Re: D1: Our SQL database

#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), I guess we are being asked to give some of those things up.

I am getting a bit ahead of myself on the Rust part (presumably that will eventually be supported as part of workers-rs), but I think the feelings still stand if you consider the JS ecosystem.

Edit: I may actually be wrong, but presumably the entire surface isn't covered because there's no file opening, etc.

Re: D1: Our SQL database

#100

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.
Post reply on HN