|$ curl https://forge-ai.dev/api/markdown?path=docs/databases

$cat docs/databases-—-getting-started.md

updated Recently·50 min read·published

Databases — Getting Started

◆Databases◆Backend◆Data◆Beginner to Advanced🎯Free Tools

Introduction

A database is an organized collection of structured or unstructured data stored electronically and accessible through a database management system (DBMS). Databases are the backbone of nearly every software application — from storing user credentials and session data to powering complex analytics and real-time dashboards.

Choosing the right database technology and designing an effective data model are among the most consequential decisions in software architecture. A poor choice can lead to performance bottlenecks, costly migrations, and scalability walls that are painful to fix later.

This guide provides a comprehensive overview of database concepts, helps you understand when to use SQL versus NoSQL, and introduces the key topics you will explore in each sub-section of the databases documentation.

📝

note

Databases are not just about storage — they are about data integrity, concurrency, consistency, and the ability to query your data efficiently as your application grows.

Database Taxonomy

Databases can be classified along several dimensions. The most fundamental distinction is the data model they support:

Category	Data Model	Examples	Best For
Relational	Tables with rows and columns	PostgreSQL, MySQL, SQLite	Structured data, ACID transactions
Document	JSON/BSON documents	MongoDB, CouchDB	Flexible schemas, nested data
Key-Value	Key → Value pairs	Redis, DynamoDB, Memcached	Caching, sessions, simple lookups
Column-Family	Wide columns, sparse rows	Cassandra, HBase, ScyllaDB	Time-series, IoT, write-heavy workloads
Graph	Nodes and edges	Neo4j, ArangoDB, Neptune	Relationships, social networks, recommendations
Vector	Embedding vectors	Pinecone, Weaviate, Milvus	Similarity search, AI/ML applications
Time-Series	Timestamped data points	InfluxDB, TimescaleDB, QuestDB	Metrics, monitoring, IoT telemetry

In practice, many modern databases blur these categories. PostgreSQL supports JSONB documents, Redis can persist data, and MongoDB supports multi-document transactions. The boundaries are less rigid than they once were.

SQL vs NoSQL

The most common decision developers face is choosing between SQL (relational) and NoSQL (non-relational) databases. Here is a side-by-side comparison:

Dimension	SQL	NoSQL
Schema	Fixed, predefined schema	Dynamic, schema-on-read
Query Language	SQL (standardized)	API-specific (varies)
Scaling	Vertical (bigger server)	Horizontal (more servers)
Consistency	Strong (ACID)	Eventually consistent (BASE)
Relationships	JOINs, foreign keys	Embedded documents, references
Transactions	Multi-row, multi-table	Limited or single-document
Best For	Complex queries, data integrity	Rapid iteration, flexible schemas

sql-join-example.sql

SQL

1	-- SQL: Relational query with JOINs
2	SELECT
3	u.name,
4	u.email,
5	COUNT(o.id) AS order_count,
6	SUM(o.total) AS total_spent
7	FROM users u
8	LEFT JOIN orders o ON o.user_id = u.id
9	WHERE u.created_at >= '2025-01-01'
10	GROUP BY u.id, u.name, u.email
11	HAVING SUM(o.total) > 100
12	ORDER BY total_spent DESC
13	LIMIT 20;

mongodb-aggregation.js

JavaScript

1	// MongoDB: Equivalent aggregation pipeline
2	db.users.aggregate([
3	{
4	$lookup: {
5	from: "orders",
6	localField: "_id",
7	foreignField: "userId",
8	as: "orders",
9	},
10	},
11	{
12	$addFields: {
13	orderCount: { $size: "$orders" },
14	totalSpent: { $sum: "$orders.total" },
15	},
16	},
17	{ $match: { totalSpent: { $gt: 100 } } },
18	{ $sort: { totalSpent: -1 } },
19	{ $limit: 20 },
20	{ $project: { name: 1, email: 1, orderCount: 1, totalSpent: 1 } },
21	]);

ℹ

info

SQL is better when your data has clear relationships and you need complex queries with joins. NoSQL excels when your data is naturally hierarchical and you need to scale horizontally.

ACID Properties

ACID is an acronym that describes the four key properties of reliable database transactions. Understanding these properties is essential for choosing the right database for your use case.

bank-transfer.sql

SQL

1	-- ACID Transaction Example: Bank Transfer
2	BEGIN;
3
4	-- Deduct from sender
5	UPDATE accounts
6	SET balance = balance - 500.00
7	WHERE id = 'sender-id' AND balance >= 500.00;
8
9	-- Check if deduction succeeded
10	-- (rows affected = 0 means insufficient funds)
11	-- If failed, ROLLBACK; otherwise:
12
13	-- Credit to receiver
14	UPDATE accounts
15	SET balance = balance + 500.00
16	WHERE id = 'receiver-id';
17
18	-- Record the transaction
19	INSERT INTO transfers (sender_id, receiver_id, amount, created_at)
20	VALUES ('sender-id', 'receiver-id', 500.00, NOW());
21
22	COMMIT;

