Skip to content
Lesson 12 of 22

Databases for Vibe Coders

13 min read

When You Need a Database

Not every project needs a database from day one. But the moment your app needs to remember something after the user closes the browser, you need persistent storage.

Here are the signs:

  • Users create accounts and expect their data to be there when they come back
  • You're storing content that users generate — posts, tasks, messages, files
  • You need to search, filter, or sort data
  • Multiple users need to see the same data
  • You need to track history or changes over time

A database is a structured way to store, retrieve, and manage data. It's not a file full of JSON (though it can feel like that when you're starting out). It's a system designed for concurrent access, data integrity, and efficient queries.

Types of Databases

Relational databases (PostgreSQL, MySQL, SQLite) store data in tables with rows and columns. Tables can reference each other through relationships. They use SQL (Structured Query Language) for queries. This is the default choice for most web applications.

Document databases (MongoDB, Firebase Firestore) store data as flexible JSON-like documents. They're easier to get started with but harder to maintain as your data becomes more complex and interconnected.

For this course, we focus on relational databases because they're the industry standard for web apps, they work beautifully with Prisma, and AI generates excellent SQL and relational schemas.

SQLite vs. PostgreSQL

These are the two databases you'll encounter most in modern web development. Each has a clear use case.

SQLite: The Zero-Config Database

SQLite stores your entire database in a single file. No server to install, no configuration, no ports to manage. It's built into every smartphone and is the most widely deployed database engine in the world.

Best for:

  • Local development and prototyping
  • Small to medium apps with one server
  • Embedded applications
  • Learning and experimentation

Limitations:

  • Single writer at a time (no concurrent writes from multiple servers)
  • Doesn't support some advanced SQL features
  • Not ideal for high-traffic production apps

PostgreSQL: The Production Standard

PostgreSQL is a full-featured, client-server database. It runs as a separate process (or service) and handles concurrent connections, complex queries, full-text search, JSON storage, and much more.

Best for:

  • Production web applications
  • Apps with multiple servers or serverless functions
  • Complex queries and relationships
  • Apps that need full-text search or JSON columns

Decision Guide

Starting a new project? Use SQLite in development for speed and simplicity. Plan to switch to PostgreSQL for production. Prisma makes this switch nearly painless — you change one line in your config.

Development:  SQLite   → fast, zero setup, runs anywhere
Production:   PostgreSQL → scalable, concurrent, full-featured

Prisma ORM: Your AI-Friendly Database Layer

An ORM (Object-Relational Mapping) lets you work with your database using your programming language instead of writing raw SQL. Prisma is the best ORM for vibe coding because:

  1. Declarative schema — you describe your data model in a readable schema file, and Prisma generates everything else
  2. Type-safe queries — TypeScript knows the shape of your data, so you get autocomplete and error checking
  3. Readable API — prisma.user.findMany() reads like English
  4. AI-friendly — Prisma's schema format and query API are well-represented in AI training data

When you tell AI to "create a user with an email and password," and you're using Prisma, AI generates clean, type-safe code that works on the first try.

Setting Up Prisma

Installation

npm install prisma @prisma/client
npx prisma init

This creates a prisma/ directory with a schema.prisma file and a .env file for your database URL.

Configuring the Datasource

For SQLite (development):

// prisma/schema.prisma
datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

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

For PostgreSQL (production):

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

The env("DATABASE_URL") reads from your .env file:

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/myapp"

The Schema File

The schema.prisma file is the single source of truth for your database structure. Everything — tables, columns, relationships, indexes — is defined here. Prisma uses this file to generate the database client, create migrations, and provide type safety.

Schema Design with AI

This is where vibe coding shines. You describe your data model in natural language, and AI creates the Prisma schema. Here's how to think about it.

Describing Your Data to AI

Prompt: "Create a Prisma schema for a blog platform. Users can create posts. Posts can have multiple tags. Users can comment on posts. Each user has a profile with bio and avatar URL."

AI translates this into a schema:

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  password  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  profile   Profile?
  posts     Post[]
  comments  Comment[]
}

