6 ms·

Simple declarative schema migration for SQLite

109 points by drothlis 4y ago

harryvederci 4y ago
Interesting approach, I'm taking notes! Something I'm currently doing for my CV application[0] is auto-generating variables for every table/column name in my SQLite DB. As I'm using a dynamically typed language, it's normally easy to mess up with a typo when getting a valuable out of a query result. But when I use the auto-generated variables I'll know I messed up during compile time. That also means my SQLite schema is always my source of truth. Maybe there are better ways to do it, but I find it really useful so far! [0] https://withoutdistractions.com/cv https://withoutdistractions.com/cv - I'm doing a "Show HN" today here: https://news.ycombinator.com/item?id=31246696 https://news.ycombinator.com/item?id=31246696 (I have to admit I'm kinda jaleous you beat me to the front page within half an hour. What sacrifice did you make to which algorithm deity? I won't tell anyone.)
- drothlis 4y ago
  Thanks! Coincidentally one of the first articles I wrote for my blog was about compile-time checking of table/column names: https://david.rothlis.net/d/templates/ https://david.rothlis.net/d/templates/ (Just something I was messing around with, not anything I ever used in production.)
  - simonw 4y ago
    Spotted this in the code: def _left_pad(text, indent=" "): """Maybe I can find a package in pypi for this?""" return "\n".join(indent + line for line in text.split('\n')) You can use the textwrap module from the Python standard library for this: textwrap.indent('hello\nthis\n is a demo', ' ') # ' hello\n this\n is a demo'
    - drothlis 4y ago
      Ha! I use `textwrap.dedent` all the time but didn't make the leap of logic to check for the existence of `indent`. :-) I suspect this was a leftover from Python 2.7 days.
