Type Constraints in 65 lines of SQL

PostgreSQL has a rich and extensible type system. Beyond enums and composite types, we can:

apply data validation rules
override comparison operators like = / + / -
create custom aggregations
define casting rules between types

With a little effort, a user-defined type can feel indistinguishable from a built-in. In this article we focus on validation and ergonomics while quickly touching on a few other concepts.

To illustrate, we’ll create an semver data type to represent Semantic Versioning values. We’ll then add validation rules to make invalid states unrepresentable.

SemVer

A (very) loose primer on SemVer:

SemVer is a specification for representing software versions that communicate information about backwards compatibility. The type is typically represented as a string with 5 components.

Where pre-release and metadata are optional.

The intent of each component is outside the scope of this article but, as an example, incrementing the major version number notifies users that the release includes at least one backwards incompatible change.

For a concise representation of the full spec, check out the grammar.

SQL

For our purposes, we’ll assume that the SemVer type is a critical component of the application that needs to be queried flexibly and efficiently.

Storing Components

To that end, we’ll store each component of the version as a separate field on a composite type.

create type semver_components as (
    major int,
    minor int,
    patch int,
    pre_release text[],
    build_metadata text[]
);

We can create an instance of this type in SQL by casting a tuple as the semver_components type.

select
    (1, 2, 3, array['beta', '1'], array['meta'])::semver_components
-- returns: (1,2,3,{'beta','1'},{'meta'})

Unfortunately, our definition is far too permissive.

select
    (null, -500, null, array['?'], array[''])::semver_components
-- returns: (,-500,,{'?'},{''

Our data type has no problem accepting invalid components. To list a few of the SemVer rules we violated:

Major version must not be null
Minor version must be ≥ 0
Patch version must not be null
Pre-release elements must only include characters [A-z0-9]
Build metadata elements may not be empty strings

We need to add some validation rules to meet our “make invalid states unrepresentable” goal.

Validation

Domains are Postgres’ solution for optionally layering constraints over a data type. Domains are to types what check constraints are to tables. If you’re not familiar with check constraints, you can think of them as equivalent to zod/pydantic in javascript/python.

Let's codify some SemVer rules, layer them on the semver_components type, and give the new domain a friendly name.

create domain semver
    as semver_components
    check (
        -- major: non-null positive integer
        (value).major is not null and (value).major >= 0
        -- minor: non-null positive integer
        and (value).minor is not null and (value).minor >= 0
        -- patch: non-null positive integer
        and (value).patch is not null and (value).patch >= 0
        and semver_elements_match_regex(
            (value).pre_release,
            '^[A-z0-9]{1,255}$'
        )
        and semver_elements_match_regex(
            (value).build_metadata,
            '^[A-z0-9\.]{1,255}$'
        )
    );

which references a helper function:

create or replace function semver_elements_match_regex(
    parts text[],
    regex text
)
returns bool
language sql
as $$
    -- validates that *parts* nullable array of non-empty strings
    -- where each element of *parts* matches *regex*
    select
        $1 is null
        or (
            (
                select (
                    bool_and(pr_arr.elem is not null)
                    and bool_and(pr_arr.elem ~ $2)
                )
                from
                    unnest($1) pr_arr(elem)
            )
            and array_length($1, 1) > 0
        )
$$;

Now, if we repeat our positive and negative test cases using the semver type (vs semver_components) we still accept valid states:

-- Success Case
select
    (1, 2, 3, array['beta', '1'], array['meta'])::semver,
-- returns: (1,2,3,{'beta','1'},{'meta'})

while invalid states are rejected with an error:

-- Failure Case
select
    (null, -500, null, array['?'], array[''])::semver
-- ERROR:  value for domain semver violates check constraint "semver_check"
-- SQL state: 23514

Testing

Our validation doesn’t have to be called manually. The semver domain can be used anywhere you’d use the semver_components type and the validations are automatically applied.

-- A table with a semver column
create table package_version(
    id bigserial primary key,
    package_name text not null,
    package_semver semver not null -- semver column
);

-- Insert some valid records
insert into package_version( package_name, package_semver )
values
    ('supabase-js', (2, 2, 3, null, null)),
    ('supabase-js', (2, 0, 0, array['rc', '1'], null)
);

-- Attempt to insert an invalid record (major is null)
insert into package_version( package_name, package_semver )
values
    ('invalid-js', (null, 1, 0, array['asdf'], null));
-- ERROR:  value for domain semver violates check constraint "semver_check"

Good stuff!

We’re 48 lines of SQL in and have solved for making invalid states unrepresentable. Now lets think about ergonomics.

Displaying

Now that our data type is well constrained, you might notice that selecting values from a semver typed column returns a tuple, rather than the SemVer string we’re used to seeing.

select
    *
from
    package_version
/*
id | package_name |    package_semver
-------------------------------------
 1 | supabase-js  |         (2,2,3,,)
 2 | supabase-js  | (2,0,0,"{rc,1}",)
*/

For example: (2,0,0,"{rc,1}",) vs 2.0.0-rc.1

We could work around that problem with some custom casts, but I’d recommend keeping everything explicit with a function call.

create or replace function semver_to_text(semver)
    returns text
    immutable
    language sql
as $$
    select
        format('%s.%s.%s', $1.major, $1.minor, $1.patch)
        || case
            when $1.pre_release is null then ''
            else format('-%s', array_to_string($1.pre_release, '.'))
        end
        || case
            when $1.build_metadata is null then ''
            else format('+%s', array_to_string($1.build_metadata, '.'))
        end
$$;

Which allows us to query the package_version table and retrieve a string representation of the data.

select
    id,
    package_name,
    semver_to_text(package_semver) as ver -- cast as text
from
    package_version
/*
id | package_name |   ver
------------------------------
 1 | supabase-js  |      2.2.3
 2 | supabase-js  | 2.0.0-rc.1
*/

Or, better yet, use a generated column

create table package_version(
    id bigserial primary key,
    package_name text not null,
    package_semver semver not null,
  semver_text text generated always as (semver_to_text(package_semver)) stored
);

so the text representation is persisted along with the semver type and incurs no query/filter penalty.

Other Tricks

Postgres provides all the tools you could want to make your data types/domains work with SQL as seamlessly as builtins.

For example, you could:

add convenience functions to parse a semver type from text
override the equality operator (=) to correctly reflect that versions differing only in build metadata are considered equal
add a max function to efficiently query for the newest version of each package from within the database

to name a few.

Aligning the right parts of your business’ logic with the database can dramatically improve throughput, decrease IO, and simplify application code.

Conclusion

Admittedly, building performant and ergonomic custom data types in Postgres involves a lot of ceremony.

That said, in cases where:

the type’s data integrity is critical
the type is well specified
the type’s spec does not change (or changes infrequently)

Teaching Postgres to have first class support for your custom type can be transformative for data integrity and performance.

Type Constraints in 65 lines of SQL

SemVer

SQL

Storing Components

Validation

Testing

Displaying

Other Tricks

Conclusion

Geo Queries with PostGIS in Ionic Angular

HappyTeams unlocks better performance and reduces cost with Supabase

Build in a weekend, scale to millions

Type Constraints in 65 lines of SQL

SemVer#

SQL#

Storing Components#

Validation#

Testing#

Displaying#

Other Tricks#

Conclusion#

Geo Queries with PostGIS in Ionic Angular

HappyTeams unlocks better performance and reduces cost with Supabase

Build in a weekend, scale to millions

SemVer

SQL

Storing Components

Validation

Testing

Displaying

Other Tricks

Conclusion