[PhilipTrauner]

There's a similar project (https://github.com/erik/squabble), that uses a portable version of Postgres' SQL parser to spit out an AST, which lints can consequently traverse. This approach alleviates the need for bringing up a Postgres cluster to lint the schema. I'd be curious to know if there's something to be gained by depending on a running database instance (especially because it complicates CI).

[yasserf]

That's an interesting approach.

I usually run a postgres db (as a docker image on circleCI) when I'm running the build. I'm not sure how scalable it is (currently we have a hundred migrations and it's a few seconds so not bad).

The flow is:

- run migrations on an empty DB - create the interfaces and enums - run typescript - derive all the json schemas from the interfaces for API validation

This means we gitignore the actual DB Types which is great in order to reduce the amount of sources of truth and deal with possible issues

This all runs as a pre-commit hook as well. I guess by doing so we sort of validate the SQL is legit via postgres itself. The only issue we face after a commit gets passed the setup stage and tries to run the migration itself is if theres a data* inconsistency we weren't aware of (trying an enum value we thought wasn't used but was). But that sort of things requires special tasks anyways and the migration fails so nothing ever goes wierd (which is why I <3 SQL migrations )

[PhilipTrauner]

> This all runs as a pre-commit hook as well.

Keep in mind that this might become problematic for folks that are running Postgres inside Docker on macOS or Windows, as IO performance is quite poor.

I put together a up / down migration validation system a while ago (start up two databases, apply all-1 up migrations on A and all up migrations + final down migration on B) for a pretty sizable schema.

Folks that were using Docker had to wait upwards of two minutes, while natively installed Postgres would finish in under 10sec.

[yasserf]

Thats good to know.

As long as their database in docker is being incrementally updated (using the same migration scripts as on production which only apply new ones) is this hit only when spinning up a clean docker image?

I'm considering exporting an SQL file whenever we run the migrations for our dev tools / tests to use the one large file. But I haven't found it as easy as export -> import to try it out yet.

[PhilipTrauner]

> As long as their database in docker is being incrementally updated (using the same migration scripts as on production which only apply new ones) is this hit only when spinning up a clean docker image?

Yes, but being able to quickly return to a known-good state by trashing the database is still useful because getting migrations right without testing them is hard.

Using named volumes helps because they don't need to be bridged through to the host filesystem.

[kristiandupont]

That's neat, I wasn't aware of it!

The difference, I suppose, is that by inspecting the schema, you can make assertions that take the whole architecture into account, rather than just the specific piece of SQL of one migration. I don't personally run the tool in CI, I run it before generating types, which is when I am writing a new migration.