SQLite BLOB: Store Binary Data, Images and Files (Tested Examples)

Ninad Pathak

October 7, 2026

Binary data, like an image or a PDF, can live right in an SQLite table with the BLOB type. Raw bytes don’t belong in a TEXT column, where encodings and escaping get in the way. In this article I’ll walk you through storing and reading binary data with SQLite’s BLOB.

TLDR

A BLOB stores bytes exactly as you give them. SQLite never converts or re-encodes them.
In SQL, write a BLOB as a hex literal: X'89504E47'.

From code, bind the bytes as a parameter. In the sqlite3 shell, readfile() loads a file.
From Python, pass a bytes object and you get bytes back.
The default limit is 1,000,000,000 bytes per BLOB. The size in BLOB(10) is ignored.

Select length(data) instead of data when you only need the size.

What is a BLOB in SQLite?

BLOB stands for binary large object. In SQLite it’s one of the five storage classes, and SQLite’s docs define it as “a blob of data, stored exactly as it was input” (Datatypes in SQLite). Any binary value fits, such as an image or a hash.

A BLOB differs from text in one important way: SQLite never applies a character encoding to it, and it compares BLOBs byte by byte. “BLOBs are always compared byte-by-byte using memcmp()” (Datatypes in SQLite).

SQLite BLOB syntax

What you want	Syntax
Declare a BLOB column	`data BLOB`
Write bytes as a literal	`X'89504E470D0A1A0A'` (hex digits, two per byte)
Load a file (sqlite3 shell)	`readfile('logo.png')`
Save to a file (sqlite3 shell)	`writefile('out.png', data)`
Size in bytes	`length(data)`
Show as hex	`hex(data)`
Hex text to BLOB	`unhex('9f86d0…')`
Bind from code	a `?` parameter with a byte array (`bytes` in Python)

How to store and read a BLOB

To show you each step, I will use a small attachments table that holds file names and their bytes.

Step 1: Create a table with a BLOB column

sqlite> CREATE TABLE attachments (id INTEGER PRIMARY KEY, name TEXT, data BLOB);

Step 2: Insert bytes as a hex literal

The eight bytes below are the signature every PNG file starts with. typeof() confirms that SQLite stored a BLOB:

sqlite> INSERT INTO attachments (name, data) VALUES ('signature', X'89504E470D0A1A0A');
sqlite> SELECT id, name, typeof(data), length(data), hex(data) FROM attachments;
id  name       typeof(data)  length(data)  hex(data)
--  ---------  ------------  ------------  ----------------
1   signature  blob          8             89504E470D0A1A0A

Step 3: Insert a file with readfile()

In the sqlite3 shell, readfile() reads a whole file and returns it as a BLOB. I generated a 1×1 pixel PNG for the test:

$ ls -l logo.png | awk '{print $5, $NF}'
66 logo.png
sqlite> INSERT INTO attachments (name, data) VALUES ('logo.png', readfile('logo.png'));
sqlite> SELECT id, name, typeof(data), length(data), hex(substr(data, 1, 8)) AS first_8_bytes FROM attachments WHERE name = 'logo.png';
id  name      typeof(data)  length(data)  first_8_bytes
--  --------  ------------  ------------  ----------------
2   logo.png  blob          66            89504E470D0A1A0A

readfile() and writefile() belong to the sqlite3 shell, not the core library: they “are extension functions and are not built into the core SQLite library” (sqlite3 CLI). In your own code, read the file and bind the bytes instead (Step 5).

Step 4: Write a BLOB back to a file

writefile() returns the number of bytes it wrote. cmp shows the copy matches the original byte for byte:

sqlite> SELECT writefile('logo_out.png', data) AS bytes_written FROM attachments WHERE name = 'logo.png';
bytes_written
-------------
66
$ cmp logo.png logo_out.png && echo identical
identical

Step 5: Store and read BLOBs from Python

In Python, a BLOB is a bytes object. Pass it as a parameter to insert it, and you get bytes back when you select it. blobopen() (Python 3.11 and later) reads part of a BLOB without loading the whole value:

import hashlib
import sqlite3

con = sqlite3.connect("py_files.db")
con.execute("CREATE TABLE IF NOT EXISTS attachments (id INTEGER PRIMARY KEY, name TEXT, data BLOB)")

