CREATE TYPE

Turso Extension: CREATE TYPE is a Turso-specific statement not available in standard SQLite. Custom types require STRICT tables. This feature is experimental and must be enabled before use.

The CREATE TYPE statement defines a user-defined type that controls how values are encoded before storage, decoded when read, validated on input, compared for ordering, and what default value to use. Custom types extend the STRICT table type system beyond the five built-in SQLite storage classes.

Syntax

CREATE TYPE [IF NOT EXISTS] type-name [(parameters)]
    BASE base-type
    ENCODE encode-expr
    DECODE decode-expr
    [OPERATOR 'op' [function-name] ...]
    [DEFAULT default-expr];

Description

A custom type wraps one of the four base storage types with user-defined logic. When a value is written to a column of a custom type, the ENCODE expression transforms it before storage. When a value is read, the DECODE expression transforms it back. This lets you store data in an efficient on-disk representation while presenting a different form to queries. Custom types work only with STRICT tables. Using a custom type name in a non-STRICT table has no effect.

Clauses

IF NOT EXISTS

Suppresses the error that would occur if a type with the same name already exists. The existing type is left unchanged.

CREATE TYPE IF NOT EXISTS cents BASE integer ENCODE value * 100 DECODE value / 100;

BASE

Specifies the underlying SQLite storage class used to store values on disk. Every custom type must have a BASE clause.

Base Type	Description
`integer`	Signed integer, stored in 1-8 bytes depending on magnitude
`real`	8-byte IEEE 754 floating-point number
`text`	UTF-8 encoded string
`blob`	Raw binary data

CREATE TYPE cents BASE integer
    ENCODE value * 100
    DECODE value / 100;

ENCODE / DECODE

The ENCODE expression is evaluated whenever a value is written to a column of this type. The DECODE expression is evaluated whenever a value is read. Both expressions use the identifier value to refer to the input.

ENCODE: Transforms the input value into the base storage form. Runs on INSERT, UPDATE, and CAST.
DECODE: Transforms the stored value back into the presentation form. Runs on SELECT.
NULL handling: NULL values bypass both ENCODE and DECODE. A NULL input produces a NULL output without evaluating the expression.

-- Store text in reversed form
CREATE TYPE reversed_text BASE text
    ENCODE turso_reverse(value)
    DECODE turso_reverse(value);

The ENCODE expression is the place to put validation logic. If the ENCODE expression raises an error, the INSERT or UPDATE is aborted.

Parameters

Custom types can accept parameters that are available in the ENCODE and DECODE expressions. Parameters are declared as a parenthesized list after the type name. Each parameter has a name and an optional type annotation.

CREATE TYPE varchar(value text, maxlen integer) BASE text
    ENCODE CASE
        WHEN length(value) <= maxlen THEN value
        ELSE RAISE(ABORT, 'value too long for varchar')
    END
    DECODE value;

When a column uses a parametric type, the arguments are supplied in parentheses after the type name:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name varchar(100),
    bio varchar(500)
) STRICT;

The first parameter is always the input value. Additional parameters are the arguments provided in the column type declaration, in order.

OPERATOR

The OPERATOR clause maps SQL operators to functions. This allows columns of a custom type to participate in comparisons, arithmetic, and ordering.

OPERATOR 'op' function-name

Component	Description
`'op'`	The operator symbol as a string literal: `'+'`, `'-'`, `'*'`, `'/'`, `'<'`, `'='`, etc.
`function-name`	The name of a SQL function to call when this operator is used. Omit to use the base type’s native comparison.

When an operator is defined, expressions like column + 1 are rewritten into function_name(column, 1).

Ordering with OPERATOR ’<’

The < operator is special: it controls how values of this type are sorted in ORDER BY, MIN, MAX, and CREATE INDEX.

OPERATOR '<' (no function name): Uses the base type’s native comparison for ordering. This is sufficient for types where the encoded form sorts correctly (e.g., text dates in ISO 8601 format).
OPERATOR '<' my_compare: Uses my_compare(a, b) as a custom comparator. The function must return a negative integer if a < b, zero if a = b, and a positive integer if a > b.
No < operator: The type cannot be used in ORDER BY or CREATE INDEX.

