What Is a Database? Every Real App Has One — Here's What AI Just Built

What Is a Database?

A database is organized, permanent storage for data. Your app's data — user accounts, posts, orders, messages, preferences, settings — all lives in a database. When your app needs that data, it asks the database. When your app creates or changes data, it tells the database to save it.

Here's the most important thing to understand: variables exist in memory and disappear when your app stops. Databases persist forever.

Think about Instagram. Every photo, every comment, every follower relationship, every notification — that's billions of rows in databases. When you open the app, it queries the database to load your feed. When you post a photo, it inserts a new row. When you log out and come back a week later, all your data is still there because it was saved to a database, not a variable.

Every real app has a database. Simple apps might have one. Complex apps might have several. You cannot build anything that persists user data without one.

Why AI Coders Need to Understand Databases

When you ask AI to build anything beyond a static page, it will create a database schema. Every time. Ask Claude to "build a user authentication system" — it creates user tables. Ask Cursor to "add a contact form that saves submissions" — it creates a submissions table. Ask AI to "build a todo app" — you get User and Todo tables with relationships.

The problem is that AI generates this code confidently, but it sometimes makes subtle mistakes that cause security issues, performance problems, or outright bugs. If you can't read the schema it generated, you can't catch those mistakes.

When AI generates a Prisma schema, SQL CREATE TABLE statements, or Supabase configuration — that's database design. Reading this article means you'll understand what that code is doing, why, and what to check for.

Real Scenario: You Asked AI to Build a Todo App

What AI Generated: A Real Prisma Schema

Here's the exact Prisma schema Claude might generate for a basic todo app with user accounts. Read through it, then we'll break down every part:

