Hacker News Reader

A handy trick for column types is check constraints.

You can define constraints on a column that ensure it is text that's valid JSON for example:

  CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT NOT NULL
      CHECK (
        json_valid(data)
        json_type(data) = 'object'
      )
  );

Or to ensure specific keys:

  CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT NOT NULL CHECK (
      json_valid(data)
      AND json_type(data) = 'object'
      AND json_type(data, '$.name') = 'text'
      AND json_type(data, '$.age') = 'integer'
    )
  );

You can even use this for things like enforcing a valid YYYY-MM-DD date, though that gets a bit convoluted:

  CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    occurred_on TEXT NOT NULL CHECK (
      length(occurred_on) = 10
      AND occurred_on GLOB
        '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
      AND date(occurred_on, '+0 days') = occurred_on
    )
  );

Correction to the above: it should use

  json_type(data, '$.name') IS 'text'

Using = fails because a missing key returns null and in SQLite null = 'text' is null: https://latest.datasette.io/_memory/-/query?sql=select+null%...

A CHECK constraint only fails when the expression is false, NULL counts as passing.

My sqlite-utils CLI tool and Python library offers solutions to both the alter table limitations and the need for schema migrations.

For alter table it offers a "transform" command which implements the pattern of creating a new table with your desired scheme, copying data to it from the old table, then renaming the tables (all in a transaction): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  sqlite-utils transform fixtures.db roadside_attractions \
    --rename pk id \
    --default name Untitled \
    --column-order id \
    --column-order longitude \
    --column-order latitude \
    --drop address

And for migrations there's a new-in-v4 "migrate" command which lets you create and execute an ordered sequence of migrations: https://sqlite-utils.datasette.io/en/stable/cli.html#running...

  sqlite-utils migrate creatures.db path/to/migrations.py

Migrations files look like this: https://sqlite-utils.datasette.io/en/stable/migrations.html#...

  from sqlite_utils import Migrations
  
  migrations = Migrations("creatures")
  
  @migrations()
  def create_table(db):
      db["creatures"].create(
          {"id": int, "name": str, "species": str},
          pk="id",
      )
  
  @migrations()
  def add_weight(db):
      db["creatures"].add_column("weight", float)

I'd def recommend simonw's CLI and Python lib for anyone doing SQLite stuff, there's all kinds of stuff there.

> Ideally you'd perform your schema migrations separately from your application

Why is that the ideal? With SQLite your database is 1:1 connected to your application (meaning there is no other application using that database), it doesn't make sense to move the app to a new version but not the database or vice versa. Running migrations on startup of the app is ideal.

Migrations are a bit more difficult to write for SQLite than they need to be (DROP column only being added recently...), though. I usually iterate a few times to get the column definitions just right so that I don't have to change them later.

As you say column types are limited (and enforcement lax) but in practice it's a non-issue because you convert the data to application-specific types when reading from db (and enforce by writing only right data types) anyway.

> Why is that the ideal? With SQLite your database is 1:1 connected to your application

I don't think this solves the issue though. To be fair, I was a bit loose with my wording and the principle is actually "don't make backwards breaking changes to your database schema" rather than "do your migrations separately", but if you do them separately it is a good way to enforce it. The issue you want to prevent is your application having bugs/issues in production necessitating a rollback, and your now rolled back application doing things that are incompatible with the current database version (or in a concurrent setting, that some applications may not be updated).

There's still the issue where you're copying over all of the migrations to your server too when you do it in the application, which is in my opinion something you are ideally able to avoid, but it's not a problem in practice until you have 1000s of migrations.

I don't see how having migrations out of the app enforces that.

For the rare case when you do rollback the safest thing to do is stop the app, downgrade the db (by running some sql if necessary) and app and rerun it. Not that different in postgres no?

Postgres has transactional DDL: you can be applying migrations in one transaction while serving live traffic from the old schema in another. By tying the schema changes directly to the application deployment it becomes harder to apply a big migration without downtime. You can't apply the migration and then cut over traffic to new app instances once the migration is complete.

SQLite has transactional DDL: you can start a transaction, do a bunch of create tables, copy data from old tables into the new tables. If an error occurs during this and a rollback occurs, everything will be just like it was before the transaction started. If a commit occurs, the migration succeeds and everyone sees the new schema on their next transaction.

