drizzle-orm/setup

326 notes in this subject, read out of this brain and free to use. This is page 5 of 6.

Vercel Postgres connection setup with drizzle

To connect Drizzle ORM to Vercel Postgres, create a file at src/db/index.ts with the following code: import { drizzle } from 'drizzle-orm/vercel-postgres'; import { config } from 'dotenv'; config({ path: '.env.local' }); export const db = drizzle();

Required packages for Drizzle with Vercel Postgres

To use Drizzle ORM with Vercel Postgres, install: drizzle-orm@rc, drizzle-kit@rc (as dev dependency), dotenv for environment variable management, and @vercel/postgres for the Vercel Postgres driver.

Vercel Postgres environment variable

To use Vercel Postgres with Drizzle ORM, copy the POSTGRES_URL from your Vercel Postgres database dashboard and add it to your .env.local or .env file as POSTGRES_URL=<YOUR_DATABASE_URL>.

Environment variable reference in Railway

Use the syntax ${{ServiceName.VARIABLE_NAME}} to reference variables from other Railway services. Example: ${{Postgres.DATABASE_URL}} references the DATABASE_URL from a PostgreSQL service and automatically resolves to the private internal connection string at runtime.

Connect Drizzle ORM to PostgreSQL database

Create a db.ts file that imports drizzle from drizzle-orm/node-postgres and initializes it with the DATABASE_URL environment variable: import { drizzle } from "drizzle-orm/node-postgres"; export const db = drizzle(process.env.DATABASE_URL!);

ES module requirement for top-level await

To use top-level await in Node.js, the package.json must include "type": "module" to treat files as ES modules.

PostgreSQL driver package for Node.js

The pg package is used as the PostgreSQL driver for Drizzle ORM with Node.js.

createSchemaFactory allows using extended Typebox instances

The createSchemaFactory function accepts a typeboxInstance option to use an extended Typebox instance instead of the default one. This is useful for frameworks like Elysia that provide extended Typebox instances with additional validation capabilities.

Boolean type mapping to Typebox schema

PostgreSQL pg.boolean(), MySQL mysql.boolean(), and SQLite sqlite.integer({ mode: 'boolean' }) all map to Typebox Type.Boolean().

Date and timestamp type mapping to Typebox schema

PostgreSQL pg.date({ mode: 'date' }), pg.timestamp({ mode: 'date' }), MySQL mysql.date({ mode: 'date' }), mysql.datetime({ mode: 'date' }), mysql.timestamp({ mode: 'date' }), and SQLite sqlite.integer({ mode: 'timestamp' }), sqlite.integer({ mode: 'timestamp_ms' }) all map to Typebox Type.Date().

String type mappings include various database columns

The following columns map to Typebox Type.String(): PostgreSQL pg.date({ mode: 'string' }), pg.timestamp({ mode: 'string' }), pg.cidr(), pg.inet(), pg.interval(), pg.macaddr(), pg.macaddr8(), pg.numeric(), pg.text(), pg.sparsevec(), pg.time(); MySQL mysql.binary(), mysql.date({ mode: 'string' }), mysql.datetime({ mode: 'string' }), mysql.decimal(), mysql.time(), mysql.timestamp({ mode: 'string' }), mysql.varbinary(); SQLite sqlite.numeric(), sqlite.text({ mode: 'text' }).

UUID type mapping to Typebox schema

PostgreSQL pg.uuid() maps to Typebox Type.String({ format: 'uuid' }).

Fixed character types map to fixed-length strings

PostgreSQL pg.char({ length: ... }) and MySQL mysql.char({ length: ... }) map to Typebox Type.String({ minLength: length, maxLength: length }).

Variable character types map to maximum-length strings

PostgreSQL pg.varchar({ length: ... }), MySQL mysql.varchar({ length: ... }), and SQLite sqlite.text({ mode: 'text', length: ... }) map to Typebox Type.String({ maxLength: length }).

MySQL tinytext maps to 255 character max length

MySQL mysql.tinytext() maps to Typebox Type.String({ maxLength: 255 }), corresponding to the unsigned 8-bit integer limit.

