Rust SQLx CLI: database migration with MySQL and PostgreSQL.
Tags: database, migration, sqlx, cli
Published: 2023-10-10 02:54:50

<em style="color:#111;">Database migration is the process of developing, and refactoring the application database as part of the application development process. <a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli" title="SQLx CLI" target="_blank">SQLx CLI</a> is Rust crate <a href="https://docs.rs/sqlx/latest/sqlx" title="sqlx" target="_blank">sqlx</a>'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.</em>

After trying <a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli" title="SQLx CLI" target="_blank">SQLx CLI</a> 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.

<h2>Table of contents</h2>

<ul>
    <li style="margin-top:10px;">
		<a href="#sqlx-cli-openssl">SQLx CLI Installation Requires OpenSSL</a>
	</li>
	
    <li style="margin-top:10px;">
        <a href="#sqlx-cli-installation">SQLx CLI Installation</a>
	</li>

	
    <li style="margin-top:10px;">
        <a href="#sqlx-cli-installation">Database Migration with SQLx CLI</a>
		
	    <ul>
            <li style="margin-top:10px;">
                <a href="#database-connection">Database Connection</a>
            </li>
			
            <li style="margin-top:10px;">
                <a href="#mysql-migration">MySQL Server</a>
            </li>			
			
            <li style="margin-top:10px;">
                <a href="#postgresql-migration">PostgreSQL Server</a>
            </li>						
	    </ul>
	</li>

	<!-- TO_DO: -->
    <li style="margin-top:10px;">
        Appendix: <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#win-10-openssl-installation-logs" title="Windows 10 OpenSSL Installation Logs" target="_blank">Windows 10 OpenSSL Installation Logs</a>
	</li>

    <li style="margin-top:10px;">
        Appendix: <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#ubuntu-22-10-openssl-installation-logs" title="Ubuntu 22.10 OpenSSL Installation Logs" target="_blank">Ubuntu 22.10 OpenSSL Installation Logs</a>
	</li>

    <li style="margin-top:10px;">
        Appendix: <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#ubuntu-22-10-sqlx-cli-failure-installation-logs" title="Ubuntu 22.10 SQLx CLI Failure Installation Logs" target="_blank">Ubuntu 22.10 SQLx CLI Failure Installation Logs</a>
	</li>

    <li style="margin-top:10px;">
        Appendix: <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#ubuntu-22-10-sqlx-cli-success-installation-logs" title="Ubuntu 22.10 SQLx CLI Success Installation Logs" target="_blank">Ubuntu 22.10 SQLx CLI Success Installation Logs</a>
	</li>
</ul>

<h3 style="color:teal;">
  <a id="sqlx-cli-openssl">SQLx CLI Installation Requires OpenSSL</a>
</h3>

The <a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli" title="SQLx CLI" target="_blank">SQLx CLI</a> 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 <a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli" title="SQLx CLI" target="_blank">SQLx CLI</a> on Windows 10, I had a problem with not having <code>OpenSSL</code> installed for some other Rust <a href="https://actix.rs/docs/">Actix Web</a> project.

This GitHub issue <a href="https://github.com/sfackler/rust-openssl/issues/1021" title="Ubuntu 18: failed to run custom build command for openssl-sys v0.9.39" target="_blank">Ubuntu 18: failed to run custom build command for openssl-sys v0.9.39</a>, <a href="https://github.com/sfackler/rust-openssl/issues/1021#issuecomment-1004042988" title="answer by riteshkumargiitian" target="_blank">answer by riteshkumargiitian</a> helps, to install, run:

```
C:\>choco install openssl
```

The installation directory is <code>C:\Program Files\OpenSSL-Win64</code>. For full detail logs, please see <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#win-10-openssl-installation-logs" title="Windows 10 OpenSSL Installation Logs" target="_blank">Windows 10 OpenSSL Installation Logs</a>. 

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

```
C:\>set OPENSSL_DIR=C:\Program Files\OpenSSL-Win64
```

This is the state of my Windows 10 machine when I install <a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli" title="SQLx CLI" target="_blank">SQLx CLI</a>. The installation just succeeds the first time.

On Ubuntu 22.10, the first installation attempt does not go through, it needs <code>OpenSSL</code>. For full detail logs, please see <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#ubuntu-22-10-sqlx-cli-failure-installation-logs" title="Ubuntu 22.10 SQLx CLI Failure Installation Logs" target="_blank">Ubuntu 22.10 SQLx CLI Failure Installation Logs</a>.