In rollback mode, only the migration thread can be active because it's a write transaction, but I'm guessing in WAL mode, readers can continue to read during the migration, as with any other write transaction. I don't use WAL mode much because for my application (HashBackup), I don't need db concurrency.

If you can accept downtime and you really don't need db concurrency, then that's great and SQLite is probably a good fit for you. There are many applications for which that isn't the case.

I would only recommend sqlite if you know what you are doing (and/or prepared to learn it inside out). It's more of a build your own database primitive (often you'll have multiple sqlite databases for different things). Which can be incredibly rewarding and deliver amazing performance outcomes, simple ops, etc.

I see the migration argument come up a lot. But, in practice with sqlite you'll be using projections where you have a source of truth database (event log) and project off it into disposable/expendable sqlite databases. So schema changes are often just delete and rebuild the projection.

I’ve never once had to change a column definition. Sure in theory that option is available. Better option is to just add a new column with the correct definition then copy over existing data in the old column.

I don’t think that’s really a positive or negative.

And the point about migrations ideally being separate is really just your own opinion. I prefer having the database definition in the same source tree as the application, ideally just a .sql file in the project.

> Better option is to just add a new column with the correct definition

After that you won't be able to change column to NOT NULL. You would need migration to create new table with not null column, copy everything, drop old table and rename the new one.

Edit: unless the table is empty.

How do you migrate in place data that doesn’t convert between types while maintaining a strict condition like NOT NULL?

This is again a scenario I’ve never run into 20ish years of SQL.

You create a nullable column and then change it to not null. Which wasn't possible in SQLite until recently.

I’m not sure what your original point is pointing out.

Some data doesn’t convert is what I’m pointing out regardless of Postgres or SQLite.

Ah, my bad, first I was wrong about null/not null, and now I see what you mean.

Wrong, you can change NOT NULL since 3.53:

https://sqlite.org/releaselog/3_53_3.html

Released a month ago, thanks, didn't know.

SQLite supports ALTER TABLE:

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

On Go you can embed your migrations in your binary:

https://oscarforner.com/blog/2023-10-10-go-embed-for-migrati...

ALTER TABLE is very limited in sqlite3, though they've been improving it (search the page for " 3." to find the version-specific changes). Any significant schema change is usually simpler to create a new table and copy the data.

FWIW, in a lot of cases you aren't expecting even thousands of simultaneous users, so SQLite is a perfectly valid option. Not to mention services like Cloudflare D2 and Turso which build on SQLite as a core with different features for scale/concurrency.

A lot of people manage to run several containerized applications on a single VPS behind a reverse proxy for personal or small groups. Managing a full rdbms takes work supporting multiple applications, or spinning up multiple instances per app in said containerized flows takes up excess resources, where SQLite would do the job just fine.

Not everything is going to be running 5+ nines of operation with distributed workloads. Plenty of real things run on a decent server with a good enough backup system in place.

It's about tradeoff, sometimes those limitations doesn't really matter that much, sometimes they are. The point is not to settle on a superior option so we never need to think the again but to understand the difference and choose accordingly.

Or at least that's how I view it. Whenever I think about using SQLite, I make sure I read these documents to see if I am fine with the limitations.

https://sqlite.org/whentouse.html

https://sqlite.org/quirks.html

Good links. To make it easier, here are the first paragraphs;

"SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen()."

> To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.

No you don’t [0]. It is less convenient than being able to directly alter columns, but you do not need to mess around with writable schemas or risk corruption.

[0]: https://www.sqlite.org/lang_altertable.html#otheralter

I’m a bit confused. That’s not a column definition change, because the original column is the same, you’re doing a data migration. That is one way you would solve this class of problems in SQLite, but it’s a bit annoying compared to a something like `alter column`.

I also treat articles about production optimisation with a bit of caution when they don't include any numbers to back up the claims.

If you're saying "do this, get that", you should be able explain how to measure and reproduce that result.

The answer to why someone might choose SQLite in production could be latency but if it is then prove it's worth the trade-offs.

I previously had a golang based crawler doing 5 concurrent process writing into the same sqlite wal, it caused the sqlite to get corrupted, and i finally decided to move to postgres instead.

> - You have limited options for dealing with schema migrations.

That's the biggest pain of dealign with SQLite.

Date and time are a deeper loss for me.