SQLite Data Federation Patterns

Cross-Database Queries Without Native FDWs

SQLite does not implement Foreign Data Wrappers like PostgreSQL. There is no built-in mechanism to mount a remote PostgreSQL table and query it transparently. What SQLite does provide is enough to build practical and predictable data federation, if you choose the correct pattern.

This article explains:

• cTrue cross-database queries using ATTACH

• FDW-style federation via virtual tables

• The production-recommended pattern for cross-engine federation

• How to implement incremental sync and safe upserts

The goal is clear execution, not abstraction.

If you need a refresher on indexing strategy before federating large datasets, see
https://www.sqliteforum.com/indexing-strategies-in-sqlite-improving-query-performance

What SQLite Can and Cannot Do

SQLite can:

• Join across multiple SQLite databases using ATTACH

• Expose external data via virtual tables if an adapter exists

• Join local tables with ingested remote data reliably

SQLite cannot:

• Perform native cross-engine joins

• Coordinate distributed transactions across engines

• Push down predicates to PostgreSQL without an adapter layer

Understanding these boundaries prevents architectural mistakes.

Pattern 1: Native Federation Across SQLite Databases

SQLite supports true cross-database joins across multiple SQLite files.

Attach databases:

ATTACH DATABASE 'customers.db' AS customers;
ATTACH DATABASE 'orders.db'    AS orders;

Query across them:

SELECT c.customer_id, c.name, o.order_id, o.total
FROM customers.customer c
JOIN orders.order o
  ON o.customer_id = c.customer_id
WHERE o.total > 100
ORDER BY o.total DESC;

This is real SQL federation.

Limitations:

• Only works for SQLite databases

• No network federation

• All databases must be accessible locally

Pattern 2: FDW-Style Federation via Virtual Tables

SQLite supports virtual tables through its extension mechanism.

If you have or build an adapter that exposes PostgreSQL as a virtual table, you can query it like this:

SELECT *
FROM pg_users
WHERE created_at >= '2024-01-01';

However, this depends entirely on the adapter:

• Does it support filter pushdown

• Does it support column projection

• Does it stream rows efficiently

If you cannot control or trust the adapter, use Pattern 3.

Pattern 3: Ingest Remote Data, Then Join Locally (Recommended)

This is the most stable and production-ready approach for cross-engine federation.

The pattern:

1. Fetch remote data from PostgreSQL

2. Load into SQLite

3. Index properly

4. Perform joins locally

This eliminates runtime cross-engine complexity.

Practical Example: Joining PostgreSQL Data with SQLite

Scenario

• SQLite contains events

• PostgreSQL contains users

• We want to join events with user attributes

Step 1: Define Local Tables

CREATE TABLE IF NOT EXISTS events (
  event_id    INTEGER PRIMARY KEY,
  user_id     INTEGER NOT NULL,
  event_type  TEXT NOT NULL,
  created_at  TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_events_user_id ON events(user_id);

Step 2: Create Local Mirror of PostgreSQL Users

CREATE TABLE IF NOT EXISTS users_pg (
  user_id    INTEGER PRIMARY KEY,
  email      TEXT NOT NULL,
  plan       TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_users_pg_user_id ON users_pg(user_id);

Step 3: Incremental Sync with Safe Upsert

Python sync script:

import sqlite3
import psycopg

SQLITE_PATH = "app.db"
PG_CONNINFO = "host=localhost dbname=mydb user=myuser password=mypass"

def main():
    sqlite_conn = sqlite3.connect(SQLITE_PATH)
    sqlite_cur = sqlite_conn.cursor()

    last_sync = "2024-01-01"

    with psycopg.connect(PG_CONNINFO) as pg_conn:
        with pg_conn.cursor() as pg_cur:
            pg_cur.execute("""
                SELECT id AS user_id, email, plan, created_at
                FROM users
                WHERE updated_at >= %s;
            """, (last_sync,))
            rows = pg_cur.fetchall()

    sqlite_cur.executemany("""
        INSERT INTO users_pg (user_id, email, plan, created_at)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(user_id) DO UPDATE SET
            email = excluded.email,
            plan = excluded.plan,
            created_at = excluded.created_at;
    """, rows)

    sqlite_conn.commit()
    sqlite_conn.close()

if __name__ == "__main__":
    main()

Install dependency:

pip install psycopg[binary]

This pattern:

• Avoids full reloads

• Preserves consistency

• Maintains indexed join keys

• Keeps control inside SQLite

Step 4: Perform Local Federated Join

SELECT e.event_id,
       e.event_type,
       e.created_at,
       u.email,
       u.plan
FROM events e
JOIN users_pg u
  ON u.user_id = e.user_id
WHERE e.created_at >= datetime('now', '-7 days')
ORDER BY e.created_at DESC;

This join is stable and predictable because it is fully local.

Design Rules Borrowed from PostgreSQL FDWs

Even though SQLite does not implement FDWs, these principles still apply:

1. Push filters to the remote system during extraction

2. Only fetch required columns

3. Index join keys locally

4. Define your freshness model explicitly

5. Avoid dual-write patterns across engines

Never assume distributed atomicity across PostgreSQL and SQLite.

When This Pattern Is Correct

Use ingest-plus-join when:

• Remote data changes in batches

• Slight replication delay is acceptable

• You require predictable join performance

• You control the sync layer

When You Should Not Use SQLite Federation

Do not use SQLite federation if:

• You require real-time cross-engine transactional consistency

• Remote datasets are extremely large and change constantly

• You need distributed transaction coordination

In those cases, use a data warehouse or a system designed for distributed federation.

Final Notes

SQLite does not provide native FDWs. It provides building blocks.

Use:

• ATTACH for SQLite-to-SQLite federation

• Virtual tables when you control the adapter

• Ingest-plus-join for cross-engine reliability

The correct pattern is the one you can reason about and measure.

Subscribe Now

If you want more execution-focused SQLite content, subscribe to SQLite Forum.

Upcoming topics include:

• Real-time sync pipelines into SQLite

• Conflict resolution strategies for upserts

• Query plan analysis for federated joins

• When to move from SQLite federation to a warehouse architecture

Subscribe to get new articles delivered directly.