Drizzle ORM

Common errors

• ER_PARSE_ERROR: You have an error in your SQL syntax

  cause Untrusted input was passed to `sql.identifier()` or `sql.as()` without proper escaping in Drizzle ORM versions prior to 0.45.2 or 1.0.0-beta.20, leading to malformed SQL queries.
  fix Upgrade Drizzle ORM to at least `0.45.2` (or `1.0.0-beta.20`) to ensure automatic escaping. Always validate or sanitize user-provided identifiers if they are directly used in `sql.identifier()`.

• Error: DrizzleError: Object key collision detected. Make sure your schema definitions are unique.

  cause Multiple schema definitions (e.g., tables, columns, relations) with the same key were found within your Drizzle ORM schema, leading to a conflict during processing.
  fix Review your Drizzle ORM schema files and ensure that all table, column, and relation definitions have unique names to prevent key collisions.

• Error: D1_ERROR: ... syntax error near '...'

  cause Migration script generated by `drizzle-kit` contained incompatible SQL syntax or commands for Cloudflare D1, or there were environment setup issues specific to D1.
  fix Upgrade `drizzle-kit` to the latest version (>=0.31.10) as a fix for D1 migration failures was released. Also, ensure your D1 environment is correctly configured and consult Drizzle ORM's official documentation for D1-specific migration guidelines.

Warnings

• breaking Previously, `sql.identifier()` and `sql.as()` functions were not properly escaping values, leading to a potential SQL Injection vulnerability (CWE-89).
  Fix: Upgrade to `drizzle-orm@0.45.2` or `v1.0.0-beta.20` and later to ensure automatic escaping. Always validate or sanitize user-provided identifiers if they are directly used in these functions.
• breaking A significant regression in the migration infrastructure was present, potentially causing issues with migration tracking, validation, and application.
  Fix: Upgrade to `1.0.0-beta.16` or later to resolve these migration issues, which included a redesign of the underlying migration architecture.
• gotcha Adding a new value to a PostgreSQL enum will no longer be automatically treated as a commutative (non-breaking) change in migration generation by `drizzle-kit`.
  Fix: Review and manually adjust migration files if adding enum values, especially when merging branches that add different enum values, as they will now be properly merged instead of being treated as commutative.
• gotcha MSSQL `real()` column types might return imprecise `float64` values due to a missing `mapFromDriverValue` implementation.
  Fix: Upgrade to `1.0.0-beta.22` or later for correct `real()` column value mapping and precision in MSSQL.

Install

• npm install drizzle-orm npm
• yarn add drizzle-orm yarn
• pnpm add drizzle-orm pnpm

Imports

• drizzle
  wrong:

  const drizzle = require('drizzle-orm/postgres-js');

  correct:

  import { drizzle } from 'drizzle-orm/postgres-js';

  The main `drizzle` function is imported from a specific driver package (e.g., `drizzle-orm/postgres-js`, `drizzle-orm/better-sqlite3`).

Quickstart

Connects to an in-memory SQLite database using `better-sqlite3`, defines a simple schema, inserts data, and queries it, demonstrating basic Drizzle ORM usage.

import { drizzle } from 'drizzle-orm/better-sqlite3';
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import Database from 'better-sqlite3';

// Initialize an in-memory SQLite database
const sqlite = new Database(':memory:');
const db = drizzle(sqlite);

// Define a simple schema for users
export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

async function main() {
  // For quickstart, manually create the table. In production, use Drizzle Kit for migrations.
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL
    );
  `);

  // Insert data
  await db.insert(users).values({ id: 1, name: 'Alice' }).execute();
  await db.insert(users).values({ id: 2, name: 'Bob' }).execute();

  // Query data
  const allUsers = db.select().from(users).all();
  console.log('Users:', allUsers); 
  // Expected output: Users: [{ id: 1, name: 'Alice' }, { id: 2, name: 'Bob' }]

  sqlite.close();
}

main();