SQLite Column Types in Drizzle ORM

SQLite Type Mapping in Drizzle

SQLite has a uniquely flexible type system — it uses type affinity rather than strict column types. Any column can store any type of value. However, Drizzle's drizzle-orm/sqlite-core module provides typed column builders that map to SQLite's type affinities and provide TypeScript type safety.

Common Type Mappings

SQLite Type	Drizzle Builder	TypeScript Type
INTEGER	integer()	number
TEXT	text()	string
REAL	real()	number
BLOB	blob()	Buffer
INTEGER (bool)	integer()	number (0 or 1)

SQLite's Simplified Type Set

SQLite only has five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. When converting SQL from other databases, many types collapse to these basics:

Original SQL Type	SQLite Affinity	Drizzle Builder
VARCHAR(n)	TEXT	text()
BOOLEAN	INTEGER	integer()
TIMESTAMP	TEXT	text()
JSON	TEXT	text()
UUID	TEXT	text()
DECIMAL	TEXT	text()

Example Conversion

CREATE TABLE notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT,
  priority INTEGER DEFAULT 0,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Produces:

import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";

export const notes = sqliteTable("notes", {
  id: integer("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  priority: integer("priority").default(0),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
});

SQLite-Specific Considerations

AUTOINCREMENT: SQLite's AUTOINCREMENT keyword is different from PostgreSQL's SERIAL. In Drizzle, an integer().primaryKey() column in SQLite is automatically auto-incrementing (SQLite's ROWID behavior). The AUTOINCREMENT keyword only prevents rowid reuse.

No native boolean: SQLite stores booleans as integers (0 or 1). Drizzle maps these to integer() rather than providing a separate boolean builder for SQLite.

Date/Time storage: SQLite has no native date/time types. Dates are typically stored as TEXT (ISO 8601 strings), INTEGER (Unix timestamps), or REAL (Julian day numbers). The converter maps TIMESTAMP and DATETIME to text() for SQLite.