Letting go PHP database migrations

I have been on a path this year where I let go ORM/query builder solutions in favor of query languages.

On the surface the solutions provide an elegant way to handle the data storage. I just got tired to jump through loops where the query language offers an easy to follow option.

Why would you bother to learn about all the methods to come to the most efficient queries, when you can just write a plain text. In the age where people create applications from specs with AI, it feels like query languages where ahead of their time.

And now my path brought me to database migrations.

From the Laravel documentation.

return new class extends Migration {

    /**

     * Run the migrations.

     */

    public function up(): void

    {

        Schema::create('flights', function (Blueprint $table) {

            $table->id();

            $table->string('name');

            $table->string('airline');

            $table->timestamps();

        });

    }

    /**

     * Reverse the migrations.

     */

    public function down(): void

    {

        Schema::drop('flights');

    }

};

Am I the only one who finds it weird you need an anonymus function to define the fields in a table?

From the Doctrine migration documentation.

final class CustomSchemaProvider implements SchemaProviderInterface

{

    public function createSchema()

    {

        $schema = new Schema();

        $table = $schema->createTable('users');

        $table->addColumn('id', 'integer', [

            'autoincrement' => true,

        ]);

        $table->addColumn('username', 'string', [

            'notnull' => false,

        ]);

        $table->setPrimaryKey(array('id'));

        return $schema;

    }

}

A bit better but it still has the same problem as an ORM, you need to know the functions to convert it to the actual query language.

Compare this to the SQL for the examples.

/* Laravel */

CREATE TABLE flights (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(255) NOT NULL,

    airline VARCHAR(255) NOT NULL,

    created_at TIMESTAMP NULL,

    updated_at TIMESTAMP NULL

) ENGINE=InnoDB;

/* Doctrine */

CREATE TABLE users (

    id INT AUTO_INCREMENT NOT NULL,

    username VARCHAR(255) NULL,

    PRIMARY KEY (id)

) ENGINE=InnoDB;

The other big problem for me is that ORM solutions only are capable to execute SQL queries. In the current landscape of database types this feels like an unnecessary constriction.

So I started a search for a better migrations tool.

What do I want from a migrations tool

• Support multiple database types.


• The migration files need to be in the query language of the database.


• As little configuration as possible. Setting DSN strings should be enough.


• Have an easy interface.


• Use it for non PHP projects.


• Having the possibility to create a driver for a database type

My search

At first I didn't have the non PHP projects requirement, so I was looking on Packagist. Too bad none of the libraries I looked at didn't support other database types than SQL. So that was a no go.

I dropped my requirements in an AI chat and it produces a list where I saw a few good candidates; Flyway, Liquibase and dbmate.

Flyway and Liquibase are the big migration tools, and both are written in Java. dbmate is the open source option and is written in Go.

While dbmate was my gut feeling favorite, I wanted to see how close the other two tools come to my requirements.

Both support multiple databases, even more than dbmate, so that is a negative point for dbmate.

For Flyway I only could find SQL examples of migration files in the documentation. So when looking for the MongoDB migrations in Flyway I found out you can use js files with the native queries. So I assume this will be the case for other databases too.

For Liquibase I could find a SQL example, and also YAML, XML and JSON examples. The other formats of creating migrations made me a bit wary about the tool. And my suspicion was correct after I saw the documentation for the MongoDB extension. Migrations need to use a non query language format.

So Liquibase is out.

For the configuration Flyway uses a TOML file. It is a little more configuration that I hoped for. but it is not enough to discard the tool.

For fun I checked Liquibase and the tool creates several configuration files, that is the nail in the coffin for Liquibase.

In a professional situation I would use Flyway.

dbmate under the loupe and finding migrate

The main reason dbmate is my gut feeling favorite is because when I need/want to create an extension I rather do it in Go than in Java.

The other reason is that the tool isn't artificially cut off options because there is no commercial product attached.

From the previous section you might have noticed MongoDB is high on my list of database support. So I was a bit sad there is no MongoDB support, not even a community based project.

This is the end of the dbmate tool.

The great thing is that dbmate provided a matrix with other migration tools, and there I found migrate.

This seems the tool that fulfills all my requirements.

It supports multiple database tools with query language migration files.

You can add the path and the DSN as a CLI argument, this means it is possible to control multiple database migrations by using composer scripts.

In the documentation they mention the drivers are dumb, so creating a driver will be easy when looking at the existing drivers. And it is written in Go.

Now I just have to use it to see what is possible and discover the limitations.

If you have other tools that meet my requirements please share them.