Databases and storage

Each table holds one kind of thing (users, orders, products) and every row has the same columns. Rows point at rows in other tables through keys, so an order stores a customer id instead of a copy of the customer, and a query can join the tables back together in one step.

The structure (the schema) is declared up front and enforced by the database, which is what makes relational databases good at keeping data consistent. PostgreSQL, MySQL, SQLite, SQL Server and Oracle are all relational.

Document stores such as MongoDB and Firestore keep each record as a JSON-like document that can nest lists and objects. Key-value stores such as Redis map a key straight to a value. Wide-column and graph databases cover more specialised shapes of data.

NoSQL databases usually let each record have its own shape and are built to spread across many machines. The trade-off is that joins, strict schemas and guarantees across many records are weaker or left to the application, although most now offer transactions in some form.

SQLite is a library that an app links in. The whole database, tables, indexes and all, is one file on disk, and reading it is a function call rather than a trip over the network. It ships inside Android, iOS, every major browser and countless desktop apps, which makes it the most widely deployed database there is.

It speaks standard SQL, supports transactions and handles databases far larger than most apps will ever need. Its limit is concurrency: many readers can work at once, but only one writer at a time. Extensions add full-text search (FTS5), JSON functions and vector search (sqlite-vec), and services such as Turso host it for web apps.

PostgreSQL stores data in tables, speaks rich SQL and keeps data correct under heavy concurrent use with transactions and strict constraints. Beyond the basics it has JSONB columns for document-style data, arrays, full-text search, window functions and row level security.

Its extension system is a big part of its appeal: PostGIS adds maps and geography, pgvector adds AI vector search, pg_cron adds scheduled jobs. Supabase, Neon, AWS RDS, Google Cloud SQL and Azure all offer it as a managed service, so you rarely have to run the server yourself.

MySQL is a relational database server owned by Oracle, with a free, open-source Community Edition. It is fast for typical web reads and writes, easy to host, and supported by practically every shared host and cloud. MariaDB was forked from it by its original authors and stays largely compatible.

Compared with PostgreSQL it has historically been simpler to get running, with a smaller feature set: fewer data types, fewer extensions and a less strict default mode. Tools such as PlanetScale and Vitess spread it across many machines.

In MongoDB a collection holds documents (stored as BSON, a binary form of JSON), and each document can contain nested objects and lists. A customer can carry their addresses inside the same document, so reading them needs no join. Queries, indexes and aggregation pipelines work on those document fields.

Documents in one collection do not have to share a shape, which makes early development quick. Multi-document transactions exist, but the design pushes you to keep related data together. MongoDB Atlas is the company's managed cloud; the Community Server is free to run yourself under the SSPL licence.

Firestore stores documents in collections, and apps talk to it directly through the Firebase SDKs rather than through a server of your own. Listeners push every change to connected clients instantly, and the SDKs cache data so an app keeps working without a connection and syncs when it comes back.

Access is controlled by security rules that Google checks on every read and write. Billing is per operation: you pay for document reads, writes and deletes plus storage, so a screen that reads thousands of documents on every visit gets expensive. Queries are deliberately limited so that they always stay fast.

Redis keeps data in memory and maps keys to values: strings, lists, sets, sorted sets, hashes and streams. Because nothing waits on a disk, reads and writes are extremely fast, which makes it the usual choice for a cache in front of a slower database, for session storage and for counting things such as requests per minute.

It can save to disk, but it is usually a fast helper beside a main database rather than the only copy of important data. A 2024 licence change led to Valkey, a fully open-source fork under the Linux Foundation, and Redis later added an AGPL option. Upstash and Redis Cloud host it for you.

Ordinary databases store a table row by row, which suits looking up one order. ClickHouse stores it column by column and compresses each column, so a question such as page views per day for a year reads only the two columns it needs. That design makes aggregate queries over huge event tables very fast.

It is built for inserting data in batches and querying it, not for updating single rows many times a second, so it usually sits beside a normal database: the app writes orders to PostgreSQL and streams events to ClickHouse. You can run it yourself for free or use ClickHouse Cloud.

A full-text index splits text into words, normalises them (lower case, word stems) and records where each word appears, so a search for 'running shoes' finds 'shoes for runners' quickly and ranks it with a formula such as BM25. A plain LIKE query cannot rank results and gets slow on big tables.

Many databases have it built in: SQLite's FTS5 extension, PostgreSQL's tsvector columns, MongoDB Atlas Search. Dedicated engines such as Elasticsearch, OpenSearch, Meilisearch and Typesense add typo tolerance and filters. Combining it with vector search gives hybrid search.

IndexedDB stores JavaScript objects (files and blobs included) in object stores with indexes, inside the browser and separately for each website. It holds far more than localStorage, often hundreds of megabytes or more depending on the browser and free disk space, and it never freezes the page because every operation is asynchronous.

Its raw API is event-based and clumsy, so most apps use a wrapper such as Dexie.js or idb. Data stays in that one browser on that one device; syncing it anywhere else is up to the app. Browsers may clear it when storage runs low unless the site asks for persistent storage.

Dexie wraps the browser's IndexedDB in a promise-based API: declare tables and indexes once, then query with calls such as where('age').above(18). Its liveQuery and React hooks re-run a query whenever the data underneath changes, which suits offline-first apps.

The library is open source. Dexie Cloud is an optional service from the same team that adds sync between devices, sign-in and sharing.

