flow php
Edit external resource

Table Query Builder

Table of Contents

The Table Query Builder provides a fluent, type-safe interface for constructing PostgreSQL DDL statements: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE, and CREATE TABLE AS.

CREATE TABLE

Basic Table Creation

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial, sql_type_varchar};

$query = create()->table('users')
    ->column(column('id', sql_type_serial())->primaryKey())
    ->column(column('name', sql_type_varchar(100))->notNull());

echo $query->toSQL();
// CREATE TABLE users (id serial PRIMARY KEY, name varchar(100) NOT NULL)

Table with Schema

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial};

$query = create()->table('users', 'public')
    ->column(column('id', sql_type_serial())->primaryKey());

echo $query->toSQL();
// CREATE TABLE public.users (id serial PRIMARY KEY)

IF NOT EXISTS

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial};

$query = create()->table('users')
    ->ifNotExists()
    ->column(column('id', sql_type_serial())->primaryKey());

echo $query->toSQL();
// CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY)

Column Definitions

Columns support various constraints and options:

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_integer, sql_type_varchar, sql_type_boolean, sql_type_timestamp};

$query = create()->table('users')
    ->column(column('id', sql_type_integer())->identity('ALWAYS'))
    ->column(column('email', sql_type_varchar(255))->notNull()->unique())
    ->column(column('active', sql_type_boolean())->default(true))
    ->column(column('created_at', sql_type_timestamp())->defaultRaw('CURRENT_TIMESTAMP'));

echo $query->toSQL();
// CREATE TABLE users (
//   id int GENERATED ALWAYS AS IDENTITY,
//   email varchar(255) NOT NULL UNIQUE,
//   active boolean DEFAULT true,
//   created_at timestamp DEFAULT CURRENT_TIMESTAMP
// )

Column with Foreign Key Reference

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial, sql_type_integer};

$query = create()->table('orders')
    ->column(column('id', sql_type_serial())->primaryKey())
    ->column(column('user_id', sql_type_integer())->notNull()->references('users', 'id'));

echo $query->toSQL();
// CREATE TABLE orders (id serial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id))

Generated Columns

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_varchar, sql_type_text};

$query = create()->table('users')
    ->column(column('first_name', sql_type_varchar(50)))
    ->column(column('last_name', sql_type_varchar(50)))
    ->column(column('full_name', sql_type_text())->generatedAs("first_name || ' ' || last_name"));

echo $query->toSQL();
// CREATE TABLE users (first_name varchar(50), last_name varchar(50), full_name pg_catalog.text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED)

Table-Level Constraints

Primary Key

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_integer, primary_key};

$query = create()->table('order_items')
    ->column(column('order_id', sql_type_integer())->notNull())
    ->column(column('product_id', sql_type_integer())->notNull())
    ->constraint(primary_key('order_id', 'product_id'));

echo $query->toSQL();
// CREATE TABLE order_items (order_id int NOT NULL, product_id int NOT NULL, PRIMARY KEY (order_id, product_id))

Unique Constraint

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial, sql_type_varchar, unique_constraint};

$query = create()->table('users')
    ->column(column('id', sql_type_serial())->primaryKey())
    ->column(column('email', sql_type_varchar(255))->notNull())
    ->constraint(unique_constraint('email'));

echo $query->toSQL();
// CREATE TABLE users (id serial PRIMARY KEY, email varchar(255) NOT NULL, UNIQUE (email))

Check Constraint

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial, sql_type_integer, check_constraint};

$query = create()->table('products')
    ->column(column('id', sql_type_serial())->primaryKey())
    ->column(column('price', sql_type_integer()))
    ->constraint(check_constraint('price > 0')->name('positive_price'));

echo $query->toSQL();
// CREATE TABLE products (id serial PRIMARY KEY, price int, CONSTRAINT positive_price CHECK (price > 0))

Foreign Key Constraint

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_serial, sql_type_integer, foreign_key, ref_action_cascade, ref_action_restrict};

$query = create()->table('orders')
    ->column(column('id', sql_type_serial())->primaryKey())
    ->column(column('user_id', sql_type_integer())->notNull())
    ->constraint(
        foreign_key(['user_id'], 'users', ['id'])
            ->onDelete(ref_action_cascade())
            ->onUpdate(ref_action_restrict())
    );

echo $query->toSQL();
// CREATE TABLE orders (id serial PRIMARY KEY, user_id int NOT NULL, FOREIGN KEY (user_id) REFERENCES ONLY users (id) ON UPDATE RESTRICT ON DELETE CASCADE)

Temporary Tables

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_integer};

$query = create()->table('temp_results')
    ->temporary()
    ->column(column('id', sql_type_integer()));

echo $query->toSQL();
// CREATE TEMPORARY TABLE temp_results (id int) ON COMMIT DROP

Unlogged Tables

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_integer};