-- Use base type comparison for ordering
CREATE TYPE cents BASE integer
    ENCODE value * 100
    DECODE value / 100
    OPERATOR '<';

-- Full operator set for a numeric type
CREATE TYPE uint BASE integer
    ENCODE CASE WHEN value >= 0 THEN value ELSE RAISE(ABORT, 'uint must be non-negative') END
    DECODE value
    OPERATOR '+' uint_add
    OPERATOR '-' uint_sub
    OPERATOR '*' uint_mul
    OPERATOR '<'
    OPERATOR '=';

The = operator, when defined, is also used to derive !=. The < operator is used to derive >, >=, and <= automatically by swapping arguments or negating the result.

DEFAULT

The DEFAULT clause sets a type-level default value. When a column of this type does not specify its own DEFAULT, this value is used.

CREATE TYPE boolean BASE integer
    ENCODE boolean_to_int(value)
    DECODE CASE WHEN value THEN 1 ELSE 0 END
    OPERATOR '<'
    DEFAULT 0;

A column-level DEFAULT overrides the type-level DEFAULT:

CREATE TABLE flags (
    id INTEGER PRIMARY KEY,
    is_active boolean,              -- uses type default (0)
    is_admin boolean DEFAULT 1      -- overrides type default
) STRICT;

Validation with RAISE

Use CASE/WHEN with RAISE in the ENCODE expression to validate input at write time. If the condition fails, the statement is aborted with the specified error message.

CREATE TYPE positive_int BASE integer
    ENCODE CASE
        WHEN value > 0 THEN value
        ELSE RAISE(ABORT, 'value must be positive')
    END
    DECODE value;

CREATE TYPE email BASE text
    ENCODE CASE
        WHEN value LIKE '%_@_%.__%' THEN lower(value)
        ELSE RAISE(ABORT, 'invalid email address')
    END
    DECODE value;

CAST Support

The CAST expression applies a custom type’s ENCODE logic:

CREATE TYPE cents BASE integer
    ENCODE value * 100
    DECODE value / 100;

SELECT CAST(42.5 AS cents);
-- 4250

This is useful for converting values to the encoded form outside of INSERT/UPDATE, such as in WHERE clauses and CHECK constraints.

CHECK Constraints with Custom Types

In STRICT tables with custom types, CHECK constraints operate on the decoded (presentation) values. When comparing a column of a custom type against a literal, use CAST to encode the literal:

CREATE TYPE cents BASE integer
    ENCODE value * 100
    DECODE value / 100;

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price cents CHECK(price >= CAST(0 AS cents))
) STRICT;

Inspecting Types

PRAGMA list_types

Lists all available types, including built-in and user-defined:

PRAGMA list_types;
-- name
-- boolean
-- varchar
-- date
-- time
-- timestamp
-- smallint
-- numeric
-- ...

sqlite_turso_types Virtual Table

The sqlite_turso_types virtual table provides the name and SQL definition of each type:

SELECT name, sql FROM sqlite_turso_types;
-- name    | sql
-- boolean | CREATE TYPE boolean(value any) BASE integer ENCODE ...
-- ...

Using with ALTER TABLE

Custom types can be used when adding columns with ALTER TABLE ADD COLUMN:

ALTER TABLE users ADD COLUMN email varchar(255);

The added column follows the same STRICT type-checking rules as columns defined in the original CREATE TABLE.

Built-in Types

Turso provides the following built-in custom types in STRICT tables:

Type	Base	Parameters	Description
`boolean`	integer	—	Constrained to 0 or 1. Aliases: `bool`.
`smallint`	integer	—	Integer constrained to -32768 through 32767. Aliases: `int2`.
`bigint`	integer	—	Integer (no range constraint). Aliases: `int8`.
`varchar(N)`	text	`maxlen`	Text with a maximum length of N characters.
`date`	text	—	ISO 8601 date (YYYY-MM-DD), validated on insert.
`time`	text	—	ISO 8601 time (HH:MM:SS), validated on insert.
`timestamp`	text	—	ISO 8601 datetime, validated on insert.
`numeric(P,S)`	blob	`precision`, `scale`	Fixed-point decimal with P total digits and S decimal places. Supports arithmetic operators.
`uuid`	blob	—	UUID stored as 16-byte blob, displayed as string. Default: `uuid4_str()`.
`inet`	text	—	Validated IP address (IPv4 or IPv6).
`bytea`	blob	—	Binary data (PostgreSQL-compatible alias).
`json`	text	—	Validated JSON text.
`jsonb`	blob	—	JSON stored in binary format, returned as JSON text on read.

