You can build tables, write queries, set up relationships and work with Prisma. Nice. Now for the habits that turn a database that works into one that's actually ready for production. These are the practices that keep your app fast, secure and reliable once real people start using it.
Indexing: Making Queries Fast
An index works like the index in the back of a textbook. You don't read every page hunting for a topic, you look it up and jump straight to the right page. Database indexes do the exact same thing.
Without an Index
Example
-- Without an index on 'email', the database reads EVERY row
-- to find the one matching email. With 1 million users, that's slow!
SELECT * FROM users WHERE email = 'alice@example.com';
Creating an Index
Example
-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);
-- Now the same query is nearly instant, even with millions of rows!
SELECT * FROM users WHERE email = 'alice@example.com';
In Prisma, you add indexes right in your schema.
Example
model User {
id Int @id @default(autoincrement())
email String @unique // @unique automatically creates an index!
name String
@@index([name]) // Add an index on name
}
model Post {
id Int @id @default(autoincrement())
authorId Int
published Boolean
createdAt DateTime @default(now())
@@index([authorId, published]) // Compound index on two columns
}
When to Add Indexes
Add indexes on the columns you use a lot to do any of these.
Filter with WHERE (WHERE email = ...)
Sort with ORDER BY (ORDER BY created_at)
Join on (JOIN ... ON user_id = ...)
Search with LIKE (WHERE name LIKE 'A%')
Normalization: Organizing Data Cleanly
Normalization means structuring your data so every piece of info lives in exactly ONE place. That keeps things consistent and stops you from wasting space.
Bad: Denormalized (Repeated Data)
Example
-- Order data with customer info repeated in every row
-- order_id | customer_name | customer_email | product | price
-- 1 | Alice | alice@example.com | Laptop | 999
-- 2 | Alice | alice@example.com | Mouse | 29
-- 3 | Alice | alice@example.com | Keyboard | 79
If Alice changes her email, you've got three rows to update. Miss one and now your data disagrees with itself.
Good: Normalized (No Repetition)
Example
-- Users table (each user stored once)
-- id | name | email
-- 1 | Alice | alice@example.com
-- Orders table (references users by ID)
-- id | user_id | product | price
-- 1 | 1 | Laptop | 999
-- 2 | 1 | Mouse | 29
-- 3 | 1 | Keyboard | 79
Now Alice's info lives in one spot. Update it once and it's right everywhere.
Security: Preventing SQL Injection
SQL injection is one of the most dangerous AND most common web security holes out there. It happens when user input gets dropped straight into a SQL query.
The Vulnerability
Example
// EXTREMELY DANGEROUS - Never do this!
const query = `SELECT * FROM users WHERE email = '${userInput}'`;
// If userInput is: ' OR '1'='1' --
// The query becomes:
// SELECT * FROM users WHERE email = '' OR '1'='1' --'
// This returns ALL users! The attacker just bypassed your login.
An attacker could even wipe out a whole table.
Example
// If userInput is: '; DROP TABLE users; --
// The query becomes:
// SELECT * FROM users WHERE email = ''; DROP TABLE users; --'
// Your users table is gone!
The Fix: Parameterized Queries
Example
-- Parameterized query (safe!)
-- The $1 is a parameter placeholder - the database treats it as data, not code
SELECT * FROM users WHERE email = $1;
Example
// In Node.js with a database driver
const result = await db.query(
'SELECT * FROM users WHERE email = $1',
[userInput] // Sent separately from the SQL, so it's never run as code
);
The Best Fix: Use Prisma
Example
// Prisma handles parameterization automatically - always safe!
const user = await prisma.user.findUnique({
where: { email: userInput },
});
// No SQL injection possible, no matter what userInput contains
Input Validation
Even with parameterized queries, you should still validate whatever users send you. This example uses Zod, so install it first with npm install zod.
Example
import { z } from 'zod';
// Define what valid input looks like
const userSchema = z.object({
email: z.email(),
name: z.string().min(1).max(100),
age: z.number().int().min(13).max(120).optional(),
});
// Validate before using
const validated = userSchema.parse(userInput);
const user = await prisma.user.create({
data: validated,
});
That catches junk data (an email with no @, or somebody claiming to be -5 years old) before it ever touches your database.
Connection Pooling
Opening a brand new database connection for every request is expensive, around 20-50ms each time. Connection pooling reuses connections instead.
Example
// Prisma 7 pools connections through the driver adapter automatically!
// But you can tune it where you create the client:
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../generated/prisma/client';
const adapter = new PrismaPg({
connectionString: process.env.DATABASE_URL,
max: 10, // max connections in the pool (10 is the default)
});
const prisma = new PrismaClient({ adapter });
Backups and Recovery
Your database is the most valuable part of your app. Protect it like it.
A client once asked me what would happen to their site if I died. Kinda morbid, sure, but it was a fair question, and it pushed me to rebuild my whole agency around real systems instead of "Andrew knows where everything is." Backups are that same question pointed at your data.
Backup Strategies
Automated daily backups. Managed databases can do this for you, but check that it's actually ON. Supabase includes daily backups on its paid plans (the free plan gets none), and on Railway you set a backup schedule on your database's volume yourself.
Point-in-time recovery. Roll your database back to any specific moment in the past.
Cross-region backups. Keep copies in different parts of the world.
Test your restores. A backup you've never actually restored from isn't a real backup.
Managed Database Advantages
A managed database service gives you a bunch of this, usually on its paid plans.
Automatic backups (usually daily, kept for a week or more)
Never hard-code your database credentials. Use environment variables instead.
Example
# .env (local development, never commit this!)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"
# Production (set this in your hosting platform's variables, not a file)
DATABASE_URL="postgresql://user:password@prod-server:5432/mydb"
Performance Tips Summary
Practice
Why
Add indexes on WHERE/ORDER BY columns
Speeds up reads from O(n) to O(log n)
Use select instead of SELECT *
Fetches only needed data
Add LIMIT to all user-facing queries
Prevents loading millions of rows
Use connection pooling
Avoids expensive connection creation
Normalize your data
Prevents update anomalies and wasted space
Use include over multiple queries
One database round trip instead of N
Add pagination (LIMIT + OFFSET)
Handles large datasets gracefully
TL;DR
Indexes make queries on big tables WAY faster, but they slow down writes
Normalize your data so each fact is stored exactly once
Parameterized queries or an ORM like Prisma stop SQL injection
Validate user input with a library like Zod before it reaches your database
Connection pooling reuses database connections so things run faster
Managed databases give you automatic backups, failover and security patches
Never commit database credentials to Git, use environment variables
Start simple, and optimize once you've measured an actual performance problem
Chapter Complete!
You made it through the SQL chapter! You've now got a solid foundation in databases and SQL. You know how data is laid out in tables, how to query it and change it, how tables connect through foreign keys and joins, how to use Prisma for type-safe database access and how to keep your database fast, secure and reliable. Every web developer leans on these skills (and now you've got them too).
What's Next?
Next up is the Supabase chapter, where you'll meet a hosted PostgreSQL service that piles real-time updates, an instant API and a dashboard on top of everything you just learned...
