SpatiaLite & GeoPackage Automation with Python

Reading the SQLite Database Header for Spatial Files

Read the first 100 bytes of any SpatiaLite or GeoPackage file and unpack them with struct to recover the page size (offset 16), the file-format write and…

Read the first 100 bytes of any SpatiaLite or GeoPackage file and unpack them with struct to recover the page size (offset 16), the file-format write and read versions (offsets 18 and 19) that tell you whether the file uses WAL or a rollback journal, the text encoding (offset 56), and the change counter and page count — the state signals you need before opening the file.

This page is part of the File Structure & Header Analysis guide. Its companion, How to Inspect a GeoPackage File Header in Python, reads the GeoPackage-specific application_id; this page reads the generic SQLite header structure that every spatial file shares, and explains what each field says about the file’s storage state.

Why This Matters

Every SpatiaLite database and every GeoPackage is a SQLite file, and every SQLite file opens with the same fixed 100-byte header. Those bytes are written before any spatial table exists, so they are readable on a file you cannot or should not open with a connection — one that is locked, mid-sync, or of uncertain integrity. The header answers questions that matter operationally: Is this file journaling with WAL, so a -wal sidecar must travel with it? What text encoding were its string columns written in? How many pages does it claim, and does that match the bytes on disk?

For field and offline-first pipelines, the WAL-versus-rollback distinction is the highest-value fact in the header. A GeoPackage copied off a device without its -wal and -shm companions silently loses every change still sitting in the write-ahead log. Detecting write version 2 at ingestion tells your tooling to demand the sidecar files before treating the copy as complete. Reading these fields costs a single 100-byte read and no database lock, which makes it a cheap, safe pre-flight gate ahead of the heavier extension loading and schema checks.

Generic SQLite header fields this page decodesSix fields at fixed byte offsets in the 100-byte header: page size at 16, file-format write and read versions at 18 and 19, change counter at 24, page count at 28, and text encoding at 56. The write and read versions reveal whether the file uses a rollback journal or write-ahead logging.PagesizeWriteverReadverChangecounterPagecountTextencoding161819242856write/read = 2 means WAL journaling
Byte offsets within the fixed 100-byte header. Write and read versions of 2 indicate write-ahead logging.

Prerequisites

  • Python 3.9+ (standard library struct and pathlib only)
  • Read access to the target .gpkg, .sqlite, or .db file
  • Awareness that all multi-byte integers in the header are big-endian
  • The GeoPackage identity fields covered in How to Inspect a GeoPackage File Header in Python — read together, the two pages decode the whole header

Primary Method

The parser below reads the full 100 bytes and returns every field this page discusses, translating the coded values (journal mode, text encoding) into readable labels.

python
# SpatiaLite / GeoPackage -- full generic SQLite header parse
import struct
from pathlib import Path
from dataclasses import dataclass

SQLITE_MAGIC = b"SQLite format 3\x00"

JOURNAL_MODE = {1: "rollback journal (legacy)", 2: "WAL (write-ahead log)"}
TEXT_ENCODING = {1: "UTF-8", 2: "UTF-16le", 3: "UTF-16be"}


@dataclass
class SqliteHeader:
    page_size: int
    write_version: int
    read_version: int
    journal_mode: str
    change_counter: int
    page_count: int
    text_encoding: str
    sqlite_version: int


def read_sqlite_header(path: str | Path) -> SqliteHeader:
    path = Path(path)
    with open(path, "rb") as fh:
        h = fh.read(100)
    if len(h) < 100:
        raise ValueError(f"Truncated file: {len(h)} bytes (need 100).")
    if h[0:16] != SQLITE_MAGIC:
        raise ValueError("Not a SQLite database (magic string mismatch).")

    # Page size: 2-byte big-endian; a raw value of 1 encodes 65536 per spec.
    raw_page = struct.unpack(">H", h[16:18])[0]
    page_size = 65536 if raw_page == 1 else raw_page

    write_version = h[18]   # single byte
    read_version  = h[19]   # single byte
    change_counter = struct.unpack(">I", h[24:28])[0]
    page_count     = struct.unpack(">I", h[28:32])[0]
    enc_code       = struct.unpack(">I", h[56:60])[0]
    sqlite_version = struct.unpack(">I", h[96:100])[0]

    # Journal mode is signalled by the write/read version pair: 2 => WAL.
    mode_code = 2 if max(write_version, read_version) >= 2 else 1
    return SqliteHeader(
        page_size=page_size,
        write_version=write_version,
        read_version=read_version,
        journal_mode=JOURNAL_MODE.get(mode_code, "unknown"),
        change_counter=change_counter,
        page_count=page_count,
        text_encoding=TEXT_ENCODING.get(enc_code, f"unknown ({enc_code})"),
        sqlite_version=sqlite_version,
    )

Note that offsets 18 and 19 are single bytes, not multi-byte integers — index them directly (h[18]) rather than unpacking. Only the wider fields (page size, counters, encoding) go through struct.unpack.

Step-by-Step Walkthrough

1. Read the page size at offset 16

The 2-byte page size sets the block alignment for the whole file. Spatial files with large geometry BLOBs or raster tiles are often written at 8 KB, 32 KB, or 64 KB. The spec encodes 65536 as the raw value 1, so handle that special case:

