How to manage your Materialize migrations with Laravel Zero?

Introduction

Managing your schema migrations is essential for any application. In this tutorial, we will show you how to manage your Materialize schema migrations with Laravel Zero.

Prerequisites

Before you start this tutorial, you need to have the following:

What is Laravel Zero?

Laravel Zero is an open-source PHP framework that can be used for creating console applications.

Demo project

I have prepared a demo project that you can use to try out Laravel Zero and Materialize!

Downloading the mzschema binary

Rather than cloning the repository, you can also use the following command to download only the executable file:

wget https://github.com/bobbyiliev/mzschema/raw/main/builds/mzschema
chmod +x mzschema
./mzschema install
Materialize Migrations  SQLite database created successfully at:$HOME/.mz_migrations/mzschema/database.sqlite

Clone the mzschema repository (optional)

Alternatively, you could clone the project and build it with the following commands:

git clone https://github.com/MaterializeInc/materialize/issues/8779
cd mzschema
php mzschema app:build

Environment variables

If your Materialize instance is running on a different host than the one you are running Laravel Zero, you can set the following environment variables to point to the correct host:

  • Add the following lines to the .env file:
MZ_CONNECTION=pgsql
MZ_HOST=127.0.0.1
MZ_PORT=6875
MZ_DATABASE=materialize
MZ_USERNAME=materialize
MZ_PASSWORD=materialize

Creating a migration

Once you have the mzschema binary installed, you can create a new directory called migrations in the same directory as the mzschema binary:

mkdir migrations
<?phpuse Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::connection('materialize')->statement(
"CREATE TABLE transfers (id int, name text)"
);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::connection('materialize')->statement(
"DROP TABLE IF EXISTS transfers"
);
}
};
  • In the down() method, we drop the transfers table. In there you would always want to define your DROP DDL statement.

Running the migrations

To run the migration, you can use the following command:

./mzschema migrate --path=./migrations/ --realpath
Do you really wish to run this command? (yes/no) [no]:
> yes
Migrating: 2022_03_16_155051_create_transfers_table
Migrated: 2022_03_16_155051_create_transfers_table (8.97ms)

Checking migration status

To check the migration status, you can use the following command:

./mzschema migrate:status --path=./migrations/ --realpath
+------+------------------------------------------+-------+
| Ran? | Migration | Batch |
+------+------------------------------------------+-------+
| Yes | 2022_03_16_155051_create_transfers_table | 2 |
+------+------------------------------------------+-------+

Rolling back the migrations

In some cases, you might want to undo a migration. To do that, you can roll back the migration, you can use the following command:

./mzschema migrate:rollback --path=./migrations/ --realpath
./mzschema migrate:refresh --path=./migrations/ --realpath

Limitations

As of the time of being, Materialize does not support ALTER statements for SOURCE and VIEW, meaning that you will have to manually create a new migration to change the source of your data.

Renaming

A possible solution to this is to create a new migration that will drop the old VIEW and create a new one with your new structure.

  • In the same migration, rename the old VIEW to transfers_old
  • Then rename the new VIEW from transfers_new to transfers
<?phpuse Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
// Create the new `transfers_new` view
DB::connection('materialize')->statement(
"CREATE TABLE transfers_new (id int, name text, amount int)"
);
// Rename the old `transfers` view to `transfers_old`
DB::connection('materialize')->statement(
"ALTER TABLE transfers RENAME TO transfers_old"
);
// Rename the new `transfers_new` view to `transfers`
DB::connection('materialize')->statement(
"ALTER TABLE transfers_new RENAME TO transfers"
);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::connection('materialize')->statement(
"DROP TABLE IF EXISTS transfers, transfers_old, transfers_new"
);
}
};

Branching

Another approach to this problem is to create a new migration that creates a new SOURCE and VIEWS with a specific version appended to the name.

Blue-green deployments

Both of the above solutions could be used in a blue-green deployment scenario.

Conclusion

This tutorial has covered the basics of managing your schema migrations with Laravel Zero.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Bobby Iliev

I am a professional System Administrator with a demonstrated history of working in the internet industry. I am a Linux lover