Store images in SQLite BLOB

by

Posted on by

SQLite BLOB Type Features

  • SQLite is a typeless database (dynamic typing), BLOB is the recommended type for binary data storage
  • No fixed size limit for BLOB columns (limited only by the maximum SQLite database file size: ~140TB)
  • Binary-safe storage (no character encoding/decoding for image data)
  • Lightweight and file-based (no server process, ideal for embedded apps/mobile/desktop tools)
  • Supports incremental BLOB I/O (read/write BLOB data in chunks for large images)
  • No separate tablespace/storage configuration (all data stored in a single .db file)

Step 1: Create a Table with BLOB Column

Basic Table (BLOB for Image Storage)

-- Create table in SQLite (via sqlite3 command line or SQLite Studio)
CREATE TABLE image_storage (
    image_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Auto-increment integer primary key
    image_name TEXT NOT NULL, -- TEXT for cross-platform compatibility
    image_blob BLOB NOT NULL, -- BLOB for image binary data
    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

Optimized Table (With Index on Name for Fast Filtering)

CREATE TABLE image_storage (
    image_id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_name TEXT NOT NULL,
    image_blob BLOB NOT NULL,
    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Add index on image_name (avoid indexing BLOB column)
CREATE INDEX idx_image_name ON image_storage(image_name);

Step 2: Insert Images into SQLite BLOB

Method 1: Using sqlite3 Command Line (Local File)

-- Prerequisite: Open SQLite database file (sqlite3 command line)
sqlite3 your_database.db;

-- Method A: Insert using readfile() function (SQLite 3.41.0+)
INSERT INTO image_storage (image_name, image_blob)
VALUES (
    'product_photo.jpg',
    readfile('/path/to/product_photo.jpg')
);

-- Method B: Insert using sqlite3 binary (batch mode, legacy compatible)
sqlite3 your_database.db "INSERT INTO image_storage (image_name, image_blob) VALUES ('product_photo.jpg', readfile('/path/to/product_photo.jpg'));"
COMMIT;

Note:

1. readfile() requires SQLite 3.41.0 or newer (use legacy methods for older versions)

2. File path must be absolute (or relative to the sqlite3 working directory)

3. For Windows paths: Use forward slashes (C:/images/product_photo.jpg) or double backslashes

Method 2: Using DBBlobEditor

Insert Single Image into SQLite BLOB

  1. Connect to your SQLite database in DBBlobEditor
  2. Navigate to the “image_storage” table in the target schema
  3. Click “+” button to add a record, and then Edit image stored in SQLite BLOB field

Batch Insert Images into SQLite BLOB

Step 3: Retrieve and Restore SQLite BLOB to Image

Method 1: Using SQLite Query + Python Script

-- Step 1: Get BLOB data (SQLite query)
SELECT image_blob FROM image_storage WHERE image_id = 1;

-- Step 2: Restore to file (Python script)
import sqlite3

# Connect to SQLite database file
conn = sqlite3.connect('your_database.db')
cur = conn.cursor()

# Fetch BLOB data
cur.execute("SELECT image_blob FROM image_storage WHERE image_id = 1")
blob_data = cur.fetchone()[0]

# Write to local file
with open("exported_photo.jpg", "wb") as f:
    f.write(blob_data)

# Cleanup
cur.close()
conn.close()

Method 2: DBBlobEditor Export

Retrieve and Restore Single Image

  1. Connect to SQLite database in DBBlobEditor
  2. Query the “image_storage” table and locate the target image_blob column
  3. Double click to View image stored in SQLite BLOB field
  4. Click menu “File” -> “Save” to save BLOB data as a local image (JPG/PNG/GIF/WEBP/etc.) file

Batch Export SQLite BLOB to Images

SQLite Specific Notes

  • For very large images (>100MB): Use incremental BLOB I/O (sqlite3_blob_open() in C API) to avoid memory overflow
  • Enable WAL (Write-Ahead Logging) mode for better performance with BLOB writes:PRAGMA journal_mode = WAL;
  • Compact database after large BLOB deletions: VACUUM; (recovers disk space)
  • Avoid storing extremely large BLOBs in SQLite for high-concurrency scenarios (use file paths + external storage instead)
  • SQLite has no user privilege system – protect the .db file via OS-level permissions
  • Use parameterized queries in applications to insert BLOB data (prevents SQL injection)

Related Guides