D1: Our SQL database • Hacker News Board

Re: D1: Our SQL database

#81

Post by anyfactor » Wed, May 11, 2022, 2:56 PM UTC

chrisshroba wrote: post #65

vaughan wrote: post #48

Earlier quoted context omitted.

Has anyone tried to write a new modern SQLite?

DuckDB comes to mind, but I can't speak to its differences from SQLite. https://duckdb.org/

I haven't tried duckdb but I have been googling about it. I think I saw a discussion where it was mentioned that duckdb isn't a replacement for SQLite. It is an OLAP database [0] which makes its ingestion time slower than SQLite, I think. So it is meant for analytics but not as fullfledge replacement for SQLite.

[0]: https://en.wikipedia.org/wiki/Online_analytical_processing

Duckdb on HN: https://news.ycombinator.com/item?id=23287278

Re: D1: Our SQL database

#82

Post by prirun » Wed, May 11, 2022, 2:56 PM UTC

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…

* 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 using the ALTER statement, just like other databases:

https://www.sqlite.org/lang_altertable.html

For the most recent upgrade, I upgraded to strict tables, which I could also have done without a rebuild/swap. I chose to do a rebuild/swap this one time because I wanted to reorder some columns. Why? Because columns stored with default or null values don't have row space allocated if the column is at the end of the row.

Re: D1: Our SQL database

#83

Post by sophacles » Wed, May 11, 2022, 2:57 PM UTC

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? Yes sqlite doesn't have all the features postgres has. Postgres doesn't have all the features the sqlite has either. What's wrong with having different tools with different sets tradeoffs. It's a different shape of Lego and that's fine - some things call for a 1/3height 2x2 and others call for a full height 1x8.

Re: D1: Our SQL database

#84

Post by jgrahamc » Wed, May 11, 2022, 2:57 PM UTC

lucasyvas wrote: post #78

To the person from Cloudflare I complained to in last year's thread about putting your money where your mouth is on serverless databases: You weren't lying, and this is super cool - the SQLite hype train also seems to be in full force.

:-)

Re: D1: Our SQL database

#85

Post by throwaway894345 » Wed, May 11, 2022, 2:57 PM UTC

lucasyvas wrote: post #78

To the person from Cloudflare I complained to in last year's thread about putting your money where your mouth is on serverless databases: You weren't lying, and this is super cool - the SQLite hype train also seems to be in full force.

It's interesting to see a relatively old technology get hyped.

Re: D1: Our SQL database

#86

Post by dinkleberg » Wed, May 11, 2022, 2:57 PM UTC

This is convenient, I’ve been building an app which is using SQLite but am wanting to deploy it to Cloudflare pages. I expected I was going to have to switch to a hosted Postgres instance somewhere, but this could be perfect.

Re: D1: Our SQL database

#87

Post by jpcapdevila » Wed, May 11, 2022, 2:59 PM UTC

vlovich123 wrote: post #42

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…

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

Are you guys using litestream or a similar approach? E.g storing WAL frames in a durable object.

Re: D1: Our SQL database

#88

Post by greg-m » Wed, May 11, 2022, 2:59 PM UTC

vlovich123 wrote: post #42

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…

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 :)

Re: D1: Our SQL database

#89

Post by jpcapdevila » Wed, May 11, 2022, 3:00 PM UTC

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?

I think the most successful attempt would be Realm.

https://realm.io/

Re: D1: Our SQL database

#90

Post by ignoramous » Wed, May 11, 2022, 3:00 PM UTC

endisneigh wrote: post #21

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.