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
- SQLx CLI Installation
- Database Migration with SQLx CLI
- Appendix: Windows 10 OpenSSL Installation Logs
- Appendix: Ubuntu 22.10 OpenSSL Installation Logs
- Appendix: Ubuntu 22.10 SQLx CLI Failure Installation Logs
- Appendix: Ubuntu 22.10 SQLx CLI Success Installation Logs
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
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
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
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
On Ubuntu 22.10:
$ cargo install sqlx-cli
-- 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/
❷ Create the .env
file.
Content of /home/behai/rust/sqlx-mysql-migration/.env:
DATABASE_URL=mysql://root:pcb.2176310315865259@localhost:3306/membership
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
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.
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>
For <name>
, let's call it init
. 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 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:
-- 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 membership
database, then we create the first table: member
.
Content of migrations/20231008021418_init.down.sql:
-- 08/10/2023.
DROP TABLE IF EXISTS `member`;
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
The output is brief:
Applied 20231008021418/migrate init (2.419887162s)
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
The output is:
Applied 20231008021418/revert init (810.615999ms)
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
Content of migrations/20231008081406_stage1.up.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:
-- 08/10/2023.
DROP TABLE IF EXISTS `duration`;
Apply stage1
with command:
$ sqlx migrate run
Assuming that init
has been applied. The output is:
Applied 20231008081406/migrate stage1 (1.742237765s)
Table _sqlx_migrations
should now contain two (2) entries. Table duration
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 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
❷ The .env
file.
Content of /home/behai/rust/sqlx-postgresql-migration/.env:
DATABASE_URL=postgresql://postgres:pcb.2176310315865259@localhost:5432/membership?schema=public
💥 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
❸ 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 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
Content of migrations/20231008104430_init.up.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
-- 08/10/2023.
DROP TABLE IF EXISTS "member";
DROP TYPE IF EXISTS genders;
❺ Let's also create the second database script stage1
:
$ sqlx migrate add -r stage1
Content of migrations/20231008111030_stage1.up.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
-- 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 _sqlx_migrations
, are created under schema public
as shown in the screenshot below:
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:
- https://www.omgubuntu.co.uk/2022/09/ubuntu-2210-kinetic-kudu-default-wallpaper
- https://in.pinterest.com/pin/337277459600111737/
- https://www.rust-lang.org/
- https://www.freepnglogos.com/uploads/logo-mysql-png/logo-mysql-mysql-logo-png-images-are-download-crazypng-21.png
- https://icon-icons.com/download/170836/PNG/512/
Top comments (0)