Dump and Load Database Schema Migration

[Fs02]

Background

If you've used rails in the past, chances are you've seen this db/schema.rb file. It's a snapshot of current database schema that written in ruby DSL. The dumped schema is database agnostic, and can be used again to restore database schema, something that very useful in CI environment, where running hundreds of migrations one by one for every test build will consume a lot of time. Not only that, this file is also useful during peer review, to see what's the result of migration.

In REL, we would like to have something similar, but in go. It's potential usage is not limited to loading schema in CI environment (which we don't need anyway given we have reltest package 😏 ), but also the following:

• Validate record modification in reltest package. eg: making sure Set("name", "your name") is properly setting to an existing field in database with the correct type.
• Validate query in reltest package. eg: making sure where.Eq("name", "your name") is a valid query against a valid field with valid type and operator.
• To implement auto schema migration generator Automatic schema generation? #49. The snapshot of database can be compared to modification of the related struct, and a proper migration can be generated based on that. eg: https://alembic.sqlalchemy.org/en/latest/autogenerate.html
• As a foundation for type safe query builder generator, that can extend REL's query builder.

Implementation

Adapter Updates

Two new function needs to be added on each adapter to support this feature.
Implementation of each database might be completely different, so we may not be able to reuse Load and Dump method across all adapter.

type Adapter interface {
        // ...

        // load schema back to database.
        // Probably will call Apply, after setting some variable (ex: disabling foreign key check)
	Load(ctx context.Context, schema Schema) error

        // Dump database schema as plain go struct.
	Dump(ctx context.Context) (Schema, error)
}

Dump Result

// file: db/schema.go

package db

// TBD

CLI

• rel dump - Dump database schema to db/schema.go.
• rel load - Load db/schema.go to database.
• rel migrate and rel rollback - Modification for this to always call dump after command executed.

Tasks

• SQLite3 Adapter Dump and Load function.
• MySQL Adapter Dump and Load function.
• Postgres Adapter Dump and Load function.
• Migrator function to translate Adapter dump function result to golang code in db/schema.go as migration DSL.
• rel dump command.
• rel load command.
• rel migrate and rel rollback modification.