All Posts

Creating and Managing PostgreSQL with Docker, Drizzle ORM, and Supabase

This guide shows you how to set up PostgreSQL using Docker for local development and Supabase for production. We'll use Drizzle ORM to interact with both databases using the same code.

Requirements#

Before we begin, ensure you have the following installed:

Initial Setup#

Project Creation

Create a new Next.js project and install dependencies:

Terminal
// Create project
pnpm create next-app@latest your_app_name --typescript
cd your_app_name
 
// Install dependencies
pnpm add drizzle-orm postgres @neondatabase/serverless
pnpm add -D drizzle-kit

Project Structure

Organize your project files like this:

Project Structure
.
├── app/
│   └── api/
│       └── hello/
│           └── route.ts    // API endpoints
├── drizzle/
│   ├── schema.ts           // Database tables
│   └── index.ts            // Database client
├── .env                    // Environment variables
├── docker-compose.yml      // Local database
├── drizzle.config.ts       // Drizzle configuration
└── package.json            // Project dependencies

Package Configuration

  1. Configure Drizzle in drizzle.config.ts:
drizzle.config.ts
import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
  schema: "./drizzle/schema.ts",
  out: "./drizzle/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});
  1. Add scripts to package.json:
package.json
{
  "scripts": {
    // Next.js commands
+   "dev": "next dev --turbo",
+   "build": "next build",
+   "start": "next start",
 
    // Database commands
+   "db:generate": "pnpm drizzle-kit generate",
+   "db:push": "dpnpm drizzle-kit push",
+   "db:studio": "pnpm drizzle-kit studio",
 
    // Docker commands
+   "docker:up": "docker-compose up -d",
+   "docker:down": "docker-compose down",
+   "docker:logs": "docker-compose logs -f",
 
    // Combined commands
+   "dev:db": "pnpm docker:up && pnpm dev",
+   "setup:local": "pnpm docker:up && pnpm db:push"
  }
}

Database Setup#

Local Setup with Docker

Create docker-compose.yml for local development:

docker-compose.yml
version: "3.9"
 
services:
  postgres:
    image: postgres:latest
    container_name: your_container_name
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: your_username
      POSTGRES_PASSWORD: your_password
      POSTGRES_DB: your_database_name
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U your_username -d your_database_name"]
      interval: 5s
      timeout: 5s
      retries: 5
 
volumes:
  postgres_data:

Production Setup with Supabase

  1. Go to supabase.com
  2. Create a new project e.g., your_project_name
  3. Navigate to Project Settings → Database
  4. Copy your connection string format: postgresql://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase co:5432/postgres

Environment Configuration

Create .env file with your database URLs:

.env
DATABASE_URL="postgresql://your_username:your_password@localhost:5432/your_database_name"
SUPABASE_DATABASE_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:5432/postgres"

Database Implementation#

Schema Definition

Create drizzle/schema.ts:

drizzle/schema.ts
import { pgTable, serial, text } from "drizzle-orm/pg-core";
 
// Simple message table
export const messages = pgTable("messages", {
  id: serial("id").primaryKey(),
  text: text("text").notNull(),
});

Database Connection

Create drizzle/index.ts:

drizzle/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
 
// Get database URL based on environment
const databaseUrl =
  process.env.NODE_ENV === "production"
    ? process.env.SUPABASE_DATABASE_URL
    : process.env.DATABASE_URL;
 
if (!databaseUrl) {
  throw new Error("No database URL provided");
}
 
// Configure database client
const client = postgres(databaseUrl, {
  ssl: process.env.NODE_ENV === "production",
  max: 1,
});
 
// Create database instance
export const db = drizzle(client);

API Implementation

Create app/api/hello/route.ts:

app/api/hello/route.ts
import { db } from "@/drizzle";
import { messages } from "@/drizzle/schema";
import { NextResponse } from "next/server";
 
// Get the latest message
export async function GET() {
  try {
    const [message] = await db.select().from(messages).limit(1);
    return NextResponse.json({
      message: message?.text ?? "No message yet",
    });
  } catch (error) {
    return NextResponse.json({ message: "Hello, World!" }, { status: 500 });
  }
}
 
// Save a new message
export async function POST(request: Request) {
  try {
    const { text } = await request.json();
    await db.insert(messages).values({ text });
    return NextResponse.json({ success: true });
  } catch (error) {
    return NextResponse.json(
      { error: "Failed to save message" },
      { status: 500 },
    );
  }
}

Running and Testing#

Setup Commands

  1. Replace placeholder values:

  2. Start the database and push schema:

Terminal
pnpm setup:local
  1. Start development server:
Terminal
pnpm dev:db

Testing the API

Test your endpoints using curl:

Terminal
curl -X POST http://localhost:3000/api/hello \
  -H "Content-Type: application/json" \
  -d '{"text":"Hello, Database!"}'
 
curl http://localhost:3000/api/hello

Best Practices & Troubleshooting#

Development Best Practices

Production Best Practices

Common Issues

  1. Docker Connection Issues:

  2. Schema Push Failures:

Useful Commands