Databases and SQL

Design comes before SQL. MySQL Explained spends as long on modelling and normalisation as on MySQL itself: a chapter on creating a model and defining tables, a chapter taking a design through first, second, third and Boyce-Codd normal forms, and two complete worked designs, a human resources and training database and a recipe database, each with review questions and exercises.[14] A beginner's .NET tutorial does the same at smaller scale, choosing SQL Server, drawing the schema on paper, creating a first database and adding test data before any C# is written.[35]

Two Elixir books treat modelling as their subject. Northwind Elixir Traders rebuilds the Northwind Traders database with Ecto migrations, changesets, constraints and associations, choosing SQLite over Ecto's default PostgreSQL on purpose so that SQLite's limits on constraints and foreign keys become the teaching material; a chapter on associations introduces the N+1 query problem and preloading, and later chapters add window functions and work around SQLite's missing INTERVAL.[23] Its sequel, Phoenix Product Codex, models suppliers, brands, categories, families, variants and bundles for a real industrial-equipment trader, with the business reasoning behind each decision, such as storing discounts as integers to avoid floating-point error; the author's earlier prototype, with no foreign keys or joins and integrity enforced in a script, is described as work best left to the database.[28] At the warehouse end, Design Driven Data Engineering puts conceptual, canonical, dimensional and event models before any platform choice.[31]

The SQL most people write is SELECT with WHERE, ORDER BY, GROUP BY and joins, and the two practice books teach it by example. Top 30 SQL Interview Coding Tasks runs thirty tasks against five tables, customers, products, suppliers, orders and order items, because a recruiter reuses one dataset across interviews; its sample covers retrieval, conditions, the IN operator with a list and with a subquery, INSERT, and UPDATE for all rows, one row and a subset.[12] SQL Problem Solving gives each problem a prompt, an input table, the expected output, a solution and a short explanation, and the explanations name real traps: NOT IN with a NULL in the list returns no rows, a LIKE pattern that starts with a wildcard cannot use an ordinary index, and LIMIT is spelt TOP in SQL Server and FETCH FIRST in Oracle.[13] Its expert volume treats pivoting as conditional aggregation, a SUM over a CASE per column, and ends on sargability, whether a predicate can use an index at all.[13]

A DuckDB book reaches the same material from the other side, teaching SELECT, WHERE, LIKE, IN, BETWEEN, joins, aggregation and query plans in its second chapter as the foundation for spatial analysis,[18] and the Ecto book arrives at window functions with partitions and frames by its fifteenth.[23]

Application code reaches a relational database through a driver, and the driver's behaviour matters. High-Performance Java Persistence opens with JDBC batching and finds that the MySQL driver does not send batched statements in one request unless a connection property rewrites them, and that the Oracle driver ignores batching for anything but prepared statements; it recommends a modest batch size of ten to thirty and shows a bulk UPDATE running an order of magnitude faster than batched updates.[15] It also explains why sequences suit batch inserts better than identity columns: the key can be fetched before the insert, so nothing depends on the driver returning generated keys.[15]

Above the driver sits the object-relational mapper. The impedance mismatch between tables and object graphs is what Hibernate and JPA exist to bridge; the persistence context acts as a transactional write-behind cache that flushes entity state changes as SQL at the last moment, and the book's position is that the schema belongs to the database and must be migrated incrementally under version control.[15] The same layering appears elsewhere: Ecto in Elixir,[23] Sequel in Ruby, which Ruby on Roda pairs with the Roda routing framework for a JSON API,[36] Entity Framework and LINQ to Entities in .NET, one chapter of an interview compendium beside SQL Server and T-SQL,[41] and the Mongoose document mapper in Node.js.[26]

The first problem is persistence. Build Your Own Database in Go From Scratch counts as a database only what persists to disk, handles data larger than memory and is built from scratch, which rules in MySQL, PostgreSQL and SQLite and rules out caches and spreadsheets.[1] Ordinary file writes give no durability guarantee, so making files durable means implementing half a database: the fsync system call confirms that written data has reached the medium, and a database answers its client only after it, while atomicity requires that a crash mid-write loses the latest data entirely or not at all.[1] Overwriting a file in place fails both tests, atomic rename fails on power loss unless the directory is also synced, and an append-only log with a checksum on each entry survives a torn write because the corrupt last entry can be detected and ignored on recovery.[1]

The second problem is indexing, and the disk decides it. Solid-state latency is three orders of magnitude above RAM, so only two structures work on disk: the B+tree and the log-structured merge tree.[1] A B+tree is a balanced n-ary tree, short so that a lookup costs few disk reads, with internal nodes holding only keys and nodes sized in pages because disk input and output happens in pages; an LSM-tree instead buffers updates in a small structure and merges them down through exponentially larger levels, trading write amplification against query cost.[1] For crash safety the book chooses a copy-on-write B+tree, in which an update copies the path from leaf to root and leaves the old tree intact, which also gives snapshot isolation and effortless recovery, and it explains the alternative that MySQL calls double-write: save full copies of the updated pages, sync them, then update in place, so that recovery can blindly reapply the copies.[1]

Build a SQL Database Engine in C++ starts in the same place with a different temperament. Its first unit builds a 4,096-byte page with a twenty-byte self-describing header; the slotted layout, in which a directory of four-byte slots grows from one end while variable-length records grow from the other, so a record's slot id survives the movement of its neighbours; erase, fragmentation and compaction; a pager that moves pages between memory and one file, with the torn write as the unit's reproduced bug; and a free list.[2] Every claim is measured: the safe memcpy accessors compile to the same instruction as an unsafe pointer cast, and a table of records per page shows a 25 per cent slot tax on sixteen-byte records and space lost to quantisation on kilobyte ones, the arithmetic that later decides B+tree height.[2] Thirteen further units add the B+tree, a buffer pool, records and a catalog, a parser, an executor, joins, aggregation, indexes and a planner, vectorised execution, a write-ahead log with a kill-9 recovery harness, and a capstone checked against PostgreSQL and timed against SQLite; concurrency, MVCC and a wire protocol are out of scope by design.[2]

The same storage questions recur in PostgreSQL's own terms: Database Engine Level Thinking covers heap pages, TOAST, the visibility and free-space maps, the buffer pool, WAL and full-page writes, then B-trees, HOT updates, index-only scans and hash indexes, written by a backend engineer alongside their own learning as a living draft,[8] and PostgreSQL Internals Mastery: A Comprehensive Guide gives two chapters to the 8 kB page, the ctid, fillfactor, and the buffer cache's clock-sweep eviction and checkpoints.[9] Code a database in 45 steps (Go) breaks the whole build, storage engine, LSM-tree indexes, SQL, transactions and ACID, into forty-five test-driven puzzles of under fifty lines each.[4]

Deep dive into a SQL query follows one query through PostgreSQL 18. The postmaster accepts a connection, authenticates it and starts a dedicated backend process, one per connection, which isolates sessions but limits how far connections scale and makes pooling essential; background processes handle vacuuming, dirty pages and the write-ahead log.[7] The query then passes through the pipeline the book counts as six stages, connection included: the parser builds a parse tree without knowing whether the tables exist; the analyser resolves names, types and permissions; the rewriter expands views and adds row-level security as WHERE conditions; the planner weighs indexes, statistics and join orders to choose a plan; and the executor runs it in a pull-based model, each operator producing a row only when the one above asks.[7] Memory is managed in hierarchical contexts, so destroying a query's context frees everything under it even after an error, and a shared buffer pool caches pages for every process.[7]

The from-scratch books build the same pipeline. Build a Database Server has the reader write a recursive-descent parser, a type checker that finds errors before a statement runs, NULL semantics, query plans as iterators, joins, grouping and aggregates, comparing each piece with PostgreSQL, MySQL, SQLite and DuckDB.[3] How Query Engines Work, by a committer on Apache Arrow and DataFusion, keeps to the query side: a columnar type system on Arrow, logical and physical plans, a DataFrame API, SQL parsing with a Pratt parser, hash joins, subquery decorrelation, predicate push-down and cost-based optimisation, then parallel and distributed execution.[6] The Go book ends with a SQL-like language whose parser and interpreter are written with nothing but recursion.[1]

A transaction is a group of changes that takes effect entirely or not at all. The ACID properties describe what a database promises about it, and one source is careful about each: atomicity is about all-or-nothing state changes, not concurrency; consistency means conformance to the database's own rules; isolation is the most configurable and most relaxed of the four, with serialisable rarely the default because it is too slow for most applications; durability means that completed work is permanent.[16] The same source separates isolation levels, which apply to transactions, from the consistency models of distributed systems, which apply to single values.[16]

Most engines implement isolation with multi-version concurrency control, keeping old row versions so that readers never block writers. PostgreSQL's version is the subject of a chapter in PostgreSQL Internals Mastery: A Comprehensive Guide: transaction identifiers, the xmin and xmax fields on every tuple, the visibility algorithm, and the vacuum that reclaims old versions, after a chapter on isolation levels with a payment-system case study.[9] Thinking in PostgreSQL frames the same mechanism as the shift an InnoDB engineer must make, from a clustered primary key to a heap plus separate indexes and real MVCC tuples, and from purge to autovacuum, freezing and transaction age.[11] From the application side, the Java book notes that under two-phase locking or MVCC alike writers block conflicting writers, so a large batch should be broken into pieces that release locks promptly, and that no isolation level prevents lost updates across a multi-request conversation, which is why JPA offers optimistic locking.[15] Distributed transactions across regions, sagas against two-phase commit, and CQRS with event sourcing are the ground of System Design Mastery - Advanced.[40]

A key-value store has the simplest interface, get, set and delete by key, and the Go book argues that it is also the layer beneath SQL: relational databases are built on key-value-like storage engines, and SQL is a user interface rather than anything fundamental.[1] Redis is the production example, and two books rebuild it. Build Your Own Redis with C/C++ goes from socket programming and a request-response protocol through an event loop, hashtables, a balanced binary tree, sorted sets, TTL expiration and a thread pool in C and C++,[5] and Rust Projects - Write a Redis Clone follows the CodeCrafters challenge in Rust and Tokio, binding a port, parsing the RESP protocol, implementing GET and SET and active and passive key expiry, and arriving at the actor model for concurrency.[27]

A document database stores JSON-like documents in collections without a fixed schema. MongoDB is the one the application books use: the MEAN tutorial installs it on three operating systems and inserts and finds a first document from the shell,[25] and the Keystone.js book, whose framework creates a MongoDB collection for every model and queries it through Mongoose, explains the 24-character object id that identifies a document across collections.[26] The Angular Firebase Survival Guide (2018) gives the clearest short account of modelling in such a system, for Google's Firestore: a collection is an array and a document an object; documents should stay small, under the one-megabyte limit, so a post's comments go in a subcollection fetched separately; a document reference is the counterpart of a foreign key; and a many-to-many relationship, such as users starring films, belongs in a root collection of its own so that both sides can be queried.[24] Its rule is to use Firestore over Firebase's older Realtime Database unless the dataset is small, the queries simple and there are no relationships to model.[24]

Scaling means a bigger machine or more machines, and the hardware architecture decides which. One source lays out three: shared memory, many cores on one machine, which scales vertically and suits the traditional relational engine; shared disk, independent nodes over a storage network, which Oracle and SQL Server use until the central disk becomes the bottleneck; and shared nothing, machines sharing neither, which scales linearly and is what horizontally scaled databases use.[16] Cassandra, begun at Facebook for inbox search and open-sourced in 2009, is a peer-to-peer shared-nothing store with no special nodes, linearly scalable in Netflix's benchmarks and suited to time series such as events and sensor readings; it is not suited to strong consistency, to ad hoc queries, since the data model must be designed around the queries, or to small loads, and because it has no transactions its atomicity holds only for one write at the row level on each node.[16]

The Apache Ignite book describes a memory-centric platform that serves as a cache, a compute grid or a database with SQL and ACID transactions. Its architecture chapter explains a shared-nothing topology of identical nodes with no master, and how data is partitioned: because a plain hash table fixes the number of nodes, Ignite maps keys to one of 1,024 partitions and partitions to nodes by rendezvous hashing, so that a topology change moves the least data.[17] Its durable memory keeps data off the Java heap in fixed-size pages with the same layout in memory and on disk, each with a type, version, checksum and id in its header, and its data pages pack items from the front and values from the back, the slotted layout once more; the database chapter covers SQL and scan queries, collocated distributed joins and native persistence.[17]

Transactional and analytical work want different engines. The Go book draws the line by latency and cost: OLTP touches small amounts of data through indexes and needs immediate answers, which is what B+trees and LSM-trees serve, while OLAP scans large amounts for aggregations and joins, indexes little, stores by column and tolerates latency, which is why ClickHouse, DuckDB and the big-data systems are analytical only.[1] A business primer describes the same split as operational relational databases feeding a column-store warehouse that analysts and business-intelligence tools read.[29]

DuckDB is the embedded analytical engine, a single file with no server, and Spatial Data Management with DuckDB uses it as a geographic information system: after what makes it different and when not to use it, the book teaches SQL, then loads Shapefile, GeoJSON and GeoParquet through the spatial extension, runs geometry operations and spatial joins, and maps the results from Python.[18] SQLite is the embedded transactional counterpart, and the two Elixir books show it carrying real applications, with its type affinity, limited constraints and missing INTERVAL as the price.[23][28] Beyond one machine, Spark reads from JDBC databases such as MySQL and from HBase, Hive and Cassandra as well as files, keeps its resilient distributed datasets in memory for speed over Hadoop's disk-based MapReduce, and offers Spark SQL beside streaming and machine learning.[33]

A graph database stores entities and the relationships between them and is queried by matching patterns. Cypher is the common language, and Cypher 103 with ladybugdb teaches it on LadybugDB, an embedded graph engine that installs in one line, through one evolving project of AI agents, their owners, tools and agreements, from schema definition and pattern matching to recursive queries, shortest paths, subqueries and transactions, with exercises in every chapter and Neo4j as the comparison point.[20] GraphDuck : duckdb for embedded Ai agents and graphs argues the opposite case, that graph thinking belongs inside the database one already has: it builds adjacency-list graphs, PageRank with recursive CTEs and shortest paths in plain SQL on DuckDB, then property graphs, triple stores, hypergraphs and metagraphs, with the DuckPGQ extension for the SQL:2023 standard's SQL/PGQ pattern matching.[19] Metagraph for AI Agents takes the modelling further, from the observation that a triple is not enough, through hypergraphs, whose edges join sets of nodes but cannot themselves be referenced, to metagraphs, where an edge can be a node, implemented side by side in a relational schema, in Cypher and in TypeDB's TypeQL.[38]