MySQL longtext maps to 4294967295 character max length

MySQL mysql.longtext() maps to Typebox Type.String({ maxLength: 4_294_967_295 }), corresponding to the unsigned 32-bit integer limit.

Enum types map to Typebox Type.Enum

PostgreSQL pg.text({ enum: ... }), pg.char({ enum: ... }), pg.varchar({ enum: ... }); MySQL mysql.tinytext({ enum: ... }), mysql.mediumtext({ enum: ... }), mysql.text({ enum: ... }), mysql.longtext({ enum: ... }), mysql.char({ enum: ... }), mysql.varchar({ enum: ... }), mysql.mysqlEnum(..., ...); SQLite sqlite.text({ mode: 'text', enum: ... }) all map to Typebox Type.Enum(enum).

MySQL tinyint signed maps to -128 to 127 range

MySQL mysql.tinyint() maps to Typebox Type.Integer({ minimum: -128, maximum: 127 }), corresponding to the 8-bit signed integer range.

MySQL tinyint unsigned maps to 0 to 255 range

MySQL mysql.tinyint({ unsigned: true }) maps to Typebox Type.Integer({ minimum: 0, maximum: 255 }), corresponding to the unsigned 8-bit integer range.

MySQL smallint unsigned maps to 0 to 65535 range

MySQL mysql.smallint({ unsigned: true }) maps to Typebox Type.Integer({ minimum: 0, maximum: 65_535 }), corresponding to the unsigned 16-bit integer range.

32-bit integer signed types and mapping

PostgreSQL pg.integer(), pg.serial(); MySQL mysql.int() map to Typebox Type.Integer({ minimum: -2_147_483_648, maximum: 2_147_483_647 }), corresponding to the 32-bit signed integer range.

MySQL int unsigned maps to 32-bit unsigned range

MySQL mysql.int({ unsigned: true }) maps to Typebox Type.Integer({ minimum: 0, maximum: 4_294_967_295 }), corresponding to the unsigned 32-bit integer range.

48-bit double precision float types and mapping

PostgreSQL pg.doublePrecision(); MySQL mysql.double(), mysql.real(); SQLite sqlite.real() map to Typebox Type.Number({ minimum: -140_737_488_355_328, maximum: 140_737_488_355_327 }), corresponding to the 48-bit integer range.

MySQL double unsigned maps to unsigned 48-bit range

MySQL mysql.double({ unsigned: true }) maps to Typebox Type.Number({ minimum: 0, maximum: 281_474_976_710_655 }), corresponding to the unsigned 48-bit integer range.

64-bit integer with number mode mapping

PostgreSQL pg.bigint({ mode: 'number' }), pg.bigserial({ mode: 'number' }); MySQL mysql.bigint({ mode: 'number' }), mysql.bigserial({ mode: 'number' }); SQLite sqlite.integer({ mode: 'number' }) map to Typebox Type.Integer({ minimum: -9_007_199_254_740_991, maximum: 9_007_199_254_740_991 }), corresponding to JavaScript's minimum and maximum safe integers.

MySQL serial maps to JavaScript safe integer range

MySQL mysql.serial() maps to Typebox Type.Integer({ minimum: 0, maximum: 9_007_199_254_740_991 }), corresponding to JavaScript's maximum safe integer.

MySQL bigint unsigned bigint mode mapping

MySQL mysql.bigint({ mode: 'bigint', unsigned: true }) maps to Typebox Type.BigInt({ minimum: 0, maximum: 18_446_744_073_709_551_615n }), corresponding to the unsigned 64-bit integer range.

MySQL year type maps to 1901 to 2155 range

MySQL mysql.year() maps to Typebox Type.Integer({ minimum: 1_901, maximum: 2_155 }).

Point geometry xy mode mapping

PostgreSQL pg.geometry({ type: 'point', mode: 'xy' }), pg.point({ mode: 'xy' }) map to Typebox Type.Object({ x: Type.Number(), y: Type.Number() }).

Vector types mapping to Typebox arrays