Property	Meaning	Example
Atomicity	All or nothing — a transaction either completes fully or rolls back entirely	Bank transfer debits and credits both succeed or both fail
Consistency	Every transaction brings the database from one valid state to another	Total money in the system never changes after a transfer
Isolation	Concurrent transactions do not interfere with each other	Two transfers on the same account execute sequentially
Durability	Committed data survives crashes and restarts	After COMMIT, data is written to disk (WAL)

⚠

warning

Many NoSQL databases trade ACID compliance for availability and partition tolerance (CAP theorem). MongoDB added multi-document ACID transactions in version 4.0, but they come with performance overhead. Always verify whether your specific use case truly requires full ACID semantics.

CAP Theorem

The CAP theorem states that a distributed data store can provide at most two of the following three guarantees simultaneously: Consistency, Availability, and Partition tolerance. Since network partitions are inevitable in distributed systems, you must choose between consistency and availability when a partition occurs.

cap-theorem.txt

Bash

1	# CAP Theorem Choices in Practice:
2
3	# CP (Consistency + Partition tolerance)
4	# MongoDB (default), HBase, Redis Cluster, PostgreSQL
5	# → May return errors during partitions
6	# → Best for: financial systems, inventory management
7
8	# AP (Availability + Partition tolerance)
9	# Cassandra, DynamoDB, CouchDB, Riak
10	# → Always returns data, may be stale
11	# → Best for: social media feeds, analytics, IoT
12
13	# CA (Consistency + Availability)
14	# Traditional single-node databases (PostgreSQL, MySQL)
15	# → No partition tolerance (not distributed)
16	# → Best for: single-server applications

Data Modeling Basics

Data modeling is the process of defining how data is stored, organized, and related. A well-designed data model reduces redundancy, enforces integrity, and makes queries efficient.

schema.sql

SQL

1	-- Relational data model for an e-commerce application
2	CREATE TABLE users (
3	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4	email VARCHAR(255) UNIQUE NOT NULL,
5	name VARCHAR(255) NOT NULL,
6	created_at TIMESTAMPTZ DEFAULT NOW()
7	);
8
9	CREATE TABLE products (
10	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
11	name VARCHAR(255) NOT NULL,
12	description TEXT,
13	price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
14	stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
15	category_id UUID REFERENCES categories(id),
16	created_at TIMESTAMPTZ DEFAULT NOW()
17	);
18
19	CREATE TABLE orders (
20	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
21	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
22	status VARCHAR(20) NOT NULL DEFAULT 'pending'
23	CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
24	total DECIMAL(10, 2) NOT NULL DEFAULT 0,
25	created_at TIMESTAMPTZ DEFAULT NOW()
26	);
27
28	CREATE TABLE order_items (
29	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
30	order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
31	product_id UUID NOT NULL REFERENCES products(id),
32	quantity INTEGER NOT NULL CHECK (quantity > 0),
33	unit_price DECIMAL(10, 2) NOT NULL,
34	UNIQUE(order_id, product_id)
35	);

ℹ

info

When modeling relational data, normalize to 3NF (Third Normal Form) first, then denormalize selectively for read performance. Over-normalization leads to complex JOINs; under-normalization leads to data duplication and update anomalies.

When to Use What

Choosing a database depends on your data characteristics, access patterns, consistency requirements, and scale expectations. Here is a practical decision guide:

Use Case	Recommended	Why
E-commerce / transactions	PostgreSQL, MySQL	ACID, complex queries, relational integrity
User sessions / caching	Redis	Sub-millisecond latency, TTL support
Content management	MongoDB	Flexible schemas, nested documents
Real-time analytics	ClickHouse, TimescaleDB	Columnar storage, time-series optimization
IoT / time-series data	InfluxDB, TimescaleDB	Time-partitioned storage, retention policies
Social graph / recommendations	Neo4j, Neptune	Graph traversals, relationship queries
Full-text search	Elasticsearch, Meilisearch	Inverted indexes, relevance scoring
AI vector search	Pinecone, pgvector, Weaviate	Approximate nearest neighbor, embeddings
Prototype / small app	SQLite	Zero config, single file, embedded

multi-db.ts

JavaScript

