Moving from SQLite
Port SQLite schemas, queries, and persistence to embedded PostgreSQL.
Use this guide when your app needs PostgreSQL types, queries, or extensions and currently stores data in SQLite. Oliphaunt opens PostgreSQL storage; it does not open an existing SQLite database file.
Compare the storage and SQL model
| SQLite assumption | PostgreSQL equivalent |
|---|---|
| One database file | A managed directory or an explicit WASIX storage provider |
? query parameters | $1, $2, and subsequent parameters |
| Flexible column typing | Declared PostgreSQL types and explicit casts |
INTEGER PRIMARY KEY row IDs | An identity column or an application-generated key |
PRAGMA configuration | PostgreSQL startup or session settings |
| Load an extension library | Package/select the extension, then CREATE EXTENSION |
| Copy a database file | Use SDK backup and restore |
Port a small schema first
Create a disposable Oliphaunt database and port one table. For example, use an identity column for generated IDs and a PostgreSQL timestamp for creation time:
CREATE TABLE notes (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
body text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);Port the corresponding queries with parameters:
INSERT INTO notes (body) VALUES ($1) RETURNING id;
SELECT id, body, created_at FROM notes ORDER BY created_at DESC;Bind values through your SDK. Check its decoding rules for bigint, timestamps, JSON, arrays, and null values before changing application models.
Transfer application data
- Create the PostgreSQL schema in a new persistent database.
- Read records through your existing SQLite integration.
- Convert values to the PostgreSQL types you chose, including dates and booleans.
- Insert with parameterized queries in bounded transactions.
- Compare row counts and representative values, including nulls and large integers.
- Switch the app to the new storage only after validation succeeds.
Keep the original SQLite database until migration is complete and recoverable. A SQLite file or SQL dump is not an Oliphaunt physical backup; PostgreSQL may reject SQLite-specific SQL in a dump.
Revisit concurrency and backups
One embedded handle executes one session's work in order. Use a native server if the app requires a PostgreSQL connection pool with independent sessions.
Replace file-copy backup code with the SDK's backup and restore methods. Test restore to a new destination with the app's selected extensions.
Measure the tradeoff
Compare startup time, first-query latency, memory, and installed size using your actual schema and workload. PostgreSQL adds capabilities and a larger runtime. SQLite remains a good choice when a small single-file database already meets your app's needs. See Measure performance.