SQL Tutorial #7: PostgreSQL Setup — A Real Database for Real Projects

You have been practicing with SQLite. It is great for learning. But for real projects, you need a real database.

PostgreSQL (often called “Postgres”) is the most popular open-source relational database. It is used by companies of all sizes, from startups to enterprises. In this article, you will set it up, connect to it, and learn its unique features.

In the previous article, you learned about indexes and performance. Now you will put everything together with a production-ready database.

Why PostgreSQL?

There are many databases to choose from. Here is why PostgreSQL stands out:

Free and open source — no licensing costs, ever
Feature-rich — JSONB, arrays, full-text search, window functions, CTEs, and more
Standards-compliant — follows the SQL standard closely
Reliable — battle-tested for decades, used by Instagram, Spotify, and Netflix
Great community — excellent documentation, active forums, frequent updates
Extensible — supports custom types, functions, and extensions like PostGIS (geographic data)

PostgreSQL vs other databases:

Feature	PostgreSQL	MySQL	SQLite
JSONB support	Excellent	Basic	No
Window functions	Full	Full	Full
Full-text search	Built-in	Basic	Extension
Concurrent writes	Excellent	Good	Limited
Deployment	Server	Server	File-based
Best for	Web apps, analytics	Web apps	Mobile, embedded, learning

Installing PostgreSQL with Docker

The easiest way to run PostgreSQL is with Docker. If you followed our Docker Compose tutorial, you already know the basics.

Step 1: Create a docker-compose.yml file:

services:
  postgres:
    image: postgres:17
    container_name: bookstore-db
    environment:
      POSTGRES_USER: bookstore
      POSTGRES_PASSWORD: secret123
      POSTGRES_DB: bookstore
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

This sets up:

PostgreSQL 17 (change to a newer version like postgres:18 if available)
A database called bookstore
A user called bookstore with password secret123
Port 5432 exposed on your machine
A volume to keep data when the container restarts

Step 2: Start the database:

docker compose up -d

Step 3: Verify it is running:

docker compose ps

NAME            STATUS    PORTS
bookstore-db    Up        0.0.0.0:5432->5432/tcp

Your PostgreSQL database is running.

Installing PostgreSQL Natively

If you prefer not to use Docker:

macOS (with Homebrew):

brew install postgresql@17
brew services start postgresql@17

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-client
sudo systemctl start postgresql

Windows:

Download the installer from postgresql.org/download/windows and follow the setup wizard.

psql — The PostgreSQL Command-Line Client

psql is the official command-line tool for PostgreSQL. It lets you run queries, manage databases, and inspect your data.

Connect to your Docker database:

docker exec -it bookstore-db psql -U bookstore -d bookstore

Or if PostgreSQL is installed locally:

psql -U bookstore -d bookstore

You will see the psql prompt:

bookstore=#

Useful psql commands:

Command	What it does
`\l`	List all databases
`\c dbname`	Connect to a different database
`\dt`	List all tables in the current database
`\d tablename`	Describe a table (show columns, types, constraints)
`\di`	List all indexes
`\du`	List all users/roles
`\q`	Quit psql
`\?`	Show all psql commands
`\timing`	Toggle query timing (shows how long queries take)

These commands start with a backslash. They are psql-specific, not SQL commands.

Run a SQL query:

SELECT version();

                          version
------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc...

Creating Databases and Users

Create a new database:

CREATE DATABASE my_project;

Create a new user:

CREATE USER alex WITH PASSWORD 'strongpassword';

Grant permissions:

-- Give alex full access to the bookstore database
GRANT ALL PRIVILEGES ON DATABASE bookstore TO alex;

-- Give alex access to all tables in the public schema
GRANT ALL ON ALL TABLES IN SCHEMA public TO alex;

Switch to a different database:

\c my_project

You are now connected to database "my_project" as user "bookstore".

PostgreSQL-Specific Features

PostgreSQL has features that most other databases do not. Here are the most useful ones.

SERIAL and Auto-Incrementing IDs

In SQLite, you use INTEGER PRIMARY KEY for auto-incrementing IDs. In PostgreSQL, use SERIAL or GENERATED ALWAYS AS IDENTITY.

