Adding a Database

Store and retrieve data persistently with MySQL or PostgreSQL. Perfect for user data, content, orders, and more.

Reading time: 20 min Difficulty: Intermediate

View examples as:

Ask Claude Manual Code

When Do You Need a Database?
MySQL vs PostgreSQL
Setting Up MySQL
Setting Up PostgreSQL
Designing Your Schema
CRUD Operations
Security Best Practices
Backups & Maintenance

When Do You Need a Database?

You need a database when your app needs to:

Remember user information - Accounts, preferences, profiles
Store content - Blog posts, comments, products
Track transactions - Orders, payments, history
Search and filter data - Finding specific records quickly
Relate data together - Users have orders, orders have items

Simple Alternative

For very simple apps (static content, simple forms), you might get away with JSON files or SQLite. But for anything with users or growing data, use a proper database.

MySQL vs PostgreSQL

Feature	MySQL	PostgreSQL
Ease of Setup	Slightly easier	More configuration
Performance	Great for reads	Better for complex queries
JSON Support	Good	Excellent (JSONB)
Full-Text Search	Basic	Advanced
Community/Hosting	More common	Growing fast
Best For	Web apps, WordPress	Complex apps, analytics

Recommendation

Use MySQL if you're building a typical web app, blog, or e-commerce site. Use PostgreSQL if you need complex queries, JSON storage, or advanced features.

Setting Up MySQL

Let's install MySQL and create your first database.

Install and Configure MySQL

Example prompt:

"Install MySQL on my server and create a database called 'myapp'. Create a user 'myapp_user' with a strong password that can only access this database. Store the credentials in environment variables."

# Install MySQL
sudo apt update
sudo apt install mysql-server -y

# Secure the installation
sudo mysql_secure_installation

# Log in to MySQL
sudo mysql

# Create database and user
CREATE DATABASE myapp;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Connect from Your App

Example prompt:

"Create a database connection module for my Node.js app. Use mysql2 with connection pooling. Read credentials from environment variables (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME). Include a helper function to run queries with automatic connection handling."

// db.js - MySQL connection with pooling
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Helper function for queries
async function query(sql, params) {
    const [rows] = await pool.execute(sql, params);
    return rows;
}

module.exports = { pool, query };

Setting Up PostgreSQL

PostgreSQL offers more advanced features for complex applications.

Install and Configure PostgreSQL

Example prompt:

"Install PostgreSQL on my server and create a database called 'myapp'. Create a user 'myapp_user' with a strong password. Configure pg_hba.conf for local connections. Store credentials in environment variables."

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib -y

# Switch to postgres user
sudo -u postgres psql

# Create database and user
CREATE USER myapp_user WITH PASSWORD 'your_secure_password';
CREATE DATABASE myapp OWNER myapp_user;
GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;
\q

Connect from Your App

Example prompt:

"Create a database connection module for my Node.js app using the pg library. Use connection pooling. Read credentials from DATABASE_URL environment variable (standard Postgres connection string format). Include error handling."

// db.js - PostgreSQL connection with pooling
const { Pool } = require('pg');

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    // Or use individual params:
    // host: process.env.DB_HOST,
    // user: process.env.DB_USER,
    // password: process.env.DB_PASSWORD,
    // database: process.env.DB_NAME,
    max: 20,
    idleTimeoutMillis: 30000,
});

// Helper function for queries
async function query(text, params) {
    const result = await pool.query(text, params);
    return result.rows;
}

module.exports = { pool, query };

Designing Your Schema

A good database schema is the foundation of your app. Plan your tables before writing code.

Create Tables

Example prompt:

"Create the database schema for an e-commerce app. I need tables for: users (id, email, password_hash, name, created_at), products (id, name, description, price, stock, image_url), orders (id, user_id, status, total, created_at), and order_items (order_id, product_id, quantity, price_at_time). Add appropriate indexes and foreign keys."

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

-- Products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    image_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    status ENUM('pending','paid','shipped','delivered') DEFAULT 'pending',
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Order items table
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price_at_time DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CRUD Operations

CRUD stands for Create, Read, Update, Delete - the four basic operations you'll do with your data.

Create (INSERT)

Example prompt:

