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
- Connect to your SQLite database in DBBlobEditor
- Navigate to the “image_storage” table in the target schema
- 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
- Connect to SQLite database in DBBlobEditor
- Query the “image_storage” table and locate the target image_blob column
- Double click to View image stored in SQLite BLOB field
- 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)