NAME
DBIO::DuckDB::Deploy - Deploy and upgrade DuckDB schemas via test-deploy-and-compare
VERSION
version 0.900007
DESCRIPTION
DBIO::DuckDB::Deploy orchestrates schema deployment and upgrades for DuckDB using the test-deploy-and-compare strategy, parallel to DBIO::SQLite::Deploy and DBIO::PostgreSQL::Deploy.
For upgrades it:
- 1. Introspects the live database via
information_schema/duckdb_* - 2. Connects to a fresh in-memory DuckDB database
- 3. Deploys the desired schema (from DBIO classes) into that in-memory DB
- 4. Introspects the in-memory database the same way
- 5. Computes the diff between the two models using DBIO::DuckDB::Diff
DuckDB supports :memory: so the throwaway DB is as cheap as with SQLite -- no CREATE DATABASE dance like PostgreSQL.
my $deploy = DBIO::DuckDB::Deploy->new(
schema => MyApp::DB->connect("dbi:DuckDB:dbname=app.duckdb"),
);
$deploy->install; # fresh
my $diff = $deploy->diff; # or step-by-step
$deploy->apply($diff) if $diff->has_changes;
$deploy->upgrade; # convenience
Transactional DDL -- the engine has it, the rollback override ends the orphan
DBIO::DuckDB::Storage declares _use_transactional_ddl(1), so "_execute_ddl" in DBIO::Deploy::Base brackets the deploy loop in a transaction and a failed apply rolls back whole. Among the drivers answering 1, DuckDB is the only one where the engine is not the whole story: the DBI driver's rollback reopens a transaction, and this storage's _exec_txn_rollback override (ADR 0005) ends the orphan itself.
The engine is not the reason the declaration needed an override. Measured on libduckdb v1.5.3 through DBD::DuckDB 0.16, CREATE TABLE, DROP TABLE, ALTER TABLE in its add / drop / alter-column forms, CREATE INDEX, DROP INDEX, CREATE SEQUENCE and CREATE VIEW are all taken back by a ROLLBACK, and INSTALL, LOAD, ATTACH and DETACH are accepted inside a transaction block rather than refused the way PostgreSQL refuses ALTER TYPE ... ADD VALUE. DuckDB says so itself for the ALTER TABLE half -- "All the changes made by ALTER TABLE fully respect the transactional semantics, i.e., they will not be visible to other transactions until committed, and can be fully reverted through a rollback" (https://duckdb.org/docs/stable/sql/statements/alter_table) -- and documents no statement this driver emits as one that may not run inside a transaction. Nothing here needs core's _ddl_statement_is_transactional escape.
There is one engine-side caveat, and it is forward-looking rather than a breakage today. DuckDB refuses to alter a column an index depends on, and the same page documents the remedy: "if you have an index on a column you will first need to drop the index, alter the table, and then recreate the index". That remedy only works unbracketed. DROP INDEX followed by ALTER TABLE ... ALTER COLUMN ... TYPE inside one transaction still dies with Cannot change the type of this column: an index depends on it!, because the type-change check does not see the uncommitted DROP INDEX; the identical pair outside a transaction succeeds. It is specific to the type change -- DROP COLUMN and SET NOT NULL after a DROP INDEX both commit normally. DBIO::DuckDB::Diff cannot emit that pair today, because indexes are diffed after columns, so a type change on an indexed column already fails either way. A wrap would, however, foreclose the documented fix for it.
The deploy path is not the reason either. DBIO::DuckDB::Diff emits plain statements in tables-then-columns-then-indexes order; a table rebuild (DBIO::DuckDB::Diff::Rebuild) is create, copy, drop and rename -- plain statements too, which the one transaction of the apply takes back whole if the copy fails. There is no pragma bracket a wrap could swallow and no statement issued outside the loop. With the capability declared true the real path works end to end -- install, diff, apply, then an empty second diff -- and a batch whose last statement fails leaves nothing behind instead of the earlier statements applied.
The reason for the override is rollback in DBD::DuckDB, which issues ROLLBACK and then immediately BEGIN TRANSACTION again. That is correct for AutoCommit=0, and DBI restores AutoCommit=1 straight afterwards -- but the AutoCommit STORE handler that would commit the reopened transaction sits behind an unreachable elsif, so the 0 to 1 transition does nothing at all. Every txn_do that rolls back therefore returns with DuckDB still inside a transaction that DBI believes is closed, and the next txn_begin dies with cannot start a transaction within a transaction -- or, when the rollback followed a failed statement, Current transaction is aborted (please ROLLBACK). This is not caused by the capability and is reached today by any txn_do that rolls back; what the capability decides is whether an apply is dragged into it.
So the 1 buys the recovery. A failed apply rolls back whole -- the earlier statements are gone, not left half-applied -- and the connection survives, because this storage's _exec_txn_rollback override (ADR 0005) closes the stray transaction the driver reopened. Re-running upgrade is safe: the diff is recomputed against live state and the second run picks up only what is left. Without the override, bracketed, that re-run is what breaks: on a file DSN it dies on the poisoned connection and only an explicit disconnect clears it, and on the :memory: DSN it does not even raise, because DBIO::Storage::DBI's _exec_txn_begin retries begin_work on reconnect and a reconnect to :memory: is a new, empty database with the deployed schema gone.
The override is the whole of the fix: it ends the stray transaction itself (ADR 0005), so the 1 stands on its own feet and needs nothing from DBD::DuckDB. It is worth revisiting whenever DBD::DuckDB is upgraded -- the upstream STORE branch fix (giterlizzi/perl-DBD-DuckDB#54) makes the override a no-op.
Only the live connection is affected either way. "_build_target_model" does call _execute_ddl, but with a handle it opened itself, and core never brackets a handle the storage does not own.
ATTRIBUTES
schema
A connected DBIO::Schema instance using the DBIO::DuckDB component. Required.
catalog
Optional catalog name (e.g. a locally-attached file database). When set, the source introspection is scoped to this catalog. Useful for multi-catalog deployments and DuckLake.
Not applicable for Quack RPC remotes. The remote catalog is opaque to information_schema -- deploy the schema on the server process instead, not against the client-side quack attach. Use PRAGMA table_info('remote.t') on the client to verify remote columns.
METHODS
install
$deploy->install;
Generates DDL via "install_ddl" in DBIO::DuckDB::DDL and executes each statement against the connected database. Suitable for fresh installs. The declared extensions are resolved first ("_resolve_extension_list"), so a class that is missing or of another driver refuses before anything is written. The rest is inherited from DBIO::Deploy::Base.
diff
my $diff = $deploy->diff;
Computes the difference between the live database and the desired state. Spins up a throwaway in-memory DuckDB, deploys the desired schema there, introspects both, and returns a DBIO::DuckDB::Diff object. The orchestration is inherited from DBIO::Deploy::Base; only the in-memory target build ("_build_target_model") and the catalog-aware source introspection ("_new_introspect") are DuckDB-specific.
apply
$deploy->apply($diff);
Applies a DBIO::DuckDB::Diff object. No-op if the diff has no changes. Inherited from "apply" in DBIO::Deploy::Base, with its guard for destructive and lossy operations (allow_destructive) and its dry_run; the driver takes part through "_check_before_apply" and "_execute_diff".
upgrade
my $diff = $deploy->upgrade;
Convenience: calls "diff" then "apply". Returns the diff object if changes were applied, or undef if the database was already up to date. Inherited from DBIO::Deploy::Base.
_check_before_apply
The driver's half of "apply" (ADR 0080): once the diff is released, and before the first statement runs, it looks at the rows of the live tables and refuses with a DBIO::Exception naming table and column what DuckDB would refuse for them half way through the diff, after earlier statements are applied:
a table that has to be rebuilt (DBIO::DuckDB::Diff::Rebuild) while another table has a foreign key to it -- DuckDB does not drop such a table -- or while it points to itself -- the key would keep the temporary name through the rename: the message names both tables and why the table is rebuilt;
a
UNIQUEconstraint a rebuilt table gets over duplicate values;a
NOT NULLcolumn without a default that the diff adds to a table that holds rows -- there is no value for the rows already there;a column that becomes
NOT NULLin a table that has aNULLin it.
An empty table, and a table with no NULL in the column, pass.
_execute_diff
DuckDB cannot run the SET NOT NULL of a column whose rows an UPDATE just filled in the same transaction. A diff that has such a step ("late_sql" in DBIO::DuckDB::Diff::Column) runs in two transactions: everything else, then those steps. A failure in the second leaves the column added, filled and nullable, and the next upgrade finds exactly that to finish. A diff without such a step is run as "_execute_diff" in DBIO::Deploy::Base runs it.
diff
my $diff = $deploy->diff;
As "diff" in DBIO::Deploy::Base, and refuses a rebuild that cannot run (see "_check_before_apply"), so a caller who only looks at the diff hears of it too.
_new_introspect
Factory for the source-side introspector. Overrides "_new_introspect" in DBIO::Deploy::Base to forward the catalog attribute (for locally-attached file / DuckLake catalogs) when it is set.
_resolve_extension_list
my $list = $self->_resolve_extension_list; # DBIO::Extension::List, or undef
The declared list of extensions resolved to classes, without the installed state: classes loaded, interface version and driver checked ("resolve_list" in DBIO::DuckDB::Extension). undef when the schema declares none.
_extension_list
my $entries = $self->_extension_list;
The hook of "_extension_list" in DBIO::Deploy::Base: the entries of "_resolve_extension_list" with the installed state attached, read from duckdb_extensions() over the live connection (installed, version; installed is 0 for a declared extension DuckDB does not know). [] when the schema declares none, and then nothing is read.
_build_target_model
Builds the desired-state model: connects a throwaway in-memory DuckDB, activates the declared extensions there ("duckdb_extensions" in DBIO::DuckDB: INSTALL and LOAD, as on a connection of the schema), deploys the install DDL there, and introspects it. DuckDB supports :memory:, so this is as cheap as with SQLite -- no temporary-database dance like PostgreSQL. The temp DB is never scoped to catalog.
SEE ALSO
DBIO::DuckDB - schema component
DBIO::DuckDB::DDL - generates DDL
DBIO::DuckDB::Introspect - reads live database state
DBIO::DuckDB::Diff - compares two introspected models
DBIO::SQLite::Deploy - sibling implementation
AUTHOR
DBIO Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.