# Insert: pass a bytes object as a parameter
with open("logo.png", "rb") as f:
    data = f.read()
cur = con.execute("INSERT INTO attachments (name, data) VALUES (?, ?)", ("logo.png", data))
con.commit()
row_id = cur.lastrowid

# Read: a BLOB comes back as bytes
blob = con.execute("SELECT data FROM attachments WHERE id = ?", (row_id,)).fetchone()[0]
print(type(blob).__name__, len(blob), blob[:8])
with open("logo_copy.png", "wb") as f:
    f.write(blob)
print("same SHA-256:", hashlib.sha256(blob).hexdigest() == hashlib.sha256(data).hexdigest())

# Read part of a BLOB without loading all of it (Python 3.11+)
with con.blobopen("attachments", "data", row_id) as b:
    print("first 4 bytes via blobopen:", b.read(4))

print("max BLOB length on this connection:", con.getlimit(sqlite3.SQLITE_LIMIT_LENGTH))
try:
    con.execute("SELECT randomblob(1000000001)")
except sqlite3.DataError as e:
    print("DataError:", e)
con.close()

$ python3.14 blob_python.py
bytes 66 b'\x89PNG\r\n\x1a\n'
same SHA-256: True
first 4 bytes via blobopen: b'\x89PNG'
max BLOB length on this connection: 1000000000
DataError: string or blob too big

Examples

These examples answer the questions that come up once you’re storing BLOBs.

BLOB vs TEXT

The same three bytes are different values as TEXT and as a BLOB, so they don’t compare equal. CAST and hex() convert between them:

sqlite> SELECT typeof('abc') AS text_type, typeof(X'616263') AS blob_type, 'abc' = X'616263' AS equal, CAST(X'616263' AS TEXT) AS as_text, hex('abc') AS hex_of_text;
text_type  blob_type  equal  as_text  hex_of_text
---------  ---------  -----  -------  -----------
text       blob       0      abc      616263

Use TEXT for strings you’ll read or search, and BLOB for bytes that have no text encoding.

Use a BLOB as a primary key

A SHA-256 hash is 32 bytes as a BLOB, half the size of its 64-character hex text. BLOB columns can be indexed and used as keys, and SQLite searches them with the index:

sqlite> CREATE TABLE files_by_hash (sha256 BLOB PRIMARY KEY, name TEXT) WITHOUT ROWID; INSERT INTO files_by_hash VALUES (unhex('9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08'), 'test.txt');
sqlite> SELECT name FROM files_by_hash WHERE sha256 = unhex('9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08');
name
--------
test.txt
sqlite> EXPLAIN QUERY PLAN SELECT name FROM files_by_hash WHERE sha256 = X'00';
QUERY PLAN
`--SEARCH files_by_hash USING PRIMARY KEY (sha256=?)

Get the size without loading the bytes

SELECT * on a table of images pulls every image into memory. Ask for length(data) when you only need the size:

sqlite> SELECT id, name, length(data) AS bytes FROM attachments;
id  name       bytes
--  ---------  -----
1   signature  8
2   logo.png   66

Edge cases

Size is where BLOBs behave differently from what other databases lead you to expect.

The size in BLOB(n) is ignored

SQLite accepts a size after the type name and ignores it. A BLOB(10) column stores 20 bytes without complaint:

sqlite> CREATE TABLE sized (b BLOB(10)); INSERT INTO sized VALUES (randomblob(20)); SELECT length(b) FROM sized;
length(b)
---------
20

The maximum BLOB size

The limit is set by SQLITE_MAX_LENGTH, which defaults to 1,000,000,000 bytes. A build can raise it to no more than 2,147,483,645 bytes (Limits in SQLite). The Python run above hit the default limit with string or blob too big. The sqlite3 shell that ships with macOS was built with the higher limit:

$ sqlite3 :memory: ".limit length"
              length 2147483645

Large files may be faster outside the database

SQLite’s own measurements found that BLOBs smaller than about 100 KB read faster from the database file, while larger ones read faster from separate files (Internal versus external BLOBs). For large media, a common design stores the file on disk and its path in the table.

Conclusion

Use a BLOB column for any value that’s bytes rather than text. Insert it with a bound parameter from your code, or with readfile() while you’re working in the shell. Keep large files outside the database when reads get slow, and select length() instead of the bytes when you only need the size.