NAME

DBIO::PostgreSQL::DDL - Generate PostgreSQL DDL from DBIO schema classes

VERSION

version 0.900007

DESCRIPTION

DBIO::PostgreSQL::DDL generates PostgreSQL DDL statements directly from DBIO schema and result classes, without going through SQL::Translator. This produces correct, PostgreSQL-native SQL that preserves all PostgreSQL-specific features.

The generated DDL is used by "install" in DBIO::PostgreSQL::Deploy for fresh installs and by the test-deploy side of "diff" in DBIO::PostgreSQL::Deploy for upgrade diffing. Reach for this class directly when you want the raw SQL text; use DBIO::PostgreSQL::Deploy when you want database orchestration.

METHODS

install_ddl

my $sql = DBIO::PostgreSQL::DDL->install_ddl($schema);

Generates a complete PostgreSQL DDL script for the connected schema object.

This call asks no server, so it does not know the control file of an extension. An extension whose control file fixes its schema (age in ag_catalog, postgis_topology in topology, postgis_tiger_geocoder in tiger) must name that schema here, in pg_extensions(age => { schema => 'ag_catalog' }); one that names none is written WITH SCHEMA public, which the server refuses for such an extension. DBIO::PostgreSQL::Deploy has a server: it passes the fixed schemas as the option extension_control_schemas (a hash of extension name to schema), so its install and the temporary database of its diff need no declaration; a declared schema other than the fixed one is refused. For the temporary database of a diff it passes a second option, extension_installed_schemas (extension name to schema): the schema the database under diff holds an extension in. An extension that is neither declared with a schema nor fixed by its control file is created there instead of in public, so that a column of one of its types has the same type name on both sides. That schema is the database's, not a declaration: pg_schemas need not name it, and it is created (CREATE SCHEMA IF NOT EXISTS, not for public) ahead of the extension. The declared schema and the fixed one win over it.

The script is ordered to satisfy dependencies:

1. CREATE EXTENSION IF NOT EXISTS ... WITH SCHEMA statements, one per extension -- the schema "pg_extensions" in DBIO::PostgreSQL names for it (after the CREATE SCHEMA of that schema, which must be declared by pg_schemas unless it is public); else, from the option extension_installed_schemas, the one the database holds it in; else public. An extension whose control file fixes a schema, passed by the caller as extension_control_schemas, is written without the clause: the server installs it in that schema, creating it, and refuses WITH SCHEMA for one that does not exist yet
2. CREATE SCHEMA IF NOT EXISTS statements (skipping public); on a connection that names its namespace each is a DO block that creates the schema only where pg_namespace lacks it
3. Enum types, composite types, and functions from DBIO::PostgreSQL::PgSchema subclasses
4. CREATE TABLE statements with columns, primary keys, indexes, triggers, and RLS from DBIO::PostgreSQL::Result classes
5. FOREIGN KEY constraints from belongs_to relationships (on by default, off per class with pg_fk_constraints_from_rels(0), per relationship with is_foreign_key_constraint => 0, see "pg_fk_constraints_from_rels" in DBIO::PostgreSQL::Result), emitted only after every table above has been created; one to a class that is not loaded or to a table outside the deploy is refused unless it says is_foreign_key_constraint => 0
6. CREATE VIEW for the non-virtual DBIO::ResultSource::View sources, each behind the views its deploy_depends_on names (see "VIEWS")
7. One DO block per entry of pg_settings, which stores it on the database the script runs in (see "pg_settings" in DBIO::PostgreSQL)

The script does not end with SET search_path, and nothing in it writes "pg_search_path" in DBIO::PostgreSQL: that list is the path of the connections of the schema, set by the storage when each one opens ("_apply_connection_schema" in DBIO::PostgreSQL::Storage). A SET would change the search_path of the session that runs the script -- for "install" in DBIO::PostgreSQL::Deploy the connection of the application -- and nothing needs it: every object is created before where the end of the script would have put it. The entries of pg_search_path are still checked (see "NAMES AND VALUES"), so a declaration refused before is refused now.

