Database - Annie Mei

Annie Mei uses SQLx for PostgreSQL database access and PostgreSQL for data storage. The bot uses an async connection pool for queries and runs its own SQLx migrations at startup.

The bot owns settings tables in the annie_mei schema and reads OAuth-linked account data from auth-service-owned tables in the shared database. Keep bot and auth migrations coordinated when changing shared behavior.

Overview

SQLx provides:

Async queries — Non-blocking database access compatible with the Tokio runtime
Compile-time query checking — Optional query! macro that verifies SQL at build time
Connection pooling — Reusable database connections via sqlx::PgPoolOptions
Type-safe models — #[derive(FromRow)] for mapping query results to Rust structs

Configuration

The database connection pool is configured in src/utils/database.rs:

use sqlx::{Pool, Postgres, postgres::PgPoolOptions};
use std::time::Duration;

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

    PgPoolOptions::new()
        .max_connections(10)
        .min_connections(0)
        .max_lifetime(Duration::from_secs(20 * 60))
        .idle_timeout(Duration::from_secs(60))
        .test_before_acquire(true)
        .connect(&database_url)
        .await
        .expect("Error creating database pool")
}

Connection pool settings

Setting	Value	Purpose
`max_connections`	10	Maximum concurrent connections
`min_connections`	0	Minimum idle connections
`max_lifetime`	20 minutes	Connection recycling interval
`idle_timeout`	60 seconds	Idle connection cleanup
`test_before_acquire`	true	Validate connections before use

Migration ownership

The bot service runs migrations from its own migrations/ directory during startup. The current bot-owned migration creates settings tables in the annie_mei schema:

migrations/
├── 20260516000001_create_settings_tables.up.sql
└── 20260516000001_create_settings_tables.down.sql

These migrations create:

annie_mei.user_settings for per-member preferences
annie_mei.guild_settings for Discord server preferences

The bot still connects to a database that already contains the auth-service-owned OAuth schema. The current OAuth schema migrations live in the companion auth service repo under migrations/. The auth service runs them on startup with sqlx::migrate!("./migrations").

Do not use the bot repo’s migrations to prepare the OAuth schema. The bot migrations only create bot-owned settings tables and do not create oauth_credentials or oauth_sessions.

Bot repo migrations

migrations/
├── 20260516000001_create_settings_tables.up.sql
└── 20260516000001_create_settings_tables.down.sql

Auth repo schema migrations

auth/migrations/
├── 20260328000001_create_oauth_credentials.up.sql
├── 20260328000002_create_oauth_sessions.up.sql
├── 20260401000003_add_oauth_credential_relink_fields.up.sql
└── 20260505000001_add_anilist_username_to_oauth_credentials.up.sql

Creating auth schema migrations

Create new OAuth schema migrations from the auth service repo:

cd ../auth
sqlx migrate add -r <migration_name>

This creates reversible SQLx migration files:

migrations/YYYYMMDDHHMMSS_<migration_name>.up.sql
migrations/YYYYMMDDHHMMSS_<migration_name>.down.sql

Coordinate schema changes with the bot repo when you add, rename, or remove columns that the bot reads or deletes.

Running migrations

The auth service runs migrations during startup. To run them manually from the auth service repo:

cd ../auth
sqlx migrate run

To inspect migration state:

cd ../auth
sqlx migrate info

Querying with SQLx

The bot reads from the auth-service-owned oauth_credentials table using sqlx::query_as with FromRow structs:

src/models/db/oauth_credential.rs

#[derive(Clone, PartialEq, Eq, FromRow)]
pub struct OAuthCredential {
    pub discord_user_id: String,
    pub anilist_id: i64,
    pub anilist_username: Option<String>,
}

impl OAuthCredential {
    pub async fn get_by_discord_id(
        user_discord_id: UserId,
        pool: &DbPool,
    ) -> Result<Option<OAuthCredential>, sqlx::Error> {
        sqlx::query_as::<_, OAuthCredential>(
            "SELECT discord_user_id, anilist_id, anilist_username \
             FROM oauth_credentials WHERE discord_user_id = $1"
        )
        .bind(user_discord_id.get().to_string())
        .fetch_optional(pool)
        .await
    }
}

Key points:

