In a multi-engine database client, every driver has its own specifics. PostgreSQL and MySQL are network servers with well-defined protocols. MongoDB exposes a document API. Redis works through key-value commands. SQLite is different: it's an embedded engine that reads and writes directly to a file on disk. This particularity changes everything, from the connection all the way to concurrency handling.
QoreDB's SQLite driver is designed to respect this nature. No server configuration, no network credentials. A file path, a local connection pool, and a set of capabilities exposed through the same DataEngine trait as the other drivers.
A file driver, not a network driver
The first visible difference is at the connection stage. Where PostgreSQL expects a host, a port and credentials, SQLite expects a path to a file. The driver validates that path strictly: only recognized extensions are accepted (.db, .sqlite, .sqlite3, .db3, .s3db, .sl3). This validation prevents accidentally opening a file that isn't a SQLite database.
The driver also supports in-memory mode with the :memory: syntax, useful for tests or temporary work. If the target file doesn't exist yet, the driver can create it automatically, which matches SQLite's native behavior.
On the pooling side, the driver uses SQLx with a configurable pool: 5 connections maximum by default, a 30-second acquisition timeout, and a minimum of 0 connections at rest. These values are adjustable per connection. The pool is sized for desktop use, not for a web server with hundreds of concurrent requests.
Why WAL is enabled by default
SQLite offers several journaling modes. The default mode (rollback journal) locks the entire file during a write, which blocks all concurrent reads. WAL mode (Write-Ahead Logging) reverses this logic: writes go into a separate journal file, and reads keep going on the stable version of the main file.
For a database client, this is a natural choice. QoreDB's connection pool can have several simultaneous readers (the data grid, the navigation tree, autocomplete) while a write is in progress. Without WAL, those reads would be blocked for the duration of each transaction. The driver therefore enables WAL systematically on every connection, with a 30-second busy timeout to handle lock contention without failing immediately.
A single namespace per file
QoreDB's universal data model rests on three levels: Namespace, Collection, Record. For PostgreSQL, a namespace corresponds to a schema. For MySQL, it's a database. For SQLite, it's the file itself. Each SQLite file constitutes a single namespace, named after the file (or "memory" for in-memory databases).
This mapping is faithful to the reality of SQLite. There is no concept of a schema or of multiple databases within a single file. The driver doesn't simulate something that doesn't exist: it exposes a single namespace, with the tables and views listed from sqlite_master, filtering out the system tables (those prefixed with sqlite_).
Schema inspection via PRAGMA
SQLite doesn't expose its metadata the same way traditional SQL engines do. There is no standardized information_schema. The native mechanism is the PRAGMA statements. The driver uses them systematically: PRAGMA table_info for columns (name, type, nullability, default value, primary key), PRAGMA foreign_key_list for foreign keys, PRAGMA index_list and PRAGMA index_info for indexes.
The result is the same TableSchema object as for the other drivers. The interface doesn't know whether the metadata comes from a PRAGMA or from a query on pg_catalog. That's the role of the DataEngine trait: to expose a uniform surface without hiding the underlying differences.
Dynamic typing and value extraction
SQLite uses dynamic typing, unlike PostgreSQL or MySQL where every column has a fixed type. A column declared INTEGER can hold text. The driver handles this reality by trying several types in order during extraction: integer (i64 then i32), float (f64), boolean (0/1 mapping), text, blob, then null as a last resort. This approach guarantees that data is read correctly even when the content doesn't match the declared type.
Transactions, mutations and streaming
The SQLite driver implements the full set of mutation capabilities of the DataEngine trait. Inline editing in the data grid generates UPDATEs with WHERE clauses on primary keys, insertions use parameterized queries with ? placeholders, and deletions target rows by their primary key (composite or not). All of these operations go through parameterized queries to prevent any SQL injection.
Transactions are handled through a dedicated connection taken from the pool at the moment of BEGIN. This connection is held in a Mutex until COMMIT or ROLLBACK, then returned to the pool. This mechanism guarantees that all the queries of a transaction go through the same connection, which is essential with SQLite.
Result streaming is also supported. Rows are sent one by one through a tokio::sync::mpsc channel, which makes it possible to display results in the data grid as they arrive without loading the entire dataset into memory.
File maintenance and integrity
The driver exposes four maintenance operations through the graphical interface. VACUUM compacts the file by rebuilding the entire database, which reclaims the space of deleted rows. ANALYZE updates the internal statistics used by the query optimizer. REINDEX rebuilds the indexes of a given table. PRAGMA integrity_check verifies the structural consistency of the database.
These operations are particularly useful for SQLite because the file can accumulate unused space over the course of deletions and modifications. On a development file used daily, a regular VACUUM keeps the file size reasonable and read performance optimal.
In practice: use cases for the SQLite driver
The SQLite driver in QoreDB covers three main scenarios. The first is exploring existing databases: a mobile app that stores its data in a .db file, an Electron project with embedded SQLite, or a CLI tool that persists its state locally. With QoreDB, you open the file, navigate the tables, inspect the schema and run queries, exactly as you would with a remote PostgreSQL database.
The second is rapid prototyping. Create a SQLite file, define a few tables, insert data through the data grid's inline editing, all without installing anything. SQLite is available everywhere, and the driver takes advantage of that.
The third is cross-database federation. Through the DuckDB engine built into QoreDB, you can join data from a SQLite file with data from a PostgreSQL server in a single query. The SQLite driver provides the source data, DuckDB orchestrates the join.
QoreDB's SQLite driver treats a file engine as a first-class engine. WAL enabled by default for concurrency, PRAGMA for inspection, dynamic typing handled cleanly, and all the mutation and streaming capabilities of the DataEngine trait. It's a driver built for developers who work with SQLite files every day, without compromising on features.
Stay updated on new releases
Subscribe to get product releases, new drivers notifications, and technical tutorials.