Turso free tier: a SQLite catalogue with stable pagination

Turso’s free tier includes 5 GB of storage and 500 million rows read per month. For a small catalogue, the useful question is how each page finds its records: a seven-item response can still require a scan of the whole table.

The example below builds a 600-item SQLite catalogue, filters by category and walks through every page. It checks that adding an index preserves the results, that tied timestamps do not lose items, and that a SQL dump can be reopened. These are local SQLite checks. No Turso account, hosted workload or bill was tested.

What Turso Free includes

The current pricing table, checked on 27 October 2026, lists:

No credit card is required to start. These are plan allowances tracked at organisation level, not 100 separate copies of the storage and read budget. Sync is a separate allowance associated with database synchronisation; do not read that row as 3 GB of ordinary website bandwidth. The embedded-replica documentation explains the local-copy model and now recommends Turso Sync for new projects that need synchronization.

Turso’s billing documentation counts rows scanned, which can exceed rows returned. Updates also read records, creating an index scans existing data, and quota-exceeding queries can fail with BLOCKED. That page still uses older plan names; the table above comes from the current pricing page.

A read allowance is therefore a poor starting point for a promise such as “this supports a million page views”. The app may make several queries per request, and those queries can do very different amounts of work. If you are still choosing a database or somewhere to run the application, the free-compute guide covers that broader decision.

Choose the engine you are testing

This recipe uses Python’s standard sqlite3 module and vanilla SQLite. The recorded run used Python 3.14.6 with SQLite 3.53.4.

Turso’s names now cover more than one engine. libSQL is its SQLite fork; the newer Turso Database is a ground-up rewrite. Current documentation says both are available on Turso Cloud. Its local-development guide recommends the newer embedded Turso packages for local files and offers a local libSQL server for libSQL-specific work.

Running this script proves the SQL and the checks on the stated SQLite version. Run them again through the engine and client you actually choose before treating them as a compatibility test for that setup.

A category filter needs an order and a cursor

The catalogue has three categories: tools, books and parts. Items sort newest first, then by descending ID. Several items deliberately share each timestamp, so the ID is part of both the order and the page cursor.

Save the following three Python blocks, in order, as catalogue_check.py. It uses only the standard library, creates disposable database files and removes them when it finishes. Run it with python3 catalogue_check.py, using Python 3.8 or later with SQLite 3.15 or later (the minimum for row-value comparisons). The checks use assert, so do not run it with python -O.

import json
import sqlite3
import sys
import tempfile
from pathlib import Path

SCHEMA = """
CREATE TABLE _schema_version (
  version INTEGER PRIMARY KEY,
  applied_at TEXT NOT NULL
);
INSERT INTO _schema_version VALUES (1, '2026-10-27');
CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  category TEXT NOT NULL,
  title TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
"""
FIRST = """SELECT id, category, title, created_at FROM items
WHERE category = ?
ORDER BY created_at DESC, id DESC LIMIT ?"""
NEXT = """SELECT id, category, title, created_at FROM items
WHERE category = ? AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC LIMIT ?"""
INDEX = """CREATE INDEX items_category_newest
ON items(category, created_at DESC, id DESC)"""

created_at is a non-null integer timestamp; id makes the order unique. The _schema_version table records the migration without relying on PRAGMA user_version, which is read-only on Turso Cloud.

The first-page query has no cursor. Later pages use (created_at, id) < (?, ?) to continue below the last returned pair. This is SQLite’s row-value pagination pattern, with both keys descending.

def page(db, category, cursor=None, size=7):
    if not 1 <= size <= 100:
        raise ValueError("page size must be 1..100")
    if cursor is None:
        return db.execute(FIRST, (category, size)).fetchall()
    return db.execute(NEXT, (category, *cursor, size)).fetchall()

def walk(db, category):
    rows, pages, cursor = [], [], None
    while batch := page(db, category, cursor):
        rows.extend(batch)
        pages.append([r[0] for r in batch])
        cursor = (batch[-1][3], batch[-1][0])
    return rows, pages

def plan(db, sql, params):
    return [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql, params)]

The category, cursor and limit are bound values. Keep this separation when adapting the query to another client. Python’s parameter-binding documentation explains why values belong in placeholders rather than SQL string interpolation.

page() is the app-facing operation. For the next request, retain the final row’s timestamp and ID alongside the selected category. Reset the cursor when the category changes. The fixture uses seven records per page and bounds the page size to 1–100.

Check every page, then change the plan

This final block seeds 200 items per category, records the plans, creates the index and repeats the checks. Expected rows are sorted independently in Python, so the comparison does not rely on two copies of the same SQL mistake. It also tries a hostile category value and restores an export into a separate file.