$query = create()->table('cache_data')
    ->unlogged()
    ->column(column('id', sql_type_integer()));

echo $query->toSQL();
// CREATE UNLOGGED TABLE cache_data (id int)

Table Inheritance

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_varchar};

$query = create()->table('employees')
    ->column(column('department', sql_type_varchar(100)))
    ->inherits('persons');

echo $query->toSQL();
// CREATE TABLE employees (department varchar(100)) INHERITS (persons)

Partitioned Tables

<?php

use function Flow\PostgreSql\DSL\{create, column, sql_type_integer, sql_type_timestamp};

// Range partitioning
$query = create()->table('logs')
    ->column(column('id', sql_type_integer()))
    ->column(column('created_at', sql_type_timestamp()))
    ->partitionByRange('created_at');

echo $query->toSQL();
// CREATE TABLE logs (id int, created_at timestamp) PARTITION BY RANGE (created_at)

// List partitioning
$query = create()->table('sales')
    ->column(column('region', sql_type_varchar(50)))
    ->partitionByList('region');

// Hash partitioning
$query = create()->table('data')
    ->column(column('id', sql_type_integer()))
    ->partitionByHash('id');

CREATE TABLE AS

Basic CREATE TABLE AS

<?php

use function Flow\PostgreSql\DSL\{create, select, col, table};

$selectQuery = select()
    ->select(col('id'), col('name'))
    ->from(table('users'));

$query = create()->tableAs('users_backup', $selectQuery);

echo $query->toSQL();
// CREATE TABLE users_backup AS SELECT id, name FROM users

With IF NOT EXISTS

<?php

use function Flow\PostgreSql\DSL\{create, select, col, table};

$selectQuery = select()
    ->select(col('id'), col('name'))
    ->from(table('users'));

$query = create()->tableAs('users_backup', $selectQuery)
    ->ifNotExists();

echo $query->toSQL();
// CREATE TABLE IF NOT EXISTS users_backup AS SELECT id, name FROM users

With Column Names

<?php

use function Flow\PostgreSql\DSL\{create, select, col, table};

$selectQuery = select()
    ->select(col('id'), col('name'))
    ->from(table('users'));

$query = create()->tableAs('users_backup', $selectQuery)
    ->columnNames('user_id', 'user_name');

echo $query->toSQL();
// CREATE TABLE users_backup(user_id, user_name) AS SELECT id, name FROM users

Structure Only (No Data)

<?php

use function Flow\PostgreSql\DSL\{create, select, col, table};

$selectQuery = select()
    ->select(col('id'), col('name'))
    ->from(table('users'));

$query = create()->tableAs('users_backup', $selectQuery)
    ->withNoData();

echo $query->toSQL();
// CREATE TABLE users_backup AS SELECT id, name FROM users WITH NO DATA

ALTER TABLE

Add Column

<?php

use function Flow\PostgreSql\DSL\{alter, column, sql_type_varchar};

$query = alter()->table('users')
    ->addColumn(column('email', sql_type_varchar(255))->notNull());

echo $query->toSQL();
// ALTER TABLE users ADD COLUMN email varchar(255) NOT NULL

Drop Column

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->table('users')
    ->dropColumn('temp_column');

echo $query->toSQL();
// ALTER TABLE users DROP temp_column

// With CASCADE
$query = alter()->table('users')
    ->dropColumn('temp_column', cascade: true);

echo $query->toSQL();
// ALTER TABLE users DROP temp_column CASCADE

Alter Column Type

<?php

use function Flow\PostgreSql\DSL\{alter, sql_type_text};

$query = alter()->table('users')
    ->alterColumnType('name', sql_type_text());

echo $query->toSQL();
// ALTER TABLE users ALTER COLUMN name TYPE pg_catalog.text

Set/Drop NOT NULL

<?php

use function Flow\PostgreSql\DSL\alter;

// Set NOT NULL
$query = alter()->table('users')
    ->alterColumnSetNotNull('email');

echo $query->toSQL();
// ALTER TABLE users ALTER COLUMN email SET NOT NULL

// Drop NOT NULL
$query = alter()->table('users')
    ->alterColumnDropNotNull('email');

echo $query->toSQL();
// ALTER TABLE users ALTER COLUMN email DROP NOT NULL

Set/Drop Default

<?php

use function Flow\PostgreSql\DSL\alter;

// Set default
$query = alter()->table('users')
    ->alterColumnSetDefault('status', "'active'");

echo $query->toSQL();
// ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active'

// Drop default
$query = alter()->table('users')
    ->alterColumnDropDefault('status');

echo $query->toSQL();
// ALTER TABLE users ALTER COLUMN status DROP DEFAULT

Add Constraint

<?php

use function Flow\PostgreSql\DSL\{alter, unique_constraint};

$query = alter()->table('users')
    ->addConstraint(unique_constraint('email')->name('users_email_unique'));

