Database

Rise uses PostgreSQL for data storage with SQLX for compile-time verified SQL queries and migrations.

Overview

Schema: Projects, Teams, Deployments, Service Accounts, Users

Schema Management

Rise uses SQLX migrations for database schema versioning.

Migrations Directory

Migrations in ./migrations/ (project root) with timestamp-based names.

Creating Migrations

sqlx migrate add <description>

Creates migrations/<timestamp>_<description>.sql. Edit and add SQL.

Running Migrations

Development: mise db:migrate (auto-run by mise backend:run)

Production: sqlx migrate run

Migration Best Practices

1. Test on production copy first
2. Use CREATE INDEX CONCURRENTLY in PostgreSQL
3. Avoid blocking operations on large tables
4. Test rollback procedures

SQLX Compile-Time Verification

.sqlx/ directory contains query metadata for offline builds.

cargo sqlx prepare              # Generate metadata
cargo sqlx prepare --check      # Verify cache

Regenerate after migrations or SQL query changes.

Writing Queries

Use sqlx::query! macro for compile-time verification (syntax, types, columns).

Database Access

Development

Connect to the local PostgreSQL database:

# Using psql
docker-compose exec postgres psql -U rise -d rise

# Or with connection string
psql postgres://rise:rise123@localhost:5432/rise

Common queries:

-- List all projects
SELECT * FROM projects;

-- Show deployment status
SELECT name, status, created_at FROM deployments ORDER BY created_at DESC LIMIT 10;

-- Count users
SELECT COUNT(*) FROM users;

-- Show team membership
SELECT t.name, u.email
FROM teams t
JOIN team_members tm ON t.id = tm.team_id
JOIN users u ON tm.user_id = u.id;

Production

Use read-only access for debugging:

# Connect with read-only user
psql postgres://rise_readonly:password@rds-endpoint:5432/rise

# Limit query results
\set LIMIT 100
SELECT * FROM projects LIMIT :LIMIT;

Never run write queries directly on production. Use migrations instead.

Resetting the Database

Development

Completely reset the development database:

# Remove database volume
docker-compose down -v

# Start fresh
mise backend:run

This deletes all data and re-runs migrations.

Soft Reset (Keep Schema)

Delete data without removing the schema:

# Connect to database
psql postgres://rise:rise123@localhost:5432/rise

# Truncate tables (preserves schema)
TRUNCATE deployments, projects, teams, team_members, users, service_accounts RESTART IDENTITY CASCADE;

Common Patterns

Transactions

Use transactions for multi-step operations:

#![allow(unused)]
fn main() {
let mut tx = pool.begin().await?;

sqlx::query!(
    "INSERT INTO projects (name, owner_type, owner_id) VALUES ($1, $2, $3)",
    name,
    "user",
    user_id
)
.execute(&mut *tx)
.await?;

sqlx::query!(
    "INSERT INTO audit_log (action, user_id) VALUES ($1, $2)",
    "create_project",
    user_id
)
.execute(&mut *tx)
.await?;

tx.commit().await?;
}

Optional Fields

Handle NULL columns:

#![allow(unused)]
fn main() {
let deployment = sqlx::query!(
    r#"
    SELECT id, name, expires_at
    FROM deployments
    WHERE id = $1
    "#,
    deployment_id
)
.fetch_one(&pool)
.await?;

// expires_at is Option<DateTime<Utc>>
if let Some(expiry) = deployment.expires_at {
    println!("Expires at: {}", expiry);
}
}

Custom Types

Use Postgres ENUM types:

CREATE TYPE visibility AS ENUM ('public', 'private');

ALTER TABLE projects ADD COLUMN visibility visibility NOT NULL DEFAULT 'public';

In Rust:

#![allow(unused)]
fn main() {
#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "visibility", rename_all = "lowercase")]
enum Visibility {
    Public,
    Private,
}
}

Performance Considerations

Indexes

Create indexes for frequently queried columns:

-- Lookups by owner
CREATE INDEX idx_projects_owner ON projects(owner_type, owner_id);

-- Deployment status queries
CREATE INDEX idx_deployments_status ON deployments(status) WHERE status != 'stopped';

-- Expiration cleanup
CREATE INDEX idx_deployments_expires_at ON deployments(expires_at) WHERE expires_at IS NOT NULL;

Connection Pooling

Configure connection pool size in config/production.toml based on load and database limits.

Query Optimization

Use EXPLAIN ANALYZE to optimize slow queries:

EXPLAIN ANALYZE
SELECT * FROM deployments
WHERE project_id = 123 AND status = 'running'
ORDER BY created_at DESC;

Troubleshooting

“Migrations have not been run”

Problem: Backend can’t start because migrations are pending.

Solution:

mise db:migrate

“SQLX cache is out of date”

Problem: Query metadata doesn’t match actual database schema.

Solution:

cargo sqlx prepare

“Connection refused”

Problem: Can’t connect to PostgreSQL.

Solution:

# Check if PostgreSQL is running
docker-compose ps postgres

# Check logs
docker-compose logs postgres

# Restart
docker-compose restart postgres

Deadlocks

Problem: Transactions blocking each other.

Solution:

• Keep transactions short
• Always acquire locks in the same order
• Use SELECT ... FOR UPDATE NOWAIT to fail fast

Next Steps

• Learn about local development: See Local Development
• Production database setup: See Production Setup