PostgreSQL pg.halfvec({ dimensions: ... }), pg.vector({ dimensions: ... }) map to Typebox Type.Array(Type.Number(), { minItems: dimensions, maxItems: dimensions }).

Line geometry abc mode mapping

PostgreSQL pg.line({ mode: 'abc' }) maps to Typebox Type.Object({ a: Type.Number(), b: Type.Number(), c: Type.Number() }).

Line geometry tuple mode mapping

PostgreSQL pg.line({ mode: 'tuple' }) maps to Typebox Type.Tuple([Type.Number(), Type.Number(), Type.Number()]).

JSON and JSONB types mapping

PostgreSQL pg.json(), pg.jsonb(); MySQL mysql.json(); SQLite sqlite.blob({ mode: 'json' }), sqlite.text({ mode: 'json' }) map to Typebox Type.Recursive((self) => Type.Union([Type.Union([Type.String(), Type.Number(), Type.Boolean(), Type.Null()]), Type.Array(self), Type.Record(Type.String(), self)])).

SQLite blob buffer mode mapping

SQLite sqlite.blob({ mode: 'buffer' }) maps to Typebox Union allowing strings, numbers, booleans, null, arrays, and objects.

Array type mapping in PostgreSQL

PostgreSQL pg.dataType().array(...) maps to Typebox Type.Array(baseDataTypeSchema, { minItems: size, maxItems: size }).

64-bit integer with bigint mode mapping

PostgreSQL pg.bigint({ mode: 'bigint' }), pg.bigserial({ mode: 'bigint' }); MySQL mysql.bigint({ mode: 'bigint' }); SQLite sqlite.blob({ mode: 'bigint' }) map to Typebox Type.BigInt({ minimum: -9_223_372_036_854_775_808n, maximum: 9_223_372_036_854_775_807n }), corresponding to the full 64-bit signed integer range.

Install typebox-legacy with dependencies

To use drizzle-typebox legacy, install drizzle-orm@rc and @sinclair/typebox.

createSelectSchema validates query results against table schema

The createSelectSchema function from drizzle-orm/typebox-legacy defines the shape of data queried from the database and can be used to validate API responses. When using Value.Parse() to validate rows, all columns returned by the query must be included in the select statement, or the validation will fail.

createInsertSchema validates insert data against table schema

The createInsertSchema function from drizzle-orm/typebox-legacy defines the shape of data to be inserted into the database and can be used to validate API requests. It enforces required fields and their types based on the table definition.

createUpdateSchema validates update data, excludes generated columns

The createUpdateSchema function from drizzle-orm/typebox-legacy defines the shape of data to be updated in the database and can be used to validate API requests. Generated columns cannot be updated and will cause validation to fail. All non-generated fields become optional for updates.

24-bit float type mapping

PostgreSQL pg.real() and MySQL mysql.float() map to Typebox Type.Number().min(-8_388_608).max(8_388_607), corresponding to the 24-bit integer range.

MySQL mediumint signed maps to 24-bit range

MySQL mysql.mediumint() maps to Typebox Type.Integer({ minimum: -8_388_608, maximum: 8_388_607 }), corresponding to the 24-bit signed integer range.

Upgrade to Drizzle v1 - install RC versions

To upgrade to Drizzle v1, install drizzle-orm@rc and drizzle-kit@rc using npm.

Drizzle supports PostgreSQL, MySQL, and SQLite databases

Drizzle operates natively through industry-standard database drivers and supports all major PostgreSQL, MySQL, and SQLite drivers. New drivers are being added regularly.

SingleStore table creation with singlestoreTable

To define a table in Drizzle for SingleStore, use the singlestoreTable function from drizzle-orm/singlestore-core. The function takes a table name and an object defining columns. Example: singlestoreTable('users', { id: int(), email: varchar({ length: 256 }) }). You can use either direct imports of column types, a callback function with a table alias (t), or import everything as a namespace.

Automatic camelCase to snake_case mapping in SingleStore