"Create a function to add a new user to the database. It should accept email, password, and name. Hash the password with bcrypt before storing. Return the new user's ID. Handle duplicate email errors gracefully."

const bcrypt = require('bcrypt');
const { query } = require('./db');

async function createUser(email, password, name) {
    const passwordHash = await bcrypt.hash(password, 10);

    try {
        const result = await query(
            'INSERT INTO users (email, password_hash, name) VALUES (?, ?, ?)',
            [email, passwordHash, name]
        );
        return { id: result.insertId, email, name };
    } catch (error) {
        if (error.code === 'ER_DUP_ENTRY') {
            throw new Error('Email already exists');
        }
        throw error;
    }
}

Read (SELECT)

Example prompt:

"Create functions to: 1) get a user by ID, 2) get a user by email (for login), 3) list all products with pagination (page number and limit), 4) search products by name."

async function getUserById(id) {
    const users = await query(
        'SELECT id, email, name, created_at FROM users WHERE id = ?',
        [id]
    );
    return users[0] || null;
}

async function getUserByEmail(email) {
    const users = await query(
        'SELECT * FROM users WHERE email = ?',
        [email]
    );
    return users[0] || null;
}

async function getProducts(page = 1, limit = 20) {
    const offset = (page - 1) * limit;
    return await query(
        'SELECT * FROM products ORDER BY created_at DESC LIMIT ? OFFSET ?',
        [limit, offset]
    );
}

async function searchProducts(searchTerm) {
    return await query(
        'SELECT * FROM products WHERE name LIKE ? OR description LIKE ?',
        [`%${searchTerm}%`, `%${searchTerm}%`]
    );
}

Update and Delete

Example prompt:

"Create functions to update a user's profile (name only) and delete a user account. For delete, also remove their orders. Use a transaction to ensure both deletes succeed or both fail."

async function updateUserName(userId, newName) {
    const result = await query(
        'UPDATE users SET name = ? WHERE id = ?',
        [newName, userId]
    );
    return result.affectedRows > 0;
}

async function deleteUser(userId) {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        // Delete order items first (foreign key constraint)
        await connection.execute(
            'DELETE oi FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE o.user_id = ?',
            [userId]
        );

        // Delete orders
        await connection.execute('DELETE FROM orders WHERE user_id = ?', [userId]);

        // Delete user
        await connection.execute('DELETE FROM users WHERE id = ?', [userId]);

        await connection.commit();
        return true;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
}

Security Best Practices

SQL Injection Prevention

NEVER concatenate user input directly into SQL queries. Always use parameterized queries (prepared statements) as shown in the examples above.

Use parameterized queries - Never build SQL with string concatenation
Hash passwords - Use bcrypt or argon2, never store plain text
Limit database permissions - Create users with only the permissions they need
Keep credentials secure - Use environment variables, never commit to git
Validate input - Check data types and lengths before database operations
Use HTTPS - Encrypt data in transit

Backups & Maintenance

Automated Backups

Example prompt:

"Set up automated daily backups for my MySQL database. Store them in /backups with timestamps in the filename. Keep the last 7 days of backups and delete older ones. Run at 3 AM daily via cron."

#!/bin/bash
# /scripts/backup-db.sh

BACKUP_DIR="/backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp"

# Create backup
mysqldump -u myapp_user -p"$DB_PASSWORD" $DB_NAME > $BACKUP_DIR/backup_$TIMESTAMP.sql

# Compress
gzip $BACKUP_DIR/backup_$TIMESTAMP.sql

# Delete backups older than 7 days
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

# Add to crontab:
# 0 3 * * * /scripts/backup-db.sh

Pro Tip

Test your backups regularly by restoring to a test database. A backup that doesn't restore is useless!

← Building Your First Website Next: User Authentication →

Sign In

Check your email!

Adding a Database

Table of Contents

When Do You Need a Database?

MySQL vs PostgreSQL

Setting Up MySQL

Install and Configure MySQL

Connect from Your App

Setting Up PostgreSQL

Install and Configure PostgreSQL

Connect from Your App

Designing Your Schema

Create Tables

CRUD Operations

Create (INSERT)

Read (SELECT)

Update and Delete

Security Best Practices

Backups & Maintenance

Automated Backups