Sqlite – DrizzleORM

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).

import { text, sqliteTable } from "drizzle-orm/sqlite-core";
 
const table = sqliteTable('table', {
	text: text('text')
});
 
text('text')
text('union').$type<'union' | 'string' | 'type'>()

CREATE TABLE `table` (
	`text` text
);

Blob

A blob of data, stored exactly as it was input.

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
);

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
);

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
);

MySQL Conditional operators