Menu

SQLite BLOB: Store and Read Binary Data

SQLite stores each value with a storage class. BLOB is the class for raw byte data. In a regular, non-STRICT table, declaring a column as BLOB gives it BLOB affinity; it does not force every value in that column to be a BLOB. You can inspect a stored value with typeof(). See SQLite’s storage classes and type affinity.

Use a BLOB when an application needs to keep binary content in the database, such as a small image, document, or application-specific byte sequence. For large files, compare the database growth and backup costs with storing the file outside SQLite and keeping its path or identifier in the database.

Create a BLOB column

Declare a column with the BLOB type name:

CREATE TABLE assets (
    id INTEGER PRIMARY KEY,
    data BLOB NOT NULL
);

The declaration gives data BLOB affinity. The value’s actual storage class depends on what the statement inserts. Use typeof(data) to check the stored class rather than relying on the declaration alone.

Insert and inspect a hexadecimal BLOB literal

In SQL, write a BLOB literal as X'...' with an even number of hexadecimal digits. This example stores the six ASCII bytes for SQLite:

INSERT INTO assets (data)
VALUES (X'53514C697465');

SELECT
    typeof(data) AS storage_class,
    length(data) AS byte_count,
    hex(data) AS hex_value
FROM assets;

storage_class  byte_count  hex_value
-------------  ----------  ------------
blob           6           53514C697465

SQLite’s BLOB literal syntax is convenient for short test values. For a real file, read its bytes in application code and bind them as a query parameter instead of constructing SQL text from the file contents.

Store and read a file with Python

Python’s sqlite3 module maps bytes to SQLite BLOB values and maps BLOB values read from SQLite back to bytes. Bind the data with a placeholder:

import sqlite3
from pathlib import Path

connection = sqlite3.connect("files.db")
try:
    connection.execute(
        """CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            data BLOB NOT NULL
        )"""
    )

    image_data = Path("image.png").read_bytes()
    cursor = connection.execute(
        "INSERT INTO files (name, data) VALUES (?, ?)",
        ("image.png", image_data),
    )
    file_id = cursor.lastrowid
    connection.commit()

    row = connection.execute(
        "SELECT data FROM files WHERE id = ?",
        (file_id,),
    ).fetchone()
    if row is None:
        raise LookupError("The saved file was not found")

    Path("image-restored.png").write_bytes(row[0])
finally:
    connection.close()

The ? placeholders keep file bytes separate from SQL syntax. The Python sqlite3 type mapping documents bytes as the value to bind for a BLOB.

  • typeof() returns the storage class of a value.
  • hex() displays BLOB bytes as hexadecimal text.
  • randomblob() creates a BLOB containing pseudo-random bytes.
  • zeroblob() creates a BLOB of a requested length filled with zero bytes.

You can run the SQL examples or open a local database file in the SQLite SQL playground, which runs SQLite in your browser. BLOB result values display as hexadecimal; values over 64 bytes show the first 64 bytes and total length. To inspect the full BLOB, download the database and open it in a local SQLite client.