Rust SeaORM: Creating PostgreSQL Tables

Hey there! If you're looking to dive into creating tables with SeaORM in Rust, you've come to the right place. This post is all about making that process smooth and straightforward. Let's get started!

Introduction to SeaORM

Before we dive in, let's talk a bit about SeaORM. It's a fantastic tool for working with databases in Rust. It makes things like creating and managing tables a breeze. So, if you're working on a Rust project that needs database interaction, SeaORM is a tool you should definitely check out.

Setting Up Your Cargo Workspace

First things first, let's set up our Cargo workspace. If you already have a Cargo workspace, feel free to skip this part. Otherwise, we're going to create a new one and call it seaorm-by-example.

Create a new directory like this:

mkdir seaorm-by-example

Then, inside the seaorm-by-example directory, create a new Cargo.toml file with the following content:

[workspace]
members = [
    "migration",
]

This configuration is all about the Cargo workspace, and it does not include a [package] section. Here is where we'll add our migration crate.

Configuring the Database Connection

Next up, let's get our database connection ready. Create a .env file in the seaorm-by-example directory with your database details.

DATABASE_URL=protocol://username:password@localhost/database

Creating the Migration Library Crate

Now, it's time to install sea-orm-cli and set up our migration library crate.

Install sea-orm-cli:

cargo install sea-orm-cli

Initialize the migration crate:

$ sea 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!

After running this, your seaorm-by-example directory should look like this:

.
├── Cargo.lock
├── Cargo.toml
└── migration
    ├── Cargo.toml
    ├── README.md
    └── src
        ├── lib.rs
        ├── m20220101_000001_create_table.rs
        └── main.rs

Don't forget to enable the necessary features in migration/Cargo.toml:

[dependencies.sea-orm-migration]
version = "0.12.0"
features = [
  "runtime-tokio-rustls",
  "sqlx-postgres"
]

Generating New Migrations

To create a new migration file:

$ sea migrate generate "create user table"
Generating new migration...
Creating migration file `./migration/src/m20240106_061448_create_user_table.rs`
Adding migration `m20240106_061448_create_user_table` to `./migration/src/lib.rs`

Update the generated file with your table specifications. Here's an example of what it might look like:

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::Name).string().not_null())
                    .col(ColumnDef::new(User::Age).integer().not_null())
                    .to_owned(),
            )
            .await
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .drop_table(Table::drop().table(User::Table).to_owned())
            .await
    }
}

#[derive(DeriveIden)]
enum User {
    // We use iden="" to set the custom name for the table
    #[sea_orm(iden = "users")]
    Table,
    Id,
    Name,
    Age,
}

You can customize table or column names using #[sea_orm(iden = "")].

Running the Migration

Finally, it's time to run your migration and create the table in your database.

Run the migration:

$ sea migrate up
Applying all pending migrations
Applying migration 'm20240106_061448_create_user_table'
Migration 'm20240106_061448_create_user_table' has been applied

To use a custom schema, specify it when running the migration:

$ sea migrate up -s seaorm_by_example
Applying all pending migrations
Applying migration 'm20240106_061448_create_user_table'
Migration 'm20240106_061448_create_user_table' has been applied

And there you have it! Your table should now be all set up in your PostgreSQL database. Stay tuned for my next post where I'll cover CRUD operations on your new table. Happy coding!