-- SERIAL (older syntax, still common)
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

-- GENERATED ALWAYS AS IDENTITY (newer, recommended)
CREATE TABLE authors (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

With SERIAL or IDENTITY, the database assigns the next number automatically:

INSERT INTO authors (name, country) VALUES ('Leo Tolstoy', 'Russia');
-- id is automatically set to 1

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'UK');
-- id is automatically set to 2

UUID — Universally Unique IDs

UUIDs are random, globally unique identifiers. They look like this: 550e8400-e29b-41d4-a716-446655440000.

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE orders (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

INSERT INTO orders (customer_id, total) VALUES (1, 29.99);
-- id is automatically a UUID like: a1b2c3d4-e5f6-7890-abcd-ef1234567890

UUIDs are useful when:

Multiple databases generate IDs independently
You do not want sequential IDs exposed in URLs (security)
You are building distributed systems

JSONB — Storing JSON Data

PostgreSQL can store and query JSON data. JSONB is the binary format — it is faster for queries than plain JSON.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    metadata JSONB
);

INSERT INTO products (name, metadata) VALUES
('Laptop', '{"brand": "Acme", "ram": 16, "storage": "512GB"}'),
('Phone', '{"brand": "Acme", "ram": 8, "storage": "256GB", "color": "black"}');

Query JSON fields:

-- Get the brand from the JSON
SELECT name, metadata->>'brand' AS brand
FROM products;

| name   | brand |
|--------|-------|
| Laptop | Acme  |
| Phone  | Acme  |

-> returns a JSON element (as JSON)
->> returns a JSON element (as text)

Filter by JSON values:

SELECT name FROM products
WHERE metadata->>'ram' = '16';

| name   |
|--------|
| Laptop |

Check if a JSON field exists:

SELECT name FROM products
WHERE metadata ? 'color';

| name  |
|-------|
| Phone |

JSONB is powerful, but do not use it for everything. If you always query a field, it should probably be a regular column with an index. Use JSONB for flexible, optional, or nested data.

Arrays — Storing Lists

PostgreSQL supports array columns. This is useful when a row needs a list of values.

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[]
);

INSERT INTO books (title, tags) VALUES
('SQL Basics', ARRAY['sql', 'beginner', 'database']),
('Advanced PostgreSQL', ARRAY['sql', 'postgresql', 'advanced']);

Query arrays:

-- Find books with the 'beginner' tag
SELECT title FROM books WHERE 'beginner' = ANY(tags);

| title      |
|------------|
| SQL Basics |

Check if an array contains all specified values:

SELECT title FROM books WHERE tags @> ARRAY['sql', 'database'];

| title      |
|------------|
| SQL Basics |

@> means “contains all of.”

CREATE TABLE with Constraints

Constraints enforce rules on your data. PostgreSQL supports all standard constraints plus some extras.

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    city TEXT DEFAULT 'Unknown',
    age INTEGER CHECK (age >= 0 AND age <= 150),
    created_at TIMESTAMP DEFAULT NOW()
);

Constraint	Meaning
`PRIMARY KEY`	Unique identifier for each row. Cannot be NULL.
`NOT NULL`	Column must have a value.
`UNIQUE`	No two rows can have the same value in this column.
`DEFAULT`	Value used when no value is provided.
`CHECK`	Custom validation rule.
`FOREIGN KEY`	References a row in another table.

Foreign key with actions:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1 CHECK (quantity > 0),
    order_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE RESTRICT
);

ON DELETE CASCADE — if a customer is deleted, their orders are deleted too
ON DELETE RESTRICT — if a book has orders, you cannot delete the book
ON DELETE SET NULL — if the referenced row is deleted, set the foreign key to NULL

ALTER TABLE — Modifying Tables

After creating a table, you often need to change it.

Add a column:

ALTER TABLE books ADD COLUMN isbn TEXT;

Drop a column:

ALTER TABLE books DROP COLUMN isbn;

Rename a column:

ALTER TABLE books RENAME COLUMN title TO book_title;

Change a column’s type:

