SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Antipattern: Assume You Must Use Files

Antipattern: Assume You Must Use Files

Let's learn the drawbacks of storing images for web applications.

We'll cover the following...

Conceptually, an image is an attribute in a table. For example, the Accounts table may have a portrait_image column.

MySQL
CREATE TABLE Accounts (
  account_id SERIAL PRIMARY KEY
  account_name VARCHAR(20),
  portrait_image BLOB
);

Likewise, we can store multiple images of the same type in a dependent table. For example, a bug may have multiple screenshots that illustrate it.

MySQL
CREATE TABLE Screenshots (
  bug_id BIGINT UNSIGNED NOT NULL,
  image_id SERIAL NOT NULL,
  screenshot_image BLOB,
  caption VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

That much is straightforward, but choosing the data type for an image is a subject of controversy. Raw binary data for an image can be stored in a BLOB data type, as shown previously. However, many people instead store the image as a file on the filesystem and store the path to this file as a VARCHAR.

MySQL
CREATE TABLE Screenshots (
  bug_id BIGINT UNSIGNED NOT NULL,
  image_id BIGINT UNSIGNED NOT NULL,
  screenshot_path VARCHAR(100),
  caption VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

Software developers argue passionately about this issue. There are good reasons for both solutions, but it’s common for many programmers to be unequivocally convinced that we should always store files external to the database. However, there are several real risks that are associated with this design, and these are described in the following sections.

Files don’t obey DELETE

The first problem is that of garbage collection. If our images are outside the database and we delete the row that contains the ...