Skip to main content

SQLite type support

Foundations JDBC supports SQLite via the xerial sqlite-jdbc driver. SQLite's type system is famously minimal — five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) plus declared-type "affinity" hints — so this page covers a smaller surface than the other dialects.

SQLite is dynamically typed

By default, every column accepts every storage class regardless of the declared type. INSERT into an INTEGER column accepts a string, an INTEGER PRIMARY KEY accepts a float, and VARCHAR(10) accepts a 10MB string. The only exception is STRICT tables (CREATE TABLE … STRICT, since SQLite 3.37) which enforce the declared type. Use STRICT tables for any new schema you create with this library — it's the only way to make SQLite behave like every other database in the suite. See STRICT Tables.

Date/time storage is text-based by default

The xerial driver writes DATE / DATETIME / TIMESTAMP columns as ISO-8601 TEXT (yyyy-MM-dd HH:mm:ss.SSS). Foundations JDBC follows that convention — SqliteTypes.date, .datetime, .instant all read and write text. If you set dateClass(INTEGER) or dateClass(REAL) on SqliteConfig, you must supply your own date/time bindings.

Storage classes & type affinity​

Per SQLite docs §2–3, every value belongs to one of five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) regardless of column declarations. The declared type only suggests an "affinity":

AffinityTriggered by declared text containing...
INTEGERINT (matches INTEGER, BIGINT, INT8, MEDIUMINT, …)
TEXTCHAR, CLOB, or TEXT (matches VARCHAR, NCHAR, CLOB, …)
BLOBBLOB, or no declaration at all
REALREAL, FLOA, or DOUB
NUMERICanything else (default)

Each SqliteTypes value below picks one canonical declared name and registers the common SQL aliases as vendor-type names so query analysis accepts equivalent declarations (e.g. BIGINT, INT2 → integer; VARCHAR(100), CLOB → text).

Integer types​

SQLite TypeJava TypeAliases recognised by query analysis
INTEGERLongint, int2, int4, int8, bigint, smallint, tinyint, mediumint, unsigned big int
BIGINTLong(same set)
INTInteger(same set)
SMALLINTShort(same set)
TINYINTByte(same set)
val integerType: SqliteType<Long> = SqliteTypes.integer
val bigintType: SqliteType<Long> = SqliteTypes.bigint
val intType: SqliteType<Int> = SqliteTypes.int_
val smallintType: SqliteType<Short> = SqliteTypes.smallint
val tinyintType: SqliteType<Byte> = SqliteTypes.tinyint

Boolean​

SQLite TypeJava TypeNotes
BOOLEANBooleanStored as INTEGER 0/1; the TRUE/FALSE keywords work since SQLite 3.23.0
val boolType: SqliteType<Boolean> = SqliteTypes.boolean_

Floating-point types​

SQLite TypeJava TypeNotes
REALDouble8-byte IEEE 754 (canonical)
DOUBLE / DOUBLE PRECISIONDoubleAliases
FLOATFloatStored as REAL — SQLite has no 32-bit float storage class
val realType: SqliteType<Double> = SqliteTypes.real
val doubleType: SqliteType<Double> = SqliteTypes.double_
val floatType: SqliteType<Float> = SqliteTypes.float_

Numeric / decimal​

SQLite TypeJava TypeNotes
NUMERICBigDecimalArbitrary precision; SQLite does not enforce (p,s)
DECIMAL(p,s)BigDecimalSame — (p,s) is a documentation label

BigDecimal is bound and read as plain text — the xerial driver's setBigDecimal/getBigDecimal are unimplemented (they throw column -1 out of bounds), so the library falls back to setString + BigDecimal::toPlainString and reads via getString. Precision is preserved exactly.

val numericType: SqliteType<java.math.BigDecimal> = SqliteTypes.numeric
val decimalType: SqliteType<java.math.BigDecimal> = SqliteTypes.decimal
val precise: SqliteType<java.math.BigDecimal> = SqliteTypes.decimalOf(18, 6)

String types​

SQLite TypeJava TypeNotes
TEXTStringUTF-8 (canonical)
VARCHAR(n)Stringn is a label; SQLite does not enforce length
CHAR(n)StringSame — label only
CLOBStringTEXT alias
val textType: SqliteType<String> = SqliteTypes.text
val varcharType: SqliteType<String> = SqliteTypes.varcharOf(255)
val charType: SqliteType<String> = SqliteTypes.charOf(10)
val clobType: SqliteType<String> = SqliteTypes.clob

Binary types​

SQLite TypeJava Type
BLOB / BINARY / VARBINARYbyte[]
val blobType: SqliteType<ByteArray> = SqliteTypes.blob

Date/time types​