simonw 4y ago
For anyone who needs to implement the 12 step procedure (https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter) from the SQLite documentation for applying complex alters, I've built a CLI tool and Python library that can apply that for you. CLI example: https://sqlite-utils.datasette.io/en/stable/cli.html#cli-transform-table https://sqlite-utils.datasette.io/en/stable/cli.html#cli-tra... sqlite-utils transform fixtures.db roadside_attractions \ --rename pk id \ --default name Untitled \ --type longitude float \ --type latitude float \ --drop address Python example: https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-transform https://sqlite-utils.datasette.io/en/stable/python-api.html#... table.tranform( rename={"pk":"id"}, defaults={"name": "Untitled"}, types={ "latitude": float, "longitude": float }, drop={"address"} ) Wrote more about those here: https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-... (Just noticed this is already mentioned at the bottom of the blog entry!)
- drothlis 4y ago
  We are big fans of Datasette (though I've yet to use it in real life). I think it's important work.
simonw 4y ago
This is an interesting approach to this problem. I like the idea of building the new schema as an in-memory database. You could even go a step further and compare them using a SQL query that joins across the two databases (SQLite supports this, it's pretty neat). I'm a bit nervous about how edge-cases might screw things up, but a nice thing about SQLite is that backups are really cheap so I guess you can protect against any risks by creating a backup before running this script. My preferred approach to database migrations is the one implemented by Django: migrations are a sequence of transformations stored in files on disk, and the database includes a table that says which of those migrations have been applied. This keeps everything in version control and means there's no chance of the wrong migration being applied in the wrong way. It's quite a bit more work to setup though. I don't have my own Django-style migration system for SQLite yet and I really need one. I really love how clean and short the implementation of this is! https://david.rothlis.net/declarative-schema-migration-for-sqlite/migrator.py https://david.rothlis.net/declarative-schema-migration-for-s...
- billllll 4y ago
  I worked with Django at a startup for <1y on a team of ~10 engineers. Django migrations are easy and better than a lot of what's out there, but I thought they could be better for the following reasons: 1. Merge migrations are annoying. Especially when you get some engineering velocity going, it's very common to get some conflict. Almost every single time, the merge migrations did not conflict at all. 2. Migrations made checking out someone's code to run on your local machine difficult. You gotta remember to unmigrate before checking out your own branch again, otherwise some future migration may fail. 3. Migrating/unmigrating can be hard to reason with. There has been a lot of cases were a junior engineer pulls the latest code, then realizes their DB is borked due to migrations (maybe a phantom migration that is no longer in their codebase). In some of those cases, we just tell them to start from scratch, since it's easier. The solution outlined by OP is nice because it eliminates the overhead of those migration files, and only looks at the intended state (the SQL file), and the actual state (the database). While the migration files do provide some necessary ceremony for dealing with user data in prod, I'm not sure if we've ever gotten anything good out of said ceremony versus the overhead we needed to maintain.
billllll 4y ago
We had something similar at Google, where you change some CREATE TABLE statements in an SQL file, then run a migration based on that changed SQL file. It was insanely nice. I think this tool can be super useful, especially with a lot more people using something like Litestream. I'm personally (and maybe naively) of the opinion that DB migrations should be as painless/automated and without ceremony as possible. I wish there were more tools like this out in the wild.
- simonw 4y ago
  I believe database migrations should be boring and common. At many places I've worked they've been exciting and rare - which massively hurts engineering velocity, to the point that sometimes teams will build things in a really convoluted way rather than go through the bureaucracy of having a schema change accepted!
  - azeirah 4y ago
    At my work right now (where we're using Laravel, which has migrations as a cultural standard) we have many migrations. But 95% if not more of them are adding stuff, very few are removing or refactoring stuff. The database evolved under harsh deadlines and growth so there's a lot of technical debt in there. It's definitely not terrible, but just wished to see people talking about using migrations to refactor databases a bit more.
wmanley 4y ago
Disclosure: I'm the co-author of the blog post. One nice thing about this approach is that because it works by inspecting a database that has been put in a desired state. This means it doesn't matter how you set up your database (with an ORM, a SQL file, etc.) the same function can be used to do the migration. I'd really like if something like this were built into SQLite. That way we'd have more confidence that it would be/stay correct and complete. It seems generic enough that it could be a part of built-in functionality. I would have proposed it upstream, but AIUI the SQLite authors don't accept external patches. Edit: Another benefit of this being built-in: there are plenty of places where SQLite is applicable where you can't, or don't want to run Python.
- simonw 4y ago
  SQLite actually ships a little-known utility called sqldiff that implements a subset of this idea: https://www.sqlite.org/sqldiff.html https://www.sqlite.org/sqldiff.html It's more focused on content diffs than schema diffs though.
  - wmanley 4y ago
    Wow, I hadn't come across that before. It's so close to being what we want, but not quite right. They've even considered this specific use case: > 3. The sqldiff utility is not designed to support schema migrations and is forgiving with respect to differing column definitions. Normally, only the column names and their order are compared for like-named tables before content comparison proceeds. > > However, the single-table comparison option, with "sqlite_schema" named, can be used to show or detect detailed schema differences between a pair of databases. When doing this, the output should not be used directly to modify a database.
- mleonhard 4y ago
  An additional benefit: The CREATE TABLE statements serve as documentation of the structure of the production database.
bvrmn 4y ago
Related project: Alembic[1]. It has tight integration with SQLAlchemy and allows autogenerate migrations based on code/db changes. [1]: https://alembic.sqlalchemy.org/en/latest/ https://alembic.sqlalchemy.org/en/latest/
Sytten 4y ago
Nice tool! One common problem I have with SQLite is the DDL to alter tables is quite limited and the performance is not super. I actually opened https://sqlite.org/forum/forumpost/8b4e95fd55 https://sqlite.org/forum/forumpost/8b4e95fd55 a few days ago because it is currently a pain to drop a column that has a foreign key constraint. Concerning performances, I am dealing with sqlite databases of a couple GB (5-20) and dropping a column can easily take 15-20m plus it doubles the size of the table on disk. If someone has tips on how to improve that I am interested!
- anitil 4y ago
  I believe it doesn't vacuum by default so you can potentially reduce the size back down again by running vacuum. I believe it is slow but I've never used it
tenken 4y ago
Why are all these tools written in python? I'm not complaining, sometimes I'd like to see such a tool in Php or Bash or something where say my project doesn't need to include yet another Language dependency ... Doesn't SQLite cli for example ship as a bash or win32 CMD file...?
jitl 4y ago
I think this is an interesting way to generate migration scripts at design time, but I wouldn't trust auto-migration code in production that migrates from arbitrary schema to arbitrary schema. Take migrations that require copying the entire table - this could be fine for small tables, but extremely expensive for large tables. If disk space is a concern, this will also use 2x disk space! I at least want any such operations to be obviously visible at code review time, so the team can discuss any performance implications before we ship such a migration. Our team uses a rigid workflow to maintain the SQLite schemas for our mobile and desktop apps: 1. Generate a new migration. This creates ${SCHEMA_VERSION}-migration-name.sql, where SCHEMA_VESION will be the PRAGMA user_version after migration. We also generate a matching ${SCHEMA_VERSION}-migration-name.test.ts. 2. The developer updates the migration file to do the thing. This part we could replace with declarative table diffing as in the article, which would be cool! The developer also fills in the test. Our tests typically write some rows to the DB at SCHEMA_VERSION-1, perform the migration, and then make assertions about how the rows look after the migration. 3. When the changes are ready for review, the developer runs a command that packs up all the migrations into generated allMigrations.{json,ts} files, along with an auto-generated "fast-forward" migration that is just the final database SQL dump, and a JSON description of the expected end result schema. 4. On end-user devices, at app boot we read PRAGMA user_version and apply the migrations transactionally in order, to the latest migration. At the end, we assert that the schema in the database matches the expected schema. If the database is new, like on a fresh install, we apply the fast-forward migration instead.

109 points by drothlis 4y ago

Interesting approach, I'm taking notes! Something I'm currently doing for my CV application[0] is auto-generating variables for every table/column name in my SQLite DB. As I'm using a dynamically typed language, it's normally easy to mess up with a typo when getting a valuable out of a query result. But when I use the auto-generated variables I'll know I messed up during compile time. That also means my SQLite schema is always my source of truth. Maybe there are better ways to do it, but I find it really useful so far! [0] https://withoutdistractions.com/cv https://withoutdistractions.com/cv - I'm doing a "Show HN" today here: https://news.ycombinator.com/item?id=31246696 https://news.ycombinator.com/item?id=31246696 (I have to admit I'm kinda jaleous you beat me to the front page within half an hour. What sacrifice did you make to which algorithm deity? I won't tell anyone.)

drothlis 4y ago
Thanks! Coincidentally one of the first articles I wrote for my blog was about compile-time checking of table/column names: https://david.rothlis.net/d/templates/ https://david.rothlis.net/d/templates/ (Just something I was messing around with, not anything I ever used in production.)
- simonw 4y ago
  Spotted this in the code: def _left_pad(text, indent=" "): """Maybe I can find a package in pypi for this?""" return "\n".join(indent + line for line in text.split('\n')) You can use the textwrap module from the Python standard library for this: textwrap.indent('hello\nthis\n is a demo', ' ') # ' hello\n this\n is a demo'
  - drothlis 4y ago
    Ha! I use `textwrap.dedent` all the time but didn't make the leap of logic to check for the existence of `indent`. :-) I suspect this was a leftover from Python 2.7 days.

simonw 4y ago

For anyone who needs to implement the 12 step procedure (https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter) from the SQLite documentation for applying complex alters, I've built a CLI tool and Python library that can apply that for you. CLI example: https://sqlite-utils.datasette.io/en/stable/cli.html#cli-transform-table https://sqlite-utils.datasette.io/en/stable/cli.html#cli-tra... sqlite-utils transform fixtures.db roadside_attractions \ --rename pk id \ --default name Untitled \ --type longitude float \ --type latitude float \ --drop address Python example: https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-transform https://sqlite-utils.datasette.io/en/stable/python-api.html#... table.tranform( rename={"pk":"id"}, defaults={"name": "Untitled"}, types={ "latitude": float, "longitude": float }, drop={"address"} ) Wrote more about those here: https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-... (Just noticed this is already mentioned at the bottom of the blog entry!)

drothlis 4y ago
We are big fans of Datasette (though I've yet to use it in real life). I think it's important work.

simonw 4y ago

This is an interesting approach to this problem. I like the idea of building the new schema as an in-memory database. You could even go a step further and compare them using a SQL query that joins across the two databases (SQLite supports this, it's pretty neat). I'm a bit nervous about how edge-cases might screw things up, but a nice thing about SQLite is that backups are really cheap so I guess you can protect against any risks by creating a backup before running this script. My preferred approach to database migrations is the one implemented by Django: migrations are a sequence of transformations stored in files on disk, and the database includes a table that says which of those migrations have been applied. This keeps everything in version control and means there's no chance of the wrong migration being applied in the wrong way. It's quite a bit more work to setup though. I don't have my own Django-style migration system for SQLite yet and I really need one. I really love how clean and short the implementation of this is! https://david.rothlis.net/declarative-schema-migration-for-sqlite/migrator.py https://david.rothlis.net/declarative-schema-migration-for-s...

billllll 4y ago
I worked with Django at a startup for <1y on a team of ~10 engineers. Django migrations are easy and better than a lot of what's out there, but I thought they could be better for the following reasons: 1. Merge migrations are annoying. Especially when you get some engineering velocity going, it's very common to get some conflict. Almost every single time, the merge migrations did not conflict at all. 2. Migrations made checking out someone's code to run on your local machine difficult. You gotta remember to unmigrate before checking out your own branch again, otherwise some future migration may fail. 3. Migrating/unmigrating can be hard to reason with. There has been a lot of cases were a junior engineer pulls the latest code, then realizes their DB is borked due to migrations (maybe a phantom migration that is no longer in their codebase). In some of those cases, we just tell them to start from scratch, since it's easier. The solution outlined by OP is nice because it eliminates the overhead of those migration files, and only looks at the intended state (the SQL file), and the actual state (the database). While the migration files do provide some necessary ceremony for dealing with user data in prod, I'm not sure if we've ever gotten anything good out of said ceremony versus the overhead we needed to maintain.

billllll 4y ago

We had something similar at Google, where you change some CREATE TABLE statements in an SQL file, then run a migration based on that changed SQL file. It was insanely nice. I think this tool can be super useful, especially with a lot more people using something like Litestream. I'm personally (and maybe naively) of the opinion that DB migrations should be as painless/automated and without ceremony as possible. I wish there were more tools like this out in the wild.

simonw 4y ago
I believe database migrations should be boring and common. At many places I've worked they've been exciting and rare - which massively hurts engineering velocity, to the point that sometimes teams will build things in a really convoluted way rather than go through the bureaucracy of having a schema change accepted!
- azeirah 4y ago
  At my work right now (where we're using Laravel, which has migrations as a cultural standard) we have many migrations. But 95% if not more of them are adding stuff, very few are removing or refactoring stuff. The database evolved under harsh deadlines and growth so there's a lot of technical debt in there. It's definitely not terrible, but just wished to see people talking about using migrations to refactor databases a bit more.

wmanley 4y ago

Disclosure: I'm the co-author of the blog post. One nice thing about this approach is that because it works by inspecting a database that has been put in a desired state. This means it doesn't matter how you set up your database (with an ORM, a SQL file, etc.) the same function can be used to do the migration. I'd really like if something like this were built into SQLite. That way we'd have more confidence that it would be/stay correct and complete. It seems generic enough that it could be a part of built-in functionality. I would have proposed it upstream, but AIUI the SQLite authors don't accept external patches. Edit: Another benefit of this being built-in: there are plenty of places where SQLite is applicable where you can't, or don't want to run Python.

simonw 4y ago
SQLite actually ships a little-known utility called sqldiff that implements a subset of this idea: https://www.sqlite.org/sqldiff.html https://www.sqlite.org/sqldiff.html It's more focused on content diffs than schema diffs though.
- wmanley 4y ago
  Wow, I hadn't come across that before. It's so close to being what we want, but not quite right. They've even considered this specific use case: > 3. The sqldiff utility is not designed to support schema migrations and is forgiving with respect to differing column definitions. Normally, only the column names and their order are compared for like-named tables before content comparison proceeds. > > However, the single-table comparison option, with "sqlite_schema" named, can be used to show or detect detailed schema differences between a pair of databases. When doing this, the output should not be used directly to modify a database.
mleonhard 4y ago
An additional benefit: The CREATE TABLE statements serve as documentation of the structure of the production database.

bvrmn 4y ago

Related project: Alembic[1]. It has tight integration with SQLAlchemy and allows autogenerate migrations based on code/db changes. [1]: https://alembic.sqlalchemy.org/en/latest/ https://alembic.sqlalchemy.org/en/latest/

Sytten 4y ago

Nice tool! One common problem I have with SQLite is the DDL to alter tables is quite limited and the performance is not super. I actually opened https://sqlite.org/forum/forumpost/8b4e95fd55 https://sqlite.org/forum/forumpost/8b4e95fd55 a few days ago because it is currently a pain to drop a column that has a foreign key constraint. Concerning performances, I am dealing with sqlite databases of a couple GB (5-20) and dropping a column can easily take 15-20m plus it doubles the size of the table on disk. If someone has tips on how to improve that I am interested!

anitil 4y ago
I believe it doesn't vacuum by default so you can potentially reduce the size back down again by running vacuum. I believe it is slow but I've never used it

tenken 4y ago

Why are all these tools written in python? I'm not complaining, sometimes I'd like to see such a tool in Php or Bash or something where say my project doesn't need to include yet another Language dependency ... Doesn't SQLite cli for example ship as a bash or win32 CMD file...?

jitl 4y ago

I think this is an interesting way to generate migration scripts at design time, but I wouldn't trust auto-migration code in production that migrates from arbitrary schema to arbitrary schema. Take migrations that require copying the entire table - this could be fine for small tables, but extremely expensive for large tables. If disk space is a concern, this will also use 2x disk space! I at least want any such operations to be obviously visible at code review time, so the team can discuss any performance implications before we ship such a migration. Our team uses a rigid workflow to maintain the SQLite schemas for our mobile and desktop apps: 1. Generate a new migration. This creates ${SCHEMA_VERSION}-migration-name.sql, where SCHEMA_VESION will be the PRAGMA user_version after migration. We also generate a matching ${SCHEMA_VERSION}-migration-name.test.ts. 2. The developer updates the migration file to do the thing. This part we could replace with declarative table diffing as in the article, which would be cool! The developer also fills in the test. Our tests typically write some rows to the DB at SCHEMA_VERSION-1, perform the migration, and then make assertions about how the rows look after the migration. 3. When the changes are ready for review, the developer runs a command that packs up all the migrations into generated allMigrations.{json,ts} files, along with an auto-generated "fast-forward" migration that is just the final database SQL dump, and a JSON description of the expected end result schema. 4. On end-user devices, at app boot we read PRAGMA user_version and apply the migrations transactionally in order, to the latest migration. At the end, we assert that the schema in the database matches the expected schema. If the database is new, like on a fresh install, we apply the fast-forward migration instead.