I went from using the various big player postgres clusters to SQLite, we have an MAU in 7 figures, all backed by SQLite durable objects. We have to think differently about the access patterns but the benefits have been worth it.
SQLite is all you need for durable workflows
131–140 of 413 posts
Re: SQLite is all you need for durable workflows
#132Earlier quoted context omitted.
Woof. That sounds very complicated. If you need that kind of write concurrency, use an unlogged table in postgres [0]. Then you don't have to invent a whole sharded thing yourself. [0] https://www.postgresql.org/docs/current/sql-createtable.html...
There are so many unfortunate footguns with unlogged tables, that I'd argue that the goroutine route is preferable.
Such as?
Re: SQLite is all you need for durable workflows
#133I started using SQLite for a home project after years of reading about it, I was shocked at the poor type system coming from Postgres. It is really inferior, not sure why it gets so much praise. https://sqlite.org/datatype3.html https://www.postgresql.org/docs/current/datatype.html Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so man…
You can use strict tables: https://sqlite.org/stricttables.html
This could enforce dates are strings. They wanted to enforce dates are dates I thought.
Re: SQLite is all you need for durable workflows
#134I started using SQLite for a home project after years of reading about it, I was shocked at the poor type system coming from Postgres. It is really inferior, not sure why it gets so much praise. https://sqlite.org/datatype3.html https://www.postgresql.org/docs/current/datatype.html Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so man…
Yes, this is basically my only issue with SQLite. SQLite with a strict type system would be great.
[deleted]
Re: SQLite is all you need for durable workflows
#135The biggest annoyance about SQLite for me is no ability to:
ALTER TABLE users MODIFY COLUMN…
ALTER TABLE users ALTER COLUMN…
ALTER TABLE users ADD CONSTRAINT…
Re: SQLite is all you need for durable workflows
#136Earlier quoted context omitted.
Well if you run a tiny single-threaded app then SQLite is a nice simplification over spinning up a separate machine for Postgres.
I use postgres for very simple apps. I have a Dockerfile I use in my boilerplate repo. It takes a single make cmd for me to build, start and run migrations. Its as simple as using sqlite.
Its 2x the infra. You have to manage an additional process, auth, backups, logging, etc.
Re: SQLite is all you need for durable workflows
#137I started using SQLite for a home project after years of reading about it, I was shocked at the poor type system coming from Postgres. It is really inferior, not sure why it gets so much praise. https://sqlite.org/datatype3.html https://www.postgresql.org/docs/current/datatype.html Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so man…
It gets praise because of stuff other than the type system.
I agree it is disappointing, especially before strict tables.
You should check out DuckDB which is basically SQLite but with proper types. Although it is also OLAP (struct of arrays) rather than OLTP (array of structs) which may have worse performance for typical SQLite loads. In practice I doubt it matters if you have an application where you're considering either.
Re: SQLite is all you need for durable workflows
#138I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…
I think you'd be surprised to learn how many real production apps are actually running on top of SQLite (by way of Cloudflare D1).
Re: SQLite is all you need for durable workflows
#139And all you need is pen and paper to do calculations.
Re: SQLite is all you need for durable workflows
#140Earlier quoted context omitted.
You can use strict tables: https://sqlite.org/stricttables.html
This could enforce dates are strings. They wanted to enforce dates are dates I thought.
Storing dates as INTEGER (year * 10000 + month * 100 + day, e.g. 20260530) is not so bad. Proper date / timestamp types would be great though.