Storing Images in a Database

An SQL database can store image bytes, but capability does not settle whether the bytes belong there. The decision depends on transaction boundaries, file size, read patterns, backups, replication, delivery, and cleanup.

Think of the database as a library catalog. The catalog records ownership, format, location, and status. The archive can be a BLOB column in the same building or a separate file store. Either choice works only when the catalog and archive still agree after uploads, replacements, backups, and failures.

How to Choose the Storage Model

Choose database BLOBs or external file storage against the same criteria:

• Atomicity: must the bytes commit or roll back with one database row?
• Size: how large is one image, and how quickly will the complete collection grow?
• Read path: does the application stream individual images or repeatedly fetch them beside ordinary rows?
• Delivery: does the image need direct file serving, caching, range requests, or a media-processing pipeline?
• Recovery: can the chosen backup and restore process recover matching metadata and bytes?
• Access: which system decides whether an image is public, private, or restricted?

Database storage can simplify atomic row-plus-file changes and one-system backups. It also enlarges the database, backup set, restore job, and replication stream. External storage keeps ordinary queries and database backups smaller, but introduces cross-system consistency and cleanup work.

Ordinary product photos and article images often benefit from external storage. Small signatures, generated thumbnails, or controlled attachments can justify BLOB storage when their transaction and recovery requirements outweigh separate delivery.

How to Store Image Metadata

A metadata row gives the catalog one reviewable record for each image:

CREATE TABLE product_images (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT NOT NULL,
  object_key TEXT NOT NULL UNIQUE,
  original_filename TEXT,
  mime_type TEXT NOT NULL,
  byte_size BIGINT NOT NULL CHECK (byte_size > 0),
  width INT NOT NULL CHECK (width > 0),
  height INT NOT NULL CHECK (height > 0),
  checksum_sha256 CHAR(64),
  processing_state VARCHAR(20) NOT NULL DEFAULT 'pending',
  visibility VARCHAR(20) NOT NULL DEFAULT 'private',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP
);

CREATE INDEX product_images_product_created_idx
ON product_images (product_id, created_at DESC);

This example uses PostgreSQL-compatible identity and check syntax. A MySQL version would use AUTO_INCREMENT, InnoDB, and equivalent constraints. The storage model stays the same: a generated key locates the bytes, while typed columns describe the facts needed by application queries.

Do not store a temporary signed URL as the durable location. Store the internal object key and generate a public or signed URL when serving the image. URLs can expire or change domains without changing image ownership.

How to Record an Upload from PHP

The upload handler should validate the file, generate its storage key, write the bytes, and record metadata through a prepared statement. The storage client is left outside this example because the boundary matters more than one provider library.

<?php
$file = $_FILES['image'] ?? null;

if (!$file || $file['error'] !== UPLOAD_ERR_OK) {
    throw new RuntimeException('Upload failed');
}

$maxBytes = 5 * 1024 * 1024;
if ($file['size'] <= 0 || $file['size'] > $maxBytes) {
    throw new RuntimeException('Image size is not allowed');
}

$finfo = new finfo(FILEINFO_MIME_TYPE);
$mimeType = $finfo->file($file['tmp_name']);
$allowedTypes = ['image/jpeg', 'image/png', 'image/webp'];

if ($mimeType === false || !in_array($mimeType, $allowedTypes, true)) {
    throw new RuntimeException('Image type is not allowed');
}

$dimensions = getimagesize($file['tmp_name']);
if ($dimensions === false) {
    throw new RuntimeException('Image dimensions are unavailable');
}

[$width, $height] = $dimensions;

$objectKey = sprintf(
    'products/%d/images/%s',
    $productId,
    bin2hex(random_bytes(16))
);

// Write the validated bytes through the storage service here.

$statement = $pdo->prepare(
    'INSERT INTO product_images
     (product_id, object_key, original_filename, mime_type, byte_size, width, height)
     VALUES
     (:product_id, :object_key, :original_filename, :mime_type, :byte_size, :width, :height)'
);

$statement->execute([
    'product_id' => $productId,
    'object_key' => $objectKey,
    'original_filename' => basename($file['name']),
    'mime_type' => $mimeType,
    'byte_size' => $file['size'],
    'width' => $width,
    'height' => $height,
]);

The server generates the object key and treats the original filename as untrusted display metadata. Fileinfo performs the MIME check, while getimagesize() reads dimensions only because PHP warns that a non-image file can pass that function. The database insert uses parameters, while the storage write remains an explicit step with its own failure path.

When Database BLOBs Fit

MySQL 9.7 defines four BLOB types with different maximum lengths. PostgreSQL 18.4 provides bytea for variable-length binary strings. These types make small binary values ordinary row data, which can be useful when one database transaction must protect both metadata and bytes.

Set an application limit below the type's theoretical maximum:

