Database Fundamentals

A database is an organised system for storing, retrieving, and managing data. Every app that remembers anything — user accounts, orders, messages, settings — uses a database.

If Cartara flagged this in your diff, you likely added database queries, defined a schema, connected to a database, or changed how data is stored or retrieved.

The Two Families

Almost every database falls into one of two families:

• SQL (Relational) — data stored in structured tables with defined relationships
• NoSQL (Non-relational) — data stored in more flexible formats: documents, key-value pairs, etc.

For most apps: start with SQL. A well-structured Postgres database handles millions of rows with ease and is flexible enough for almost any data model.

SQL Databases

How They Work

SQL databases store data in tables — rows and columns, like a spreadsheet. Each table has a defined schema: specific columns with specific data types.

Tables link to each other through relationships. A users table and an orders table connect via a user_id column:

```
users orders
───────────────── ──────────────────────────
id | name | email id | user_id | product | amount
1 | Matt | m@x.com 1 | 1 | Widget | 29.00
2 | Sara | s@x.com 2 | 1 | Gadget | 49.00
3 | 2 | Widget | 29.00
```

Querying with SQL

SQL (Structured Query Language) is how you talk to relational databases. It reads like English:

```sql
-- Get a specific user
SELECT name, email FROM users WHERE id = 1;

-- Get all orders for that user
SELECT * FROM orders WHERE user_id = 1;

-- Get the total spent by each user
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;
```

In practice, most apps use an ORM (Object Relational Mapper) like Prisma or Drizzle to write queries in TypeScript/JavaScript instead of raw SQL. But knowing SQL basics helps enormously when debugging.

Why SQL Is the Default Choice

• Data integrity — the schema enforces structure; you can't store the wrong type of data
• Relationships — joins let you query across related tables efficiently
• ACID transactions — operations either fully succeed or fully fail (critical for financial data)
• Flexibility — SQL is powerful enough to answer questions you haven't thought of yet

Popular SQL Databases

PostgreSQL via Supabase is the recommended starting point for most small apps.

NoSQL Databases

NoSQL databases trade strict structure for flexibility, often at the cost of consistency or query power. Use them when you have a specific need, not by default.

Document Databases

Store data as JSON-like documents. No fixed schema — each document can have different fields.

```json
{
"id": "user_123",
"name": "Matt",
"preferences": {
"theme": "dark",
"notifications": ["email", "sms"]
}
}
```

Use when: Content with flexible or nested structure, user profiles, product catalogs.
Tools: MongoDB, Firestore (Firebase).

Key-Value Stores

A simple key maps to a value. Extremely fast for reads and writes.

Use when: Caching, session storage, rate limiting counters.
Tools: Redis, Memcached.

Vector Databases

Store numerical embeddings and support similarity search. Used in AI applications for semantic search and RAG systems.

Use when: AI-powered search, recommendation systems.
Tools: Pinecone, Weaviate, pgvector (a Postgres extension).

Core Concepts

Indexes

An index makes lookups faster — like a book's index pointing to page numbers. Without one, the database reads every row to find a match. With one, it jumps directly to the right rows.

Index the columns you filter or sort by most often. Too many indexes slow down writes; too few slow down reads.

Transactions

A transaction is a group of operations that succeed or fail together. When transferring money between accounts, both the debit and the credit must succeed — or neither should. Transactions guarantee this.

```sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If anything fails between BEGIN and COMMIT, neither update happens
```

Schema Migrations

As your app grows, you need to change the database structure — adding columns, creating tables. A migration is a versioned script that applies these changes safely. See Database Migrations.

What You'll See in Your Code

Database code typically shows up in diffs as:

Schema definitions (Prisma example):
```prisma
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
orders Order[]
createdAt DateTime @default(now())
}
```

Queries using an ORM (Drizzle example):
```ts
// Fetch a user with all their orders
const user = await db.query.users.findFirst({
where: eq(users.id, userId),
with: { orders: true },
});
```

Raw SQL (in migrations or complex queries):
```sql
CREATE INDEX idx_orders_user_id ON orders(user_id);
```

Practical Tips

Use a managed database. Supabase, PlanetScale, Railway, and Neon handle backups, scaling, and maintenance. Don't run your own database server until you have a strong reason to.

Enable backups on day one. Before you have a single real user.

Use an ORM. Prisma or Drizzle lets you write type-safe queries in TypeScript and auto-generates types from your schema.

Don't optimise prematurely. A well-structured Postgres database handles millions of rows with basic indexing. Solve real performance problems when they arise.

Separate dev and prod databases. Always.

Related Concepts

• Database Migrations
• Data Modeling and Schema Design
• Database Scaling and Performance
• Authentication and Authorization
• System Design Basics
• From Prototype to Production