sqldef: Benefis and usage

sqldef is a CLI tool to maintain RDBMS schema along with corresponding configs.
It is a ridgepole clone written in Go.

Unlike standard migrations in popular web frameworks, their configs define the whole schema instead of the differences between schema versions. With traditional migrations, you cannot confirm the implemented schema in the database until the migration is applied. However, ridgepole and sqldef treat the configuration as the specification, making this aspect decisively different.

Differences between sqldef and ridgepole

Since sqldef and ridgepole serve the same purpose, users may compete, but there is no decisive advantage or disadvantage due to the difference in config language. The choice depends on the aspects prioritized by the project.

sqldef’s schema definition is in SQL. SQLs are not be fully compatible, and sqldef has its variations such as mysqldef, psqldef, sqlite3def, and mssqldef, each with different behaviors.
ridgepole’s schema definition is in the Rails migration DSL, and, in principle, the description is common across DBMS.

The differences between them may not be easily understood through external feature comparisons. Both tools have the --export option as a key feature, allowing you to dump the DB schema during operation and compare it with the configuration.

Finally, manual verification is crucial

Migration tools like sqldef and ridgepole robustly support maintaining the quality of the DB schema.
However, regardless of the tool chosen, it is essential to separately verify that the schema is accurately reflected and behaves as intended.

Unit tests are insufficient for behavioral confirmation, especially when including the DB, and integration tests become necessary.
Even with an accumulation of integration tests, it’s challenging to guarantee the absence of contradictions. Therefore, manual code verification of the schema becomes crucial.

The schema files of ridgepole and sqldef allow a comprehensive view of all tables in the DB, making logical verification easier.
While there may be instances where definitions deviate from implementation, the straightforward comparability in such cases is a notable advantage.

psqldef operation

As mentioned earlier, sqldef provides a CLI for each DBMS. For PostgreSQL, you would place psqldef in a directory included in the PATH and use it accordingly.

# export
$ psqldef -U<DB user> --export [--skip-view] <DB name> [> <schema filename>]

# apply
$ cat <schema filename> | psqldef -U<DB user> [--dry-run] [--skip-view] <DB name>

Options such as host and password should be specified following the standard psql command. User option is added in the above as an example.

It’s important to understand in advance that if it doesn’t behave as expected, there could be behaviors like columns disappearing with the data.

Given that there can be critical oversights, such as unintentionally triggering a delete-and-add behavior instead of a column name change, migration requires a mental model switch.

Before applying any changes, it’s crucial to take the latest backup of the actual database.
Additionally, as a rehearsal, checking the output of --dry-run can be beneficial.

Combining with psql

psqldef tracks not only tables but also Views, Materialized Views, and similar objects. However, because Views may contain complex SELECT statements, errors can occur.

By adding the --skip-view option, you can exclude them from management. You can confirm the difference in behavior by using the --export option.

For DB objects that support CREATE OR REPLACE, including Views, the standard psql command can effectively update them. By combining psql and psqldef, you can manage quite complex databases.