Returns the DDL as a single string with statements separated by blank lines. Every piece of it ends in its own semicolon -- on a line of its own where the declared SQL closes with a -- comment. A semicolon behind such a comment on the same line belongs to the comment for everything that reads the script as SQL: the server when it gets it as one piece (psql -f, a single $dbh->do), and DBIO::PostgreSQL::Deploy, which cuts it into statements by the same reading ("_execute_ddl" in DBIO::Deploy::Base). The statement would have no end and run into the one after it.

A foreign key that item 5 skips -- only a belongs_to whose condition is no column equality -- leaves a note in the script, -- skipped FK for rel ..., which is such a piece too: a comment with a semicolon of its own behind it. The deploy does not send it to the server and runs the statement after it; read as one piece it is an empty statement.

What else the declared SQL may hold -- a comment line ahead of a statement, a semicolon inside a string literal -- is the deploy's to read; see "_execute_ddl" in DBIO::Deploy::Base for what it reads and what it does not. The body of a function or a procedure has to come dollar-quoted: a BEGIN ATOMIC ... END body is cut at its semicolons, and the install fails on the server with a syntax error.

The schema need not load the DBIO::PostgreSQL component. Items 1, 2, 3 and 7 come from the component's schema-level declarations; a schema without them is given the component's own defaults, so it deploys exactly what a component-loaded schema that declares nothing deploys. This is the shape "deploy_async" in DBIO::Storage::Async meets on a connection opened with { async => ... } and no load_components('PostgreSQL').

A schema connected with the connect key schema gets the script of its namespace: see "A CONNECTION THAT NAMES ITS NAMESPACE".

The schema is only read: nothing is written into its sources, and a second call returns the same script.

See "NAMES AND VALUES" for how names and values are written.

refuse_control_schema

DBIO::PostgreSQL::DDL::refuse_control_schema($extension, $declared, $fixed);

Dies with the refusal for an extension whose declared schema is not the one its control file fixes (pg_available_extension_versions.schema): naming the extension and both schemas. The server would refuse the CREATE EXTENSION (must be installed in schema ...); this is said before any statement.

control_schemas_sql

my $sql = DBIO::PostgreSQL::DDL::control_schemas_sql(@extensions);

The question to a server for the schemas the control files of the given extensions fix: pg_catalog.pg_available_extension_versions, column schema, for the version CREATE EXTENSION installs, the default one. Rows of (name, schema), only for an extension that has a fixed schema. The names are written into the statement as literals, so it carries no placeholder and goes out over any transport. Asked by "_extension_control_schemas" in DBIO::PostgreSQL::Deploy (over its connection) and by "deploy_async" in DBIO::PostgreSQL::Storage::Async (over a pooled one).

check_control_schemas

DBIO::PostgreSQL::DDL::check_control_schemas($schema, \%fixed);

Dies with "refuse_control_schema" for the first declared extension (in declaration order) whose declared schema ("pg_extensions" in DBIO::PostgreSQL) is not the one %fixed -- extension name to the schema its control file fixes -- holds for it. Nothing else is checked.

NAMES AND VALUES

Every name a declaration gives -- schema, table, view, column, index, trigger, policy, constraint, enum and composite type, extension -- is written as an identifier by the server's own rule ("quote_ident" in DBIO::PostgreSQL::Util): bare when it is plain lower case and no key word, in double quotes otherwise. So user and order can be columns, and a MixedCase table is created as MixedCase, which is the name the queries ask for -- they quote every identifier. Written bare it would be folded to mixedcase.

A source name is taken apart at its dots the way the SQL maker takes it apart for a query: table('auth.users') is the table users in the schema auth, each name quoted for itself. A pg_schema goes in front. A scalar-ref source name (table(\'cd')) is literal SQL and is written as given.

The roles of a policy (roles in "pg_rls" in DBIO::PostgreSQL::Result) are names too, except PUBLIC, CURRENT_USER, CURRENT_ROLE and SESSION_USER, which stand for a role and are written as given ("quote_role" in DBIO::PostgreSQL::Util).

A string default_value, an enum value and a pg_settings value are written as string literals with an embedded apostrophe doubled ("quote_literal" in DBIO::PostgreSQL::Util).