ALTER TABLE books ALTER COLUMN price TYPE NUMERIC(10, 2);

Add a constraint:

ALTER TABLE books ADD CONSTRAINT unique_title UNIQUE (title);

Drop a constraint:

ALTER TABLE books DROP CONSTRAINT unique_title;

Add a NOT NULL constraint:

ALTER TABLE books ALTER COLUMN title SET NOT NULL;

Migrations — Managing Schema Changes

When you work on a real project, your database schema changes over time. You add tables, add columns, change types. These changes are called migrations.

A migration is a file that describes a change to the database schema. Migrations are numbered and run in order.

Example: Two migration files

-- 001_create_authors.sql
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

-- 002_create_books.sql
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id),
    price NUMERIC(10, 2),
    published_year INTEGER
);

Why migrations matter:

Every team member runs the same migrations and gets the same database
You can see the history of every schema change
You can roll back a change if something goes wrong
Migrations run automatically in CI/CD pipelines

Simple migration tracking:

CREATE TABLE schema_migrations (
    version INTEGER PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT NOW()
);

-- After running migration 001:
INSERT INTO schema_migrations (version) VALUES (1);

-- Check which migrations have been applied:
SELECT * FROM schema_migrations ORDER BY version;

In real projects, use a migration tool:

Flyway — popular for Java projects
Alembic — popular for Python (SQLAlchemy)
golang-migrate — popular for Go projects
Prisma Migrate — popular for Node.js

Backup and Restore

Always back up your database. Things break. Mistakes happen.

pg_dump — Creating Backups

# Backup to a SQL file
pg_dump -U bookstore -d bookstore > backup.sql

# Backup in custom format (compressed, faster restore)
pg_dump -U bookstore -d bookstore -Fc > backup.dump

# From Docker
docker exec bookstore-db pg_dump -U bookstore -d bookstore > backup.sql

pg_restore — Restoring Backups

# Restore from a SQL file
psql -U bookstore -d bookstore < backup.sql

# Restore from custom format
pg_restore -U bookstore -d bookstore backup.dump

# From Docker
docker exec -i bookstore-db psql -U bookstore -d bookstore < backup.sql

Backup best practices:

Schedule daily automatic backups (use cron or a cloud provider’s backup feature)
Keep backups in a different location than the database
Test your backups by restoring them regularly
Keep at least the last 7 daily backups

Connection Strings

Applications connect to PostgreSQL using a connection string. The format is:

postgresql://username:password@host:port/database

Examples:

# Local Docker database
postgresql://bookstore:secret123@localhost:5432/bookstore

# Remote database
postgresql://alex:mypassword@db.example.com:5432/production

Most programming languages use this format:

# Python (psycopg2)
import psycopg2
conn = psycopg2.connect("postgresql://bookstore:secret123@localhost:5432/bookstore")

// Node.js (pg)
const { Client } = require('pg');
const client = new Client('postgresql://bookstore:secret123@localhost:5432/bookstore');

// Rust (SQLx)
let pool = PgPool::connect("postgresql://bookstore:secret123@localhost:5432/bookstore").await?;

Setting Up Our Complete Bookstore Database

Let us create a proper bookstore database in PostgreSQL with all the features we have learned in this series:

-- Create tables with proper constraints
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL UNIQUE,
    author_id INTEGER REFERENCES authors(id) ON DELETE SET NULL,
    price NUMERIC(10, 2) CHECK (price >= 0),
    published_year INTEGER,
    in_stock BOOLEAN DEFAULT true,
    tags TEXT[],
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER DEFAULT 1 CHECK (quantity > 0),
    order_date DATE DEFAULT CURRENT_DATE
);

