Skip to content

Database and Migrations

This guide will cover how to manage your PostgreSQL database with Docker and handle migrations using Alembic. The guide is designed to help you set up your database for local development, manage migrations, and follow best practices for production environments.

Table of Contents

  1. Setting Up the Database Locally with Docker
  2. Running Database Migrations
  3. Alembic Commands Manual
  4. Best Practices for Production Databases
  5. Troubleshooting Common Database Issues

Setting Up the Database Locally with Docker

The project uses Docker to set up and manage the PostgreSQL database for local development. Follow these steps to get your database up and running:

  1. Ensure Docker is running on your local machine. You can install Docker by following the official Docker installation guide.

  2. Start the PostgreSQL database using Taskfile:

task docker-compose

This command will:

  • Spin up a Docker container running PostgreSQL.
  • Initialize the database based on the environment variables in your local.env file.

  • Apply any pending migrations via Alembic.

  • Access the database:

  • Username: Defined in local.env (e.g., db_username=craftyourstartup)

  • Password: Defined in local.env (e.g., db_password=craftyourstartup)
  • Port: 54323 (customized for local development)

You can use a PostgreSQL client like DBeaver or command line tools to connect to the database using the above credentials.

We recommend using DBeaver, a free and open-source database tool that provides a graphical interface for viewing and managing your PostgreSQL database. It simplifies tasks like querying the database, managing tables, and performing maintenance operations.

  • To connect to your local PostgreSQL database in DBeaver:
    1. Open DBeaver and click on New Database Connection.
    2. Select PostgreSQL as the database type.
    3. Enter your connection details from the local.env file, such as:
      • Host: localhost
      • Port: 54323
      • Username: craftyourstartup
      • Password: craftyourstartup
    4. Click Finish to establish the connection.

Running Database Migrations

All database migrations are handled through Alembic. To simplify the process, we use Poetry and Taskfile. The key commands are wrapped in tasks, so you don't have to run the Alembic commands directly.

Running Migrations Locally

To create a new migration or apply migrations to the local database, use the following tasks:

  • Create a migration revision:
task alembic-revision-local -- "your_message_here"

This will generate a new migration file based on the changes in your SQLAlchemy models.

  • Apply migrations:
task alembic-upgrade-local

This command ensures the local database schema is updated with the latest changes defined in the Alembic migration scripts.

Running Migrations in Production

To create and apply migrations in production, use the equivalent production tasks:

  • Create a production migration revision:
task alembic-revision-prod -- "your_message_here"
  • Apply migrations in production:
task alembic-upgrade-prod

Alembic Commands (Manual)

If you need to run any Alembic commands manually (e.g., downgrades), ensure that the environment variables are loaded before running them with Poetry:

  • Rollback migrations:
set -o allexport && source local.env && set +o allexport && poetry run alembic downgrade -1

This rolls back the last applied migration. You can also specify a migration ID to rollback to a specific point.

Best Practices for Production Databases

When deploying to production, follow these best practices for database management:

  1. Automated Backups:

    • Ensure regular backups are configured (e.g., using pg_dump or managed cloud database services like AWS RDS).
    • Store backups in secure, redundant locations.

  2. Monitor Database Performance:

    • Use monitoring tools to track database performance (e.g., pg_stat_statements in PostgreSQL).
    • Configure alerts for key metrics (e.g., connection count, slow queries).

  3. Use SSL Connections:

    • In production, ensure that all database connections are secured using SSL by setting db_sslmode=require.

  4. Scaling:

    • Plan for database scaling, especially for production environments. Use managed services that offer read replicas and autoscaling (e.g., AWS RDS, Google Cloud SQL).

Troubleshooting Common Database Issues

Here are some common database issues and their solutions:

  1. Database Connection Issues:

    • Ensure Docker is running and the database container is up using docker ps.
    • Verify that the db_host, db_port, and db_password values in your .env file are correct.

  2. Failed Migrations:

    • Check the Alembic versions/ directory to see if a migration script has errors or is incomplete.
    • Run set -o allexport && source local.env && set +o allexport && poetry run alembic current to ensure your database is on the correct migration state.

  3. Permission Errors:

    • If you encounter permission errors, ensure that the PostgreSQL user defined in your .env file has the required privileges.

  4. Docker Container Not Starting:

    • Check the Docker logs with docker logs <container_name> to identify any startup issues, such as port conflicts or misconfigured environment variables.

By following this guide, you should have your database set up and running smoothly, both for local development and production environments.