model Profile {
  id        String  @id @default(cuid())
  bio       String?
  avatarUrl String?
  userId    String  @unique
  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          String   @id @default(cuid())
  title       String
  content     String
  published   Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  authorId    String
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  comments    Comment[]
  tags        Tag[]
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  createdAt DateTime @default(now())

  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  postId    String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

Let's break down the relationship types this schema uses.

One-to-Many Relationships

A user has many posts. A post belongs to one user:

model User {
  posts Post[]    // One user → many posts
}

model Post {
  authorId String
  author   User   @relation(fields: [authorId], references: [id])
}

The authorId field stores the foreign key. The author field is a virtual relation — it doesn't create a column but lets you navigate between records.

Many-to-Many Relationships

Posts can have many tags. Tags can belong to many posts:

model Post {
  tags Tag[]
}

model Tag {
  posts Post[]
}

Prisma creates an implicit join table behind the scenes. You don't need to define it yourself (though you can if you need extra fields on the relationship).

One-to-One Relationships

A user has exactly one profile:

model User {
  profile Profile?   // Optional: user might not have a profile yet
}

model Profile {
  userId String @unique   // @unique enforces one-to-one
  user   User   @relation(fields: [userId], references: [id])
}

The @unique constraint on userId ensures each user can have at most one profile.

Indexes for Performance

As your data grows, queries slow down. Indexes speed up lookups on specific columns:

model Post {
  authorId  String
  createdAt DateTime @default(now())

  @@index([authorId])
  @@index([createdAt])
  @@index([authorId, createdAt])  // Composite index
}

Prompt: "Add indexes to the Post model for common queries: finding posts by author, listing recent posts, and finding posts by author sorted by date."

Migrations Workflow

Migrations are versioned changes to your database schema. When you modify your Prisma schema, you create a migration that describes what changed, then apply it to your database.

Development Workflow

# After changing schema.prisma:
npx prisma migrate dev --name add-post-status

This command:

  1. Compares your schema to the current database
  2. Generates a SQL migration file
  3. Applies the migration to your database
  4. Regenerates the Prisma client

Production Deployment

npx prisma migrate deploy

This applies all pending migrations in order. It never generates new migrations — only runs existing ones.

Why Migrations Matter

Migrations are a history of your database changes. They let you:

  • Track what changed and when
  • Roll forward (apply changes) on new environments
  • Ensure all environments have the same database structure
  • Collaborate with other developers without schema conflicts

Name your migrations meaningfully: add-user-roles, create-comments-table, add-post-published-field. Future you will thank present you.

Seeding Data

Seed scripts create initial data for development. They're essential for having realistic data to work with.

// prisma/seed.ts
import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

async function main() {
  // Create users
  const alice = await prisma.user.create({
    data: {
      email: "alice@example.com",
      name: "Alice Johnson",
      password: "hashed-password-here",
      profile: {
        create: { bio: "Full-stack developer and coffee enthusiast" },
      },
    },
  })

  // Create posts with tags
  await prisma.post.create({
    data: {
      title: "Getting Started with Prisma",
      content: "Prisma makes database access easy...",
      published: true,
      authorId: alice.id,
      tags: {
        connectOrCreate: [
          { where: { name: "prisma" }, create: { name: "prisma" } },
          { where: { name: "database" }, create: { name: "database" } },
        ],
      },
    },
  })

  console.log("Database seeded successfully")
}

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

Add to your package.json:

{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Run with:

npx prisma db seed

Prompt: "Create a seed script with 5 users, 20 posts distributed among them, relevant tags, and sample comments."

Common Query Patterns with Prisma

These are the operations you'll use every day. Learn the patterns, and you'll know exactly how to prompt AI for database operations.

Create

// Create one
const user = await prisma.user.create({
  data: { email: "bob@example.com", name: "Bob" },
})

// Create many
const users = await prisma.user.createMany({
  data: [
    { email: "user1@example.com", name: "User 1" },
    { email: "user2@example.com", name: "User 2" },
  ],
})

Read

// Find by unique field
const user = await prisma.user.findUnique({
  where: { email: "alice@example.com" },
})

// Find many with conditions
const posts = await prisma.post.findMany({
  where: { published: true, authorId: userId },
  orderBy: { createdAt: "desc" },
  take: 10,
  skip: 0,
})

// Include related data
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: { posts: true, profile: true },
})

Update

// Update one
const updated = await prisma.post.update({
  where: { id: postId },
  data: { title: "Updated Title" },
})

// Update many
await prisma.post.updateMany({
  where: { authorId: userId },
  data: { published: false },
})

// Upsert (create if doesn't exist, update if it does)
const profile = await prisma.profile.upsert({
  where: { userId: userId },
  update: { bio: "Updated bio" },
  create: { userId: userId, bio: "New bio" },
})