To install <code>OpenSSL</code>, run:

```
$ sudo apt install pkg-config
```

It should succeed. For full detail logs, please see <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#ubuntu-22-10-openssl-installation-logs" title="Ubuntu 22.10 OpenSSL Installation Logs" target="_blank">Ubuntu 22.10 OpenSSL Installation Logs</a>.

<h3 style="color:teal;">
  <a id="sqlx-cli-installation">SQLx CLI Installation</a>
</h3>

We install <a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli" title="SQLx CLI" target="_blank">SQLx CLI</a> with <code>cargo</code>. 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
```

On Ubuntu 22.10:

```
$ cargo install sqlx-cli
```

-- For the full Ubuntu 22.10 successful installation logs, please see <a href="https://behai-nguyen.github.io/2023/10/09/rust-10-sqlx-cli-with-mysql-postgresql.html#ubuntu-22-10-sqlx-cli-success-installation-logs" title="Ubuntu 22.10 SQLx CLI Success Installation Logs" target="_blank">Ubuntu 22.10 SQLx CLI Success Installation Logs</a>.

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

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

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

<h3 style="color:teal;">
  <a id="sqlx-cli-installation">Database Migration with SQLx CLI</a>
</h3>

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 <a href="https://behainguyen.wordpress.com/2023/09/22/docker-on-ubuntu-22-10-running-mysql8-0-34-debian-with-custom-config-socket-database-and-log-files-reside-on-host-machine/" title="Docker on Ubuntu 22.10: running mysql:8.0.34-debian with custom config, socket, database and log files reside on host machine." target="_blank">Docker on Ubuntu 22.10: running mysql:8.0.34-debian with custom config, socket, database and log files reside on host machine</a>.

The PostgreSQL server is also a Docker container, discussed in <a href="https://behainguyen.wordpress.com/2023/01/13/using-postgresql-official-docker-image-on-windows-10-and-ubuntu-22-10-kinetic/" title="Using PostgreSQL Official Docker image on Windows 10 and Ubuntu 22.10 kinetic." target="_blank">Using PostgreSQL Official Docker image on Windows 10 and Ubuntu 22.10 kinetic</a>.

<h4 style="color:teal;">
  <a id="database-connection">Database Connection</a>
</h4>

<a href="https://github.com/launchbadge/sqlx/tree/main/sqlx-cli#usage" title="SQLx CLI Usage" target="_blank">SQLx CLI Usage</a> discusses both <code>.env</code> file and command line option <code>--database-url</code>. We'll use the <code>.env</code>, later on, we could add more run time info to this file as required by the application. <strong>I think</strong> the <code>.env</code> file should sit in the same directory as the <code>Cargo.toml</code> file.

<h4 style="color:teal;">
  <a id="mysql-migration">MySQL Server</a>
</h4>

❶ 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 <code>/home/behai/rust</code>, create a new project <code>sqlx-mysql-migration</code>, and change to the project directory:

```
$ cargo new sqlx-mysql-migration
$ cd sqlx-mysql-migration/
```

❷ Create the <code>.env</code> file.

```
Content of /home/behai/rust/sqlx-mysql-migration/.env:
```

```ini
DATABASE_URL=mysql://root:pcb.2176310315865259@localhost:3306/membership
```

We've seen the value of <code>DATABASE_URL</code> in other Rust code before, for example, <a href="https://behainguyen.wordpress.com/2023/09/12/rust-mysql-connect-execute-sql-statements-and-stored-procs-using-crate-sqlx/" title="Rust & MySQL: connect, execute SQL statements and stored procs using crate sqlx." target="_blank">Rust & MySQL: connect, execute SQL statements and stored procs using crate sqlx</a>, where it is <code>mysql://root:pcb.2176310315865259@localhost:3306/employees</code>. In this example, we're working with a <code>membership</code> database.

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

```
$ sqlx database create
```

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
```

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

<strong>Please note</strong>, 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>
```

For <code>&lt;name></code>, let's call it <code>init</code>. The command is then:

```
$ sqlx migrate add -r init
```

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
```

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

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

```
Content of migrations/20231008021418_init.up.sql:
```

```sql
-- 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`)
);
```

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

```
Content of migrations/20231008021418_init.down.sql:
```

```sql
-- 08/10/2023.

