Skip to content

Database & Migrations

PostgreSQL database with Alembic migration management.

Quick Start

Bash
1
2
3
4
5
6
7
8
# Start PostgreSQL
task db:docker-start

# Apply migrations
task db:migrate-up

# Create first user (Top G+)
task db:user-create -- --email [email protected]

Database Setup

Start Database

Bash
1
2
3
4
5
# Start PostgreSQL with Docker Compose
task db:docker-start

# Verify it's running
docker ps | grep postgres

Database accessible at:

  • Host: localhost
  • Port: 54323
  • Database: craftyourstartup
  • Username: craftyourstartup
  • Password: craftyourstartup

Connect to Database

Using psql:

Bash
1
psql -h localhost -p 54323 -U craftyourstartup -d craftyourstartup

Using GUI (DBeaver recommended):

  • Host: localhost
  • Port: 54323
  • Database: craftyourstartup
  • Username: craftyourstartup
  • Password: craftyourstartup

Database Models

All Tiers

User:

  • id (UUID)
  • email (unique)
  • password_hash
  • full_name
  • created_at, last_login
  • verified, is_superuser
  • articles (relationship)

Article:

  • id (int)
  • title, content, author
  • published_at, is_published
  • user_id (foreign key)

Top G & AI Velocity Only

Purchase (one-time payments):

  • id, user_id
  • product_type, price_id
  • transaction_id (Stripe)
  • amount, currency
  • purchase_date, is_successful

Subscription (recurring payments):

  • id, user_id
  • stripe_subscription_id
  • plan, status
  • start_date, end_date

Migrations

Create Migration

After modifying models in app/models.py:

Bash
1
2
3
4
5
6
# Create migration
task db:migrate-create -- "add new field to user"

# Review generated file in migrations/versions/
# Then apply
task db:migrate-up

Apply Migrations

Bash
1
2
3
4
5
# Local
task db:migrate-up

# Production
task db:migrate-up-prod

Check Migration Status

```bash

Current version

task db:migrate-current

Migration history

task db:migrate-history

Full status

task db:migrate-status

Text Only
1
2
3
4
5
6
7
8
### Rollback Migration

```bash
# Rollback one migration
task db:migrate-down

# Production (CAUTION!)
task db:migrate-down-prod

Manual Alembic Commands

If you need direct Alembic access:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# Auto-generate migration
poetry run alembic revision --autogenerate -m "description"

# Apply migrations
poetry run alembic upgrade head

# Rollback one
poetry run alembic downgrade -1

# Show current
poetry run alembic current

# Show history
poetry run alembic history

Migration Workflow

1. Modify Model

Python
1
2
3
4
# app/models.py
class User(UUIDModelBase, table=True):
    # ... existing fields
    avatar_url: Optional[str] = None  # NEW FIELD

2. Create Migration

```bash task db:migrate-create -- "add avatar_url to user"

Text Only
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
### 3. Review Migration

Check `migrations/versions/latest_file.py`:

```python
def upgrade() -> None:
    op.add_column('user', sa.Column('avatar_url', sa.String(), nullable=True))

def downgrade() -> None:
    op.drop_column('user', 'avatar_url')

4. Apply Migration

```bash task db:migrate-up

Text Only
1
2
3
4
5
6
### 5. Update Code

```python
# app/schemas/user.py
class UserUpdate(SQLModel):
    avatar_url: Optional[str] = None

6. Generate API Client

Bash
1
task frontend:generate-client

Common Issues

Database connection refused

```bash

Check Docker is running

docker ps

Restart database

docker-compose down docker-compose up -d

Wait 10 seconds

task db:migrate-up

Text Only
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
### Migration fails

  ```bash
# Check current status
task db:migrate-current

# Manual rollback if needed
poetry run alembic downgrade -1

# Fix migration file
# Re-apply
task db:migrate-up

Duplicate migration

Bash
1
2
3
# Delete duplicate file from migrations/versions/
# Recreate properly
task db:migrate-create -- "description"

Can't connect with psql

```bash

Ensure using correct port

psql -h localhost -p 54323 -U craftyourstartup

Check db_port in local.env matches Docker Compose

Text Only
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
## Production Database

### Setup

Use managed database:

- Railway PostgreSQL
- Digital Ocean Managed Database
- AWS RDS
- Google Cloud SQL

Configure in `prod.env`:
```env
db_host=your-prod-host.com
db_port=5432
db_database=craftyourstartup_prod
db_username=prod_user
db_password=secure_password
db_sslmode=require

Apply Migrations

Bash
1
ENV_FILE=prod.env task db:migrate-up-prod

Or:

Bash
1
task db:migrate-up-prod

Backup Before Migrations

Bash
1
2
3
4
5
6
7
8
# Backup database
pg_dump -h $DB_HOST -U $DB_USER $DB_NAME > backup.sql

# Apply migration
task db:migrate-up-prod

# If issues, restore
psql -h $DB_HOST -U $DB_USER $DB_NAME < backup.sql

Database Tools

Recommended:

For inspection:

Bash
1
2
3
4
5
6
7
8
# Tables
\dt

# Table structure
\d user

# Query
SELECT * FROM user LIMIT 10;

Learning Resources

Learn FastAPI & Databases: Free interactive tutorials covering FastAPI with PostgreSQL.