DEV Community

Cover image for Rust SQLx CLI: database migration with MySQL and PostgreSQL.
Be Hai Nguyen
Be Hai Nguyen

Posted on

Rust SQLx CLI: database migration with MySQL and PostgreSQL.

#database #migration #sqlx #cli

Database migration is the process of developing, and refactoring the application database as part of the application development process. SQLx CLI is Rust crate sqlx's associated command-line utility for managing this migration process. In this post, we're looking at how to use this CLI for both MySQL and PostgreSQL, on both Windows 10 and Ubuntu 22.10.

After trying SQLx CLI out, I'd say that we could actually use this CLI as a generic tool for managing database development for applications written in languages other than Rust. I feel really fond of this CLI.

Table of contents

SQLx CLI Installation Requires OpenSSL

The SQLx CLI documentation does not mention it, but it seems that it does require OpenSSL. I only find this one out after some failed attempt to install it.

Prior to installing SQLx CLI on Windows 10, I had a problem with not having OpenSSL installed for some other Rust Actix Web project.

This GitHub issue Ubuntu 18: failed to run custom build command for openssl-sys v0.9.39, answer by riteshkumargiitian helps, to install, run:

C:\>choco install openssl
Enter fullscreen mode Exit fullscreen mode

The installation directory is C:\Program Files\OpenSSL-Win64. For full detail logs, please see Windows 10 OpenSSL Installation Logs.

The next step is to set environment variable OPENSSL_DIR. Note that there're no double quotes, ("") around the directory path:

C:\>set OPENSSL_DIR=C:\Program Files\OpenSSL-Win64
Enter fullscreen mode Exit fullscreen mode

This is the state of my Windows 10 machine when I install SQLx CLI. The installation just succeeds the first time.

On Ubuntu 22.10, the first installation attempt does not go through, it needs OpenSSL. For full detail logs, please see Ubuntu 22.10 SQLx CLI Failure Installation Logs.

To install OpenSSL, run:

$ sudo apt install pkg-config
Enter fullscreen mode Exit fullscreen mode

It should succeed. For full detail logs, please see Ubuntu 22.10 OpenSSL Installation Logs.

SQLx CLI Installation

We install SQLx CLI with cargo. I already have Rust installed on both OSes. It does not matter under which directory we run the installation command from.

On Windows 10:

C:\>cargo install sqlx-cli
Enter fullscreen mode Exit fullscreen mode

On Ubuntu 22.10:

$ cargo install sqlx-cli
Enter fullscreen mode Exit fullscreen mode

-- For the full Ubuntu 22.10 successful installation logs, please see Ubuntu 22.10 SQLx CLI Success Installation Logs.

Only after the installation, we get informed where the CLI executables are. The documentation does not mention it beforehand:

● On Windows 10: C:\Users\behai.cargo\bin</code>. And C:\Users\behai.cargo size on disk is about 1.32GB.

● On Ubuntu 22.10: /home/behai/.cargo/bin/. And /home/behai/.cargo is about 1.00GB.

Database Migration with SQLx CLI

We'll look at both MySQL and PostgreSQL, illustrated examples are on Ubuntu 22.10. On Windows 10, commands are the same.

The MySQL server used is a Docker container discussed in Docker on Ubuntu 22.10: running mysql:8.0.34-debian with custom config, socket, database and log files reside on host machine.

The PostgreSQL server is also a Docker container, discussed in Using PostgreSQL Official Docker image on Windows 10 and Ubuntu 22.10 kinetic.

Database Connection

SQLx CLI Usage discusses both .env file and command line option --database-url. We'll use the .env, later on, we could add more run time info to this file as required by the application. I think the .env file should sit in the same directory as the Cargo.toml file.

MySQL Server

❶ We don't actually need an existing Rust project to start a database migration process. But I create one for this run. So I'll repeat it in this post.

While under /home/behai/rust, create a new project sqlx-mysql-migration, and change to the project directory:

$ cargo new sqlx-mysql-migration
$ cd sqlx-mysql-migration/
Enter fullscreen mode Exit fullscreen mode

❷ Create the .env file.

Content of /home/behai/rust/sqlx-mysql-migration/.env:
Enter fullscreen mode Exit fullscreen mode 
DATABASE_URL=mysql://root:pcb.2176310315865259@localhost:3306/membership
Enter fullscreen mode Exit fullscreen mode

We've seen the value of DATABASE_URL in other Rust code before, for example, Rust & MySQL: connect, execute SQL statements and stored procs using crate sqlx, where it is mysql://root:pcb.2176310315865259@localhost:3306/employees. In this example, we're working with a membership database.