SQLite has no date/time storage class. Foundations JDBC writes everything as ISO-8601 TEXT, the xerial driver default. Reads parse the text back into the Java type.

SQLite TypeJava TypeWire format
DATELocalDateyyyy-MM-dd
TIMELocalTimeHH:mm[:ss[.fraction]]
DATETIME / TIMESTAMPLocalDateTimeyyyy-MM-dd HH:mm:ss.SSS (or T separator)
TIMESTAMP (UTC)Instantyyyy-MM-ddTHH:mm:ss[.fraction]Z
val dateType: SqliteType<java.time.LocalDate> = SqliteTypes.date
val timeType: SqliteType<java.time.LocalTime> = SqliteTypes.time
val datetimeType: SqliteType<java.time.LocalDateTime> = SqliteTypes.datetime
val timestampType: SqliteType<java.time.LocalDateTime> = SqliteTypes.timestamp
val instantType: SqliteType<java.time.Instant> = SqliteTypes.instant
Sub-millisecond precision is truncated

The default LocalDateTime writer formats with .SSS precision. Microseconds and nanoseconds are silently dropped on write. Instant round-trips at full nanosecond precision because Instant.toString() emits whatever precision the value carries.

UUID​

SQLite TypeJava TypeStorage
UUIDjava.util.UUIDCanonical 36-character TEXT
val uuidType: SqliteType<java.util.UUID> = SqliteTypes.uuid

JSON​

SQLite TypeJava TypeNotes
JSONJsonStored as TEXT; use SQLite's built-in JSON1 functions (json(), json_extract(), ->, ->>)
val jsonType: SqliteType<Json> = SqliteTypes.json

val data = Json("{\"name\": \"SQLite\"}")

SQLite's JSON1 extension is built into the engine since 3.38 (Feb 2022). A binary JSONB form exists since 3.45 — it's stored as BLOB and accessed via jsonb_* functions; foundations-jdbc does not currently expose a separate type for it, so use SqliteTypes.blob if you need the raw binary form.

Enums​

SQLite has no native enum type. The library encodes Java enums as TEXT — pair the column with a CHECK (col IN (...)) constraint in DDL for static enforcement.

enum class Status { PENDING, ACTIVE, COMPLETED }

// SQLite has no native enum — pair with `CHECK (col IN ('PENDING','ACTIVE','COMPLETED'))` in DDL.
val statusType: SqliteType<Status> = SqliteTypes.ofEnum<Status>()

Nullable types​

Any type can be made nullable via .opt():

val notNull: SqliteType<Long> = SqliteTypes.integer
val nullable: SqliteType<Long?> = SqliteTypes.integer.opt()

Custom domain types​

Wrap base types with custom Java types using transform:

data class ProductId(val value: Long)

val productIdType: SqliteType<ProductId> =
    SqliteTypes.integer.transform(::ProductId, ProductId::value)

What SQLite doesn't have​

For parity with the other dialect pages, here's what's deliberately absent:

FeatureSQLite equivalent
Arrays (int[], text[])None — use JSON arrays or junction tables
Composite/STRUCT/OBJECT typesNone — use JSON or denormalised columns
MapsNone — use JSON objects
RangesNone
IntervalsNone — store as INTEGER seconds or TEXT ISO-8601
Network types (inet, cidr, macaddr)None — use TEXT
Geometry/GISRequires the SpatiaLite extension
Stored procedures / CallableStatementNone — SqliteType.outParam() always returns Optional.empty()
COPY / streaming bulk insertNone — use prepared INSERT in a transaction with addBatch()
Unsigned integersNone — INTEGER is signed 64-bit

Connection configuration​

Build a config with SqliteConfig.builder("path/to/file.db"), SqliteConfig.inMemory() (one connection only — each getConnection(":memory:") opens an independent database), or SqliteConfig.sharedInMemory() for file::memory:?cache=shared.

Common options:

  • .foreignKeys(true) — SQLite has foreign-key enforcement off by default; turn it on per connection
  • .journalMode("WAL") + .synchronous("NORMAL") — recommended for concurrent readers
  • .busyTimeoutMs(5000) — wait this long when the database is locked instead of failing immediately
  • .dateClass(DateClass.TEXT) — keep the default (the codecs assume it); change only if you supply your own bindings

Query analysis​

Query analysis works on SQLite. Column types are matched against the affinity-aware vendor-name aliases registered on each SqliteType. The xerial driver's ResultSetMetaData.getColumnTypeName() reports the literal text from CREATE TABLE, which the analyzer normalises (strips precision, lowercases) and compares against the type's alias set.

For empty result sets the driver may report NULL (0) as the column type — the analyzer treats that as "unknown" and skips the type assertion rather than failing. Pre-create some sample rows in your test fixture if you want type checking on every column.