Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Database

Pattern

A reusable solution you can apply to your work.

Understand This First

  • Data Model – the database stores the data model’s entities.

Context

Programs run in memory, and memory is temporary. Turn off the computer and everything in RAM disappears. A database is a system designed to store data persistently: to write it to disk (or to a network) so it survives restarts, crashes, and hardware failures. Databases sit at the architectural level because the choice of database technology shapes what your application can do, how fast it can do it, and how reliably it does it.

Nearly every non-trivial application uses a database. A to-do app, a banking platform, and an AI agent’s memory system all rely on some form of persistent data storage.

Problem

How do you store data so that it survives beyond the lifetime of a single program execution, and so that multiple users or processes can access it reliably?

Saving data to a flat file works for simple cases, but it breaks down quickly. What happens when two users try to write at the same time? How do you find one record among millions without reading the entire file? How do you ensure that a half-finished write doesn’t corrupt the file? These are the problems databases were built to solve.

Forces

  • You need data to persist across restarts and crashes.
  • Multiple users or processes may need to read and write the same data concurrently.
  • Different types of data (structured, semi-structured, unstructured) call for different storage approaches.
  • The database must be fast enough for the application’s needs and reliable enough for the application’s stakes.
  • Operational complexity (backups, migrations, scaling) increases with database sophistication.

Solution

Choose a database technology that matches your data’s shape and your application’s access patterns. The major families are:

Relational databases (PostgreSQL, MySQL, SQLite) store data in tables with rows and columns, enforce a Schema, and use SQL for queries. Best for structured data with well-defined relationships. They support Transactions and strong Consistency.

Document databases (MongoDB, CouchDB) store data as semi-structured documents (often JSON). Good when your data’s shape varies across records or when you want to store nested objects without splitting them across tables.

Key-value stores (Redis, DynamoDB) map keys to values with minimal structure. Extremely fast for simple lookups; less useful for complex queries.

Graph databases (Neo4j) model data as nodes and edges. Best when relationships between entities are the primary thing you query.

For most applications — especially those built by small teams or with AI agent assistance — a relational database (PostgreSQL or SQLite) is the safest starting choice. It handles a wide range of workloads, enforces data integrity, and has decades of tooling and documentation.

How It Plays Out

A team building a project management tool starts by storing tasks in a JSON file. It works for one user, but the moment two people edit simultaneously, changes get overwritten. They switch to SQLite, and concurrency is handled. As the team grows and needs network access to the data, they migrate to PostgreSQL. Each step trades simplicity for capability.

When asking an AI agent to build an application, specifying the database technology upfront prevents the agent from making ad hoc choices. “Use PostgreSQL with the schema I provided” produces much better results than “store the data somewhere.” Without guidance, agents may default to in-memory storage or flat files that won’t survive beyond a prototype.

Tip

SQLite is an excellent choice for prototypes, single-user applications, and embedded systems. It requires no server setup and stores everything in a single file. When directing an AI agent to build a quick proof of concept, SQLite reduces the setup friction to nearly zero.

Example Prompt

“Set up a SQLite database for this prototype. Create the tables from the schema I provided. Use SQLite for now — we’ll migrate to PostgreSQL later when we need multi-user support.”

Consequences

A database gives your application reliable, queryable, concurrent-safe persistence. It provides the foundation for CRUD operations, Transactions, and data Consistency. A well-chosen database makes your application’s data layer almost invisible. It just works.

The costs include operational overhead (backups, monitoring, upgrades, migrations), the learning curve of the query language and tooling, and the risk of choosing the wrong database type for your workload. Migrating from one database technology to another is expensive because it touches almost every layer of the application. This makes the initial choice consequential, even though “just pick PostgreSQL” is right more often than not.

  • Enables: Schema (Database) — the database schema defines the structure of stored data.
  • Enables: CRUD — databases provide the machinery for create, read, update, and delete operations.
  • Enables: Transaction — databases implement transactions to protect state integrity.
  • Enables: Consistency — database constraints and transactions enforce consistency.
  • Uses / Depends on: Data Model — the database stores the data model’s entities.
  • Refined by: Data Normalization / Denormalization — normalization decisions shape how data is organized within the database.