Skip to content

domain-query-builder.md

Latest commit

 

History

History
379 lines (243 loc) · 5.9 KB

domain-query-builder.md

File metadata and controls

379 lines (243 loc) · 5.9 KB

Domain Query Builder

[DOC_LINK:/documentation/components/libs/postgresql.md]

[TOC]

The Domain Query Builder provides a fluent, type-safe interface for constructing PostgreSQL domain management statements: CREATE DOMAIN, ALTER DOMAIN, and DROP DOMAIN.

Domains are user-defined data types with optional constraints. They are useful for defining reusable data types with specific validation rules.

CREATE DOMAIN

Basic Domain Creation

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('email')
    ->as('text');

echo $query->toSql();
// CREATE DOMAIN email AS text

With Schema

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('public.email')
    ->as('text');

echo $query->toSql();
// CREATE DOMAIN public.email AS text

NOT NULL

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('email')
    ->as('text')
    ->notNull();

echo $query->toSql();
// CREATE DOMAIN email AS text NOT NULL

NULL

Explicitly allow NULL values:

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('email')
    ->as('text')
    ->null();

echo $query->toSql();
// CREATE DOMAIN email AS text NULL

DEFAULT

Set a default value:

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('email')
    ->as('text')
    ->default("'default@example.com'");

echo $query->toSql();
// CREATE DOMAIN email AS text DEFAULT 'default@example.com'

CHECK Constraint

Add a CHECK constraint:

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('positive_int')
    ->as('int4')
    ->check('VALUE > 0');

echo $query->toSql();
// CREATE DOMAIN positive_int AS int4 CHECK (value > 0)

Named Constraint

Add a named CHECK constraint:

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('positive_int')
    ->as('int4')
    ->constraint('positive_check')
    ->check('VALUE > 0');

echo $query->toSql();
// CREATE DOMAIN positive_int AS int4 CONSTRAINT positive_check CHECK (value > 0)

COLLATE

Specify a collation:

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('email')
    ->as('text')
    ->collate('en_US');

echo $query->toSql();
// CREATE DOMAIN email AS text COLLATE "en_US"

Multiple Constraints

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->domain('email')
    ->as('text')
    ->notNull()
    ->check("VALUE ~ '^.+@.+$'");

echo $query->toSql();
// CREATE DOMAIN email AS text NOT NULL CHECK (value ~ '^.+@.+$')

ALTER DOMAIN

SET NOT NULL

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->setNotNull();

echo $query->toSql();
// ALTER DOMAIN email SET NOT NULL

DROP NOT NULL

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->dropNotNull();

echo $query->toSql();
// ALTER DOMAIN email DROP NOT NULL

SET DEFAULT

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->setDefault("'default@example.com'");

echo $query->toSql();
// ALTER DOMAIN email SET DEFAULT 'default@example.com'

DROP DEFAULT

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->dropDefault();

echo $query->toSql();
// ALTER DOMAIN email DROP DEFAULT

ADD CONSTRAINT

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->addConstraint('valid_email', "VALUE ~ '^.+@.+$'");

echo $query->toSql();
// ALTER DOMAIN email ADD CONSTRAINT valid_email CHECK (value ~ '^.+@.+$')

DROP CONSTRAINT

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->dropConstraint('valid_email');

echo $query->toSql();
// ALTER DOMAIN email DROP CONSTRAINT valid_email

DROP CONSTRAINT CASCADE

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->dropConstraint('valid_email')
    ->cascade();

echo $query->toSql();
// ALTER DOMAIN email DROP CONSTRAINT valid_email CASCADE

VALIDATE CONSTRAINT

Validate an existing constraint:

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('email')
    ->validateConstraint('valid_email');

echo $query->toSql();
// ALTER DOMAIN email VALIDATE CONSTRAINT valid_email

With Schema

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->domain('public.email')
    ->setNotNull();

echo $query->toSql();
// ALTER DOMAIN public.email SET NOT NULL

DROP DOMAIN

Simple Drop

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->domain('email');

echo $query->toSql();
// DROP DOMAIN email

IF EXISTS

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->domain('email')
    ->ifExists();

echo $query->toSql();
// DROP DOMAIN IF EXISTS email

CASCADE

Drop all objects that depend on the domain:

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->domain('email')
    ->cascade();

echo $query->toSql();
// DROP DOMAIN email CASCADE

RESTRICT

Refuse to drop the domain if any objects depend on it (default behavior):

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->domain('email')
    ->restrict();

echo $query->toSql();
// DROP DOMAIN email

Multiple Domains

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->domain('email', 'phone', 'url');

echo $query->toSql();
// DROP DOMAIN email, phone, url

Combined Options

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->domain('email')
    ->ifExists()
    ->cascade();

echo $query->toSql();
// DROP DOMAIN IF EXISTS email CASCADE

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