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

Solution: Use BLOB Data Types As Needed

Explore how to store images inside SQL databases using BLOB data types to avoid issues with external files. Understand the benefits such as automatic deletion, transaction safety, access control, and how to handle image loading and retrieval for applications.

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 ...