The entries of pg_search_path are names, checked by the same rule ($user excepted, which stands for the role's schema); none is written, the script holds no such statement; the storage sets the declared path on every connection instead ("pg_search_path" in DBIO::PostgreSQL). The keys of pg_settings are names too, and a dotted key is a dotted name, each part written by that rule for itself: app.current_user_id stays bare, DateStyle is written "DateStyle". A name already in double quotes -- an entry, or a part of a key -- is refused, naming the unquoted form, rather than quoted a second time into another name.

What a declaration documents as SQL is written as given: an index's where, expression, using, the values of with and operator classes, a policy's using and with_check, a trigger's when, event and execute, a CHECK constraint's definition, a default_value => \'...', every type string (data_type, pg_enum_type, pg_type_name, the field types of a composite type), pg_extra_ddl, a function's and a view's SQL. None of it is checked; the server judges it. A name inside such a string that needs quotes has to carry them.

Four declarations are not SQL and are checked: a column's size (digits, or two numbers of digits joined by a comma), a trigger's for_each (ROW or STATEMENT), a policy's for (SELECT, INSERT, UPDATE, DELETE or ALL; upper or lower case) and the keys of an index's with, which are storage parameter names (letters, digits, underscores, one optional dot) and written as given. A declaration that breaks the rule is refused naming the option and the object, not the value.

The enum type generated for a column with extra => { list => [...] } and no pg_enum_type is named {table}_{column}_enum and lives in the schema of its table.

COLUMN TYPES AND SIZE

A column's data_type names its PostgreSQL type and is written as given (an enum or composite column names it in pg_enum_type or pg_type_name). The eight portable base types of DBIO::Schema::Type are the exception: integer, text, char, boolean, numeric, double, blob and timestamp are deployed as DBIO::PostgreSQL::Adapter resolves them -- integer as bigint, timestamp as timestamptz. varchar, timestamptz and float are written by their long names (character varying, timestamp with time zone, double precision).

A column's size is written as the type's modifier, for every type that takes one and under each of its names ("type_takes_modifier" in DBIO::PostgreSQL::Util), where the server's grammar wants it ("type_with_modifier" in DBIO::PostgreSQL::Util):

{ data_type => 'character', size => 10 }             # character(10)
{ data_type => 'varchar',   size => 255 }            # character varying(255)
{ data_type => 'bit',       size => 8 }              # bit(8)
{ data_type => 'varbit',    size => 8 }              # varbit(8)
{ data_type => 'decimal',   size => [ 10, 2 ] }      # decimal(10,2)
{ data_type => 'numeric',   size => 10 }             # numeric(10)
{ data_type => 'time',      size => 0 }              # time(0)
{ data_type => 'timestamp', size => 3 }              # timestamptz(3)
{ data_type => 'timestamp with time zone', size => 3 }
                                                     # timestamp(3) with time zone
{ data_type => 'interval',  size => 3 }              # interval(3)
{ data_type => 'vector',    size => 1536 }           # vector(1536)

A scalar is the one value the type takes, or the first of them -- the precision of a numeric; an arrayref names them in order. A size that is any other reference is refused.

A size beside a type without a modifier is not written. PostgreSQL has no integer(11) -- it is a syntax error -- and data_type => 'integer', size => 11 is what a schema carried over from another engine is full of; such a column is deployed as its type alone.

Neither is a size beside a type this distribution does not know (an extension's other than pgvector's, a domain) or beside an array type. Write the modifier into the data_type there, which is taken as it stands: data_type => 'geometry(Point,4326)', data_type => 'varchar(10)[]'.

INDEXES

CREATE INDEX comes from two declarations: pg_index ("pg_index" in DBIO::PostgreSQL::Result) and the index declaration of the result source that idx of DBIO::Cake and indices write (index_declarations, ADR 0061 in core). The latter gives name, columns and unique, and its own driver => { pg => {...} } block gives using, expression, opclasses, include, with and where. Where both use the same name, the canonical declaration wins. A schema is bound to one driver, so what this driver does not write is refused, and the install DDL is not written: options, a type other than unique, an entry that carries only another driver's block (such as sqlite => { where => ... }), and a key the pg block does not know. An empty pg => {} block is the plain index of the columns and unique.

The declaration is read from the source itself, not from the pg_indexes method the core derives from it for the transition.

An index over exactly the columns, in that order, of a unique constraint of the same table is not written, plain or declared unique: the CONSTRAINT ... UNIQUE line has the index the server keeps for it, and a second one is a duplicate. An index with an expression, a where, another access method, operator classes, include columns or storage parameters is another object and is written. A database installed before this has the duplicate; an upgrade drops it by name.

VIEWS

A DBIO::ResultSource::View source that is not virtual and has a view_definition is created with CREATE VIEW, behind every table and foreign key. A view without a pg_schema is therefore created where the session puts it (the first existing schema of its search_path), like a table without one; "pg_search_path" in DBIO::PostgreSQL is not written into the script, it is the path of the connections.

Among themselves the views come in the order of their source names, except that a view stands behind every view its deploy_depends_on names:

package MyApp::DB::Result::Top;
__PACKAGE__->table_class('DBIO::ResultSource::View');
__PACKAGE__->table('a_top');
__PACKAGE__->result_source_instance->view_definition('SELECT id FROM z_base');
__PACKAGE__->result_source_instance->deploy_depends_on(
    ['MyApp::DB::Result::Base'],              # the view z_base
);

That declaration is all there is to go by: a definition is SQL as given, and which relations it reads only the server knows. A view on a view that does not declare it is created in the order of the names and, when that is the wrong one, fails on the server with relation ... does not exist.

A class deploy_depends_on names that is no view of this script -- a table, a virtual view, a class the schema does not hold -- orders nothing.

Refused, with refusing to write the install DDL: ... and no script: views whose deploy_depends_on run in a circle, the message naming the views and their classes; and a view that two sources declare (the same relation name in the same pg_schema), whether or not both carry a definition and whether or not it is the same one -- the message names the view and both sources with their result classes.

A CONNECTION THAT NAMES ITS NAMESPACE

A schema connected with the connect key schema works in one namespace, and the script written for it installs into that namespace ("ONE SCHEMA CLASS IN SEVERAL NAMESPACES" in DBIO::PostgreSQL):

my $schema = MyApp::DB->connect($dsn, $user, $pass, { schema => 'tenant_a' });
print DBIO::PostgreSQL::DDL->install_ddl($schema);

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;

DO $dbio$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = 'tenant_a') THEN
    CREATE SCHEMA tenant_a;
  END IF;
