sqldef VS skeema

Thanks! Yeah definitely agree that building out declarative table management for Postgres would be a major effort. A few open source projects I've seen in that area include:

https://github.com/sqldef/sqldef (Go)

https://github.com/bikeshedder/tusker (Python but being ported to Rust)

https://github.com/tyrchen/renovate (Rust)

https://github.com/blainehansen/postgres_migrator (Rust)

Some of these are based on parsing SQL, and others are based on running the CREATEs in a temporary location and introspecting the result.

The schema export side can be especially tricky for Postgres, since it lacks a built-in equivalent to MySQL's SHOW CREATE TABLE. So most of these declarative pg tools shell out to pg_dump, or require the user to do so. But sqldef actually implements CREATE TABLE dumping in pure Golang if I recall correctly, which is pretty cool.

There's also the question of implementing the table diff logic from scratch, vs shelling out to another tool or using a library. For the latter path, there's a nice blog post from Supabase about how they evaluated the various options: https://supabase.com/blog/supabase-cli#choosing-the-best-dif...

If you wish to auto-generate migrations, there are declarative schema change tools available for most relational databases. I'm the creator of Skeema [1] which provides them for MySQL, but there are options for other DBs too [2][3][4].

Prisma's migration system actually partially copied Skeema's design, while giving credit in a rather odd fashion which really rubbed me the wrong way: "The workflow of working with temporary databases and introspecting it to determine differences between schemas seems to be pretty common, this is for example what skeema does." [5]

While I doubt I was the first person to ever use that technique, I absolutely didn't copy it from anywhere, and it was never "pretty common". I'm not aware of any other older schema change systems that work this way.

[1] https://www.skeema.io

[2] https://github.com/djrobstep/migra

[3] https://github.com/k0kubun/sqldef

[4] https://david.rothlis.net/declarative-schema-migration-for-s...

[5] https://github.com/prisma/prisma-engines/blob/6be410e/migrat...

By the way, I have also made a repository that uses sqldef as a migration tool to make migration as easy as possible, so please take a look at it.

sqldef-gitops-cdk sqldef is an open source idempotent schema management for MySQL, PostgreSQL, and others. This repo provides a GitOps approach using AWS CDK of how you can approach database table migration.

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 (and therefore Tusker) executes the SQL, 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...)

Thanks! I know of a couple Postgres tools that work in a declarative fashion: migra [1] and sqldef [2].

Migra is Postgres-specific. Its model is similar to Skeema's, in that the desired-state CREATEs are run in a temporary location and then introspected, to build an in-memory understanding of the desired state which can be diff'ed against the current actual state. (This approach was also borrowed by Prisma Migrate [3]). In this manner, the tool doesn't need a SQL parser, instead relying on the real DBMS to guarantee the CREATE is interpreted correctly with your exact DBMS version/flavor/settings.

In contrast, sqldef supports multiple databases, including Postgres and MySQL (among others). Unlike other tools, it uses a SQL parser-based approach to build its in-memory understanding of the desired state. As a DB professional, personally this approach scares me a bit, given the amount of nonstandard stuff in each DBMS's SQL dialect. But I'm inherently biased on this topic. And I will note sqldef's author is a core Ruby committer and JIT author, and is extremely skilled at parsers.

[1] https://databaseci.com/docs/migra

[2] https://github.com/k0kubun/sqldef

[3] https://github.com/prisma/prisma-engines/blob/main/migration...

Declarative schema management tools make this much easier. The concept is your schema repo just stores CREATE statements, and the schema management tool knows how to generate DDL to transition between the current state in your DB and the desired state in your repo.

I'm the author of declarative schema management tool skeema (https://www.skeema.io, for MySQL / MariaDB). Some other options in this space are sqldef (https://github.com/k0kubun/sqldef, for MySQL or Postgres) and migra (https://github.com/djrobstep/migra, for Postgres). In MS SQL Server, SSDT DACPACs are also somewhat similar.

If a tool blindly drops columns, that's just a bad tool! It doesn't mean the concept is flawed.

Thousands of companies successfully use declarative schema management. Google and Facebook are two examples at a large scale, but it's equally beneficial at smaller scales too. As long as the workflow has sufficient guardrails, it's safe and it speeds up development time.

Some companies use it to auto-generate migrations (which are then reviewed/edited), while others use a fully declarative flow (no "migrations", but automated guardrails and human review).

I'm the author of Skeema (https://github.com/skeema/skeema) which has provided declarative flow for MySQL and MariaDB since 2016. Hundreds of companies use it, including GitHub, SendGrid, Cash App, Wix, Etsy, and many others you have likely heard of. Safety is the primary consideration throughout all of Skeema's design: https://www.skeema.io/docs/features/safety/

Meanwhile a few declarative solutions that support Postgres include sqldef, Migra, Tusker (which builds on Migra), and Atlas.

I’d argue the obvious answer is address the lack of great answers for declarative schema migration in PostgreSQL. There is Skeema https://github.com/skeema/skeema but it doesn’t support Postgres and Prisma iirc forces you into an ORM, atlas looks perfect but has a nonstandard license.

Ahh I see now, you've founded https://github.com/skeema/skeema which is great!

Keep it up!

Using tmpfs for MySQL/MariaDB's data directory helps tremendously. If you're using Docker natively on Linux, use `docker run --tmpfs /var/lib/mysql ...` and that'll do the trick. Only downside is each container restart is slightly slower due to having to re-init the database instance from scratch.

Tuning the database server settings can help a lot too. You can add overrides to the very end of your `docker run` command-line, so that they get sent as command-line args to the database server. For example, use --skip-performance-schema to avoid the overhead of performance_schema if you don't need it in your test/CI environment.

For MySQL 8 in particular, I've found a few additional options help quite a lot: --skip-innodb-adaptive-hash-index --innodb-log-writer-threads=off --skip-log-bin

A lot of other options may be workload-specific. My product Skeema [1] can optionally use ephemeral containerized databases [2] for testing DDL and linting database objects, so the workload is very DDL-heavy, which means the settings can be tuned pretty differently than a typical DML-based workload.

[1] https://github.com/skeema/skeema/

[2] https://www.skeema.io/docs/options/#workspace

Atlas hasn’t made it on my radar until now — surprising considering how many stars it has. Based on the description, it looks like it can do something similar to skeema except it isn’t limited to one flavor of sql like skeema. I’m looking forward to trying it out in my next postgres project.

VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case. But with utf8mb4, it's now 4N bytes, which (with a high N) may exceed internal limits such as maximum length of an index key.

IIRC, there were additional problems in older versions of MySQL, situations where sort buffers were sized to a fixed length equal to the value's worst-case size or something like that. So sorting a large number of utf8mb4 values would use a lot more memory than utf8mb3 values (again, iirc, I might be wrong on this).

So the safer and more backwards-compatible approach was to introduce utf8mb4 as a new separate charset, and allow users to choose. MySQL 8 is now transitioning towards deprecating utf8mb3, and will finally make the utf8 alias point to utf8mb4 sometime in the near future.

That said, there are still a bunch of unpleasant uses of utf8mb3 internally in things like information_schema. I develop schema management tooling and recently lost a week to some of the more obscure ones in https://github.com/skeema/skeema/commit/bf38edb :)

I realize my paradigm on schema driven projects comes probably from my background. I found a very similar tool by chance when reading through my latest feeds and found this tool: https://github.com/skeema/skeema