❸ Now that we have the DATABASE_URL ready, and of course the target MySQL server is running, we create the database with:

$ sqlx database create
Enter fullscreen mode Exit fullscreen mode

If there was no problem, there would be no output. We should use MySQL Workbench to verify that the database has been created.

We can drop the database with:

$ sqlx database drop
Enter fullscreen mode Exit fullscreen mode

It'll ask for confirmation. Again, we can use MySQL Workbench to verify that the database has been dropped.

Please note, from this point on, we need the database to exist.

❹ Create the first database script. The command takes the format:

$ sqlx migrate add -r <name>
Enter fullscreen mode Exit fullscreen mode

For <name>, let's call it init. The command is then:

$ sqlx migrate add -r init
Enter fullscreen mode Exit fullscreen mode

The output is:

Creating migrations/20231008021418_init.up.sql
Creating migrations/20231008021418_init.down.sql

Congratulations on creating your first migration!

Did you know you can embed your migrations in your application binary?
On startup, after creating your database connection or pool, add:

sqlx::migrate!().run(<&your_pool OR &mut your_connection>).await?;

Note that the compiler won't pick up new migrations if no Rust source files have changed.
You can create a Cargo build script to work around this with `sqlx migrate build-script`.

See: https://docs.rs/sqlx/0.5/sqlx/macro.migrate.html
Enter fullscreen mode Exit fullscreen mode

A sub-directory named migrations/ has been created, in it there're two (2) empty files 20231008021418_init.up.sql and 20231008021418_init.down.sql.

After we made some changes to the application database, we might want to revert these changes for some reason. The 20231008021418_init.up.sql script file is where we write the SQL statements to update the database. The script file 20231008021418_init.down.sql is where we write SQL statements to undo what 20231008021418_init.up.sql does.

Content of migrations/20231008021418_init.up.sql:
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

ALTER DATABASE `membership` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `id` int NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` varchar(100) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(32) NOT NULL,
  `last_name` varchar(32) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `joined_date` date NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_unique` (`email`)
);
Enter fullscreen mode Exit fullscreen mode

Basically, we alter some properties of membership database, then we create the first table: member.

Content of migrations/20231008021418_init.down.sql:
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TABLE IF EXISTS `member`;
Enter fullscreen mode Exit fullscreen mode

We undo what the up script does: we remove the member table from the database.

❺ Let's run the init migration. The command is:

$ sqlx migrate run
Enter fullscreen mode Exit fullscreen mode

The output is brief:

Applied 20231008021418/migrate init (2.419887162s)
Enter fullscreen mode Exit fullscreen mode

MySQL Workbench shows that the member table has been created, and there's also an additional table _sqlx_migrations -- and there's one (1) row in it, this row should be self-explanatory.

Let's just undo that. The command is:

$ sqlx migrate revert
Enter fullscreen mode Exit fullscreen mode

The output is:

Applied 20231008021418/revert init (810.615999ms)
Enter fullscreen mode Exit fullscreen mode

Verify that the member table has been removed, and the table _sqlx_migrations is empty?

❻ Let's create another database update script, we name it stage1:

$ sqlx migrate add -r stage1
Enter fullscreen mode Exit fullscreen mode 
Content of migrations/20231008081406_stage1.up.sql:
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TABLE IF EXISTS `duration`;
CREATE TABLE `duration` (
  `id` smallint NOT NULL,
  `months` smallint NOT NULL,
  `expiry_date` date NULL,
  PRIMARY KEY (`id`)
) comment='Membership duration in months.';

insert into duration ( id, months )
values
    ( 1, 6 ),
    ( 2, 12 ),
    ( 3, 18 ),
    ( 4, 24 );
Enter fullscreen mode Exit fullscreen mode 
Content of migrations/20231008081406_stage1.down.sql:
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TABLE IF EXISTS `duration`;
Enter fullscreen mode Exit fullscreen mode

Apply stage1 with command:

$ sqlx migrate run
Enter fullscreen mode Exit fullscreen mode

Assuming that init has been applied. The output is:

Applied 20231008081406/migrate stage1 (1.742237765s)
Enter fullscreen mode Exit fullscreen mode

Table _sqlx_migrations should now contain two (2) entries. Table duration should also have been created.

❼ Let's undo:

$ sqlx migrate revert
Enter fullscreen mode Exit fullscreen mode

Output:

Applied 20231008081406/revert stage1 (488.29367ms)
Enter fullscreen mode Exit fullscreen mode

Let's do another undo:

