[HN Gopher] Migra: Diff for PostgreSQL schemas
___________________________________________________________________
Migra: Diff for PostgreSQL schemas
Author : thunderbong
Score : 54 points
Date : 2022-02-25 08:23 UTC (1 days ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| gavinray wrote:
| This tool is incredibly useful
|
| I would also like to share a very similar tool that is much less
| known:
|
| https://github.com/bikeshedder/tusker
|
| It's the same thing, except instead of requiring two DB URL's to
| do the diffing, you give it a "schema.sql" file and a folder path
| that has your migrations in it, and the path to the current DB.
|
| Then it introspects the DB, looks at "schema.sql" + the migration
| files, and figures out what you've changed in "schema.sql" that
| isn't in your migrations yet and generates the diff as a new
| migration. Really amazing workflow.
|
| If you have trouble visualizing it, the workflow would be
| something like: 1. DB is empty, migrations
| empty, schema.sql has "CREATE TABLE todo (id int, description
| text);" 2. You run the diff, it generates a
| migration that contains the CREATE TABLE statement
| 3. You modify "schema.sql", maybe adding a new column like
| "boolean is_completed" to "todo" 4. You re-run the
| diff, it sees the DB has the table, the migration for the table
| is present, but a new column is added. So it generates
| an "ALTER TABLE .. ADD COLUMN .." migration. 5.
| Rinse, repeat.
| evanelias wrote:
| I work heavily in this space and can add some more details :)
|
| Tusker actually uses Migra to power its functionality:
| https://github.com/bikeshedder/tusker#how-does-it-work
|
| Tusker's flow is somewhat similar to sqldef
| https://github.com/k0kubun/sqldef , although the internal
| mechanics are quite different. Migra/Tusker executes the SQL in
| a temporary location, introspects it, and diffs the
| introspected in-memory representation -- in other words, using
| the database directly as the canonical parser. In contrast,
| sqldef parses the SQL itself, builds an in-memory
| representation based on that, and then does the diff that way.
|
| I'm the author of Skeema https://www.skeema.io which provides a
| similar declarative workflow for MySQL and MariaDB schema
| changes. Skeema uses an execute-and-introspect approach similar
| to Migra/Tusker, although each object is split out into its own
| .sql file for easier management in version control, with a
| multi-level directory hierarchy if you have multiple database
| instances and multiple schemas.
|
| Skeema was conceptually inspired by Facebook's internal
| database schema change flow, as FB has used declarative schema
| management submission/review/execution company-wide for over a
| decade now. Skeema actually predates both Migra and sqldef
| slightly, although it did not influence them, all were
| developed separately.
|
| In turn, Prisma Migrate and Vitess/PlanetScale declarative
| migrations were directly inspired by Skeema's approach,
| paradigms, and/or even direct use of source code in Vitess's
| case. (Although they're finally moving to a parser-based
| approach instead, which I recommended they do over a year ago,
| as it makes more sense for their use-case -- their whole
| product inherently requires a thorough SQL parser anyway... and
| ironically, sqldef is based on the Vitess SQL parser!)
| dang wrote:
| Related:
|
| _Migra - A schema diff tool for PostgreSQL_ -
| https://news.ycombinator.com/item?id=16673526 - March 2018 (29
| comments)
|
| (Reposts are fine after a year or so:
| https://news.ycombinator.com/newsfaq.html)
| morelish wrote:
| Sorry for being an idiot. I don't quite understand the point of
| the tool. You have to have two different versions (x and y) of
| the same database running? And you're trying to work out how the
| versions diverged (e.g. what sql statements you'd need to issue
| to change x into y)?
|
| I'm sort of confused. Is this for people who don't know how to
| write database migrations? Surely not. What is this for? Sorry
| for being stupid.
| jvolkman wrote:
| Tools like this allow you to keep a single desired schema up to
| date and then auto-generate commands to move from the current
| database state to desired state. So instead of manually writing
| 'alter table foo rename column bar to baz', you just change the
| column name in the 'create table' section of your golden schema
| and generate the alter statement.
| digitxpc wrote:
| The main use case I found was that my production database had a
| bunch of changes we did manually (early stage startup and all),
| so I used Migra to figure out what changes we needed to make to
| keep the migrations in sync with what was actually in
| production.
|
| The more common use case is this idea in development---
| experiment with different schemas manually and then use a tool
| like Migra to figure out what migration to write, without
| keeping in your head what changes you've made.
___________________________________________________________________
(page generated 2022-02-26 23:00 UTC)