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'
      )
  );

  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'
    )
  );

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

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.

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

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

  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’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.

> 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.

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

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...

> 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

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

That's the biggest pain of dealign with SQLite.

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.