DROP TABLE IF EXISTS `member`;
```

We undo what the <code>up</code> script does: we remove the <code>member</code> table from the database.

❺ Let's run the <code>init</code> migration. The command is:

```
$ sqlx migrate run
```

The output is brief:

```
Applied 20231008021418/migrate init (2.419887162s)
```

MySQL Workbench shows that the <code>member</code> table has been created, and there's also an additional table <code>_sqlx_migrations</code> -- 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
```

The output is:

```
Applied 20231008021418/revert init (810.615999ms)
```

Verify that the <code>member</code> table has been removed, and the table <code>_sqlx_migrations</code> is empty?

❻ Let's create another database update script, we name it <code>stage1</code>:

```
$ sqlx migrate add -r stage1
```

```
Content of migrations/20231008081406_stage1.up.sql:
```

```sql
-- 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 );
```

```
Content of migrations/20231008081406_stage1.down.sql:
```

```sql
-- 08/10/2023.

DROP TABLE IF EXISTS `duration`;
```

Apply <code>stage1</code> with command:

```
$ sqlx migrate run
```

Assuming that <code>init</code> has been applied. The output is:

```
Applied 20231008081406/migrate stage1 (1.742237765s)
```

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

❼ Let's undo:

```
$ sqlx migrate revert
```

Output:

```
Applied 20231008081406/revert stage1 (488.29367ms)
```

Let's do another undo:

```
$ sqlx migrate revert
```

Output:

```
Applied 20231008021418/revert init (445.333376ms)
```

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

<h4 style="color:teal;">
  <a id="postgresql-migration">PostgreSQL Server</a>
</h4>

The process for PostgreSQL databases is identical to MySQL databases.

❶ Create a new directory for this migration. 

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

```
$ mkdir sqlx-postgresql-migration
$ cd sqlx-postgresql-migration
```

❷ The <code>.env</code> file.

```
Content of /home/behai/rust/sqlx-postgresql-migration/.env:
```

```ini
DATABASE_URL=postgresql://postgres:pcb.2176310315865259@localhost:5432/membership?schema=public
```

💥 <span style="font-weight:bold;color:blue;">Please note:</span> the parameter <code>schema=public</code> might cause the error:

<span style="font-weight:bold;color:red;"><code>
ERROR: no schema has been selected to create in
</code></span>

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

```ini
search_path = 'ompdev1, "$user", public'	# schema names
```

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

```
$ sqlx database create
$ sqlx database drop
```

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

<strong>Please note</strong>, from this point on, we need the database to exist.

❹ Create the first <code>init</code> database script:

```
$ sqlx migrate add -r init
```

```
Content of migrations/20231008104430_init.up.sql
```

```sql
-- 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);
```

```
Content of migrations/20231008104430_init.down.sql
```

```sql
-- 08/10/2023.

DROP TABLE IF EXISTS "member";
DROP TYPE IF EXISTS genders;
```

❺ Let's also create the second database script <code>stage1</code>:

```
$ sqlx migrate add -r stage1
```

```
Content of migrations/20231008111030_stage1.up.sql
```

```sql
-- 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 );
```

```
Content of migrations/20231008111030_stage1.down.sql
```

```sql
-- 08/10/2023.

DROP TABLE IF EXISTS "duration";
```

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

```
$ sqlx migrate run
$ sqlx migrate revert
```

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

```
Applied 20231008104430/migrate init (121.005913ms)
Applied 20231008111030/migrate stage1 (55.043293ms)
```

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

![086-01.png](https://behainguyen.files.wordpress.com/2023/10/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:

<ul>
<li>
<a href="https://www.omgubuntu.co.uk/2022/09/ubuntu-2210-kinetic-kudu-default-wallpaper" target="_blank">https://www.omgubuntu.co.uk/2022/09/ubuntu-2210-kinetic-kudu-default-wallpaper</a>
</li>
<li>
<a href="https://in.pinterest.com/pin/337277459600111737/" target="_blank">https://in.pinterest.com/pin/337277459600111737/</a>
</li>
<li>
<a href="https://www.rust-lang.org/" target="_blank">https://www.rust-lang.org/</a>
</li>
<li>
<a href="https://www.freepnglogos.com/uploads/logo-mysql-png/logo-mysql-mysql-logo-png-images-are-download-crazypng-21.png" target="_blank">https://www.freepnglogos.com/uploads/logo-mysql-png/logo-mysql-mysql-logo-png-images-are-download-crazypng-21.png</a>
</li>
<li>
<a href="https://icon-icons.com/download/170836/PNG/512/" target="_blank">https://icon-icons.com/download/170836/PNG/512/</a>
</li>
</ul>