// schema.prisma — what Claude generated for a todo app with auth

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

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  todos     Todo[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Todo {
  id        String   @id @default(cuid())
  title     String
  completed Boolean  @default(false)
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

This looks complex if you've never seen it before. But each part is doing something specific and logical. Let's decode it.

Understanding Each Part

The generator and datasource blocks

These two blocks at the top configure how Prisma works:

• generator client — Tells Prisma to generate a JavaScript client library that your app uses to talk to the database.
• datasource db — Tells Prisma which database to connect to. provider = "postgresql" means you're using PostgreSQL. url = env("DATABASE_URL") means the actual database connection string is stored in your .env file — never in the code itself.

Models = Tables

Each model in Prisma becomes a table in your database. A table is like a spreadsheet where every row is one record and every column is one field.

In this schema:

• User model → a users table in PostgreSQL. Each row is one user account.
• Todo model → a todos table. Each row is one todo item.

Primary Keys and IDs

Every model has an id field:

id  String  @id  @default(cuid())

Breaking this down:

• String — the data type. This ID will be a string, not a number.
• @id — marks this as the primary key. Every table needs one unique identifier per row. This is how you look up a specific record.
• @default(cuid()) — automatically generates a unique ID when a new record is created. cuid() creates a collision-resistant, URL-friendly ID like clh7f6p0q0000356e4u2mopxt. You never set this manually — the database generates it.

Data Types

Every field has a type that tells the database what kind of data to expect and enforce:

The ? after a type (like String? for name) means the field is optional — it can be null. Without the ?, the field is required and must have a value.

The @unique Constraint

Look at the email field:

email  String  @unique

@unique is a database constraint that says: no two rows in this table can have the same email value. If you try to create a second user with an email that already exists, the database will reject it with a "unique constraint violation" error.

This is how your app prevents duplicate accounts. Without @unique, two users could register with the same email address, causing login to break in confusing ways. This is a critical constraint — always verify it's on email fields.

Automatic Timestamps

createdAt DateTime @default(now())
updatedAt DateTime @updatedAt

@default(now()) automatically sets the timestamp to the current time when a record is created. You never set this manually — the database handles it.

@updatedAt is a Prisma-specific decorator that automatically updates this timestamp every time the record is changed. Together, these two fields give you a complete audit trail of when every record was created and last modified.

Relationships: One User Has Many Todos

This is the most important concept in relational databases — and the part that trips up most beginners. Look at these two fields in the Todo model:

userId  String
user    User   @relation(fields: [userId], references: [id])

And this field in the User model:

todos  Todo[]

Here's what's happening:

• userId String — This is a "foreign key." Every Todo stores the ID of the User who owns it. This is how the database knows which todos belong to which user.
• user User @relation(...) — This tells Prisma how to join the tables. "The userId field in this Todo references the id field in the User table." This is the relationship definition.
• todos Todo[] — In the User model, this field doesn't store data in the database — it's a virtual field that Prisma uses to let you query a user's todos easily. The [] means "an array of Todos."

In plain English: one User has many Todos. Each Todo belongs to exactly one User. This is called a "one-to-many" relationship and it's the most common relationship pattern in databases.

SQL vs NoSQL: What AI Chooses and Why

When you ask AI to build a database, it will choose between two fundamental types. Understanding the difference helps you ask better questions and catch when AI made the wrong choice for your use case.

When AI Picks Each One

• Next.js app with auth → AI almost always chooses PostgreSQL + Prisma. Structured data, type safety, migrations.
• Firebase or React Native app → AI often chooses Firestore. Real-time sync, offline support, tight Firebase integration.
• Quick local prototype → AI might choose SQLite. Zero configuration, no cloud account needed, file-based.
• High-scale real-time features → AI might suggest MongoDB. Flexible documents, horizontal scaling.

Supabase: The Best Starting Point for Vibe Coders

Supabase is what happens when someone builds a developer-friendly layer on top of PostgreSQL. You get a full PostgreSQL database, a visual Studio interface to see your tables and data, built-in authentication, auto-generated REST and GraphQL APIs, and a generous free tier — all in one place.

What AI Gets Wrong About Databases

AI generates database schemas confidently, but there are consistent patterns of mistakes. Here's what to check every time:

1. Missing Indexes on Frequently Queried Columns

An index is a data structure that makes looking up rows by a specific column fast. Without an index on commonly queried columns, your app has to scan the entire table to find a match — which is fine for 100 rows, catastrophic for 1 million rows.

AI often forgets to add indexes. Check: any column you frequently query by (WHERE email = ?, WHERE userId = ?, WHERE status = ?) should have an index. In Prisma, that's @@index([columnName]). Ask AI: "Review this schema and add appropriate database indexes for the query patterns in this app."

2. Storing Passwords as Plain Text

This is the most dangerous database mistake possible. If AI generates a User model with a password String field and your app stores passwords directly, you have a critical security vulnerability. When (not if) your database leaks, every user's password is exposed in plain text.

Passwords must always be hashed using bcrypt or Argon2 before storing. The stored value should look like $2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LEWM4K3AzsBEEMN7i, not mysecretpassword.

3. No Data Validation Constraints

AI often creates schemas that accept any data without enforcing rules. A String field without constraints can store an empty string, a 10,000-character rant, or invalid data that breaks your app. Real schemas need constraints: required fields should be NOT NULL, email fields should use @unique, lengths should be limited where it matters.

4. Over-Engineering Simple Schemas

AI sometimes adds three tables where one field would do. If your app stores a user's preferences, AI might generate a separate UserPreferences model with a foreign key relationship. But for simple, non-queryable preferences, a preferences Json column on the User model is simpler, faster, and perfectly appropriate. More tables isn't always better.

5. Not Handling Null Query Results

Prisma's findUnique and findFirst return null when no record is found. AI-generated code often doesn't check for this:

// AI-generated code that can crash your app:
const user = await prisma.user.findUnique({ where: { id: userId } });
return user.email; // 💥 TypeError if user is null

// Correct version:
const user = await prisma.user.findUnique({ where: { id: userId } });
if (!user) {
  return res.status(404).json({ error: "User not found" });
}
return user.email; // Safe — we know user exists

How to Debug Database Issues with AI

Database errors have intimidating messages but predictable causes. Here are the ones you'll see most often:

Common Error Messages and What They Mean

• "relation does not exist" — PostgreSQL can't find the table you're trying to query. This usually means your migrations haven't run. Fix: run npx prisma migrate dev or check if you're connected to the right database.
• "unique constraint violation" — You tried to insert a row with a value that already exists in a @unique column (usually email). Your app should catch this and return a user-friendly error like "An account with this email already exists."
• "foreign key constraint violation" — You tried to create a row that references an ID that doesn't exist in the parent table. For example, creating a Todo with a userId that doesn't exist in the users table. Or trying to delete a User who still has Todos (if you haven't set up cascade delete).

Prisma Migration Errors

Prisma migrations apply your schema changes to the actual database. The most common migration error is a conflict between your schema and existing data. For example, adding a NOT NULL column to a table that already has rows — what value should those existing rows have?

When migrations fail, ask AI: "My Prisma migration failed with this error: [paste error]. My current schema is [paste schema] and I'm trying to add [describe change]. How do I fix this migration?"

Visual Tools That Make Databases Approachable

• Supabase Studio — Built into Supabase. Visual table browser, query editor, relationship viewer. Start here if you're on Supabase.
• TablePlus — A desktop app that connects to any database (PostgreSQL, MySQL, SQLite, Redis). Excellent for browsing and editing data visually. Free tier available.
• Prisma Studio — Run npx prisma studio in your project to open a browser-based data browser for your Prisma database. Built-in and free.

The Full Picture: How Your App Talks to the Database

Here's the flow when a user signs up on your todo app:

1. User fills out the signup form in the browser and clicks submit.
2. Your JavaScript frontend sends a request to your API.
3. The API receives the request, hashes the password with bcrypt.
4. The API calls prisma.user.create({ data: { email, name, password: hashedPassword } }).
5. Prisma translates that into SQL: INSERT INTO users (id, email, name, password, created_at) VALUES ...
6. PostgreSQL executes the SQL and saves the new row to disk.
7. Prisma returns the created user object to your API.
8. Your API responds to the frontend with the new user data (minus the password).

Every single one of those steps happened because you asked AI to "build user signup." Understanding this flow means you can debug any failure point in the chain.

FAQ