1	// Example: Multi-database architecture in a Next.js app
2	// Using PostgreSQL for core data and Redis for caching
3
4	import { PrismaClient } from "@prisma/client";
5	import { createClient } from "redis";
6
7	const prisma = new PrismaClient();
8	const redis = createClient({ url: process.env.REDIS_URL });
9
10	// Core data lives in PostgreSQL (ACID, relationships)
11	async function getUserProfile(userId) {
12	// Check Redis cache first (sub-ms latency)
13	const cached = await redis.get(`user:${userId}:profile`);
14	if (cached) return JSON.parse(cached);
15
16	// Fall back to PostgreSQL (ms latency, complex JOINs)
17	const user = await prisma.user.findUnique({
18	where: { id: userId },
19	include: {
20	orders: { take: 5, orderBy: { createdAt: "desc" } },
21	preferences: true,
22	},
23	});
24
25	// Cache for 5 minutes
26	await redis.setEx(`user:${userId}:profile`, 300, JSON.stringify(user));
27	return user;
28	}

Consistency Models

Understanding consistency models helps you choose the right trade-offs for your application. Not every use case requires strong consistency — and the performance cost of strong consistency can be significant.

consistency-models.txt

Bash

1	# Consistency Models (strongest → weakest):
2
3	# 1. Strong Consistency (Linearizability)
4	# → Read always returns the most recent write
5	# → PostgreSQL, MongoDB (causal consistency mode)
6	# → Use for: financial transactions, inventory
7
8	# 2. Causal Consistency
9	# → Operations that are causally related are seen in order
10	# → MongoDB (default), CockroachDB, Spanner
11	# → Use for: chat applications, collaborative editing
12
13	# 3. Read-Your-Writes Consistency
14	# → You always see your own writes
15	# → Most session-based systems
16	# → Use for: user settings, profile updates
17
18	# 4. Eventual Consistency
19	# → All replicas converge to the same value eventually
20	# → Cassandra, DynamoDB, Redis (async replication)
21	# → Use for: social feeds, analytics, non-critical reads

✓

best practice

Start with strong consistency (PostgreSQL) for your core data. Add caching layers (Redis) for read-heavy paths. Use eventual consistency only for non-critical data where stale reads are acceptable. This gives you the best balance of correctness and performance.

Connection Management

Database connections are expensive to create. Each connection spawns a new process or thread on the database server, consuming memory and CPU. Using connection pooling reuses existing connections, dramatically improving performance under load.

connection-pool.ts

TypeScript

1	// Connection pooling with Prisma (built-in pool)
2	// prisma/schema.prisma
3	generator client {
4	provider = "prisma-client-js"
5	}
6
7	datasource db {
8	provider = "postgresql"
9	url = env("DATABASE_URL")
10	}
11
12	// Prisma manages a connection pool automatically
13	// Default pool size: num_physical_cpus * 2 + 1
14	// Configure via: DATABASE_URL?connection_limit=20
15
16	// Direct connection (bypass pool) — for migrations
17	// DATABASE_URL?pgbouncer=false&connect_timeout=10
18
19	// Manual pooling with node-postgres (pg)
20	import { Pool } from "pg";
21
22	const pool = new Pool({
23	connectionString: process.env.DATABASE_URL,
24	max: 20, // max connections in pool
25	idleTimeoutMillis: 30000, // close idle connections after 30s
26	connectionTimeoutMillis: 2000, // fail after 2s if no connection
27	});
28
29	async function query(text: string, params?: unknown[]) {
30	const start = Date.now();
31	const result = await pool.query(text, params);
32	const duration = Date.now() - start;
33	console.log("Executed query", { text, duration, rows: result.rowCount });
34	return result;
35	}

⚠

warning

In serverless environments (Vercel, AWS Lambda), each function invocation may create a new connection. Use a connection pooler like PgBouncer or Prisma Accelerate to avoid exhausting your database connection limit.

Replication Patterns

Replication copies data across multiple database servers for redundancy, read scaling, and disaster recovery. The three primary patterns each serve different needs.

replication.txt

Bash

1	# Replication Patterns:
2
3	# Primary-Replica (most common)
4	# ┌─────────┐ ┌─────────┐ ┌─────────┐
5	# │ Primary │────▶│ Replica │ │ Replica │
6	# │ (R/W) │ │ (R) │ │ (R) │
7	# └─────────┘ └─────────┘ └─────────┘
8	# → Reads scale horizontally
9	# → Writes go through primary only
10	# → PostgreSQL, MySQL, MongoDB
11
12	# Multi-Primary (bi-directional)
13	# ┌─────────┐◀───▶┌─────────┐
14	# │ Primary │ │ Primary │
15	# │ A │ │ B │
16	# └─────────┘ └─────────┘
17	# → Writes can go to either node
18	# → Conflict resolution needed
19	# → CockroachDB, Galera (MySQL)
20
21	# Peer-to-Peer (distributed)
22	# ┌─────────┐◀───▶┌─────────┐
23	# │ Node A │◀───▶│ Node B │
24	# └────┬────┘ └────┬────┘
25	# └───────▶┌──────┘
26	# │ Node C │
27	# └────────┘
28	# → Every node is equal
29	# → Cassandra, Riak, DynamoDB