with tempfile.TemporaryDirectory() as folder:
    db = sqlite3.connect(Path(folder) / "catalogue.db")
    db.executescript(SCHEMA)
    categories = ("tools", "books", "parts")
    seed = [(i, categories[i % 3], f"Item {i:04d}",
             1700000000 + (i // 12) * 60) for i in range(1, 601)]
    db.executemany("INSERT INTO items VALUES (?, ?, ?, ?)", seed)
    db.commit()
    # Python, not another paginated query, supplies the expected result.
    expected = {c: sorted([r for r in seed if r[1] == c],
                         key=lambda r: (r[3], r[0]), reverse=True)
                for c in (*categories, "missing")}
    baseline = {c: walk(db, c) for c in expected}
    for c in expected:
        assert baseline[c][0] == expected[c]
    cursor = (baseline["tools"][0][6][3], baseline["tools"][0][6][0])
    # A timestamp-only cursor demonstrably skips tied rows at this boundary.
    wrong_next = db.execute("""SELECT id, category, title, created_at FROM items
        WHERE category = ? AND created_at < ?
        ORDER BY created_at DESC, id DESC LIMIT ?""", ("tools", cursor[0], 7)).fetchall()
    assert wrong_next != baseline["tools"][0][7:14]
    assert baseline["tools"][0][6][3] == baseline["tools"][0][7][3]
    before = {"first": plan(db, FIRST, ("tools", 7)),
              "next": plan(db, NEXT, ("tools", *cursor, 7))}
    db.execute(INDEX)
    db.commit()
    after = {"first": plan(db, FIRST, ("tools", 7)),
             "next": plan(db, NEXT, ("tools", *cursor, 7))}
    for c in expected:
        assert walk(db, c) == baseline[c]
        ids = [r[0] for r in walk(db, c)[0]]
        assert len(ids) == len(set(ids))
    hostile = "tools' OR 1=1 --"
    assert page(db, hostile) == []
    assert db.execute("SELECT COUNT(*) FROM items").fetchone()[0] == 600
    dump = "\n".join(db.iterdump())
    dump_path = Path(folder) / "catalogue.sql"
    dump_path.write_text(dump, encoding="utf-8")
    restored_path = Path(folder) / "restored.db"
    restored = sqlite3.connect(restored_path)
    restored.executescript(dump_path.read_text(encoding="utf-8"))
    restored.close()
    restored = sqlite3.connect(restored_path)
    for c in expected:
        assert walk(restored, c) == baseline[c]
    assert restored.execute("SELECT * FROM _schema_version").fetchall() == [(1, "2026-10-27")]
    assert restored.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    assert restored.execute("SELECT sql FROM sqlite_master WHERE name = 'items_category_newest'").fetchone()[0] == INDEX
    report = {"python": sys.version.split()[0], "sqlite": sqlite3.sqlite_version,
              "seed_rows": len(seed), "category_rows": 200,
              "tools_pages": len(baseline["tools"][1]),
              "first_two_tools_pages": baseline["tools"][1][:2],
              "plans_before": before, "plans_after": after,
              "all_categories_equal": True, "unique_ids": True, "timestamp_only_cursor_skips_ties": True,
              "missing_category": [], "hostile_parameter": [],
              "dump_reopened": True, "schema_and_index_restored": True,
              "integrity_check": "ok"}
    print(json.dumps(report, indent=2))
    restored.close()
    db.close()

On the recorded run, the first two tools pages contained these IDs:

600, 597, 594, 591, 588, 585, 582
579, 576, 573, 570, 567, 564, 561

IDs 582 and 579 have the same timestamp. A cursor containing only created_at < last_timestamp skips 579 and other remaining items with that timestamp. The script checks that this tempting simplification produces a different next page. Keeping the ID in the cursor preserves the boundary.

Each populated category produced 29 pages, with all 200 expected records present exactly once. The indexed pages matched the unindexed pages, including their order. The missing category and tools' OR 1=1 -- each returned no rows, and the catalogue still contained 600 items.

That is deterministic pagination over a fixed dataset. It does not create a snapshot across separate web requests. A newly inserted item above the cursor will appear after a refresh; changing a record’s category or sort keys can move it across the boundary. Keep the creation timestamp immutable for this design, and define a separate snapshot strategy if an export must represent one instant in time.

What the index actually changed

Both the first-page and next-page queries initially produced:

SCAN items
USE TEMP B-TREE FOR ORDER BY

After creating items_category_newest, the first page produced:

SEARCH items USING INDEX items_category_newest (category=?)

The next page produced:

SEARCH items USING INDEX items_category_newest (category=? AND created_at<?)

The index starts with the category filter, then follows the requested timestamp-and-ID order. The recorded plans use it to search the category range and no longer report a temporary sort. title is outside the index, so this is not a covering-index example.

Those are observed query plans, not exact scan counts or a speed benchmark. SQLite also warns that the plan-output format can change. A hosted usage counter is still needed to establish the rows charged for the chosen Turso engine and client.

Turso already has a good indexing and billing article and runnable SQL examples, including precomputed aggregates. This example keeps to one catalogue query: category, order and cursor all have to agree. An unfiltered newest-items feed would be another query to inspect, rather than a reason to assume this category-first index handles every screen.

The extra index occupies storage and needs maintenance as records change. For a new database, create it before importing the catalogue; the script deliberately adds it afterwards to expose the plan difference. If the screen shows a live total alongside the list, inspect that COUNT(*) query’s plan and usage separately before adding aggregate tables and triggers.

Keep the restore check

The script writes a SQL dump, imports it into restored.db, closes that connection and opens the file again. All category pages still match. It also checks the migration row, index definition and PRAGMA integrity_check, which returned ok.

This is a local logical-export check. It does not exercise Turso’s one-day restore window, backup retention or an engine migration. For an existing hosted database, Turso documents exporting with turso db shell your-database .dump and loading that SQL into a local SQLite file. Try the restore with your actual schema and data before relying on the export.

When adapting this to an application, keep page() and its ordering checks together. A change to the category filter or sort order should also change the cursor and index test.