python
# SpatiaLite / GeoPackage -- page size with the 65536 special case
import struct
raw = struct.unpack(">H", header[16:18])[0]
page_size = 65536 if raw == 1 else raw
print(f"page size: {page_size} bytes")

2. Read the write and read versions at offsets 18 and 19

These two single bytes are the header’s journaling signal. A value of 1 means the classic rollback-journal mode; 2 means write-ahead logging. When either is 2, the file has (or recently had) a -wal companion:

python
# SpatiaLite / GeoPackage -- detect WAL vs rollback
write_version = header[18]
read_version  = header[19]
uses_wal = max(write_version, read_version) >= 2
print(f"journaling: {'WAL' if uses_wal else 'rollback journal'}")

For field pipelines this is the decisive check: a WAL-mode GeoPackage must be copied together with its -wal and -shm sidecars or you lose uncommitted changes. WAL mode itself is configured through PRAGMA journal_mode=WAL, and its role in concurrent access is covered in the Extension Compatibility in Spatial SQLite guide.

3. Read the change counter at offset 24 and page count at offset 28

The change counter increments on every committed write transaction; the page count is the database size in pages. Together they let you reason about the file’s write history and expected on-disk size:

python
# SpatiaLite / GeoPackage -- counters
import struct
change_counter = struct.unpack(">I", header[24:28])[0]
page_count     = struct.unpack(">I", header[28:32])[0]
print(f"changes committed: {change_counter}, pages: {page_count}")

4. Read the text encoding at offset 56

The 4-byte encoding field governs how TEXT columns — attribute values, layer names, styling metadata — are stored. Nearly all modern spatial writers use UTF-8 (1), but legacy or Windows-origin files may use UTF-16, which changes how you decode attribute strings pulled straight from pages:

python
# SpatiaLite / GeoPackage -- text encoding
import struct
enc = {1: "UTF-8", 2: "UTF-16le", 3: "UTF-16be"}
code = struct.unpack(">I", header[56:60])[0]
print(f"text encoding: {enc.get(code, 'unknown')}")

5. Combine the fields into a file-state summary

Reading all five together gives a one-line profile of the file before you open it: its block size, whether it needs sidecars, how much it has been written, its declared size, and its string encoding. That profile is what you log per file in a batch run.

Verification

Confirm every decoded field against SQLite’s own PRAGMAs. Agreement across all of them proves the offsets and endianness are correct:

python
# SpatiaLite / GeoPackage -- verify header parse against PRAGMAs
import sqlite3

hdr = read_sqlite_header("field.gpkg")
print(hdr)

conn = sqlite3.connect("file:field.gpkg?mode=ro", uri=True)
assert conn.execute("PRAGMA page_size").fetchone()[0] == hdr.page_size
assert conn.execute("PRAGMA page_count").fetchone()[0] == hdr.page_count
# journal_mode reflects the live mode; 'wal' should agree with the header signal
mode = conn.execute("PRAGMA journal_mode").fetchone()[0]
conn.close()
print(f"PRAGMA journal_mode={mode}; header says {hdr.journal_mode}")

From the shell you can confirm the size arithmetic without Python:

bash
# SpatiaLite / GeoPackage -- page_size * page_count should match file bytes
sqlite3 field.gpkg "PRAGMA page_size; PRAGMA page_count;"
# Multiply the two and compare with: stat -c %s field.gpkg

Alternative Approaches or Edge Cases

Change counter versus version-valid-for. If the change counter at offset 24 differs from the version-valid-for field at offset 92, the file was written by an old SQLite or a write was interrupted. Reading both and comparing them is a lightweight interrupted-write detector.

Reserved bytes per page. Encrypted or checksum-augmented spatial files reserve trailing bytes on every page (offset 20 holds that count). If you compute usable page payload for low-level parsing, subtract this reserved-byte value; a nonzero value here also hints the file may be an SQLCipher or checksum-VFS database rather than plain SQLite.

Zero page count on a nonempty file. SQLite treats the offset-28 page count as authoritative only when the change counter matches version-valid-for; some tools leave it zero. When it is zero, fall back to file_size // page_size for an estimate.

Troubleshooting

ValueError: Not a SQLite database (magic string mismatch)

Cause: The first 16 bytes are not SQLite format 3\x00 — the file is encrypted, compressed, truncated at the front, or simply not SQLite.

Fix: Confirm the source. An SQLCipher-encrypted spatial file has random-looking leading bytes by design and cannot be header-parsed without the key; decrypt it first. A truncated transfer must be re-fetched.

journal_mode reads WAL but no -wal file is present

Cause: The file was last closed cleanly (SQLite checkpointed and removed the -wal), or the sidecar was dropped during a copy. The header still records write version 2.

Fix: A missing -wal after a clean checkpoint is harmless — all data is in the main file. But if the file was copied while open, re-copy it with its -wal and -shm companions to avoid losing uncommitted changes.

Decoded page size or counters look absurd (e.g. billions of pages)

Cause: The bytes were unpacked little-endian, or a single-byte field (offset 18/19) was read as a multi-byte integer.

Fix: Use big-endian format strings (">H", ">I") for the wide fields and index the write/read versions as single bytes (header[18]). The SQLite header is big-endian throughout.