Database

Annie Mei uses Diesel ORM for type-safe database interactions and PostgreSQL for data storage.

The current implementation uses an r2d2 connection pool in src/utils/database.rs and runs embedded migrations on startup. Use those helpers as the source of truth when the examples on this page are too generic.

Overview

Diesel provides:

Type-safe queries - Compile-time guarantees for SQL correctness
Migration management - Version-controlled schema changes
Auto-generated schema - Rust types from database tables

Never manually edit src/schema.rs - it’s auto-generated by Diesel migrations.

Configuration

Diesel is configured via diesel.toml at the project root:

[print_schema]
file = "src/schema.rs"

[migrations_directory]
dir = "migrations"

This tells Diesel:

Where to write the generated schema (src/schema.rs)
Where to find migration files (migrations/ directory)

Creating Migrations

Migrations are SQL files that modify the database schema. Each migration has two parts:

up.sql - Apply changes (e.g., create table)
down.sql - Revert changes (e.g., drop table)

Generate Migration Files

Use the Diesel CLI to create migration scaffolding:

diesel migration generate create_songs

This creates a new directory:

migrations/YYYY-MM-DD-HHMMSS_create_songs/
├── up.sql
└── down.sql

The timestamp prefix ensures migrations run in chronological order.

Write the Up Migration

Edit up.sql to define schema changes:

migrations/YYYY-MM-DD-HHMMSS_create_songs/up.sql

-- Your SQL goes here
CREATE TABLE songs (
    id SERIAL PRIMARY KEY,
    media_id bigint NOT NULL,
    title text NOT NULL,
    artist text,
    spotify_url text,
    created_at timestamp NOT NULL DEFAULT NOW()
)

Write the Down Migration

Edit down.sql to undo the changes:

migrations/YYYY-MM-DD-HHMMSS_create_songs/down.sql

-- This file should undo anything in `up.sql`
DROP TABLE songs

The down migration should completely revert the up migration, allowing you to roll back if needed.

Run the Migration

Apply the migration to your database:

diesel migration run

This:

Executes the SQL in up.sql
Updates src/schema.rs with the new table
Records the migration in the __diesel_schema_migrations table

Verify Schema Update

Check that src/schema.rs was updated:

src/schema.rs

diesel::table! {
    songs (id) {
        id -> Int4,
        media_id -> Int8,
        title -> Text,
        artist -> Nullable<Text>,
        spotify_url -> Nullable<Text>,
        created_at -> Timestamp,
    }
}

This file is auto-generated - never edit it manually.

Migration Examples

Creating a Table

From migrations/2023-01-22-200806_create_users/up.sql:

-- Your SQL goes here
CREATE TABLE users (
    discord_id bigint PRIMARY KEY,
    anilist_id bigint NOT NULL,
    anilist_username text NOT NULL,
    UNIQUE (discord_id, anilist_id)
)

Corresponding down.sql:

-- This file should undo anything in `up.sql`
DROP TABLE users

Adding an Index

From migrations/2023-02-02-080943_users_add_index_discord_id_anilist_id/up.sql:

-- Your SQL goes here
CREATE INDEX users_discord_id_anilist_id ON users (discord_id, anilist_id)

Corresponding down.sql:

-- This file should undo anything in `up.sql`
DROP INDEX users_discord_id_anilist_id

Removing a Constraint

From migrations/2023-02-02-075156_remove_unique_constraint_from_user/up.sql:

-- Your SQL goes here
ALTER TABLE users DROP CONSTRAINT users_discord_id_anilist_id_key

Corresponding down.sql:

-- This file should undo anything in `up.sql`
ALTER TABLE users ADD CONSTRAINT users_discord_id_anilist_id_key UNIQUE (discord_id, anilist_id)

Rolling Back Migrations

Revert the most recent migration:

diesel migration revert

This:

Executes the SQL in down.sql
Updates src/schema.rs to remove the table/changes
Removes the migration record from the database

Revert multiple migrations:

diesel migration revert --all

Creating Database Models

After running migrations, create Rust models in src/models/db/:

src/models/db/user.rs

use diesel::prelude::*;
use serde::{Deserialize, Serialize};

#[derive(Queryable, Selectable, Debug, Serialize, Deserialize)]
#[diesel(table_name = crate::schema::users)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct User {
    pub discord_id: i64,
    pub anilist_id: i64,
    pub anilist_username: String,
}

#[derive(Insertable)]
#[diesel(table_name = crate::schema::users)]
pub struct NewUser {
    pub discord_id: i64,
    pub anilist_id: i64,
    pub anilist_username: String,
}

Key traits:

Queryable - For selecting data from the database
Selectable - Explicitly map struct fields to columns
Insertable - For inserting new rows
AsChangeset - For updating existing rows

Querying the Database

Diesel queries are type-safe and compile-time checked:

Select Query

use diesel::prelude::*;
use crate::schema::users::dsl::*;
use crate::models::db::user::User;
use crate::utils::database::{create_pool, get_connection};

let pool = create_pool();
let connection = &mut get_connection(&pool);
let results = users
    .filter(discord_id.eq(123456789))
    .select(User::as_select())
    .load(connection)
    .expect("Error loading users");