The uuid, json, and jsonb types require their respective extensions to be compiled in. They are available by default in standard Turso builds.

Composite Types: STRUCT and UNION

In addition to encode/decode custom types, CREATE TYPE supports two composite type forms: STRUCT (named product type) and UNION (discriminated union / tagged variant). Both store data as blobs on disk and require STRICT tables.

STRUCT

A STRUCT groups multiple named fields into a single column. Use dot notation to read and filter on individual fields:

CREATE TYPE point AS STRUCT(x INT, y INT);

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    pos point
) STRICT;

INSERT INTO locations VALUES (1, struct_pack(10, 20));
INSERT INTO locations VALUES (2, struct_pack(30, 40));

-- Read fields with dot notation
SELECT pos.x, pos.y FROM locations WHERE id = 1;
-- 10|20

-- Filter and order by fields
SELECT id, pos.x FROM locations WHERE pos.y > 25 ORDER BY pos.x;
-- 2|30

Dot notation works everywhere a column expression does — SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and aggregate functions:

CREATE TYPE address AS STRUCT(street TEXT, city TEXT, zip TEXT);
CREATE TABLE people(name TEXT, home address) STRICT;

INSERT INTO people VALUES ('Alice', struct_pack('123 Main St', 'Springfield', '62704'));
INSERT INTO people VALUES ('Bob', struct_pack('456 Oak Ave', 'Springfield', '62701'));

SELECT home.city, COUNT(*) FROM people GROUP BY home.city;
-- Springfield|2

When a table name and column name collide, table references always win. Use an alias to reach the struct field:

-- t.x here is table.column, not column.field
SELECT t.x FROM t;

-- Use an alias to access the struct field
SELECT s.pos.x FROM locations AS s;

struct_pack()

Creates a struct value for INSERT and UPDATE. Arguments are positional, matching the field order in the type definition.

INSERT INTO locations VALUES (1, struct_pack(10, 20));
UPDATE locations SET pos = struct_pack(99, 88) WHERE id = 1;

struct_extract()

Function form of dot notation — struct_extract(col, 'field') is equivalent to col.field. Primarily useful in expression indexes, where dot notation cannot be used:

CREATE INDEX idx_x ON locations(struct_extract(pos, 'x'));

UNION

A UNION is a discriminated union (tagged variant). Each value carries exactly one of the declared variants, identified by a tag. Use dot notation to extract a variant’s value — it returns NULL when the active variant doesn’t match:

CREATE TYPE platform_id AS UNION(telegram INT, slack TEXT, signal TEXT);

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    platform platform_id
) STRICT;

INSERT INTO contacts VALUES (1, union_value('telegram', 12345));
INSERT INTO contacts VALUES (2, union_value('slack', 'U0ABC'));
INSERT INTO contacts VALUES (3, union_value('signal', '+1555'));

