• Home
  • Code Lab: New Project Setup

Code Lab: New Project Setup

Step-by-step guide to setting up a new project with PostgreSQL, Prisma ORM, and prisma-migrations

Code Lab: Starting a New Project with Prisma Migrations

This code lab walks you through setting up a new project with PostgreSQL, Prisma ORM, and prisma-migrations from scratch. You’ll learn how to create migrations, manage your database schema, and handle rollbacks.

What You’ll Build

By the end of this code lab, you’ll have:

  • A working Node.js project with PostgreSQL
  • Prisma ORM configured and connected
  • A migration system with rollback support
  • Two database tables (users and posts) with relationships
  • Understanding of the full migration workflow

Prerequisites

  • Node.js 20+ or Bun installed
  • PostgreSQL installed and running
  • Basic knowledge of SQL and TypeScript

Step 1: Initialize Your Project

Create a new directory and initialize a Node.js project:

mkdir my-new-project
cd my-new-project
npm init -y

Update your package.json to use ES modules:

{
  "name": "my-new-project",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "migrate": "prisma-migrations"
  }
}

Step 2: Install Dependencies

Install Prisma and prisma-migrations:

npm install @prisma/client
npm install -D prisma prisma-migrations

Step 3: Setup Prisma

Create a Prisma schema file:

mkdir prisma

Create prisma/schema.prisma:

// Prisma Schema for New Project
// Learn more: https://pris.ly/d/prisma-schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

// Models will be managed via prisma-migrations

Create a .env file with your database connection:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Replace user, password, and mydb with your actual PostgreSQL credentials.

Step 4: Initialize Prisma Migrations

Initialize the migrations directory:

npx prisma-migrations init

You should see output like:

✓ Created migration: 1234567890_initial_migration
  Location: ./prisma/migrations/1234567890_initial_migration

This creates migration.sql and down.sql inside the initial migration directory.

Step 5: Write the Initial Migration - Users Table

Open the generated migration.sql and add:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Open down.sql and add:

DROP TABLE IF EXISTS users CASCADE;

Step 6: Create Second Migration - Posts Table

Create another migration for posts:

npx prisma-migrations create create_posts_table

Put the forward change in the new migration.sql:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  author_id INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_posts_author_id ON posts(author_id);

Put the rollback in down.sql:

DROP TABLE IF EXISTS posts CASCADE;

Step 7: Check Migration Status

Before running migrations, check what’s pending:

npx prisma-migrations pending

You should see the initial users migration and the posts migration.

Check the overall status:

npx prisma-migrations status

Step 8: Run Migrations

Apply all pending migrations:

npx prisma-migrations up

You should see output confirming the migrations were applied:

┌──────────┬──────────────────────────────────────────────────┐
│ Status   │ Migrations                                       │
├──────────┼──────────────────────────────────────────────────┤
│ ✓        │ 3 migration(s) applied successfully              │
└──────────┴──────────────────────────────────────────────────┘

Verify your database now has the tables:

psql -d mydb -c "\dt"

Step 9: Generate Prisma Client

Generate the Prisma Client to interact with your database:

npx prisma generate

Step 10: Test Your Setup

Create a test script test.ts:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Create a user
  await prisma.$executeRaw`
    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Alice')
  `;

  // Get users
  const users = await prisma.$queryRaw`
    SELECT * FROM users
  `;
  console.log("Users:", users);

  // Create a post
  const [user] = await prisma.$queryRaw<Array<{ id: number }>>`
    SELECT id FROM users WHERE email = '[email protected]'
  `;

  await prisma.$executeRaw`
    INSERT INTO posts (title, content, author_id)
    VALUES ('My First Post', 'Hello World!', ${user.id})
  `;

  // Get posts with user info
  const posts = await prisma.$queryRaw`
    SELECT p.*, u.name as author_name
    FROM posts p
    JOIN users u ON p.author_id = u.id
  `;
  console.log("Posts:", posts);
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Run it:

npx tsx test.ts

Step 11: Test Rollback

Rollback the last migration:

npx prisma-migrations down

This removes the posts table. Verify:

npx prisma-migrations status

Re-apply the migration:

npx prisma-migrations up

Step 12: Advanced Operations

Rollback Multiple Migrations

npx prisma-migrations down --steps 2

Fresh Start (Rollback All + Re-run)

npx prisma-migrations fresh

Reset All Migrations

npx prisma-migrations reset

Interactive Mode

npx prisma-migrations up --interactive
npx prisma-migrations down --interactive

Next Steps

Now that you have a working setup:

  1. Add more migrations as your schema evolves
  2. Use the programmatic API in your application code
  3. Set up CI/CD to run migrations automatically
  4. Explore seeding data in migrations
  5. Add validation and constraints to your tables

Common Patterns

Data Seeding in Migrations

export async function up(prisma: PrismaClient): Promise<void> {
  // Create table
  await prisma.$executeRaw`
    CREATE TABLE roles (
      id SERIAL PRIMARY KEY,
      name VARCHAR(50) UNIQUE NOT NULL
    )
  `;

  // Seed initial data
  await prisma.$executeRaw`
    INSERT INTO roles (name) VALUES
    ('admin'),
    ('user'),
    ('moderator')
  `;
}

Adding Columns to Existing Tables

export async function up(prisma: PrismaClient): Promise<void> {
  await prisma.$executeRaw`
    ALTER TABLE users
    ADD COLUMN avatar_url VARCHAR(500),
    ADD COLUMN bio TEXT
  `;
}

export async function down(prisma: PrismaClient): Promise<void> {
  await prisma.$executeRaw`
    ALTER TABLE users
    DROP COLUMN avatar_url,
    DROP COLUMN bio
  `;
}

Creating Indexes

export async function up(prisma: PrismaClient): Promise<void> {
  await prisma.$executeRaw`
    CREATE INDEX idx_users_email ON users(email)
  `;
}

export async function down(prisma: PrismaClient): Promise<void> {
  await prisma.$executeRaw`
    DROP INDEX IF EXISTS idx_users_email
  `;
}

Troubleshooting

Migration Fails Midway

If a migration fails, check:

  1. Database connection is working
  2. SQL syntax is correct
  3. No conflicting constraints
  4. Previous migrations ran successfully

Rollback Doesn’t Work

Ensure your down() function properly reverses the up() function:

  • Drop tables created in up()
  • Remove columns added in up()
  • Delete data inserted in up()

Can’t Find Migrations

Check your configuration in .prisma-migrationsrc.json:

{
  "migrationsDir": "./migrations"
}

Summary

You’ve successfully:

  • Set up a new project with Prisma and prisma-migrations
  • Created multiple migrations with relationships
  • Learned the full migration workflow (create, run, rollback)
  • Tested your database with real data
  • Explored advanced operations

You now have a foundation for managing database migrations in your projects.