Database Setup

The midPilot Connector Generator uses PostgreSQL for data persistence and Alembic for database schema migrations. This guide covers database setup and migration management.

Database Requirements

PostgreSQL 15+

PostgreSQL 15 or later with asyncpg driver support

The application uses:

SQLAlchemy - Async ORM for database operations
Alembic - Database migration tool
asyncpg - High-performance async PostgreSQL driver

Setup Methods

Choose your preferred database setup method:

Docker Compose (Recommended)
Manual Docker
Local PostgreSQL

The easiest way to get started is using the provided Docker Compose configuration.

Configure environment

Edit your .env file:

DATABASE__HOST=localhost
DATABASE__PORT=5432
DATABASE__NAME=connector_db
DATABASE__USER=connector_user
DATABASE__PASSWORD=secure_password_here

# Auto-constructed by the app
DATABASE__URL=postgresql+asyncpg://${DATABASE__USER}:${DATABASE__PASSWORD}@${DATABASE__HOST}:${DATABASE__PORT}/${DATABASE__NAME}

Start the database

# Start only the database service
docker compose up db -d

# Or start all services
docker compose up -d

The database will:

Start PostgreSQL 15 Alpine
Create the configured database
Run health checks
Persist data in postgres_data volume

Verify the database

# Check health status
docker compose ps

# Test connection
docker compose exec db psql -U connector_user -d connector_db -c "SELECT version();"

Run PostgreSQL manually using Docker:

Start PostgreSQL container

docker run --name postgres \
  -e POSTGRES_USER=connector_user \
  -e POSTGRES_PASSWORD=secure_password \
  -e POSTGRES_DB=connector_db \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  -d postgres:15-alpine

Configure .env

DATABASE__HOST=localhost
DATABASE__PORT=5432
DATABASE__NAME=connector_db
DATABASE__USER=connector_user
DATABASE__PASSWORD=secure_password

Test connection

docker exec -it postgres psql -U connector_user -d connector_db

Use a locally installed PostgreSQL server:

Install PostgreSQL

# Ubuntu/Debian
sudo apt-get install postgresql-15

# macOS
brew install postgresql@15

# Start service
sudo systemctl start postgresql  # Linux
brew services start postgresql@15  # macOS

Create database and user

# Connect as postgres user
sudo -u postgres psql

# Create user and database
CREATE USER connector_user WITH PASSWORD 'secure_password';
CREATE DATABASE connector_db OWNER connector_user;
GRANT ALL PRIVILEGES ON DATABASE connector_db TO connector_user;
\q

Configure .env

DATABASE__HOST=localhost
DATABASE__PORT=5432
DATABASE__NAME=connector_db
DATABASE__USER=connector_user
DATABASE__PASSWORD=secure_password

Database Configuration

Connection Parameters

The application supports both individual parameters and full connection URLs:

DATABASE__HOST=localhost
DATABASE__PORT=5432
DATABASE__NAME=connector_db
DATABASE__USER=connector_user
DATABASE__PASSWORD=secure_password

If DATABASE__URL is not provided or contains placeholders (e.g., ${DATABASE__USER}), the application will automatically construct it from individual parameters.

Connection Pool Settings

Configure the SQLAlchemy connection pool:

# Connection pool size
DATABASE__POOL_SIZE=10

# Maximum overflow connections
DATABASE__MAX_OVERFLOW=20

# Enable SQL query logging (for debugging)
DATABASE__ECHO=false

DATABASE__POOL_SIZE

integer

default:"10"

Number of connections to maintain in the pool

DATABASE__MAX_OVERFLOW

integer

default:"20"

Maximum connections beyond pool_size

DATABASE__ECHO

boolean

default:"false"

Log all SQL queries (useful for debugging, but verbose)

Alembic Migrations

Alembic manages database schema changes through migrations. The application includes migration files in the alembic/versions/ directory.

Running Migrations

Check current migration status

uv run alembic current

Shows the currently applied migration revision.

View migration history

uv run alembic history

Displays all available migrations.

Run all pending migrations

uv run alembic upgrade head

Applies all migrations up to the latest version.

Verify the schema

# Connect to database
docker compose exec db psql -U connector_user -d connector_db

# List tables
\dt

# Describe a table
\d table_name

Migration Commands

# Upgrade to latest
uv run alembic upgrade head

# Upgrade to specific revision
uv run alembic upgrade abc123

# Upgrade one version
uv run alembic upgrade +1

# Show SQL without executing
uv run alembic upgrade head --sql

Existing Migrations

The application includes these migrations:

001_initial_schema.py

Purpose: Creates initial database schemaTables created:

Base tables for connector storage
Initial indexes and constraints

# View migration
cat alembic/versions/001_initial_schema.py

002_add_scrape_job_ids_column_to_.py

Purpose: Adds job tracking columnChanges:

Adds scrape_job_ids column
Updates related indexes

# View migration
cat alembic/versions/002_add_scrape_job_ids_column_to_.py

003_add_normalized_input_column_to_jobs_.py