-- Dot notation extracts the variant value (NULL if tag doesn't match)
SELECT id, platform.telegram, platform.slack FROM contacts ORDER BY id;
-- 1|12345|
-- 2||U0ABC
-- 3||

-- Combine with union_tag() to filter by variant
SELECT id, platform.slack FROM contacts WHERE union_tag(platform) = 'slack';
-- 2|U0ABC

For unions whose variants are struct types, chain dot access to reach nested fields:

CREATE TYPE telegram_msg AS STRUCT(chat_id INT, text TEXT);
CREATE TYPE msg AS UNION(telegram telegram_msg, slack TEXT);
CREATE TABLE messages(id INT, data msg) STRICT;

INSERT INTO messages VALUES (1, union_value('telegram', struct_pack(100, 'hello')));

-- col.variant.field
SELECT data.telegram.chat_id, data.telegram.text FROM messages;
-- 100|hello

union_value()

Creates a union value for INSERT and UPDATE. The first argument is the variant tag (a string literal), the second is the value. The tag is resolved against the target column’s union type.

INSERT INTO contacts VALUES (1, union_value('telegram', 12345));
UPDATE contacts SET platform = union_value('slack', 'U0XYZ') WHERE id = 1;

union_tag()

Returns the tag name of the active variant as text.

SELECT id, union_tag(platform) FROM contacts ORDER BY id;
-- 1|telegram
-- 2|slack
-- 3|signal

union_extract()

Function form of dot notation — union_extract(col, 'variant') is equivalent to col.variant. Primarily useful in expression indexes:

CREATE INDEX idx_tg ON contacts(union_extract(platform, 'telegram'));

NULL handling

NULL values propagate through all struct and union operations:

CREATE TYPE number AS UNION(i INT, f REAL);
CREATE TABLE values_t(id INT, val number) STRICT;

INSERT INTO values_t VALUES (1, union_value('i', 42));
INSERT INTO values_t VALUES (2, union_value('f', 3.14));
INSERT INTO values_t VALUES (3, NULL);

SELECT id, val.i, val.f FROM values_t ORDER BY id;
-- 1|42|
-- 2||3.14
-- 3||

Dot notation precedence

When a dot expression a.b is ambiguous (e.g., a could be a table name or a column name), table references always take priority over struct field access. This follows DuckDB’s resolution rules.

CREATE TYPE point AS STRUCT(x INT, y INT);
CREATE TABLE t(x INT, t point) STRICT;
INSERT INTO t VALUES (99, struct_pack(10, 20));

-- t.x resolves as table.column (99), not column.field (10)
SELECT t.x FROM t;
-- 99

-- Use an alias to reach the struct field
SELECT s.t.x FROM t AS s;
-- 10

For a.b.c, the resolution order is: database.table.column, then table.column.field.

Inline types not supported

Inline STRUCT/UNION declarations in column definitions are not supported. Always use CREATE TYPE first:

-- This will error:
CREATE TABLE t(data STRUCT(x INT, y INT)) STRICT;

-- Do this instead:
CREATE TYPE point AS STRUCT(x INT, y INT);
CREATE TABLE t(data point) STRICT;

Array Types

Array columns store ordered collections of values. Unlike STRUCT and UNION, arrays don’t require an explicit CREATE TYPE — declare them by appending [] to any base type name in a STRICT table column definition:

CREATE TABLE sensors (
    id INTEGER PRIMARY KEY,
    readings REAL[],
    labels TEXT[],
    flags INTEGER[]
) STRICT;

INSERT INTO sensors VALUES (1, ARRAY[1.5, 2.5, 3.5], ARRAY['temp','humidity'], '[0, 1, 1]');

SELECT readings[0], labels[1], array_length(flags) FROM sensors;
-- 1.5|humidity|3

Multi-dimensional arrays use multiple bracket pairs:

CREATE TABLE matrices (
    id INTEGER PRIMARY KEY,
    data INTEGER[][]
) STRICT;

INSERT INTO matrices VALUES (1, ARRAY[ARRAY[1,2], ARRAY[3,4]]);
SELECT data[1][0] FROM matrices;
-- 3

Element types are validated on insert — an INTEGER[] column rejects values that cannot be stored as integers. Arrays are stored internally as compact record-format BLOBs and displayed as JSON arrays on output. For the full set of array functions (array_agg, array_append, array_contains, array_slice, etc.), operators (@>, &&, ||), and subscript syntax, see Array Functions.

Restrictions

STRICT tables only: Custom type names are ignored in non-STRICT tables. The column uses standard type affinity rules instead.
Cannot drop while in use: A type cannot be dropped with DROP TYPE while any table has a column of that type.

If a non-STRICT table was created with a type name that did not exist at the time (e.g., CREATE TABLE t(x mytype)), and a custom type with that name is created afterwards (CREATE TYPE mytype ...), the type’s ENCODE/DECODE logic will not be retroactively applied to the existing table. This is not a bug: non-STRICT tables treat all type names as plain affinity hints regardless of whether a matching custom type exists. STRICT tables prevent this scenario because they reject unknown type names at CREATE TABLE time.

No subqueries in expressions: The ENCODE, DECODE, and DEFAULT expressions cannot contain subqueries, aggregate functions, or window functions.
No indexes on STRUCT/UNION columns: CREATE INDEX on a STRUCT or UNION column is not supported.

Examples

Identity Type (Passthrough)

A minimal type that stores and retrieves values without transformation:

CREATE TYPE my_text BASE text ENCODE value DECODE value;

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body my_text
) STRICT;

