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'd def recommend simonw's CLI and Python lib for anyone doing SQLite stuff, there's all kinds of stuff there.