Insert Query

use diesel::prelude::*;
use crate::schema::users;
use crate::models::db::user::NewUser;

let new_user = NewUser {
    discord_id: 123456789,
    anilist_id: 987654,
    anilist_username: "example_user".to_string(),
};

diesel::insert_into(users::table)
    .values(&new_user)
    .execute(connection)
    .expect("Error inserting user");

Update Query

use diesel::prelude::*;
use crate::schema::users::dsl::*;

diesel::update(users.filter(discord_id.eq(123456789)))
    .set(anilist_username.eq("new_username"))
    .execute(connection)
    .expect("Error updating user");

Delete Query

use diesel::prelude::*;
use crate::schema::users::dsl::*;

diesel::delete(users.filter(discord_id.eq(123456789)))
    .execute(connection)
    .expect("Error deleting user");

Database Connection

Create the pool and fetch a connection in src/utils/database.rs:

use diesel::prelude::*;
use diesel::pg::PgConnection;
use diesel::r2d2::{ConnectionManager, Pool, PooledConnection};
use std::env;

pub type DbPool = Pool<ConnectionManager<PgConnection>>;
pub type DbConnection = PooledConnection<ConnectionManager<PgConnection>>;

pub fn create_pool() -> DbPool {
    let database_url = env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");

    let manager = ConnectionManager::<PgConnection>::new(database_url);
    Pool::builder()
        .build(manager)
        .expect("Error creating database pool")
}

pub fn get_connection(pool: &DbPool) -> DbConnection {
    pool.get().expect("Error retrieving pooled database connection")
}

The bot creates the pool and runs migrations at startup in src/main.rs:199-202:

let database_pool = create_pool();
let connection = &mut get_connection(&database_pool);
run_migration(connection);

Running Migrations Programmatically

Annie Mei runs migrations automatically at startup using diesel_migrations:

src/utils/database.rs

use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};
use diesel::pg::PgConnection;

pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!();

pub fn run_migration(conn: &mut PgConnection) {
    conn
        .run_pending_migrations(MIGRATIONS)
        .expect("Failed to run migrations");
}

This ensures the database schema is always up-to-date when the bot starts.

Migration Best Practices

Always Write Down Migrations

Every up.sql must have a corresponding down.sql that fully reverts the changes.

Test Migrations

Test both up and down migrations:

diesel migration run
diesel migration revert
diesel migration run

Use Transactions

Wrap complex migrations in transactions:

BEGIN;
-- Multiple statements here
COMMIT;

Avoid Data Loss

Be cautious with:

Dropping tables
Removing columns
Changing data types

Consider adding new columns instead of modifying existing ones.

Troubleshooting

Migration fails with 'relation already exists'

The table may already exist. Check current schema:

psql $DATABASE_URL -c "\dt"

Either:

Drop the table manually and re-run
Update the migration to check for existence:

CREATE TABLE IF NOT EXISTS users (...)

Schema.rs not updating

Manually regenerate the schema:

diesel print-schema > src/schema.rs

Or delete src/schema.rs and re-run migrations.

Migration order is wrong

Diesel runs migrations in timestamp order. If you need to reorder:

Revert all migrations: diesel migration revert --all
Rename migration directories to change timestamps
Re-run migrations: diesel migration run

Can't connect to database

Verify environment variables:

echo $DATABASE_URL

Test connection:

psql $DATABASE_URL -c "SELECT 1"

Next Steps

Architecture

Understand how database fits in the architecture

Adding Commands

Use database models in commands

Diesel Documentation

Official Diesel ORM documentation

Getting Started

Self-hosting

Commands

Development

Deployment

API Reference

Overview

Configuration

Creating Migrations

Migration Examples

Creating a Table

Adding an Index

Removing a Constraint

Rolling Back Migrations

Creating Database Models

Querying the Database

Select Query

Insert Query

Update Query

Delete Query

Database Connection

Running Migrations Programmatically

Migration Best Practices

Always Write Down Migrations

Test Migrations

Use Transactions

Avoid Data Loss

Troubleshooting

Next Steps

Architecture

Adding Commands

Diesel Documentation

Getting Started

Self-hosting

Commands

Development

Deployment

API Reference

​Overview

​Configuration

​Creating Migrations

​Migration Examples

​Creating a Table

​Adding an Index

​Removing a Constraint

​Rolling Back Migrations

​Creating Database Models

​Querying the Database

​Select Query

​Insert Query

​Update Query

​Delete Query

​Database Connection

​Running Migrations Programmatically

​Migration Best Practices

Always Write Down Migrations

Test Migrations

Use Transactions

Avoid Data Loss

​Troubleshooting

​Next Steps

Architecture

Adding Commands

Diesel Documentation

Overview

Configuration

Creating Migrations

Migration Examples

Creating a Table

Adding an Index

Removing a Constraint

Rolling Back Migrations

Creating Database Models

Querying the Database

Select Query

Insert Query

Update Query

Delete Query

Database Connection

Running Migrations Programmatically

Migration Best Practices

Troubleshooting

Next Steps