Connection Strings

Database connection strings encode the protocol, credentials, host, port, database name, and options. Understanding the format is essential for configuring your application correctly.

connection-strings.txt

Bash

1	# PostgreSQL connection string format:
2	postgresql://username:password@host:port/database?options
3
4	# Examples:
5	postgresql://postgres:secret@localhost:5432/myapp
6	postgresql://user:pass@mydb.cluster.region.rds.amazonaws.com:5432/prod?sslmode=require&connect_timeout=10
7
8	# Common PostgreSQL options:
9	# sslmode=disable\|require\|verify-ca\|verify-full
10	# connect_timeout=N (seconds)
11	# application_name=myapp (shows in pg_stat_activity)
12	# pool_timeout=N (seconds to wait for pool)
13
14	# MongoDB connection string format:
15	mongodb://username:password@host:port/database?options
16
17	# With replica set:
18	mongodb://user:pass@node1:27017,node2:27017,node3:27017/mydb?replicaSet=myrs&authSource=admin
19
20	# MongoDB Atlas (_SRV):
21	mongodb+srv://user:pass@cluster0.abc123.mongodb.net/mydb?retryWrites=true&w=majority
22
23	# Redis connection string format:
24	redis://username:password@host:port
25
26	# With database number and TLS:
27	rediss://default:password@redis.example.com:6379/0

danger

Never hardcode connection strings in your source code. Always use environment variables and a .env file (which should be in .gitignore). Exposed credentials can lead to data breaches.

Documentation Sections

This databases documentation is organized into focused guides. Each section dives deep into a specific topic:

Section	Topics Covered
SQL Fundamentals	Queries, JOINs, subqueries, indexes, transactions, PostgreSQL vs MySQL
PostgreSQL Deep Dive	JSONB, CTEs, window functions, full-text search, extensions
MongoDB	CRUD, aggregation pipeline, indexes, transactions, schema design
Redis	Data structures, caching, pub/sub, sessions, rate limiting
ORMs	Prisma, Drizzle, TypeORM, Sequelize, ORM vs raw SQL
Migrations	Schema versioning, rollback strategies, seed data management
Optimization	Query plans, indexing strategies, N+1 problem, connection pooling

🔥

pro tip

If you are starting a new project and unsure what to pick, the pragmatic default is PostgreSQL + Redis. PostgreSQL handles your core data with full ACID compliance and rich features. Redis handles caching, sessions, and rate limiting. This combination covers 90% of web application needs.

Quick Environment Setup

Before diving into the individual guides, here is how to set up a local development environment with PostgreSQL, MongoDB, and Redis using Docker:

docker-compose.yml

YAML

1	# docker-compose.yml for local database development
2	version: "3.9"
3
4	services:
5	postgres:
6	image: postgres:16-alpine
7	ports:
8	- "5432:5432"
9	environment:
10	POSTGRES_USER: dev
11	POSTGRES_PASSWORD: devpassword
12	POSTGRES_DB: myapp_dev
13	volumes:
14	- pgdata:/var/lib/postgresql/data
15	healthcheck:
16	test: ["CMD-SHELL", "pg_isready -U dev"]
17	interval: 5s
18	timeout: 5s
19	retries: 5
20
21	mongodb:
22	image: mongo:7
23	ports:
24	- "27017:27017"
25	environment:
26	MONGO_INITDB_ROOT_USERNAME: dev
27	MONGO_INITDB_ROOT_PASSWORD: devpassword
28	volumes:
29	- mongodata:/data/db
30
31	redis:
32	image: redis:7-alpine
33	ports:
34	- "6379:6379"
35	command: redis-server --requirepass devpassword
36	volumes:
37	- redisdata:/data
38
39	volumes:
40	pgdata:
41	mongodata:
42	redisdata:

setup.sh

Bash

1	# Start all databases
2	docker compose up -d
3
4	# Verify they are running
5	docker compose ps
6
7	# PostgreSQL connection
8	psql postgresql://dev:devpassword@localhost:5432/myapp_dev
9
10	# MongoDB connection
11	mongosh mongodb://dev:devpassword@localhost:27017/myapp_dev?authSource=admin
12
13	# Redis connection
14	redis-cli -a devpassword
15
16	# Create your .env.local
17	cat << 'EOF' > .env.local
18	DATABASE_URL="postgresql://dev:devpassword@localhost:5432/myapp_dev"
19	MONGODB_URI="mongodb://dev:devpassword@localhost:27017/myapp_dev?authSource=admin"
20	REDIS_URL="redis://:devpassword@localhost:6379"
21	EOF

ℹ

info

Always run docker compose down -v to also remove volumes when you want a fresh start. Without-v, data persists across restarts.

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor