Databases

Introduction

There comes a point in every budding developer’s life when they need to use a database for the first time. The function of a database – reliably storing and retrieving data – is so fundamental that it’s hard to do much programming without encountering one. Yet databases sometimes seem a little unfamiliar, a little other. The database is separate to your code and interacting with it often requires using a different language, SQL. It’s tempting to learn the bare minimum and stick to what you’re comfortable with. Databases also have a reputation for being a little boring. Perhaps it’s the old-fashioned syntax of SQL or all those tutorials about storing employee records.

In this chapter, I hope to convince you that databases are in fact deeply interesting and well worth engaging with properly. They’re like a microcosm of computer science. Parsing a SQL query and generating an execution plan touches on programming language design and compilers. Databases rely on clever data structures to enable fast access to huge amounts of data. Sophisticated algorithms ensure correct concurrent behaviour and that data is never lost. The database is one of those applications that acts as a linchpin, drawing together everything that we’ve studied so far.

Databases come in a huge variety of shapes and sizes. I’m going to focus solely on relational databases, which are by far the most popular. We’ll start with the theoretical underpinnings of relational databases. From there, we’ll look at the four important guarantees that databases provide, which go by the funky acronym of ACID. The rest of the chapter will explore a typical database architecture. Due to space constraints, I’m going to assume that you have worked with a database before and can do basic SQL queries. Maybe you’ve even designed a schema for a project. But you don’t yet know why or how it all works. If this is all new to you, worry not. Introductory resources are referenced in the further reading. We won’t cover any advanced SQL tricks as I believe that they are easier to learn by solving real life problems as you encounter them.

Throughout this chapter, I’ll make reference to two databases, Postgres and SQLite, depending on which seems like the better example for a given point. Both are relational databases and so are conceptually very similar but they have very different implementations. Postgres is a high performance database offering oodles of cutting edge features. It runs as a server that