Use the 'casing' option in the Drizzle database initialization to automatically map camelCase TypeScript names to snake_case database names. Pass casing: 'snake_case' in the drizzle() constructor to avoid manual alias definitions. Example: drizzle({ connection: process.env.DATABASE_URL, casing: 'snake_case' })

SingleStore Schema object for organizing tables

Create a SingleStore schema (equivalent to a database) using singlestoreSchema() from drizzle-orm/singlestore-core. Example: const customSchema = singlestoreSchema('custom'). Then define tables within the schema using customSchema.table('users', { ... }). Note: schemas defined this way are not detected by drizzle-kit or included in migrations.

SingleStore primary key with autoincrement

Define an auto-incrementing primary key column using: id: int().primaryKey().autoincrement()

SingleStore varchar column with length constraint

Define a varchar column with a maximum length using: varchar({ length: 256 }). The length parameter is required for varchar columns.

SingleStore unique constraint on column

Add a unique constraint to a column by chaining .unique() to the column definition. Example: email: varchar({ length: 256 }).notNull().unique()

SingleStore foreign key reference

Create a foreign key reference using .references() with a callback function that returns the referenced column. Example: invitee: int().references((): AnyMySqlColumn => users.id) creates a foreign key to users.id. Another example: ownerId: int('owner_id').references(() => users.id)

SingleStore enum column type

Define an enum column using singlestoreEnum() from drizzle-orm/singlestore-core. Example: role: singlestoreEnum(['guest', 'user', 'admin']).default('guest')

SingleStore uniqueIndex constraint

Define a unique index as a table constraint. In the second parameter function passed to table(), return an array with index definitions. Example: t.uniqueIndex('email_idx').on(table.email) creates a unique index on the email column.

SingleStore regular index constraint

Define a regular (non-unique) index as a table constraint. Example: t.index('title_idx').on(table.title) creates an index on the title column.

SingleStore column with default value function

Use .$default() to set a default value computed by a function when a row is inserted. Example: slug: varchar({ length: 256 }).$default(() => generateUniqueString(16)) generates a unique string as the default value.

SingleStore notNull constraint

Make a column required by chaining .notNull() to the column definition. Example: name: varchar({ length: 256 }).notNull()

Complete SingleStore schema example with multiple tables and constraints

import { singlestoreTable as table } from 'drizzle-orm/singlestore-core'; import * as t from 'drizzle-orm/singlestore-core'; import { AnyMySqlColumn } from 'drizzle-orm/singlestore-core'; export const users = table( 'users', { id: t.int().primaryKey().autoincrement(), firstName: t.varchar('first_name', { length: 256 }), lastName: t.varchar('last_name', { length: 256 }), email: t.varchar({ length: 256 }).notNull(), invitee: t.int().references((): AnyMySqlColumn => users.id), role: t.singlestoreEnum(['guest', 'user', 'admin']).default('guest'), }, (table) => [ t.uniqueIndex('email_idx').on(table.email) ] ); export const posts = table( 'posts', { id: t.int().primaryKey().autoincrement(), slug: t.varchar({ length: 256 }).$default(() => generateUniqueString(16)), title: t.varchar({ length: 256 }), ownerId: t.int('owner_id').references(() => users.id), }, (table) => [ t.uniqueIndex('slug_idx').on(table.slug), t.index('title_idx').on(table.title), ] ); export const comments = table('comments', { id: t.int().primaryKey().autoincrement(), text: t.varchar({ length: 256 }), postId: t.int('post_id').references(() => posts.id), ownerId: t.int('owner_id').references(() => users.id), });

PlanetScale Postgres connection string format

The connection string for PlanetScale Postgres uses the format: postgresql://{username}:{pa••••••d}@{host}:{port}/postgres?sslmode=verify-full

Node-postgres driver for PlanetScale Postgres

To connect to PlanetScale Postgres databases, install the node-postgres package (pg) and its TypeScript types (@types/pg). The connection uses the standard node-postgres driver.

PlanetScale Postgres connection URL format

PlanetScale Postgres databases connect using the node-postgres driver with a connection string in the format: postgresql://{username}:{pa••••••d}@{host}:{port}/postgres?sslmode=verify-full

Give your agent this brain