Relational databases

PostgreSQL, MySQL, SQLite and SQL Server store related records in tables with keys, constraints and transactions. For most apps one of them is the right default, with JSON columns for the flexible parts.

Relational database

Also called RDBMS, SQL database, tables, joins

What: A database that stores data in tables of rows and columns, links tables with keys and is queried with SQL. Joins combine tables to answer new questions without changing how data is stored.
Why: Joins, constraints, transactions and a standard language prevent whole classes of bugs, which is why relational databases still run most business software.

SQL

SELECT c.name, o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= now() - interval '7 days';

Watch out

Packing several facts into one column, such as a comma-separated list, which makes every later query harder.

PostgreSQL as the default

Also called Postgres, PostgreSQL

What: PostgreSQL is an open source relational database that also stores and indexes JSON, has built-in full-text search and gains vectors, maps and time series through extensions.
Why: One database can carry a project a long way, with one backup, one set of credentials and one query language, and every major cloud offers it managed.

Watch out

Adding a second database before PostgreSQL has shown a real, measured limit.

MySQL, SQLite and SQL Server

Also called MySQL, SQLite, SQL Server, MariaDB

What: MySQL is common behind websites and PHP apps. SQLite is a whole database in one file inside your app, with no server. SQL Server is common in companies built on Microsoft and .NET.
Why: Choose by what your host, framework and team already support. The core SQL is shared, so skills carry across.
Example: A mobile notes app or a desktop tool: SQLite. A WordPress site: MySQL. An internal tool at a .NET company: SQL Server. A new web app: PostgreSQL.

Watch out

Treating SQLite as a toy: for single-user or read-mostly apps it is often the simplest correct choice.

JSON columns

Also called jsonb, JSON type, GIN index

What: Columns that store JSON inside a relational table. PostgreSQL's jsonb can be queried with operators such as ->> and @> and indexed with GIN; MySQL indexes JSON through generated columns.
Why: They cover the flexible corner of mostly fixed data, such as product attributes or settings, without a second database and while keeping transactions.

SQL

ALTER TABLE products ADD COLUMN attributes jsonb;
CREATE INDEX ON products USING gin (attributes);

SELECT name FROM products
WHERE attributes @> '{"screen": "OLED"}';

Watch out

Putting fields you filter, join or sort on all the time into JSON instead of real columns.

Constraints

Also called NOT NULL, UNIQUE, foreign key, CHECK constraint

What: Rules the database enforces on every write: NOT NULL, UNIQUE, CHECK and foreign keys, which make a value refer to an existing row in another table.
Why: They stop bad data at the door, whatever client writes it, so you do not clean duplicates and orphaned rows by hand later.

Watch out

Leaving constraints off "for flexibility" and discovering thousands of broken rows months later.

Migrations, drivers and ORMs

Also called migration, ORM, Prisma, SQLAlchemy, Entity Framework, database driver

What: A driver sends queries from your code; an ORM maps tables to objects. Migrations are small, versioned schema change scripts kept in Git and run in order on every environment.
Why: Migrations keep every laptop and server on the same schema. Parameterised queries, which drivers and ORMs use, prevent SQL injection.

Watch out

Editing the production schema by hand in a GUI, so environments drift apart.

One Postgres is often enough

Also called PostgreSQL extensions, PostGIS, boring stack

What: One PostgreSQL database with jsonb, full-text search, pgvector, and TimescaleDB or PostGIS when needed covers relational data, flexible fields, search, similarity, time series and locations.
Why: Every extra database is another thing to secure, back up, pay for and keep in sync. Start with one and add others when a limit is measured.

SQL

CREATE EXTENSION IF NOT EXISTS vector;      -- pgvector
CREATE EXTENSION IF NOT EXISTS pg_trgm;     -- fuzzy text
CREATE EXTENSION IF NOT EXISTS postgis;     -- locations
CREATE EXTENSION IF NOT EXISTS timescaledb; -- time series

Watch out

Assuming every extension is available on your host: check your managed provider's supported list first.

Go deeper