Types

PostgreSQL Types

SQLDelight column definitions are identical to regular PostgreSQL column definitions but support an extra column constraint which specifies the Kotlin type of the column in the generated interface.

CREATE TABLE some_types (
  some_smallint SMALLINT,               -- Retrieved as Short
  some_int2 INT2,                       -- Retrieved as Short
  some_integer INTEGER,                 -- Retrieved as Int
  some_int INT,                         -- Retrieved as Int
  some_int4 INT4,                       -- Retrieved as Int
  some_bigint BIGINT,                   -- Retrieved as Long
  some_int8 INT8,                       -- Retrieved as Long
  some_numeric NUMERIC,                 -- Retrieved as BigDecimal
  some_decimal DECIMAL,                 -- Retrieved as Double
  some_real REAL,                       -- Retrieved as Double
  some_float4 FLOAT4,                   -- Retrieved as Double
  some_double_prec DOUBLE PRECISION,    -- Retrieved as Double
  some_float8 FLOAT8,                   -- Retrieved as Double
  some_smallserial SMALLSERIAL,         -- Retrieved as Short
  some_serial2 SERIAL2,                 -- Retrieved as Short
  some_serial SERIAL,                   -- Retrieved as Int
  some_serial4 SERIAL4,                 -- Retrieved as Int
  some_bigserial BIGSERIAL,             -- Retrieved as Long
  some_serial8 SERIAL8,                 -- Retrieved as Long
  some_character CHARACTER,             -- Retrieved as String
  some_char CHAR,                       -- Retrieved as String
  some_char_var CHARACTER VARYING(16),  -- Retrieved as String
  some_varchar VARCHAR(16),             -- Retrieved as String
  some_text TEXT,                       -- Retrieved as String
  some_date DATE,                       -- Retrieved as LocalDate
  some_time TIME,                       -- Retrieved as LocalTime
  some_timestamp TIMESTAMP,             -- Retrieved as LocalDateTime
  some_timestamp TIMESTAMPTZ,           -- Retrieved as OffsetDateTime
  some_json JSON,                       -- Retrieved as String
  some_jsonb JSONB,                     -- Retrieved as String
  some_interval INTERVAL,               -- Retrieved as PGInterval
  some_uuid UUID                        -- Retrieved as UUID
  some_bool BOOL,                       -- Retrieved as Boolean
  some_boolean BOOLEAN,                 -- Retrieved as Boolean
  some_bytea BYTEA                      -- Retrieved as ByteArray
);

Custom Column Types

If you'd like to retrieve columns as custom types you can specify a Kotlin type:

import kotlin.String;
import kotlin.collections.List;

CREATE TABLE hockeyPlayer (
  cup_wins TEXT AS List<String> NOT NULL
);

However, creating the Database will require you to provide a ColumnAdapter which knows how to map between the database type and your custom type:

val listOfStringsAdapter = object : ColumnAdapter<List<String>, String> {
  override fun decode(databaseValue: String) =
    if (databaseValue.isEmpty()) {
      listOf()
    } else {
      databaseValue.split(",")
    }
  override fun encode(value: List<String>) = value.joinToString(separator = ",")
}

val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = hockeyPlayer.Adapter(
    cup_winsAdapter = listOfStringsAdapter
  )
)

Enums

As a convenience the SQLDelight runtime includes a ColumnAdapter for storing an enum as String data.

import com.example.hockey.HockeyPlayer;

CREATE TABLE hockeyPlayer (
  position TEXT AS HockeyPlayer.Position
)

val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = HockeyPlayer.Adapter(
    positionAdapter = EnumColumnAdapter()
  )
)

Value types

SQLDelight can generate a value type for a column which wraps the underlying database type if requested:

CREATE TABLE hockeyPlayer (
  id INT AS VALUE
);

Optimistic Locking

If you specify a column as a LOCK, it would have a value type generated for it, and also require that UPDATE statements correctly use the lock to perform updates.

CREATE TABLE hockeyPlayer(
  id INT AS VALUE,
  version_number INT AS LOCK,
  name VARCHAR(8)
);

-- This will fail (and the IDE plugin will suggest rewriting to the below)
updateName:
UPDATE hockeyPlayer
SET name = ?;

-- This will pass compilation
updateNamePassing:
UPDATE hockeyPlayer
SET name = ?
    version_number = :version_number + 1
WHERE version_number = :version_number;

Custom Types in Migrations

If migrations are the schema's source of truth, you can also specify the exposed kotlin type when altering a table:

import kotlin.String;
import kotlin.collection.List;

ALTER TABLE my_table
  ADD COLUMN new_column VARCHAR(8) AS List<String>;