SQLite the only database you will ever need in most cases (2021)

How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data.

Using a PAAS solution for a database, you get all that functionality.

- Will multithreading make it break? (Not with WAL mode, but you have to set it manually, as this article suggests.)

- When using a PaaS, you need to explicitly add a volume and mount it on your server machine, which you might not think to do if you're a brand new bootcamp grad

- You can't(?) run migrations from another process (or at least, people don't seem to talk about doing this), so you need to prevent your server from writing while you run the migration. Even if I'm wrong, and I would love to be, it's frustrating that people don't talk about the completely ordinary need to run migrations on a database.

- Backups just mean copying the file somewhere, which is nice, but you might need to configure that yourself instead of just using somebody's managed Postgres backup feature

So, there is at least some complexity that comes with managing a "real" SQLite web server.

(I'm probably at least 25% wrong on some details above, but that's kind of the point, it's not always easy to figure this stuff out.)

I'm a little skeptical that any given PostgreSQL free tier will stick around indefinitely, after what happened with Heroku. And once you hit 500MB, you jump immediately to $25/mo, so if you're running a hobby project, your choice is either to delete data or start paying $300/year. On the other hand, I'd expect a well-optimized read-heavy SQLite app to scale to 10GB+ without breaking a sweat (speculating wildly) and costing more like $3/mo in storage.

I speak from experience here—until recently, I ran a site that would have been 10x cheaper if it had used pure SQLite instead of managed Postgres.

Of webapps. Will make you a better developer of webapps. It may surprise you to learn that there are more kinds of software than that. Sometimes the final product is something like a binary that runs outside the browser, and deployment is no more complex that putting an tar.gz'd executable on a website.

    You don't need to be a full-blown sysadmin to 
    know how to do basic deployments, and learning 
    these things will make you a better developer.

  - Unix literacy including common tools (awk, sed, grep, etc)
  - Docker, etc.
  - Relational data stores
  - Key/value and document stores
  - Base frontend technologies (HTML, CSS)
  - Frontend frameworks and the associated language
  - Build pipelines to tie it all together

I've grown extremely disenchanted with "modern" software development. Your typical full stack engineer is frankly bad at most of the damn stack and they're not great at any of it. Nobody masters anything anymore. You get people with IQs three standard deviations above normal thinking they need to spin up an armada of AWS crap just to render "Hello World" in 2023. And they still manage to make a mess of it. I can't tell how much of this is brain damage and how much of it is a sociopathic commitment to resume-driven development.

In practicality, I'm a fan of "T-shaped" engineers. Master a piece or two of the stack, and have light knowledge of the adjacent layers and what they do. e.g. If you are an application-level coder you should know what Docket/K8s/etc are and when/why you might use it and when you should eschew them. And honestly I think it should be eschewed fairly often.

The places you can go with a bit of good old vertical scaling are pretty impressive in 2023. A single box with e.g. 24 cores, 64GB of RAM, and fast solid-state storage is a god damn supercomputer when it comes to spitting out web pages. Think about how far Stack Overflow got with that model and at which point you might start to outgrow them.

---

edit: By "mastery" I mean "fairly comprehensive competency" and not "be a recognized global authority." You can fluently use 50-75% of what the tool offers and you know what the other 25%-50% is so you can learn it and utilize it when needed. You can ship code using this tool that is maintainable and performant. For example, perhaps you've never utilized partitioning in Postgres but you know what it is and you understand the appropriate use case for it so you can learn employ it when needed.

It's only going to increase cognitive load, potentially without end (?) which doesn't sound wise long term. :(

Why make Docker and essential part of a system before you actually need it? There’s a reasonable chance that you never need it. I know this, because a lot of business and hobby systems have been in operation since before Docker existed.

Docker has value in many cases, but it certainly doesn’t have to be a necessity.

If you just want to do the minimum to run the app, it will make you worse and usually utterly terrible sysadmin, as it usually is going to be "do the minimum required amount of copy-pasting from tutorials to make the stuff running".

Then inevitably don't update shit, nor even set unattended-upgrades package.

And if the server won't get hacked from bad firewall and no updates, the server will ran out of space because dev of course didn't thought about data management and some log file in wrong place without rotation overflowed.

That's from decade+ of fixing shit of developers that think that tutorial on internet made them sysadmin.

For fun, sure, dink around and learn as long as there are no customers to affect.

Specifically: setting up database replication sucks, setting up failover sucks, setting up backups sucks (even with a PaaS you need to do this, but you can use it as your first layer), migrating database clusters to new versions or hosts sucks, keeping your host's software up to date sucks, and setting up alerting and monitoring sucks.

It's easy to get an open port to connect to, but hard to keep it there for five years.

I still self-host databases for personal projects, but I can see why so many people don't want to bother. When I've done it professionally, I was basically working as a sysadmin and managing stuff like database servers was a core job function, not something tacked on to a development job.

I can only remember 3 companies out of all 40+ I consulted and contracted for that actually needed a separate machine for app and a DB, let alone such that actually truly need several of each.

It's a very non-romantic truth but most projects out there can easily fit in a VPS with 4 CPU cores, 8GB ram and 100-200GB SSD space. App + DB + self-hosted telemetry included.

Just having sync replication is enough, doesn't have to be fancy like semi-sync.

We economize by using racks of cheap, flaky commodity hardware, but we have to be ready for one computer to die by failing each application over to another.

Other databases have a commit level that makes sure logs have been shipped.

For me this is an edge case in just about everything except financial transactions (the performance penalty of distributed transactions is pretty high!) but it is correct to note it.

Regression via a code or dependency update? Full disk? DNS is down? Too much load? All of these would bring down both servers in quick succession.

I guess something like a "once every 2 days" race condition could buy you some time if you had a 2nd server. But that's not a common error

Spinning disks and all, I was terrified to reboot them and have the boot disk fail (which was not on RAID).

The main disks failed once or twice which slowed the servers down considerably until rebuild of the raid finished. Very nervous time.

That's not fair on many levels and reflects harsher on the casual readership hereabouts than yourself.

Your comment is probably rated stellar by the time I hit enter ...

This is what we do. Have been storing 100% of our timestamps this way in SQLite for ~8 years now. Using .NET to handle the actual conversion to/from long.

  var myTimeUtc = DateTime.UtcNow;
  var myTimeUnix = new DateTimeOffset(myTimeUtc).ToUnixTimeSeconds();
  var myTimeUtc2 = DateTimeOffset.FromUnixTimeSeconds(myTimeUnix).UtcDateTime;

Any code (usually one codebase) which looks at dates in a SQLite DB can already easily do these conversions, even at the application-level.

The limited datatypes are so silly, and the limits seem pretty pointless. There's all kinds of weird datatypes, but no unsigned integers?

Of course, SQLite types are a special level of hell, where everything is stringly typed.

And it costs almost nothing to write it that way from the start, but it's a pain to separate them out later.

I'd call that a considered technical reason.

For blast radius, you have things like patching (you don't want to botch the database accidentally updating a shared dependency with your app), resource management (don't want your DB to eat all the RAM or I/O and kill your app), if someone botches maintenance there's less to break at once

You don't have to use VMs but they're one way to do it. Container orchestrators achieve many of the same goals and automatically restart workloads if a physical machine fails, too

I was looking at https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4)

However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database".

I guess this is also the piece that I'm missing. How do I run migrations? Do I deploy a new version with the migration and temporarily take down the server? I'm glad to do that.

I guess I'm also walking through this because---as I said---I'd love just to switch to SQLite but I'm still not sure how many simple non-esoteric gotchas will pop up.

    pragma journal_mode = wal;
    pragma synchronous = normal;

> A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.

https://www.sqlite.org/pragma.html#pragma_synchronous

https://docs.djangoproject.com/en/4.1/ref/databases/

"A bicycle is all the transportation you will ever need in most cases.

I bet most non-bicycle drivers have indeed never been anywhere which couldn't have been gotten to by bike. And the bike is in fact more fully featured in some ways than some cars (when will cars shrink down? not require fuel? not pollute? cause less harm?)

I agree it's important to realize when a car may be needed, but in reality it almost never is."

Even if all of that is true... I still want to use a car, for good reasons. I may not need it, and it's more complex to use and maintain. But I get enough value from it that it's worth it.

adding or dropping any constraints, including nullability, foreign keys, check constraints, changing the structure of the primary key, etc. changing a type also, even given SQLite's squishy typing model.

pretty much anything is not allowed except adding and renaming columns.

> Also, MySQL can't run a migration on a FK-constrant table without downtime.

That's not an issue for the overwhelming vast majority of MySQL databases in production, which are to be clear not running or aspiring to run at Facebook / OLTP-level scales. A table with a few million rows can be migrated in seconds, the table gets locked for a few seconds, everything keeps running after a brief pause. This is not a problem for the "most cases" use case the article refers towards.

> To do this you need an online schema migration tool which generally requires the absence of foreign keys.

if you are running at Facebook / OLTP scales or aspiring to be, then yes. Otherwise, not usually. The article here is referring to SQLite being used for "most cases", not just "running at Facebook / OLTP -level scales". Running at Facebook / OLTP-level scales is still one of the places where you most certainly would *not* be using SQLite for your primary database.

But in general I compare the intended scale to successful startups I’ve been at - where several TB of data are being read by hundreds of thousands of users per second. Typically this requires a fleet of the largest instance types most cloud providers offer.

Anyways - you’re not wrong and neither is the author - but if I’m going to choose one tool - I’d rather it work for all intentions and be a bit more complex than the other way around.

Can you share any extra info about the table layouts or queries which are slower in SQLite vs Postgres? In particular which postgres-specific optimizations have been made?

The difference between installation for sqlite and postgres is the difference between typing `apt install sqlite3` and `apt install postgres`.

The actual learning curve for sqlite is more complex because picking random sql queries on the internet will end in misunderstanding in underlying types in sqlite and broken data. Postgres tools ecosystem is much more expressive, because more people think about Postgres as about "real" database.

Setting up cron and rsync on different Windows machines (WSL, cygwin?) was not something I would have looked forward to.

Honestly splitting in to nested subfolders is not big deal anymore, it’s a single function you can write even if you’re having a “.10X” day.

Though, if you are doing a lot of joins your application is probably a better fit for a graph database instead of a “relational” one anyway

Not satire, though a bit sensationalistic to argue it’s a solid solution that’s usually overlooked because it’s “too slow”. I’m just pointing out it’s not actually slow any more.

Back in the days of scaled applications running on MySQL, DDR2 was 3200MB/s and people were so happy when their DB was small enough they could fit it in RAM.

We have a bunch of serverless functions that are generating records that fit a relational data structure. (AWS Lambda)

We would like to write these records to a persistent relational data store so another downstream process can read it.

Amazon RDS seems like an overkill.

Amazon DynamoDB (NoSQL) seems like a misfit because we want to execute relational queries and some joins against this data.

We could write to CSV on S3 and query using Athena/Presto. Seems clunky and slow.

Am I missing any obvious solution here OR there is a space here for a service that offers a lightweight relational datastore that multiple loosely coupled readers and writers can use.

I am not familiar with cloudlflare and if it has a free tier like AWS

will explore

    If you are blindly doing multi-threaded connections without understanding the implications, you also risk corrupting the database entirely.

[0]: https://www.sqlite.org/stricttables.html

Sample data:

    CREATE TABLE users (user_id serial, name text);
    CREATE TABLE comments (comment_id serial, user_id int, comment text unique);
    CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id;
    INSERT INTO users VALUES (1, 'Bob');
    INSERT INTO users VALUES (2, 'Sally');

    sqlite> .schema
    CREATE TABLE users (user_id serial, name text);
    CREATE TABLE comments (comment_id serial, user_id int, comment text unique);
    CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id
    /* user_comment_view(user_id,comment) */;


    sqlite> .schema users
    CREATE TABLE users (user_id serial, name text);


    sqlite> select * from users;
    1|Bob
    2|Sally

    test=# \d
                    List of relations
     Schema |          Name           |   Type   |  Owner   
    --------+-------------------------+----------+----------
     public | comments                | table    | postgres
     public | comments_comment_id_seq | sequence | postgres
     public | user_comment_view       | view     | postgres
     public | users                   | table    | postgres
     public | users_user_id_seq       | sequence | postgres
    (5 rows)


    test=# \d users
                                Table "public.users"
     Column  |  Type   | Collation | Nullable |                Default                 
    ---------+---------+-----------+----------+----------------------------------------
     user_id | integer |           | not null | nextval('users_user_id_seq'::regclass)
     name    | text    |           |          | 


    test=# select * from users;
     user_id | name  
    ---------+-------
           1 | Bob
           2 | Sally
    (2 rows)

Not to mention that to really benefit from scripting you need programs that you can actually execute. As far as I know Windows (which most people use) is not very friendly in that regard. Powershell improves things a bit, but I'm pretty sure you can't just manipulate .xlsx files with a simple shell script on Windows, and this is one of the lowest hanging fruits I could imagine for white collar workers.