Search⌘ K
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Solution: Use BLOB Data Types As Needed

Let's use the BLOB data type for storing images.

We'll cover the following...

If any of the issues described in the previous lesson of this chapter apply to us, we should consider storing images inside the database instead of in external files. All database brands support the BLOB data type, which we can use to store any binary data.

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

Advantages of storing an image in a BLOB column

If we store an image in a BLOB column, all the issues mentioned above are resolved:

  • The image data is stored in the database. There is no extra step to load it and there’s no risk that ...