This is sticking so hard to Sqlite, but it's really no longer true I think. Given WAL mode and tools like litestream, it's even feasible to run Sqlite in serverless offerings like Cloud Run. The schema change constraints are annoying though, even when there are known workarounds.
And, this is off topic, but Tailwind is really becoming the new Bootstrap. I open a page and it's instantly visible that this has been implemented with Tailwind (and often Tailwind UI).
Litestream author here. Pure ephemeral serverless offerings aren't currently supported with Litestream so you'll run into issues with something like Google Cloud Run. It's on the roadmap to support in the future though.
@ben Same as the other commenter, I'm currently using it without problems. I configured max instances to 1 and that seems to be working fine. What am I missing?
The main issue is if GCR tries to start up another instance before tears down your old instance during a deploy. If GCR only runs a single instance at a time then you should be fine.
One of the Bytebase authors here and I am the one who chose SQLite in the first place.
The project started in 2021 Jan and I did notice litestream. And actually, one of the main reasons I chose SQLite is because of litestream. Because I could see it could be our HA solution when needing it.
SQLite is great, but the schema change constraint is really painful as you mentioned.
As to the Tailwind, as a backend engineer working on database for most of my 10+ career and single-handedly built the site, I can only say it's godsend;)
Completely unrelated to the thread, but the graphic on your home page doesn't quite re-size correctly, at least in the latest version of Firefox. It blocks the star button at i'd say tablet -> desktop range (somewhere between full width and not quite tablet)
if you go smaller, it does at certain sizes block a bit of the text.
tl;dr it doesn't look like the main graphic on your home page handles fluid layouts
EDIT: this goes with a few other graphics on the front page as well.
I also noticed some of your copy doesn't read well grammatically. "For Developer" I think reads better as "For Developers", as an example.
Only mentioning this cause this is a really cool concept that I wish I had thought of years ago when I worked more heavily with databases and want you to succeed! One of the reasons ORMs are "nice" is the built in schema migration of a lot of the most mature ones (coming from .NET and previously Django). This is a similar idea, but for teams that need high performance SQL optimizations to be able to change schema that may be written against "raw" SQL / Window functions etc.
I wouldn't have noticed the Tailwind thing, and I'm building a product with Tailwind at the moment. But yeah. I agree that Tailwind does seem to be gaining that level of traction.
It's so, so nice, though, so I think it deserves its success.
One of my favorite features of SQLite is its memory mode. It makes writing tests really quick and easy compared to having to spin up a test Postgres database and then deleting it.
We use sqlite for everything. One novel application is the evaluation of business rules. What we do is create an in-memory sqlite DB, fill in with relevant domain info, then execute the queries against it to evaluate logical outcomes. Sqlite's unique application-defined function capability is what sold us on this path. It's very very fast too. Thousands of queries per second per instance are no problem.
We've found quite a few ways to tune DB testing to make it as light as possible but yea - it's still not free by any measure - you can ensure a sane base data set (any static data, all the table schemas) and have minimally necessary writes to set up test cases but you'll never get as close to pure in memory exercise.
I wonder if you could write a postgres shim layer to run your tests on a SQLite in-memory instance - though I guess you're at the whims of whatever that shim layer is doing matching actual postgres functionality bug for bug.
It's no longer technically true that sqlite is limited in that way. But that is relatively new territory (compared to plain sqlite or postgres for sure) so you can expect some unknown troubles there.
I'd be willing to launch a product on it in some circumstances, but depending highly on the resources, composition, goals, and risk tolerance of the team. I'm excited about where it's going but DB is so important and the alternatives are so well understood it still feels like a big move.
Re: the "tailwind is the new Bootstrap" bit - half agree.
tailwind-typography is highly opinionated and "automatically applied", unlike Tailwind itself. I don't think that Tailwind will be quite as immediately visible outside of blogs....unless TailwindUI really takes off.
Unless you're really sensitive to colors, then you might notice the Tailwind gray
That's really pushing SQLite where it was clearly not engineered to go. If you need something that is accessible over the network, use a database server that is designed for it. The right tool for the right job.
I have seen a lot of enthusiasm for using sqlite instead of postgres for django/laravel/rails type projects, or just using it for your tests. I think both of these opinions are over valued since it's easier to learn postgres once then to learn sqlite then migrate to postgres.
The real developer cost of "install postgres, createuser, createdb" is basically nothing.
I see the cost of ever using sqlite (coupling my tests/and/or app, migrating as soon as you have 2 users) as much higher.
Sqlite does seem like a safe, solid, and sane default for the embedded/fopen people though.
My sqlite-utils CLI tool and Python library includes a workaround for SQLite's ALTER TABLE limitations.
The transform CLI command (and Python method) automates the process of starting a transaction, creating a new table with the required changes, copying across the data from the old table, then dropping the old and renaming the new before committing the transaction.
Sequential UUIDs: safer for avoiding errant results from mistyped joins and better clustered database support down the line while keeping good performance levels.
What I've been doing in some of my recent projects is to support both SQlite and PostgreSQL. I do this via an abstraction layer that hides the two databases behind it, and I run the exact same set of unit tests against the two implementations.
The reason I do this is for testing. In fact, the SQLite backend is only compiled for test builds. With that, I can test my application logic against the SQLite implementation and be reasonably confident that things work with the PostgreSQL backend as well. And this is infinitely easier and faster than relying on a "real database server". No installation involved, and the same tests run in 300ms as opposed to 8+ seconds.
Yes, it's significantly more work to develop around these two implementations, but the benefits in build/test iteration times are immense. I haven't ever had to touch docker yet. And the abstraction layer also keeps me honest by forbidding me from poking into database internals.
Lowest common denominator database feature set makes a DBA sad. I love SQLite, but Postgres is just so much more feature full, it's no contest.
If all you need is what SQLite can provide, go for it! It's lightning fast and astonishingly low overhead.
But other times you just need those event triggers and advanced indexing and range types and rollup and grouping sets and writable CTEs and lateral joins and materialized views and row-level security and…
Yes, you can't possibly write triggers and checks on columns across different databases to the point you should be doing all data checks in the app if you're going to support multiple databases.
These days, there's docker, so I see less point supporting multiple databases.
I tried that but moved completely away from it. Too many issues around sql dialect and type differences for starters. I’d much rather run postgres on my dev/testing boxes, use a single set of sql-based migrations and not have to maintain an abstraction layer.
For those who don't know, there's no need to download the full MySQL package(s) that the article references ("200+ MB"); there is the "minimal" package, which includes all the required binaries - as of v8.0.28, it's ~52 MiB.
This is a relatively new introduction; v5.7 doesn't have any minimal packages.
> Then it is a lot of engineering work to update existing SQL queries. SQLite (similar to MySQL) dialect uses the question mark parameters while PostgreSQL uses positioned parameters for prepared statements.
Many database engines, including SQL Server for instance, also support question marks. Surely PostgreSQL also supports this?
As some other replies noted, Postgres does not support that. Some drivers or ORMs do their own pre-processing on query text, and add their own support for that. E.g., in ActiveRecord, you can do:
User.where("status = ?", "active").all
ActiveRecord itself sanitizes the input here and the query that's sent to Postgres does not use parameter markers at all.
Interestingly, there is one "platform" which supports ? as placeholder: ECPG (where SQL is directly embedded in C code, and post-processed to generate straight C you can feed to a compiler).
One thing to keep in mind when moving from sqlite to an "out-of-process" database is that individual query overhead will increase a lot. With sqlite it's usually no problem to do lots of small queries and combine the result, but when you need to account for network latency etc it's a different game. This could be unintended; e.g. your ORM use causes lots of queries that you didn't know or care about with sqlite. You may have to rethink the way you do things in order to keep performance.
Note that they aren't SaaS but are providing a locally-installable software it seems [1]. Then SQLite seems to be a reasonable option and PG can be overkill (but switched to PG anyway because of the listed motivations.)
Bytebase author here. We have a web service at hub.bytebase.com to deal with license purchasing and is also backed by PostgreSQL. And we also plan to offer a SaaS service in the future.
One thing the article doesn't mention is database unification. PostgreSQL is such a ubiquitous database, which enables us to keep a single database system for a long time.
Sounds like it was a smooth migration. Good to hear that because in this precise moment I'm adding SQLite support to Mapless [1] (PostgreSQL had support already [2]) so people can do these kind of smooth transitions in their Smalltalk apps.
I’d love to see a good open-source sync solution between SQLite and PostgreSQL to get the best of both worlds, like what the Watcom/Sybase/SAP SQLanywhere offered.
I have a tool that can do that in one direction - db-to-sqlite (built on top of SQLAlchemy) can create a SQLite database with a direct copy of the schema and data from a MySQL or PostgreSQL database.
I've contemplated building it to work on the other direction too but I haven't needed it myself yet so I've not done the work.
You can get pretty far if you don't have much in the way of concurrent writes. Concurrent writes can be safe mind you, but that safety comes from locks that are polled against at increasingly long intervals (at least in my experience backing Django with SQLite). If you have one thread making many short writes over a long period, another thread will check to see if it can acquire a lock at 1 second, 2 seconds, 5 seconds, etc and if it doesn't time that check exactly perfectly you'll keep polling until your timeout. That is, out of the box you don't have a fair queue for the write lock. That kind of access pattern doesn't describe all apps of course! And in WAL mode your reads won't be blocked by these long sequences of writes.
SQLite has JSON support via the json1 extension[1]. IIRC it's bundled into SQLite by default in recent releases. As for arrays, you're SOL unless you use JSON arrays. :)
This is sticking so hard to Sqlite, but it's really no longer true I think. Given WAL mode and tools like litestream, it's even feasible to run Sqlite in serverless offerings like Cloud Run. The schema change constraints are annoying though, even when there are known workarounds.
And, this is off topic, but Tailwind is really becoming the new Bootstrap. I open a page and it's instantly visible that this has been implemented with Tailwind (and often Tailwind UI).