Purpose: Adds input normalization supportChanges:

Adds normalized_input column to jobs table
Enables input deduplication

# View migration
cat alembic/versions/003_add_normalized_input_column_to_jobs_.py

Alembic Configuration

The migration system is configured in alembic.ini:

[alembic]
script_location = alembic
prepend_sys_path = .

# Database URL loaded from DATABASE__URL environment variable
sqlalchemy.url =

The database URL is read from the DATABASE__URL environment variable by alembic/env.py, ensuring consistency with application configuration.

Database Management

Backup and Restore

# Backup database
docker compose exec db pg_dump -U connector_user connector_db > backup.sql

# Backup with timestamp
docker compose exec db pg_dump -U connector_user connector_db > backup_$(date +%Y%m%d_%H%M%S).sql

# Compressed backup
docker compose exec db pg_dump -U connector_user connector_db | gzip > backup.sql.gz

# Backup schema only
docker compose exec db pg_dump -U connector_user --schema-only connector_db > schema.sql

Reset Database

To completely reset the database:

Downgrade to base

uv run alembic downgrade base

This removes all tables created by migrations.

Re-run migrations

uv run alembic upgrade head

This recreates all tables with the latest schema.

Or using Docker:

# Stop and remove volumes
docker compose down -v

# Start fresh
docker compose up -d

# Run migrations
docker compose exec midpilot-connector-gen uv run alembic upgrade head

Access Database

# Interactive shell
docker compose exec db psql -U connector_user -d connector_db

# Run single query
docker compose exec db psql -U connector_user -d connector_db -c "SELECT COUNT(*) FROM jobs;"

# Execute SQL file
docker compose exec -T db psql -U connector_user -d connector_db < query.sql

Troubleshooting

Migration fails with 'relation already exists'

Symptom: Alembic migration fails because a table/column already existsSolution:

# Check current revision
uv run alembic current

# Mark migration as applied without running it
uv run alembic stamp head

# Or rollback and retry
uv run alembic downgrade -1
uv run alembic upgrade head

Connection refused

Symptom: could not connect to server: Connection refusedSolution:

# Check if database is running
docker compose ps db

# Check database logs
docker compose logs db

# Verify environment variables
env | grep DATABASE

# Test connection manually
docker compose exec db psql -U connector_user -d connector_db

Authentication failed

Symptom: FATAL: password authentication failedSolution:

Verify DATABASE__USER and DATABASE__PASSWORD in .env
Ensure user has correct permissions
Recreate user if needed:

docker compose exec db psql -U postgres
DROP USER IF EXISTS connector_user;
CREATE USER connector_user WITH PASSWORD 'new_password';
GRANT ALL PRIVILEGES ON DATABASE connector_db TO connector_user;

Database doesn't exist

Symptom: FATAL: database "connector_db" does not existSolution:

# Create database
docker compose exec db createdb -U postgres connector_db

# Grant permissions
docker compose exec db psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE connector_db TO connector_user;"

# Run migrations
uv run alembic upgrade head

Slow migrations

Symptom: Migrations take a long timeSolution:

Check database resources
Review migration SQL for inefficiencies
Run migrations during low-traffic periods

# Preview SQL without executing
uv run alembic upgrade head --sql > migration.sql

# Review the SQL
cat migration.sql

Production Best Practices

Regular Backups

Schedule automated backups with retention policies

Test Migrations

Test migrations in staging before production

Monitor Performance

Track query performance and connection pool usage

Use Connection Pooling

Tune pool_size and max_overflow for your workload

Next Steps

Configuration

Configure database connection settings

Docker Setup

Deploy with Docker Compose

API Reference

Start using the API

Get Started

Core Concepts

Guides

Database Requirements

PostgreSQL 15+

Setup Methods

Database Configuration

Connection Parameters

Connection Pool Settings

Alembic Migrations

Running Migrations

Migration Commands

Existing Migrations

Alembic Configuration

Database Management

Backup and Restore

Reset Database

Access Database

Troubleshooting

Production Best Practices

Regular Backups

Test Migrations

Monitor Performance

Use Connection Pooling

Next Steps

Configuration

Docker Setup

API Reference

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

​Database Requirements

PostgreSQL 15+

​Setup Methods

​Database Configuration

​Connection Parameters

​Connection Pool Settings

​Alembic Migrations

​Running Migrations

​Migration Commands

​Existing Migrations

​Alembic Configuration

​Database Management

​Backup and Restore

​Reset Database

​Access Database

​Troubleshooting

​Production Best Practices

Regular Backups

Test Migrations

Monitor Performance

Use Connection Pooling

​Next Steps

Configuration

Docker Setup

API Reference

Build docs developers (and LLMs) love

Database Requirements

Setup Methods

Database Configuration

Connection Parameters

Connection Pool Settings

Alembic Migrations

Running Migrations

Migration Commands

Existing Migrations

Alembic Configuration

Database Management

Backup and Restore

Reset Database

Access Database

Troubleshooting

Production Best Practices

Next Steps