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 clients must connect to in order to perform queries and operations. SQLite, as the name suggests, is much more lightweight and stores the entire database in a single file. Its small footprint makes it a popular choice for embedding a database in other applications. Google Chrome uses SQLite to store internal data such as browsing history.

Finally, a point on terminology. The term “database” is overloaded. Technically, “database” refers to the collection of entities and their relations that make up the application’s data. Programs like Postgres and SQLite are examples of relational database management systems (RDBMS). In this chapter I’ll use “database system”, “database engine” or just “system” to refer to the program that manages the database.

What does a database offer?

Working with a database entails understanding a whole other programming paradigm, learning a new language and figuring out how to integrate the system into an existing application. Why go to all this bother when we can just store application data in a plain file?

Databases offer numerous benefits over a plain file. They provide assurances that your data is protected from loss and corruption. If you use plain files, you’re responsible for writing all that code yourself: opening and parsing the file, making bug-free modifications, gracefully handling failures, catching all of the edge cases where data can get corrupted and so on and so on. Doing all this correctly is a huge task. Just as you probably don’t want to be writing your own OS, you probably don’t want to be writing your own data management layer. On top of protecting you from disaster, databases also offer a bunch of neat features. They allow concurrent access by multiple users, improving your application’s performance. They offer a sophisticated query interface in the form of SQL, meaning you avoid having to hand-code every query. It’ll also run those queries much faster than anything you could do yourself.

In short, database systems abstract away the gory details of data storage and management. They provide a query interface that, while sometimes a little clunky, is immensely powerful and creates a clear boundary between your data and your business logic. They provide important assurances so that you don’t have to worry about data loss or corruption. Database systems have four properties that guarantee that the data they contain remains safe even in case of failures: atomicity, consistency, isolation and durability. Together they go by the acronym ACID. Let’s look at each in turn:

Atomicity

Atoms were so called because they were believed to be indivisible. Of course, we now know that they’re not indivisible after all – awkward! In computing terms, an operation or sequence of operations is atomic if it is either performed to completion or not performed at all. No halfway state is allowed, even if there’s some kind of catastrophic power failure mid-way through. This is very important from the point of view of data safety because we never want to get into a situation where an update to the data is only partially performed.

The canonical example in the textbooks is transferring money between two accounts. Peter wants to pay Paul £100. This involves two steps. First, credit Paul’s account with £100:

1 UPDATE accounts
2 SET balance = balance + 100
3 WHERE account_name = "Paul";

Then, debit Peter’s account by £100:

1 UPDATE accounts
2 SET balance = balance - 100
3 WHERE account_name = "Peter";

Note that it doesn’t matter which statement is executed first as long as they both execute. However, if an error were to occur after crediting Paul but before debiting Peter (perhaps the database system crashes or Peter doesn’t have enough money in his account) then the system erroneously “creates” money by giving Paul money without taking it from Peter. A fortuitous outcome in this case, but if the statements were executed in a different order money would disappear from the database!

Of course, the system can’t perform its operations instantaneously so there will inevitably be times when it is in the middle of performing some kind of modification to the database. How do we prevent a failure at this point from messing up the entire database? We introduce a concept known as a transaction:

 1 BEGIN
 2 
 3 UPDATE accounts
 4 SET balance = balance + 100
 5 WHERE account_name = "Paul";
 6 
 7 UPDATE accounts
 8 SET balance = balance - 100
 9 WHERE account_name = "Peter";
10 
11 COMMIT

The transaction is created by wrapping the steps in BEGIN and COMMIT statements. A transaction consists of a sequence of operations that must all succeed or all fail. We’ll see later how a database system ensures that this happens, but at a high level the system simply doesn’t consider a transaction to be completed, or committed, until all of its operations have been successfully committed. If one fails, the system performs a rollback, undoing all of the changes to return the database to its state before the transaction began.

Consistency

The consistency property ensures that the database always makes sense from the application’s point of view. A table schema defines the table’s attributes and their types. For example, a user may have a “salary” attribute, which must be a number. Constraints further restrict the possible values that an attribute can have. The “salary” attribute might be constrained to never contain a negative value because a salary can never be less than zero. If we gather all of the table schemas from a database, we have a set of expectations about the data. We have data safety when the data is consistent with these expectations. Some expectations may span multiple entities. Referential integrity requires that a foreign key in one table refers to a valid primary key in another.

Very common constraints are to state that a column can’t contain a NULL value or that every value must be unique:

1 CREATE TABLE products (
2     product_no integer,
3     name text NOT NULL,
4     price numeric,
5     UNIQUE (product_no)
6 );

In this example table, every product must have a name and its product_no must be unique. More sophisticated, custom constraints are also possible:

1 CREATE TABLE products (
2     product_no integer,
3     name text NOT NULL,
4     price numeric CHECK (price > 0),
5     UNIQUE (product_no)
6 );

Now the database will abort, or completely undo, any attempt to create or update a product with a negative price. This fits with our basic understanding of how prices work. It might sound obviously ridiculous to try to give a product a negative price, but it could happen quite easily if you’re applying a series of discounts to a product and make a mistake in the calculations. Constraints don’t mean that you don’t need to do any validation or testing in your application code. You should still try to detect invalid data as soon as it’s generated and prevent it ever getting near the database. Constraints are more like a last line of defence.

How much complex business logic you should encode in the database is a matter of debate. If there are multiple applications connecting to the same database – a web app, a mobile app etc – then it makes sense to write the logic once in the database rather than re-implementing it (and possibly introducing bugs) in each new client app. Sceptics retort that you’ll end up with business logic spread through constraints and each client app, creating an unholy mess.

Isolation