SQLite column types
Based on official SQLite docs (opens in a new tab),
each value stored in an SQLite database (or manipulated by the database engine)
has one of the following storage classes NULL
, INTEGER
, REAL
, TEXT
and BLOB
We have native support for all of them, yet if that's not enough for you - feel free to create custom types.
Integer
A signed integer, stored in 0
, 1
, 2
, 3
, 4
, 6
, or 8
bytes depending on the magnitude of the value
import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
id: integer('id')
});
// you can customize integer mode to be number, boolean, timestamp, timestamp_ms
integer('id', { mode: 'number' })
integer('id', { mode: 'boolean' })
integer('id', { mode: 'timestamp_ms' })
integer('id', { mode: 'timestamp' }) // Date
CREATE TABLE `table` (
`id` integer
);
// to make integer primary key auto increment
integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })
CREATE TABLE `table` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL
);
Real
A floating point value, stored as an 8-byte IEEE
floating point number.
import { real, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
real: real('real')
});
CREATE TABLE `table` (
`real` real
);
Text
A text string, stored using the database encoding (UTF-8
, UTF-16BE
or UTF-16LE
).
You can define { enum: ["value1", "value2"] }
config to infer insert
and select
types, it won't check runtime values
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
text: text('text')
});
// will be inferred as text: "value1" | "value2" | null
text('text', { enum: ["value1", "value2"] })
text('text', { mode: 'json' })
text('text', { mode: 'json' }).$type<{ foo: string }>()
CREATE TABLE `table` (
`text` text
);
Blob
A blob of data, stored exactly as it was input.
It's recommended to use text('', { mode: 'json' })
instead of blob('', { mode: 'json' })
,
because it supports JSON functions:
All JSON functions currently throw an error if any of their arguments are BLOBs because BLOBs are reserved for a future enhancement in which BLOBs will store the binary encoding for JSON. https://www.sqlite.org/json1.html (opens in a new tab)
import { blob, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
blob: blob('blob')
});
blob('blob')
blob('blob', { mode: 'buffer' })
blob('blob', { mode: 'bigint' })
blob('blob', { mode: 'json' })
blob('blob', { mode: 'json' }).$type<{ foo: string }>()
CREATE TABLE `table` (
`blob` blob
);
You can specify .$type<..>()
for blob inference, it won't check runtime values.
It provides compile time protection for default values, insert and select schemas.
// will be infered as { foo: string }
json: blob('json', { mode: 'json' }).$type<{ foo: string }>();
// will be infered as string[]
json: blob('json', { mode: 'json' }).$type<string[]>();
// won't compile
json: blob('json', { mode: 'json' }).$type<string[]>().default({});
Boolean
SQLite does not have native boolean
data type, yet you can specify integer
column to be in a boolean
mode.
This allows you to operate boolean values in your code and Drizzle stores them as 0 and 1 integer
values in the database.
import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
id: integer('id', { mode: 'boolean' })
});
CREATE TABLE `table` (
`id` integer
);
Bigint
Since there is no bigint
data type in SQLite, Drizzle offers a special bigint
mode for blob
columns.
This mode allows you to work with BigInt instances in your code, and Drizzle stores them as blob values in the database.
import { blob, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
id: blob('id', { mode: 'bigint' })
});
CREATE TABLE `table` (
`id` blob
);
Customizing column data type
Every column builder has a .$type()
method, which allows you to customize the data type of the column. This is useful, for example, with unknown or branded types.
type UserId = number & { __brand: 'user_id' };
type Data = {
foo: string;
bar: number;
};
const users = sqliteTable('users', {
id: integer('id').$type<UserId>().primaryKey(),
jsonField: blob('json_field').$type<Data>(),
});
Columns constraints
Not null
NOT NULL
constraint dictates that the associated column may not contain a NULL
value
const table = sqliteTable('table', {
numInt: integer('numInt').notNull()
});
CREATE TABLE table (
`numInt` integer NOT NULL
);
Default value
The DEFAULT
clause specifies a default value to use for the column if no value
is explicitly provided by the user when doing an INSERT
.
If there is no explicit DEFAULT
clause attached to a column definition,
then the default value of the column is NULL
.
An explicit DEFAULT
clause may specify that the default value is NULL
,
a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.
import { sql } from "drizzle-orm";
import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable('table', {
int1: integer('int1').default(42),
int2: integer('int2').default(sql`(abs(42))`)
});
CREATE TABLE `table` (
`int1` integer DEFAULT 42
`int2` integer DEFAULT (abs(42))
);
A default value may also be one of the special case-independent keywords CURRENT_TIME
, CURRENT_DATE
or CURRENT_TIMESTAMP
import { sql } from "drizzle-orm";
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
const table = sqliteTable("table", {
time: text("time").default(sql`CURRENT_TIME`),
date: text("date").default(sql`CURRENT_DATE`),
timestamp: text("timestamp").default(sql`CURRENT_TIMESTAMP`),
});
CREATE TABLE `table` (
`time` text DEFAULT CURRENT_TIME
`date` text DEFAULT CURRENT_DATE
`timestamp` text DEFAULT CURRENT_TIMESTAMP
);
When using $default()
or $defaultFn()
, which are simply different aliases for the same function,
you can generate defaults at runtime and use these values in all insert queries.
These functions can assist you in utilizing various implementations such as uuid
, cuid
, cuid2
, and many more
Note: This value does not affect the drizzle-kit behavior, it is only used at runtime in drizzle-orm
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
import { createId } from '@paralleldrive/cuid2';
const table = sqliteTable('table', {
id: text('id').$defaultFn(() => createId()),
});