Setting Up Local Development Databases: Docker, SQLite, and When Each Approach Wins

Every developer on your team interacts with the local development database multiple times per day. A painful setup — slow to start, difficult to reset, or different from production — creates friction that compounds across the entire team.

I have used every approach: Docker containers for Postgres/MySQL, local SQLite files, cloud-hosted development databases, and even mocked databases for tests. Each has clear strengths and failure modes.

Approach 1: Docker Containers (Postgres/MySQL)

Run the production database engine locally in a Docker container. Docker Compose starts it alongside your dev server.

Strengths: identical to production (same engine, same SQL dialect, same behavior), data is persistent across restarts (mounted volume), can run multiple databases for different projects (different ports), and team members share the same docker-compose.yml (consistent environments).

Weaknesses: Docker must be installed and running (Docker Desktop on macOS uses significant RAM), cold start takes a few seconds (container boot time), disk space accumulates (database volumes, Docker images), and Mac-specific: Docker filesystem performance is slower than native (affects write-heavy operations).

When to use: when your application uses Postgres/MySQL-specific features (JSON operators, full-text search, specific data types), when production parity matters (complex queries that behave differently across engines), and when the team is comfortable with Docker.

Our setup: docker-compose.yml defines the database service (Postgres 16, port 5432, volume for persistence). Run docker compose up and the database is available. Prisma connects through the standard connection string.

Approach 2: SQLite for Local Development

SQLite runs as a file — no server process, no Docker, no setup. Your database is a single file on disk.

Strengths: zero setup (no Docker, no installation, no server to start), instant startup (no process to boot — the file is just opened), trivially resettable (delete the file, re-seed), works everywhere (every OS supports SQLite natively), and perfect for CI (create database, run tests, discard).

Weaknesses: different SQL dialect from Postgres/MySQL (some queries behave differently), lacks features you might use in production (JSONB, materialized views, advanced indexing), single-writer limitation (concurrent writes queue — fine for development, problematic for production simulation), and some ORMs do not fully support switching between SQLite and Postgres.

When to use: when your application uses basic SQL (CRUD operations, simple joins, standard queries), when development speed matters more than production parity, when team members have varied setups (no Docker dependency), and for rapid prototyping where database behavior is not the focus.

AIDToolStack supports both: Prisma schema with a SQLite provider for local development and a MySQL provider for production. The switchable schema means local development is fast (no Docker needed) while production uses a managed MySQL instance.

Approach 3: Cloud Development Database

Use a cloud-hosted database instance for development. Each developer connects to a shared or individual cloud database.

Strengths: exact production environment (same managed service, same configuration), no local setup (just connection string in .env), always available (no Docker to start, no files to manage), and shared data for team debugging (everyone sees the same state).

Weaknesses: requires internet connection (offline development impossible), latency (cloud database adds network round-trip to every query — 50-200ms vs <1ms local), cost (cloud database instances cost money per developer), shared state problems (one developer's test data affects another), and data privacy (development data in the cloud may have compliance implications).

When to use: when production-parity is critical and Docker is not an option, when the team is distributed and needs shared development data, and for staging/QA environments (not typically for individual developer environments).

Our Recommended Setup

For most projects: Docker for anything requiring production parity, SQLite for rapid local iteration.

The pattern we follow at AIDToolStack:

Local development: SQLite database (zero setup, instant, perfect for feature development).

Integration tests: SQLite in-memory database (each test run gets a fresh database, no cleanup needed).

Staging: cloud MySQL instance (mirrors production, catches SQLite/MySQL incompatibilities before production).

Production: managed MySQL (Planetscale/AWS RDS with proper backups, scaling, and monitoring).

Database Seeding

Regardless of your database approach: seeding is essential. A fresh database with no data is useless for development. You need: realistic data (not "test test test" — actual tool names, descriptions, categories), sufficient volume (enough records to test pagination, search, and filtering), and reproducibility (every developer gets the same seed data from the same script).

Our seed script: creates users (admin and regular), populates 50+ tools with realistic metadata, creates categories and tags, generates blog posts, and sets up subscription tiers.

Running seed: prisma db seed (configured in package.json). One command, consistent development data. New team members run it once and have a fully functional local environment.

Database Reset Workflow

The developer workflow: database gets into a weird state (testing edge cases, interrupted migrations, conflicting seed data). Reset it.

With SQLite: delete the file (rm prisma/dev.db), re-run migrations (prisma migrate dev), and re-seed (prisma db seed). Takes 5 seconds.

With Docker Postgres: drop the database (docker compose exec db psql -c "DROP DATABASE devai"), re-create (docker compose exec db psql -c "CREATE DATABASE devai"), re-run migrations, and re-seed. Takes 15-30 seconds.

Make this a single command: npm run db:reset that handles the full cycle. Developers should not need to remember the steps.

Migration Workflow

Migrations in development: create a migration from schema changes (prisma migrate dev), apply to local database, commit migration files to git, and team members pull and run prisma migrate dev to apply.

Tip: never manually edit migration SQL files. If a migration is wrong: reset and re-create it. Manual edits create drift between the migration and the schema.

Environment Variable Management

Each approach needs different connection strings: SQLite uses a file path, Docker Postgres uses localhost with port, and cloud databases use remote URLs with credentials.

Pattern: .env.example with placeholder values (committed to git), .env.local with real values (gitignored), and documentation explaining which setup to use and how to configure.

For team coordination: document which database approach the project uses in the README. Do not make developers guess. "Run docker compose up -d to start the database" or "The project uses SQLite — no setup required."

The Decision Matrix

Team of 1-2, simple queries: SQLite (zero friction, instant setup).

Team of 3-10, standard web app: Docker Compose with Postgres/MySQL (production parity, consistent across team).

Team with varied OS/setups: SQLite for development, Docker for CI (avoids Docker Desktop requirement for developers on constrained machines).

Enterprise with compliance requirements: cloud development databases with access controls (audit trail, data governance).

The right choice depends on: team size, production database engine, query complexity, and how much friction you can tolerate in daily development. Choose the lowest-friction option that does not create production surprises.