FromRow — Derives sqlx::FromRow to map query columns to struct fields by name
query_as — Executes raw SQL and maps results to a typed struct
fetch_optional — Returns Ok(None) when no row matches instead of an error
Async — All queries are async and compatible with the Tokio runtime
Pool reference — Pass &DbPool rather than acquiring a connection manually

Batch lookups

The get_by_discord_ids method queries multiple users at once for guild member status:

pub async fn get_by_discord_ids(
    user_discord_ids: Vec<UserId>,
    pool: &DbPool,
) -> Result<Vec<OAuthCredential>, sqlx::Error> {
    let ids: Vec<String> = user_discord_ids.iter().map(|id| id.get().to_string()).collect();

    sqlx::query_as::<_, OAuthCredential>(
        "SELECT discord_user_id, anilist_id, anilist_username \
         FROM oauth_credentials WHERE discord_user_id = ANY($1)"
    )
    .bind(ids)
    .fetch_all(pool)
    .await
}

Writing data

The /unregister command demonstrates writing data using SQLx transactions:

async fn delete_user_registration_in_transaction(
    discord_id: u64,
    conn: &mut PgConnection,
) -> Result<DeletedRegistrations, sqlx::Error> {
    let oauth_credentials_deleted = sqlx::query(
        "DELETE FROM oauth_credentials WHERE discord_user_id = $1"
    )
    .bind(discord_id.to_string())
    .execute(conn)
    .await?
    .rows_affected();

    let oauth_sessions_deleted = sqlx::query(
        "DELETE FROM oauth_sessions WHERE discord_user_id = $1"
    )
    .bind(discord_id.to_string())
    .execute(conn)
    .await?
    .rows_affected();

    Ok(DeletedRegistrations { oauth_credentials_deleted, oauth_sessions_deleted })
}

Transactions are started from the pool:

let mut transaction = pool.begin().await?;
// ... execute statements ...
transaction.commit().await?;

Accessing the pool in commands

The connection pool is stored in Serenity’s TypeMap and accessed in command handlers:

use crate::utils::database::DatabasePoolKey;

let Some(database_pool) = get_pool_from_context(ctx).await else {
    // Handle missing pool
    return;
};

Setup

Install SQLx CLI for auth schema work

cargo install sqlx-cli --no-default-features --features postgres

Create the database

createdb annie_mei

Or using psql:

CREATE DATABASE annie_mei;

Run auth migrations manually

cd ../auth
sqlx migrate run

Check migration status

cd ../auth
sqlx migrate info

Troubleshooting

Can't connect to database

Verify environment variables:

echo $DATABASE_URL

Test connection:

psql $DATABASE_URL -c "SELECT 1"

Migration failures

For bot startup migration failures, check the cargo run startup output for the failing SQLx migration and verify the database user can create the annie_mei schema and tables.Inspect the bot migration table from the bot repo database connection:

psql $DATABASE_URL -c "SELECT version, description, success FROM annie_mei._sqlx_migrations ORDER BY installed_on DESC;"

For auth service OAuth migration failures, check migration state from the auth repo:

cd ../auth
sqlx migrate info

For persistent auth issues, inspect the auth service _sqlx_migrations table directly.

Pool exhaustion

If you see connection timeout errors:

Check max_connections in create_pool() (default: 10)
Ensure PostgreSQL’s max_connections is higher than the pool limit
Look for long-running queries or missing await points

Next Steps

Architecture

Understand how database fits in the architecture

Adding Commands

Use database connections in commands

SQLx Documentation

Official SQLx repository and docs

​Overview

​Configuration

​Connection pool settings

​Migration ownership

​Bot repo migrations

​Auth repo schema migrations

​Creating auth schema migrations

​Running migrations

​Querying with SQLx

​Batch lookups

​Writing data

​Accessing the pool in commands

​Setup

​Install SQLx CLI for auth schema work

​Create the database

​Run auth migrations manually

​Check migration status

​Troubleshooting

​Next Steps

Architecture

Adding Commands

SQLx Documentation

Overview

Configuration

Connection pool settings

Migration ownership

Bot repo migrations

Auth repo schema migrations

Creating auth schema migrations

Running migrations

Querying with SQLx

Batch lookups

Writing data

Accessing the pool in commands

Setup

Install SQLx CLI for auth schema work

Create the database

Run auth migrations manually

Check migration status

Troubleshooting

Next Steps