Production-ready microservice in Rust: 6. Connecting to a database
Posted 2023-06-11 14:50:00 by sanyi ‐ 7 min read
Connecting to a PostgreSQL database using sqlx and SeaORM
Time has come to add an SQL database to our project. I will use the sqlx and SeaORM crates for this. The database will be PostgreSQL but sqlx supports MySQL and SQLite too.
I will start with two simple entity classes: User and Dog.
To create a postgresql database container I used this docker-compose.yml configuration:
version: '3.8'
services:
db:
image: postgres:14.1-alpine
restart: always
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
ports:
- '5432:5432'
volumes:
- db:/var/lib/postgresql/data
volumes:
db:
driver: local
You can start the postgresql container with this command:
$ docker-compose up -d
PostgreSQL will be available at postgresql://postgres:[email protected]/
Now add our new dependency to shelter_main/Cargo.toml:
[dependencies]
sea-orm = { version = "0.11", features = [ "sqlx-postgres", "runtime-tokio-rustls", "macros" ] }
The "runtime-tokio-rustls" feature flag indicates the we will use the tokio async runtime and the native rust TLS implementation.
We will also need the SeaORM cli utility, run this:
$ cargo install sea-orm-cli
SeaORM can help us to create database migrations. To initialize a new migration module, run this in the main shelter project directory:
$ sea-orm-cli migrate init
Initializing migration directory...
Creating file `./migration/src/lib.rs`
Creating file `./migration/src/m20220101_000001_create_table.rs`
Creating file `./migration/src/main.rs`
Creating file `./migration/Cargo.toml`
Creating file `./migration/README.md`
Done!
Now we can edit migration/src/m20220101_000001_create_table.rs to create our first
actual migration. Something like this:
use sea_orm_migration::prelude::*;
#[derive(DeriveMigrationName)]
pub struct Migration;
#[async_trait::async_trait]
impl MigrationTrait for Migration {
async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
manager
.create_table(
Table::create()
.table(User::Table)
.if_not_exists()
.col(
ColumnDef::new(User::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(ColumnDef::new(User::Username).string().not_null())
.col(ColumnDef::new(User::Password).string().not_null())
.to_owned(),
)
.await?;
manager
.create_table(
Table::create()
.table(Dog::Table)
.if_not_exists()
.col(
ColumnDef::new(Dog::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(ColumnDef::new(Dog::Name).string().not_null())
.col(ColumnDef::new(Dog::Description).text().not_null())
.col(ColumnDef::new(Dog::DateOfBirth).date().not_null())
.col(ColumnDef::new(Dog::DateOfVaccination).date())
.col(ColumnDef::new(Dog::ChipNumber).string().not_null())
.col(ColumnDef::new(Dog::Gender).string().not_null())
.col(ColumnDef::new(Dog::IsSterilized).boolean().not_null())
.col(ColumnDef::new(Dog::Breed).string().not_null())
.col(ColumnDef::new(Dog::Size).string().not_null())
.col(ColumnDef::new(Dog::Weight).integer())
.col(ColumnDef::new(Dog::Hair).string().not_null())
.to_owned(),
)
.await
}
async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
manager
.drop_table(Table::drop().table(Dog::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(User::Table).to_owned())
.await
}
}
#[derive(Iden)]
enum User {
Table,
Id,
Username,
Password,
}
#[derive(Iden)]
enum Dog {
Table,
Id,
Name,
Description,
DateOfBirth,
DateOfVaccination,
ChipNumber,
Gender,
IsSterilized,
Breed,
Size,
Weight,
Hair,
}
Well, this is a little complicated. Start with the two enums at the bottom: they simply define the
table and column names. The iden derive macro will generate something like this:
User::Table = "user"
User::Id = "id"
Dog::Table = "dog"
Dog::ChipNumber = "chip_number"
The two methods async fn up() and async fn down() define the up and down migrations respectively.
The down migration is quite simple, just drops the two tables. The up migration is more interestring, let's look at the dog table creation in more detail:
manager
.create_table(
Table::create()
.table(Dog::Table)
.if_not_exists()
.col(
ColumnDef::new(Dog::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(ColumnDef::new(Dog::Name).string().not_null())
.col(ColumnDef::new(Dog::Description).text().not_null())
.col(ColumnDef::new(Dog::DateOfBirth).date().not_null())
.col(ColumnDef::new(Dog::DateOfVaccination).date())
.col(ColumnDef::new(Dog::ChipNumber).string().not_null())
.col(ColumnDef::new(Dog::Gender).string().not_null())
.col(ColumnDef::new(Dog::IsSterilized).boolean().not_null())
.col(ColumnDef::new(Dog::Breed).string().not_null())
.col(ColumnDef::new(Dog::Size).string().not_null())
.col(ColumnDef::new(Dog::Weight).integer())
.col(ColumnDef::new(Dog::Hair).string().not_null())
.to_owned(),
)
.await
The .table() method declares the table, the .if_not_exists() method ensures that we will
not try to re-create an already existing table. Finally the .col() methods declare all the
columns on the table.
A column definition starts with the name of the column, followed by a type declaration and
optional modifiers like .not_null() or .primary_key().
To run the migrations, we have two options: we can use the SeaORM cli or integrate the migrations into our main binary. I will show the latter now.
First, we have to add the migration module to our main Cargo.toml:
[workspace]
members = [
"shelter_main",
"migration"
]
and reference the migration module in shelter_main/Cargo.toml:
[dependencies]
migration = { path = "../migration" }
Now we can create a new command in our project: shelter_main/src/commands/migrate.rs:
use crate::settings::Settings;
use clap::{ArgMatches, Command};
use sea_orm::Database;
use migration::{Migrator, MigratorTrait};
pub fn configure() -> Command {
Command::new("migrate").about("Run database migrations")
}
pub fn handle(matches: &ArgMatches, settings: &Settings) -> anyhow::Result<()> {
if let Some(_matches) = matches.subcommand_matches("migrate") {
tokio::runtime::Builder::new_current_thread()
.enable_all()
.build()
.unwrap()
.block_on(async move {
let db_url = settings.database.url.clone().unwrap_or("".to_string());
let conn = Database::connect(db_url)
.await
.expect("Database connection failed");
Migrator::up(&conn, None).await.unwrap();
});
}
Ok(())
}
This command will create a tokio runtime, connects to the database and runs the Migrator::up()
method.
We also have to include our new command in shelter_main/src/commands/mod.rs:
mod hello;
mod serve;
mod migrate;
use crate::settings::Settings;
use clap::{ArgMatches, Command};
pub fn configure(command: Command) -> Command {
command
.subcommand(hello::configure())
.subcommand(serve::configure())
.subcommand(migrate::configure())
}
pub fn handle(matches: &ArgMatches, settings: &Settings) -> anyhow::Result<()> {
hello::handle(matches, settings)?;
serve::handle(matches, settings)?;
migrate::handle(matches, settings)?;
Ok(())
}
To test te migration we have to create a new PostgreSQL database. We already started the server earlier, let's check its container id:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED
bef482a795eb postgres:14.1-alpine "docker-entrypoint.s…" About an hour ago
Now run a docker exec command with the above container id:
$ docker exec -it bef482a795eb psql -U postgres -h 127.0.0.1
postgres=# create database shelter encoding 'utf8';
CREATE DATABASE
postgres=# exit
Now set the environment variable for our database url configuration:
$ export SHELTER__DATABASE__URL="postgresql://postgres:[email protected]/shelter"
Now we can compile our project and run the migration:
$ cargo build
$ ./target/debug/shelter_main migrate
Jun 10 09:05:11.651 INFO sea_orm_migration::migrator: Applying all pending migrations
Jun 10 09:05:11.652 INFO sea_orm_migration::migrator: Applying migration 'm20220101_000001_create_table'
Jun 10 09:05:11.682 INFO sea_orm_migration::migrator: Migration 'm20220101_000001_create_table' has been applied
Check the results of the migration:
$ docker exec -it bef482a795eb psql -U postgres -h 127.0.0.1
psql (14.1)
Type "help" for help.
postgres=# \c shelter
You are now connected to database "shelter" as user "postgres".
shelter=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | dog | table | postgres
public | dog_id_seq | sequence | postgres
public | seaql_migrations | table | postgres
public | user | table | postgres
public | user_id_seq | sequence | postgres
(5 rows)
shelter=# select * from seaql_migrations;
version | applied_at
-------------------------------+------------
m20220101_000001_create_table | 1686380711
(1 row)
shelter=# \d dog
Table "public.dog"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------------------------------
id | integer | | not null | nextval('dog_id_seq'::regclass)
name | character varying | | not null |
description | text | | not null |
date_of_birth | date | | not null |
date_of_vaccination | date | | |
chip_number | character varying | | not null |
gender | character varying | | not null |
is_sterilized | boolean | | not null |
breed | character varying | | not null |
size | character varying | | not null |
weight | integer | | |
hair | character varying | | not null |
Indexes:
"dog_pkey" PRIMARY KEY, btree (id)
shelter=# exit
Next time I will continue with the creation of the entity classes and with basic CRUD operations.
You can find the sample code on GitHub