END
$dbio$;

SET search_path TO tenant_a, public;

CREATE TABLE things (
  ...
);

The script carries itself. It installs into the namespace over the connection it was written for, over a connection that was never given the path -- the temporary database of "diff" in DBIO::PostgreSQL::Deploy -- and as a file given to psql -f.

  • Every extension is created WITH SCHEMA: the schema it names in "pg_extensions" in DBIO::PostgreSQL, public where it names none. An extension belongs to the database, and without the clause the server would put it into the first schema of the search_path.

  • The namespace is created (not public), and so is the schema of an extension, each only where pg_namespace does not have it (a DO block with the lookup, "create_schema_if_missing" in DBIO::PostgreSQL::Util): CREATE SCHEMA asks for CREATE on the database even when the schema is there, so a role that may use a namespace made for it could not install. It is created ahead of everything that is written without a schema. PostgreSQL creates an unqualified object in the first schema of the search_path that exists and passes over one that does not: with the namespace missing, the tables would be created in the schema behind it and nothing would say so.

  • SET search_path TO the path of the connection ("connection_search_path" in DBIO::PostgreSQL::Util): the namespace, then the schemas of the declared extensions. It stands behind CREATE SCHEMA and ahead of the first object, and is not repeated at the end. It is the one SET search_path a script holds: the temporary database of a diff is reached by a connection of its own and has no other way to learn the namespace, and on the connection the script was written for it sets what the connection has already. "pg_search_path" in DBIO::PostgreSQL is not read.

  • Tables, indexes, views, foreign keys and the types of enum columns are written as on every connection: without a schema, since the class names none.

Refused, with refusing to write the install DDL: ... and no script, each naming what was declared and the namespace of the connection:

  • an extension whose schema -- the one it names, or public -- is the namespace;

  • a "pg_schema_class" in DBIO::PostgreSQL binding;

  • a source whose result class declares a pg_schema, and one whose name carries its schema (table('auth.users')).

The entries of "pg_schemas" in DBIO::PostgreSQL refuse nothing; each is created as a schema, as on every connection.

SEE ALSO

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.