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)

SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers