NAME
DBIO::SQLite::Storage - SQLite storage driver for DBIO
VERSION
version 0.900007
DESCRIPTION
SQLite storage driver for DBIO. Extends DBIO::Storage::DBI with SQLite-specific behavior including INSERT ... RETURNING for single round-trip retrieval of autoincrement and retrieve_on_insert values (libsqlite 3.35+), the atomic upsert of "upsert" in DBIO::ResultSet via INSERT ... ON CONFLICT (also gated at libsqlite 3.35+, see below), the boolean-valued existence check behind "results_exist" in DBIO::ResultSet, savepoint support, foreign key pragma helpers, database file backup, and workarounds for known DBD::SQLite bugs.
Atomic upsert
supports_upsert is declared exactly when insert_returning is in effect. SQLite has had the ON CONFLICT grammar since libsqlite 3.24, but core names the written row through last_insert_id plus a SELECT on the primary key and never re-finds it by conflict target. An upsert that takes the DO UPDATE branch inserts nothing, so last_insert_rowid() still reports the previous insert -- without RETURNING the call would hand back an unrelated row. Gating the two capabilities together makes the returned row correct by construction; on libsqlite 3.24 - 3.34 upsert throws instead (ADR 0036).
supports_upsert_where -- the conditional branch, DO UPDATE SET ... WHERE ..., reachable as upsert's update_where -- is declared unconditionally instead. It is a fact about DBIO::SQLite::SQLMaker, not about the server: the WHERE arrived with the ON CONFLICT grammar itself in 3.24, so there is no version to test, and the RETURNING coupling above is about naming the written row, which a condition that does not hold has no need of -- it writes nothing, and upsert hands back undef (which is why core refuses result => 'row_or_die' together with update_where). Core consults the two capabilities together.
Existence queries
supports_exists_query is declared unconditionally, which is what makes "results_exist" in DBIO::ResultSet, "results_exist_async" in DBIO::ResultSet and "results_exist_as_query" in DBIO::ResultSet work here instead of throwing (ADR 0037). This driver does not override "_exists_value_sql" in DBIO::Storage::DBI: core's base rendering, SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END, is already the spelling SQLite wants -- only the engines that refuse a FROM-less SELECT have to append a dummy table.
No version gate, unlike the two capabilities above: CASE arrived in libsqlite 2.4.4 and the EXISTS operator in 3.1.0, both below the 3.6.0 an external libsqlite needs before DBD::SQLite will build against it at all. "has_rows" in DBIO::ResultSet stays the portable alternative for callers that must run against a driver which has not declared the capability.
Set operations
SQLite has four compound operators and only four -- UNION, UNION ALL, INTERSECT and EXCEPT: ALL attaches to UNION alone (https://www.sqlite.org/lang_select.html, "Compound Select Statements"). So four of DBIO::ResultSet's six set operations work here on core's base spelling, and the remaining two, intersect_all and except_all, have no spelling at all. This driver's "_set_operation_keyword" in DBIO::Storage::DBI override refuses those two with a typed exception at query-build time, rather than letting core's inherited INTERSECT ALL / EXCEPT ALL reach the engine as a syntax error several layers below the method the caller called (ADR 0039). except is not re-spelled: it is handed to $self->next::method and comes back EXCEPT, core's base answer, as do the other three.
The refusal is unconditional, and that is a sourced engine fact rather than the compromise ADR 0039 section 7 prices for the drivers whose gap is version-bounded (Oracle gained MINUS ALL in 21c). SQLite's four compound operators arrived together on 2000-06-06, before version 1.0, and the set has never grown: the changelog through 3.53.4 contains no INTERSECT ALL and no EXCEPT ALL. There is thus no libsqlite on which the refusal withholds an operation the engine would have accepted, and nothing here that a version probe could have answered better -- which matters, because the hook is a static class fact and may not open a connection to decide.
except and intersect are the nearest operations SQLite does have, and the exception names them -- but they are not drop-in replacements: both remove duplicate rows, which is precisely what the ... ALL variant is asked for. Choosing one is the caller's decision, which is why this driver throws instead of quietly substituting (ADR 0039 section 3).
This class is registered as the driver for SQLite and is loaded automatically when connecting to a SQLite DSN. It uses DBIO::SQLite::SQLMaker for SQL generation, LimitOffset pagination, double-quote identifiers, and DateTime::Format::SQLite for datetime parsing.
Query plans
"explain" in DBIO::ResultSet asks the storage for the whole operation of obtaining a query plan through "_explain_query" in DBIO::Storage::DBI (ADR 0040; it replaced the string-valued _explain_sql). This driver must override it, because SQLite is the only engine in this family where core's inherited EXPLAIN <query> is neither right nor loud.
SQLite accepts a bare EXPLAIN. What it returns is the VDBE bytecode of the compiled statement -- addr, opcode, p1 .. p5, comment, one row per virtual machine instruction -- which is a well-formed arrayref of row arrayrefs and so is indistinguishable, at the call site, from the plan the caller asked for. There is no exception and no empty result to notice: the wrong question is answered confidently. The bytecode never names the index the query uses, which is the one thing explain is called for.
The plan comes from EXPLAIN QUERY PLAN (https://www.sqlite.org/eqp.html), whose rows are id, parent, notused, detail, and whose detail carries the SCAN / SEARCH ... USING INDEX text. Its contents are engine-specific and deliberately unstable -- SQLite documents the output as interactive debugging material whose format may change between releases, and it did change substantially in 3.24.0 -- which is no more than ADR 0040 section 2 already says about every driver's plan rows.
Beyond the three added words the override is core's base implementation: on SQLite obtaining a plan really is one statement, executed, returning rows that are the plan, so nothing of ADR 0040's wider shape is needed here. The statement goes through "_execute_rendered" in DBIO::Storage::DBI as the contract requires, which is what keeps tracing and therefore "redact_bind_value" in DBIO::Storage::DBI over the query's own bind values, dbh_do retry, NoBindVars interpolation, and the read handler on a DBIO::Replicated::Storage.
Binds without a column
SQLite compares an INTEGER with a TEXT by storage class, and only a column (or an expression that carries a column's affinity) converts the bound value to its own type first. A bind that belongs to no column -- literal SQL such as \[ 'COUNT(x) > ?', 3 ] -- has nothing to go by, so the storage decides from the spelling of the value: an integer in canonical decimal spelling (3, -7, '99999'; no leading zero, plus sign, whitespace or fraction, within 64 bits) is bound as INTEGER, anything else as text. That makes aggregate comparisons with a count work whether the number arrives as a Perl number or as a string from a request parameter, and keeps a zip code like '01234' intact.
The decision follows the spelling, not the Perl type of the scalar, and this leaves two limits, both against an expression that has no affinity and therefore no conversion of its own:
a canonical integer string against a text expression.
lower(name) = ?with'99999'binds an INTEGER, which never equals the text'99999', so the row is not found. Against the column itself (name = ?) it works, the column's TEXT affinity turns the integer back into text.a non-integer against a numeric expression.
AVG(x) > ?with1.5binds text, and INTEGER and REAL sort below TEXT, so the comparison is always false.
SQLite has no type that is right for both lower(name) = '99999' and COUNT(x) > '3'; the storage cannot know which one is meant, so it does not guess further. State the type at the bind, which the storage leaves alone:
# text: the integer-looking string stays a string
$rs->search(\[ 'lower(name) = ?', [ { sqlt_datatype => 'varchar' } => '99999' ] ]);
# real: the number reaches SQLite as a number
$rs->search(undef, {
group_by => 'me.artistid',
having => \[ 'AVG(me.artistid) > ?', [ { dbd_attrs => DBI::SQL_REAL() } => 1.5 ] ],
});
SQL dialect of the DDL
_ddl_sql_dialect answers sqlite. The splitter that cuts install and upgrade DDL into statements ("_execute_ddl" in DBIO::Deploy::Base) reads one form differently per engine, the backslash in a string literal, and SQLite is one where it is an ordinary character: 'a\' is a complete literal. With the dialect named, a literal that ends on a backslash no longer swallows the cut behind it and the semicolon of a later literal. See "_ddl_sql_dialect" in DBIO::Storage::DBI::Capabilities.
METHODS
_extension_activation
my $activation = $storage->_extension_activation($namespace);
# { load => [ '/opt/sqlite/vec0.so' ], search_path => [] }
The seam of "_extension_activation" in DBIO::Storage::DBI: what the declared extensions ("sqlite_extensions" in DBIO::SQLite) want of a new connection, merged by core in the name order of the extensions ("activation" in DBIO::Extension::Protocol): the libraries to load. The list is resolved here, at the first use of a connection and so after the driver is resolved, never at connect: the classes are loaded, their interface version and their driver assertion checked ("resolve_list" in DBIO::SQLite::Extension). undef -- and nothing else happens to the connection -- for a schema that declares no extension and for declared extensions with no library.
_activate_extensions
$storage->_activate_extensions($activation);
The seam of "_activate_extensions" in DBIO::Storage::DBI: load_extension needs the handle and not a statement. The connection's extension loading is switched on, every library of the activation is loaded with $dbh->sqlite_load_extension in the order core merged them, and loading is switched off again, so that nothing on the connection can call SQL load_extension() afterwards. A library that cannot be loaded dies with a message naming it, and the handle is dropped from the storage, as core does with any failing connect action. A connection of an async pool is a statement runner and has no handle here, so it refuses.
_dbh_columns_info_for
Override the parent's column_info lookup. DBD::SQLite does not implement NULLABLE correctly -- columns constrained by UNIQUE ... NOT NULL come back as nullable. This override layers the truth from PRAGMA table_info (notnull) on top of the parent method's result so that is_nullable is correct for downstream consumers like "columns_info_for" in DBIO::Storage::DBI.
For a PRIMARY KEY column it also asks PRAGMA index_list whether the key is the rowid alias, because on SQLite that is the only key column which cannot hold NULL -- see "column_is_nullable" in DBIO::SQLite::Util, which both this method and "table_columns_info" in DBIO::SQLite::Introspect route the decision through so the live path and the introspect path answer the same for the same column.
backup
my $backupfile = $storage->backup($dir);
my $backupfile = $storage->backup; # defaults to './'
Copy the live SQLite database file to $dir. The backup filename includes a timestamp prefix. Returns the full path to the backup file.
Throws an exception if the database filename cannot be determined from the DSN or if the file copy fails.
vacuum
$storage->vacuum; # VACUUM
$storage->vacuum($file); # VACUUM INTO ? (bind: $file)
Rebuild the database file, defragmenting it and reclaiming free pages. With no argument this emits a bare VACUUM. Given a target filename it emits VACUUM INTO ? (libsqlite 3.27+), writing a fresh, compacted copy of the database to $file while leaving the live database untouched; the filename is passed as a bind parameter rather than interpolated into the SQL. The target file must not already exist. On a libsqlite older than 3.27 the call throws an exception naming the required and the present version, before any statement is sent; a plain vacuum works on every libsqlite.
VACUUM cannot run inside a transaction; call this outside any active transaction.
bind_attribute_by_data_type
Returns DBI::SQL_BIGINT for any integer-family column type (int, integer, tinyint, smallint, mediumint, bigint, int1, int2, int4, int8). Returns undef for all other types, deferring to the default binding behavior.
SQLite stores all numeric values as dynamically-sized integers up to 8 bytes, so binding everything as SQL_BIGINT is safe and avoids unnecessary type coercions.
with_deferred_fk_checks
$storage->with_deferred_fk_checks(sub { ... });
Execute $sub inside a transaction with PRAGMA defer_foreign_keys = ON. Foreign key constraint checking is deferred until the transaction commits, allowing bulk inserts or other operations that temporarily violate referential integrity within the same transaction. The transaction is committed automatically after $sub returns.
connect_call_use_foreign_keys
A connect-time callback that executes PRAGMA foreign_keys = ON. SQLite does not enforce foreign key constraints by default. Enable it by passing on_connect_call => 'use_foreign_keys' in your connection options:
$schema->connect(
'dbi:SQLite:db/app.db', '', '',
{ on_connect_call => 'use_foreign_keys' },
);
SEE ALSO
DBIO::SQLite - Schema component that activates this storage
DBIO::SQLite::SQLMaker - SQL generation for SQLite
DBIO::Storage::DBI - Base DBI storage class
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.