-- Add indexes on foreign keys and common query columns
CREATE INDEX idx_books_author_id ON books(author_id);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_book_id ON orders(book_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_books_price ON books(price);

-- Insert sample data
INSERT INTO authors (name, country) VALUES
('F. Scott Fitzgerald', 'USA'),
('Harper Lee', 'USA'),
('George Orwell', 'UK'),
('Jane Austen', 'UK'),
('J.D. Salinger', 'USA');

INSERT INTO books (title, author_id, price, published_year, tags) VALUES
('The Great Gatsby', 1, 12.99, 1925, ARRAY['classic', 'fiction']),
('To Kill a Mockingbird', 2, 14.99, 1960, ARRAY['classic', 'fiction']),
('1984', 3, 11.99, 1949, ARRAY['dystopian', 'classic']),
('Pride and Prejudice', 4, 9.99, 1813, ARRAY['classic', 'romance']),
('The Catcher in the Rye', 5, 13.99, 1951, ARRAY['classic', 'fiction']),
('Animal Farm', 3, 8.99, 1945, ARRAY['dystopian', 'classic']),
('Tender Is the Night', 1, 15.99, 1934, ARRAY['classic', 'fiction']);

INSERT INTO customers (name, email, city) VALUES
('Alex Johnson', 'alex@example.com', 'New York'),
('Sam Wilson', 'sam@example.com', 'London'),
('Jordan Smith', 'jordan@example.com', 'New York'),
('Taylor Brown', NULL, 'Berlin');

INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES
(1, 1, 1, '2026-01-15'),
(1, 3, 2, '2026-01-15'),
(2, 2, 1, '2026-02-01'),
(3, 5, 1, '2026-02-10'),
(1, 6, 1, '2026-03-01'),
(2, 1, 1, '2026-03-05');

This is a complete, production-style database with constraints, indexes, arrays, and proper data types.

Common Mistakes

1. Not using a volume for Docker PostgreSQL

# Bad: data is lost when the container stops
services:
  postgres:
    image: postgres:17

# Good: data persists
services:
  postgres:
    image: postgres:17
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

Without a volume, all your data disappears when you stop the container. Check our Docker Volumes tutorial for more details.

2. Using SERIAL when you should use IDENTITY

SERIAL is the older approach. It creates a sequence that is not tied to the column. GENERATED ALWAYS AS IDENTITY is the modern way and is recommended for new projects.

3. Forgetting to create indexes on foreign keys

PostgreSQL does NOT automatically create indexes on foreign key columns. You must create them yourself:

-- Foreign key exists but no index — JOINs will be slow
ALTER TABLE books ADD CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES authors(id);

-- Add the index manually
CREATE INDEX idx_books_author_id ON books(author_id);

What You Learned

In this article, you learned:

PostgreSQL is the most popular open-source database
Docker is the easiest way to run PostgreSQL
psql is the command-line client for PostgreSQL
SERIAL and IDENTITY create auto-incrementing IDs
UUID generates globally unique identifiers
JSONB stores and queries JSON data
Arrays store lists in a single column
Constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) enforce data rules
ALTER TABLE modifies existing tables
Migrations track schema changes over time
pg_dump and pg_restore handle backups

Indexes and Performance — Making Queries Fast — previous article
Docker Compose — Running Multiple Containers — setting up Docker for PostgreSQL
Docker Volumes and Networking — persistent data for Docker containers
SQL Cheat Sheet — quick reference for all SQL commands

What’s Next?

Congratulations. You have completed the SQL track of the DevTools series. You can now write queries, modify data, join tables, aggregate results, use window functions, optimize with indexes, and run a real PostgreSQL database.

To review any topic quickly, check the SQL Cheat Sheet. For the complete DevTools series including Git and Docker, visit the earlier articles in this series.

Why PostgreSQL?#

Installing PostgreSQL with Docker#

Installing PostgreSQL Natively#

psql — The PostgreSQL Command-Line Client#

Creating Databases and Users#

PostgreSQL-Specific Features#

SERIAL and Auto-Incrementing IDs#

UUID — Universally Unique IDs#

JSONB — Storing JSON Data#

Arrays — Storing Lists#

CREATE TABLE with Constraints#

ALTER TABLE — Modifying Tables#

Migrations — Managing Schema Changes#

Backup and Restore#

pg_dump — Creating Backups#

pg_restore — Restoring Backups#

Connection Strings#

Setting Up Our Complete Bookstore Database#

Common Mistakes#

What You Learned#

Related Articles#

What’s Next?#