CREATE TABLE image_blobs (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT NOT NULL,
  mime_type VARCHAR(80) NOT NULL,
  byte_size BIGINT NOT NULL
    CHECK (byte_size > 0 AND byte_size <= 1048576),
  checksum_sha256 CHAR(64) NOT NULL,
  image_bytes BYTEA NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The one-megabyte rule is an example application limit. Choose the actual limit from upload requirements, memory tests, backup growth, and response behavior. MySQL would use the appropriate BLOB family type and enforce the same application rule.

A BLOB design should also keep metadata queries separate from byte reads. Listing screens need IDs, dimensions, states, and perhaps a serving route. They do not need every binary payload in the result.

How Images Affect Backups and Memory

Image bytes inside SQL increase logical dumps, physical backups, restore time, replica traffic, storage snapshots, and disaster-recovery transfer. A backup that once held only compact relational rows can grow with every original upload and generated variant.

That growth can be acceptable when the recovery objective allows it. Measure backup duration, retained size, restore duration, and replica lag with production-shaped image volume before selecting BLOB storage.

Memory on read is the caveat many short comparisons omit. MySQL documents that a buffer used with a BLOB column grows dynamically and can become as large as the largest BLOB read during a scan. Its BLOB documentation also warns that result size is constrained by available memory and communication buffers. Application drivers can allocate their own buffers as values cross the connection.

Exclude image bytes from ordinary BLOB table queries. Fetch metadata for lists, then retrieve or stream one image by ID on the serving path:

SELECT id, product_id, mime_type, byte_size, created_at
FROM image_blobs
WHERE product_id = 4821
ORDER BY created_at DESC;

SELECT mime_type, byte_size, image_bytes
FROM image_blobs
WHERE id = 917;

External storage has a different recovery cost. Database backups remain smaller, but recovery must prove that every restored row still points to an available object and that object retention matches database retention.

How to Delete and Replace Images

SQL and external storage do not share one atomic transaction. Replacement code therefore needs an order of operations and a repair path.

1. Validate and write the replacement under a new generated key.
2. Insert the new metadata row or update the current-image reference in SQL.
3. Mark the old row inactive or set deleted_at.
4. Serve the replacement only after processing succeeds.
5. Queue deletion of the old object after a retention period.
6. Retry failed cleanup and sweep for unreferenced objects.

The new archive item reaches storage before the catalog points to it. If the SQL write fails, an orphan sweep can remove the unused object. Deleting the old object first creates the harder failure because the catalog can lose its only valid image before the replacement is ready.

Database BLOBs can update row metadata and bytes inside one transaction, but versioning and retention still need explicit rules if older images must remain recoverable.

How to Protect Private Images

Authorization does not follow automatically from storage location. A BLOB query and an external-object request both need an application rule that checks ownership and visibility before returning bytes.

Keep private external objects outside a public bucket path and serve them through an authorized route or short-lived signed URL. Keep database BLOB reads behind a query that applies the same ownership and permission checks. Validate type and size before storage, and do not trust a browser-supplied MIME field.

Read PHP security fundamentals before building a public upload route. Prepared statements protect the SQL grammar, while upload validation, storage permissions, output headers, and authorization protect separate boundaries.

Common Pitfalls

Selecting Image Bytes on List Pages

Symptom: a product list consumes excessive memory and transfers far more data than it displays. Cause: a broad SELECT list includes every BLOB value. Fix: select metadata columns for lists and fetch one binary value only on the image-serving path.

Using Original Filenames as Paths

Symptom: uploads overwrite each other or create unsafe storage paths. Cause: the application turns user-controlled filenames into object keys. Fix: generate a random server-side key, retain the basename only as display metadata, and keep authorization out of the path string.

Leaving Orphans After a Failed Write

Symptom: storage contains objects without rows, or rows point to missing objects. Cause: the file write and SQL write were treated as one transaction. Fix: define the operation order, record failures, retry cleanup, and run reconciliation using stored keys and checksums.

Backing Up Only Half the System

Symptom: a restored database contains image rows whose objects are unavailable. Cause: database and external storage retention were planned separately. Fix: back up both systems, align retention, and test a restore that opens representative images through restored metadata.

Frequently Asked Questions

Should images be stored in a database?

Store image bytes in the database when they are small, transactionally tied to the row, and worth carrying through database backups and replication. Store ordinary web images in a controlled file or object store when independent serving, caching, lifecycle rules, or large files matter more.

What should the database store for an external image?

Store a generated object key, owner relationship, MIME type, byte size, dimensions, checksum, processing state, visibility, and timestamps. Keep the original filename only as untrusted display metadata. The row should describe the image without depending on a public URL that can change.

Why can reading a BLOB use so much memory?

MySQL may enlarge an internal buffer to hold the largest BLOB value read during a scan, while a client driver may allocate another buffer as the value crosses the connection. A single large value can therefore raise memory use on both sides even when the query returns only one row.

How should an application replace an image?

Write and validate the replacement first, update the database record or insert a new version, then queue deletion of the old object after the replacement is usable. Record failures for retry because SQL and external storage do not share one atomic transaction.

How do you serve a private image without exposing a public, guessable URL to it?

Route the request through a PHP script that checks the visitor's session and permissions before reading the file, instead of linking directly to a path under the web root. Store the file outside the public directory so a leaked or guessed URL cannot bypass the check.

Should the file extension of an uploaded image be trusted to determine its type?

No. A renamed file can carry any extension regardless of its actual content. Verify the type from the file's real content, such as its detected MIME type or the dimensions an image library reads from it, and reject anything that fails that check before storing metadata or a path.

What to Read Next

Use SQL schema design basics to model ownership and lifecycle state. Continue with PostgreSQL or MySQL for database-specific storage and recovery behavior.