An ORM maps tables to classes or types, so user.posts can fetch a user's posts and a save call writes changes back. Most also manage migrations and give typed results. Examples: Prisma and Drizzle in TypeScript, SQLAlchemy and Django's ORM in Python, Hibernate in Java, Room on Android.

The convenience has a cost: generated queries can be slow or wasteful (the classic N+1 problem, one query per item in a list), and complex reports are often easier in plain SQL. Query builders sit in between, keeping the shape of SQL with type safety.

You write models in schema.prisma, run a command, and Prisma generates a client whose every query and result is typed, so a misspelt field fails at compile time. Prisma Migrate turns schema changes into SQL migration files. It supports PostgreSQL, MySQL, SQLite, SQL Server, CockroachDB and MongoDB.

The ORM is open source. The company also sells Prisma Postgres (a hosted database), connection pooling and caching, and app hosting, all optional.

Tables are defined in TypeScript files, and queries use functions that mirror SQL (select, from, where, join), so anyone who knows SQL can read them. Types flow from the table definitions into every result. drizzle-kit generates and runs migrations from those definitions.

It has no separate engine and very few dependencies, so it runs well in serverless and edge environments such as Cloudflare Workers and Vercel Edge. It supports PostgreSQL, MySQL and SQLite, including hosted flavours such as Neon, Turso and PlanetScale.

sqlx is asynchronous (it runs on Tokio) and supports PostgreSQL, MySQL and SQLite. Its query macros connect to a development database, or read a saved snapshot, at build time and confirm that each query is valid and that the Rust types match the columns, so a renamed column breaks the build instead of the app.

It is not an ORM: you write plain SQL and get typed rows back. It also ships a migration runner.

better-sqlite3 binds the SQLite C library into Node. Queries run synchronously, which sounds wrong for Node but suits SQLite, because each call finishes in microseconds and there is no network to wait on. It supports transactions, prepared statements and loading extensions such as sqlite-vec.

As a native module it has to match the Node version and platform it runs on, and it cannot run on edge runtimes. Newer Node versions also include a built-in node:sqlite module.

Point PostgREST at a database and every table, view and function becomes an endpoint: a request such as GET /orders?status=eq.paid returns JSON. It passes the caller's role to PostgreSQL, so permissions come from database roles and row level security rather than from code in the API.

Supabase uses PostgREST for its automatic API, which is why its client library can query tables directly. The flip side is that a table without proper policies can be read by anyone holding the public key.

Instead of editing tables by hand, each change (add a column, create an index, rename a table) goes into a migration file that is committed with the code. A tool records which migrations a database has already run and applies only the new ones, in order.

Prisma Migrate, drizzle-kit, the Supabase CLI, Django, Laravel, Flyway and Liquibase all work this way. A migration that rewrites a big table can lock it, so large changes are usually split into several safe steps.

Without an index, finding orders by customer means scanning the whole table. With an index on customer_id the database jumps straight to the matching rows. A composite index covers several columns in a set order, and a unique index doubles as a rule that no two rows may share a value.

Indexes are not free: each one takes storage and slows inserts and updates a little. The EXPLAIN command shows whether a query actually uses one. Adding the right index is often the single biggest speed-up for a slow page.

Placing an order might insert the order, reduce stock and record a payment. Inside a transaction, a failure halfway rolls everything back, so stock is never reduced for an order that does not exist. ACID names the guarantees: atomic (all or nothing), consistent (rules hold), isolated (concurrent work does not mix) and durable (committed means saved).

Isolation levels decide how much concurrent transactions can see of each other's work. Relational databases are built around transactions; many NoSQL stores offer them in narrower forms.

With RLS switched on, every query against a table is filtered by policies such as 'a user can read orders whose user_id is their own id'. Because the rule lives in the database, it holds on every path in: the app, an admin script or an automatic API.

It matters most on platforms such as Supabase, where apps query tables directly with a public key. A table with RLS switched off, or with a policy that simply says true, is open to anyone who has that key, so every table's policies deserve a review.

A trigger can stamp updated_at on every edit, write an audit row, or create follow-up records when an order arrives. A database function bundles several steps, such as checking and reducing stock, into one call that runs as a single transaction, which avoids race conditions between two buyers.

In PostgreSQL these are usually written in SQL or PL/pgSQL, and Supabase exposes functions to apps as RPC calls. Logic that lives in the database is powerful but easy to forget, so it belongs in migrations and deserves documentation.

pg_cron uses the same schedule syntax as Unix cron, so '0 1 * * *' runs a job at 1 a.m. every day. Jobs are plain SQL or calls to database functions, and their history is kept in a table. Supabase, AWS RDS, Google Cloud SQL and Azure support it.

It suits work that only touches the database (archiving, rollups, expiring rows). Jobs that call outside services are usually better in a queue or a scheduled function. Schedules run in UTC unless configured otherwise, which matters for daily jobs in other time zones.

Each file is an object with a key (its path), the bytes and some metadata, inside a bucket. Apps usually upload through signed URLs, so the browser sends the file straight to storage, and serve files publicly or through short-lived links. The database keeps only the key.

Amazon S3 set the standard API and most others speak it too: Cloudflare R2, Google Cloud Storage, Backblaze B2, Supabase Storage. Prices are per gigabyte stored plus, on many providers, per gigabyte downloaded (egress), which is where bills tend to grow.