echo $query->toSQL();
// ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email)

Drop Constraint

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->table('users')
    ->dropConstraint('users_email_unique');

echo $query->toSQL();
// ALTER TABLE users DROP CONSTRAINT users_email_unique

Multiple Operations

<?php

use function Flow\PostgreSql\DSL\{alter, column, sql_type_varchar};

$query = alter()->table('users')
    ->addColumn(column('phone', sql_type_varchar(20)))
    ->dropColumn('fax')
    ->alterColumnSetNotNull('email');

echo $query->toSQL();
// ALTER TABLE users ADD COLUMN phone varchar(20), DROP fax, ALTER COLUMN email SET NOT NULL

IF EXISTS

<?php

use function Flow\PostgreSql\DSL\{alter, column, sql_type_varchar};

$query = alter()->table('users')
    ->ifExists()
    ->addColumn(column('email', sql_type_varchar(255)));

echo $query->toSQL();
// ALTER TABLE IF EXISTS users ADD COLUMN email varchar(255)

Rename Column

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->table('users')
    ->renameColumn('old_name', 'new_name');

echo $query->toSQL();
// ALTER TABLE users RENAME COLUMN old_name TO new_name

Rename Constraint

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->table('users')
    ->renameConstraint('old_constraint', 'new_constraint');

echo $query->toSQL();
// ALTER TABLE users RENAME CONSTRAINT old_constraint TO new_constraint

Rename Table

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->table('users')
    ->renameTo('users_archive');

echo $query->toSQL();
// ALTER TABLE users RENAME TO users_archive

Set Schema

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->table('users')
    ->setSchema('archive');

echo $query->toSQL();
// ALTER TABLE users SET SCHEMA archive

DROP TABLE

Simple Drop

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->table('users');

echo $query->toSQL();
// DROP TABLE users

IF EXISTS

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->table('users')
    ->ifExists();

echo $query->toSQL();
// DROP TABLE IF EXISTS users

CASCADE

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->table('users')
    ->cascade();

echo $query->toSQL();
// DROP TABLE users CASCADE

Multiple Tables

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->table('users', 'orders', 'products');

echo $query->toSQL();
// DROP TABLE users, orders, products

TRUNCATE

Simple Truncate

<?php

use function Flow\PostgreSql\DSL\truncate_table;

$query = truncate_table('users');

echo $query->toSQL();
// TRUNCATE users

Multiple Tables

<?php

use function Flow\PostgreSql\DSL\truncate_table;

$query = truncate_table('users', 'orders', 'products');

echo $query->toSQL();
// TRUNCATE users, orders, products

Restart Identity

<?php

use function Flow\PostgreSql\DSL\truncate_table;

$query = truncate_table('users')
    ->restartIdentity();

echo $query->toSQL();
// TRUNCATE users RESTART IDENTITY

CASCADE

<?php

use function Flow\PostgreSql\DSL\truncate_table;

$query = truncate_table('users')
    ->cascade();

echo $query->toSQL();
// TRUNCATE users CASCADE

Data Types

The following SQL type functions are available:

Numeric Types

Function PostgreSQL Type
sql_type_integer() int
sql_type_bigint() bigint
sql_type_smallint() smallint
sql_type_serial() serial
sql_type_bigserial() bigserial
sql_type_numeric($precision, $scale) numeric(p,s)
sql_type_decimal($precision, $scale) decimal(p,s)
sql_type_real() real
sql_type_double() double precision

String Types

Function PostgreSQL Type
sql_type_text() text
sql_type_varchar($length) varchar(n)
sql_type_char($length) char(n)

Date/Time Types

Function PostgreSQL Type
sql_type_date() date
sql_type_time($precision) time
sql_type_timestamp($precision) timestamp
sql_type_timestamptz($precision) timestamptz
sql_type_interval() interval

Other Types

Function PostgreSQL Type
sql_type_boolean() boolean
sql_type_uuid() uuid
sql_type_json() json
sql_type_jsonb() jsonb
sql_type_bytea() bytea
sql_type_inet() inet
sql_type_cidr() cidr
sql_type_macaddr() macaddr
sql_type_array($elementType) type[]

Referential Actions

The following referential action functions are available for foreign key constraints:

Function Action
ref_action_cascade() CASCADE
ref_action_restrict() RESTRICT
ref_action_no_action() NO ACTION
ref_action_set_null() SET NULL
ref_action_set_default() SET DEFAULT

For a complete list of DSL functions, see the DSL reference.

Contributors

Join us on GitHub external resource
norberttech stloyd tomaszhanc DawidSajdak owsiakl f-lapinski Bellangelo jmortlock mleczakm flavioheleno mzarnecki szepeviktor xaviermarchegay scyzoryck christianc1 bendavies alexislefebvre sprtk-ches radozato inmanturbo Wiktor6 codedmonkey broncha drupol
scroll back to top