> ## Documentation Index
> Fetch the complete documentation index at: https://anniemei.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Understand database access, shared OAuth schema, and migrations for Annie Mei.

Annie Mei uses [SQLx](https://github.com/launchbadge/sqlx) for PostgreSQL database access and [PostgreSQL](https://www.postgresql.org/) for data storage. The bot uses an async connection pool for queries and runs its own SQLx migrations at startup.

<Warning>
  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.
</Warning>

## 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`:

```rust theme={}
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:

```text theme={}
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")`.

<Warning>
  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`.
</Warning>

### Bot repo migrations

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

### Auth repo schema migrations

```text theme={}
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:

```bash theme={}
cd ../auth
sqlx migrate add -r <migration_name>
```

This creates reversible SQLx migration files:

```text theme={}
migrations/YYYYMMDDHHMMSS_<migration_name>.up.sql
migrations/YYYYMMDDHHMMSS_<migration_name>.down.sql
```

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

### Running migrations

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

```bash theme={}
cd ../auth
sqlx migrate run
```

To inspect migration state:

```bash theme={}
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:

```rust src/models/db/oauth_credential.rs theme={}
#[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:

```rust theme={}
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:

```rust theme={}
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:

```rust theme={}
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:

```rust theme={}
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

```bash theme={}
cargo install sqlx-cli --no-default-features --features postgres
```

### Create the database

```bash theme={}
createdb annie_mei
```

Or using psql:

```sql theme={}
CREATE DATABASE annie_mei;
```

### Run auth migrations manually

```bash theme={}
cd ../auth
sqlx migrate run
```

### Check migration status

```bash theme={}
cd ../auth
sqlx migrate info
```

## Troubleshooting

<AccordionGroup>
  <Accordion title="Can't connect to database">
    Verify environment variables:

    ```bash theme={}
    echo $DATABASE_URL
    ```

    Test connection:

    ```bash theme={}
    psql $DATABASE_URL -c "SELECT 1"
    ```
  </Accordion>

  <Accordion title="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:

    ```bash theme={}
    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:

    ```bash theme={}
    cd ../auth
    sqlx migrate info
    ```

    For persistent auth issues, inspect the auth service `_sqlx_migrations` table directly.
  </Accordion>

  <Accordion title="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
  </Accordion>
</AccordionGroup>

## Next Steps

<CardGroup cols={2}>
  <Card title="Architecture" icon="sitemap" href="/development/architecture">
    Understand how database fits in the architecture
  </Card>

  <Card title="Adding Commands" icon="code" href="/development/adding-commands">
    Use database connections in commands
  </Card>

  <Card title="SQLx Documentation" icon="book" href="https://github.com/launchbadge/sqlx">
    Official SQLx repository and docs
  </Card>
</CardGroup>