$ sqlx migrate revert
Enter fullscreen mode Exit fullscreen mode

Output:

Applied 20231008021418/revert init (445.333376ms)
Enter fullscreen mode Exit fullscreen mode

We can see that the last update gets undo first. Also, the membership database is now an empty database. And table _sqlx_migrations has no entry.

PostgreSQL Server

The process for PostgreSQL databases is identical to MySQL databases.

❶ Create a new directory for this migration.

While under /home/behai/rust, create a new sub-directory sqlx-postgresql-migration, and move to this new sub-directory:

$ mkdir sqlx-postgresql-migration
$ cd sqlx-postgresql-migration
Enter fullscreen mode Exit fullscreen mode

❷ The .env file.

Content of /home/behai/rust/sqlx-postgresql-migration/.env:
Enter fullscreen mode Exit fullscreen mode 
DATABASE_URL=postgresql://postgres:pcb.2176310315865259@localhost:5432/membership?schema=public
Enter fullscreen mode Exit fullscreen mode

💥 Please note: the parameter schema=public might cause the error:


ERROR: no schema has been selected to create in

In the PostgreSQL server config file postgresql.conf, ensure that the search_path entry value has public. For example:

search_path = 'ompdev1, "$user", public'    # schema names
Enter fullscreen mode Exit fullscreen mode

❸ Database creation and removal commands are the same, respectively as:

$ sqlx database create
$ sqlx database drop
Enter fullscreen mode Exit fullscreen mode

We can use a client tool such as pgAdmin 4 to verify that the membership database has been created on the target server.

Please note, from this point on, we need the database to exist.

❹ Create the first init database script:

$ sqlx migrate add -r init
Enter fullscreen mode Exit fullscreen mode 
Content of migrations/20231008104430_init.up.sql
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TYPE IF EXISTS genders;

CREATE TYPE genders AS ENUM ('M', 'F');

DROP TABLE IF EXISTS "member";

CREATE TABLE "member" (
  id integer NOT NULL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password varchar(100) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(32) NOT NULL,
  last_name varchar(32) NOT NULL,
  gender genders NOT NULL,
  joined_date date NOT NULL,
  created_date timestamp with time zone NOT NULL
);

CREATE INDEX member_email_idx ON member (email);
Enter fullscreen mode Exit fullscreen mode 
Content of migrations/20231008104430_init.down.sql
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TABLE IF EXISTS "member";
DROP TYPE IF EXISTS genders;
Enter fullscreen mode Exit fullscreen mode

❺ Let's also create the second database script stage1:

$ sqlx migrate add -r stage1
Enter fullscreen mode Exit fullscreen mode 
Content of migrations/20231008111030_stage1.up.sql
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TABLE IF EXISTS "duration";
CREATE TABLE "duration" (
  id smallint NOT NULL PRIMARY KEY,
  months smallint NOT NULL,
  expiry_date date NULL
);

COMMENT ON TABLE duration IS 'Membership duration in months.';

insert into duration ( id, months )
values
    ( 1, 6 ),
    ( 2, 12 ),
    ( 3, 18 ),
    ( 4, 24 );
Enter fullscreen mode Exit fullscreen mode 
Content of migrations/20231008111030_stage1.down.sql
Enter fullscreen mode Exit fullscreen mode 
-- 08/10/2023.

DROP TABLE IF EXISTS "duration";
Enter fullscreen mode Exit fullscreen mode

❻ Commands to apply and to undo are as discussed before:

$ sqlx migrate run
$ sqlx migrate revert
Enter fullscreen mode Exit fullscreen mode

If we now apply the migration scripts, the output is:

Applied 20231008104430/migrate init (121.005913ms)
Applied 20231008111030/migrate stage1 (55.043293ms)
Enter fullscreen mode Exit fullscreen mode

And tables, including _sqlx_migrations, are created under schema public as shown in the screenshot below:

086-01.png

So barring database specific syntax differences, the process is identical for MySQL and PostgreSQL.

Thank you for reading, and I do hope you find this post useful. Stay safe as always.

✿✿✿

Feature image source:

Top comments (0)

Read next

jbx1279 profile image

Are There “Queries over Trillion-Row Tables in Seconds”? Is “N-Times Faster Than ORACLE” an Exaggeration?

jbx1279 -

orololuwa profile image

Setting up a Database Driver, Repository and Implementation of a transaction function for your Go App

Orololuwa -

suraj_kareppagol profile image

Building Linux Commands From Scratch 🐧 clear Command

Suraj Kareppagol -

trantn profile image

Migration - Module query with TypeORM version 0.3.x

Aidan -