Delete

// Delete one
await prisma.post.delete({ where: { id: postId } })

// Delete many
await prisma.comment.deleteMany({ where: { postId: postId } })

The onDelete: Cascade in your schema handles automatic deletion of related records. When you delete a user, all their posts and comments are deleted too.

Filtering, Sorting, and Pagination

const results = await prisma.post.findMany({
  where: {
    published: true,
    title: { contains: "prisma", mode: "insensitive" },
    createdAt: { gte: new Date("2026-01-01") },
    tags: { some: { name: "database" } },
  },
  orderBy: [{ createdAt: "desc" }, { title: "asc" }],
  skip: (page - 1) * pageSize,
  take: pageSize,
  include: { author: { select: { name: true, email: true } } },
})

Aggregations

const stats = await prisma.post.aggregate({
  _count: true,
  _avg: { wordCount: true },
  where: { published: true },
})

const postsByAuthor = await prisma.post.groupBy({
  by: ["authorId"],
  _count: true,
  orderBy: { _count: { id: "desc" } },
})

Drizzle as an Alternative

Prisma isn't the only ORM. Drizzle ORM takes a different approach — it's SQL-first, meaning your TypeScript code closely mirrors the SQL it generates.

// Drizzle schema
import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core"

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  published: boolean("published").default(false),
  createdAt: timestamp("created_at").defaultNow(),
})

// Drizzle query
const results = await db.select().from(posts).where(eq(posts.published, true))

When to choose Drizzle over Prisma:

  • You prefer writing SQL-like code
  • You need maximum query performance (Drizzle generates more efficient SQL in some cases)
  • You want a lighter runtime (Drizzle has no query engine binary)

When to choose Prisma:

  • You want the most AI-friendly option (Prisma has more training data)
  • You prefer a higher-level, more abstracted API
  • You value the Prisma Studio GUI for browsing data
  • You're working with a team that includes non-SQL developers

Both are excellent. For this course, we use Prisma because AI generates better Prisma code out of the box.

Database Hosting

You need somewhere to host your production database. Here are the top options with free tiers:

Neon

Serverless PostgreSQL. Your database scales to zero when idle and wakes up instantly when needed. Perfect for apps with unpredictable traffic.

  • Free tier: 0.5 GB storage, 190 hours of compute
  • Best for: Next.js apps, serverless architectures

Supabase

PostgreSQL with extras — built-in auth, real-time subscriptions, storage, and an auto-generated REST API. It's like Firebase but with PostgreSQL.

  • Free tier: 500 MB storage, 2 projects
  • Best for: Apps that want an all-in-one backend

Turso

Edge-deployed SQLite. Your database runs close to your users globally. It's SQLite for production, using a protocol called libSQL.

  • Free tier: 9 GB storage, 500 databases
  • Best for: Read-heavy apps, edge computing

PlanetScale

Serverless MySQL with a Git-like branching workflow for schema changes. Each branch is an isolated database you can merge.

  • Best for: Teams that want safe schema changes with branch-based migrations

Prompt: "Set up the Prisma datasource to use Neon PostgreSQL. Configure the connection string for serverless with the ?sslmode=require parameter."

Prompt Patterns for Database Work

Here are proven prompts for common database tasks:

Schema design: "Create a Prisma schema for a task management app with users, projects, and tasks. Users belong to projects through a membership table with roles (admin, member). Tasks have status (todo, in_progress, done), priority (low, medium, high), and optional due dates."

Adding features: "Add a comments model to the schema. Users can comment on tasks. Comments have content and a created timestamp. Include the relation to both User and Task."

Query optimization: "Add pagination to the tasks query. Accept page and limit parameters, return the tasks along with total count and total pages."

Migration: "Create a migration to add a status field to the posts table with values 'draft', 'published', and 'archived'. Default to 'draft'."

Complex queries: "Write a query that returns the top 10 users by post count, including their total post count and the date of their most recent post."

Each of these prompts gives AI enough context to generate correct, production-ready code. Be specific about field types, relationships, and constraints.

What's Next

You now know how to choose, set up, and work with databases. You can design schemas, run migrations, seed data, and write queries — all by describing what you want to AI and understanding what it generates.

But all this data needs protection. In the next lesson, we'll implement authentication and authorization — making sure users are who they say they are and can only access what they're allowed to. You'll set up OAuth login, protect routes, and implement role-based access control.