Database Best Practices

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#

-- 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 = '[email protected]';

Creating an Index#

-- 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 = '[email protected]';

In Prisma, you add indexes right in your schema.

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)#

-- Order data with customer info repeated in every row
-- order_id | customer_name | customer_email    | product   | price
-- 1        | Alice         | [email protected] | Laptop    | 999
-- 2        | Alice         | [email protected] | Mouse     | 29
-- 3        | Alice         | [email protected] | 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)#

-- Users table (each user stored once)
-- id | name  | email
-- 1  | Alice | [email protected]
 
-- 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#

// 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.

// 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#

-- Parameterized query (safe!)
-- The $1 is a parameter placeholder - the database treats it as data, not code
SELECT * FROM users WHERE email = $1;

// 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#

// 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.

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.

// 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#

1. 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.
2. Point-in-time recovery. Roll your database back to any specific moment in the past.
3. Cross-region backups. Keep copies in different parts of the world.
4. 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)
• Point-in-time recovery
• Automatic failover if the server goes down
• Security patches applied automatically
• Monitoring and alerting

# Manual backup with pg_dump (for local/unmanaged databases)
pg_dump -h localhost -U myuser mydb > backup_2024_01_15.sql
 
# Restore from backup
psql -h localhost -U myuser mydb < backup_2024_01_15.sql

Environment-Based Configuration#

Never hard-code your database credentials. Use environment variables instead.

# .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#

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...