Monetary Values as Cents

Store dollar amounts as integer cents for exact arithmetic, present them as the original value:

CREATE TYPE cents BASE integer
    ENCODE value * 100
    DECODE value / 100
    OPERATOR '<';

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    amount cents
) STRICT;

INSERT INTO invoices VALUES (1, 42);
-- Stored as 4200 on disk, queried as 42

SELECT amount FROM invoices;
-- 42

JSON Validation

Validate that inserted values are valid JSON:

CREATE TYPE json BASE text
    ENCODE json(value)
    DECODE value;

CREATE TABLE configs (
    id INTEGER PRIMARY KEY,
    data json
) STRICT;

INSERT INTO configs VALUES (1, '{"key": "value"}');
-- succeeds

INSERT INTO configs VALUES (2, 'not json');
-- Error: malformed JSON

Unsigned Integer with Operators

A non-negative integer type with arithmetic and comparison operators:

CREATE TYPE uint BASE integer
    ENCODE CASE
        WHEN value >= 0 THEN value
        ELSE RAISE(ABORT, 'uint must be non-negative')
    END
    DECODE value
    OPERATOR '<'
    OPERATOR '=';

CREATE TABLE counters (
    id INTEGER PRIMARY KEY,
    count uint DEFAULT 0
) STRICT;

INSERT INTO counters VALUES (1, 5);
SELECT * FROM counters WHERE count > 0 ORDER BY count;

Fixed-Point Decimal

Use the built-in numeric type for precise decimal arithmetic:

CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    amount numeric(10, 2),
    tax numeric(10, 2)
) STRICT;

INSERT INTO transactions VALUES (1, 99.99, 8.25);
SELECT amount + tax FROM transactions;
-- 108.24

​CREATE TYPE

​Syntax

​Description

​Clauses

​IF NOT EXISTS

​BASE

​ENCODE / DECODE

​Parameters

​OPERATOR

​Ordering with OPERATOR ’<’

​DEFAULT

​Validation with RAISE

​CAST Support

​CHECK Constraints with Custom Types

​Inspecting Types

​PRAGMA list_types

​sqlite_turso_types Virtual Table

​Using with ALTER TABLE

​Built-in Types

​Composite Types: STRUCT and UNION

​STRUCT

​struct_pack()

​struct_extract()

​UNION

​union_value()

​union_tag()

​union_extract()

​NULL handling

​Dot notation precedence

​Inline types not supported

​Array Types

​Restrictions

​Examples

​Identity Type (Passthrough)

​Monetary Values as Cents

​JSON Validation

​Unsigned Integer with Operators

​Fixed-Point Decimal

​See Also

CREATE TYPE

Syntax

Description

Clauses

IF NOT EXISTS

BASE

ENCODE / DECODE

Parameters

OPERATOR

Ordering with OPERATOR ’<’

DEFAULT

Validation with RAISE

CAST Support

CHECK Constraints with Custom Types

Inspecting Types

PRAGMA list_types

sqlite_turso_types Virtual Table

Using with ALTER TABLE

Built-in Types

Composite Types: STRUCT and UNION

STRUCT

struct_pack()

struct_extract()

UNION

union_value()

union_tag()

union_extract()

NULL handling

Dot notation precedence

Inline types not supported

Array Types

Restrictions

Examples

Identity Type (Passthrough)

Monetary Values as Cents

JSON Validation

Unsigned Integer with Operators

Fixed-Point Decimal

See Also