Bug fixed

• drizzle-kit generate fails to generate correct migrations.js file on Windows

• [BUG]: MSSQL real() column returns imprecise float64 values -- missing mapFromDriverValue

• Object key collision

• [BUG]:beta20 Migration to Cloudflare D1 fails

• [BUG]: drizzle-kit migrate: migration SQL failures exit with code 1 but print no error (MigrateProgress hides rejection)

• [BUG]: MS SQL Filtered Index - Drizzle Kit incorrectly generates fully qualified column name in WHERE clause

• [BUG]: [Drizzle-kit][Pg] Altering a UNIQUE constraint: generated SQL ignores the new changes

• [BUG]: Non-commutative migration false positive: create_index commutativity check ignores table name

• Commutativity checks were updated for PostgreSQL and MySQL. Before, create_index statements could be reported as non-commutative even when they targeted different tables, and --ignore-conflicts could still collapse multi-parent history down to a single parent. Now index/table/schema footprints are resolved more accurately, unrelated index branches are accepted, and --ignore-conflicts preserves all open leaf parents for the next migration

• fix: mark mssql and @types/mssql as optional peer dependencies

More fixes for the drizzle-kit migration process and commutativity checks

• Adding a value to a PostgreSQL enum will no longer be treated as commutative
• Enum values added in different leaves will now be merged properly

• Fixed sql.identifier(), sql.as() escaping issues. Previously all the values passed to this functions were not properly escaped causing a possible SQL Injection (CWE-89) vulnerability

Thanks to @EthanKim88, @0x90sh and @wgoodall01 for reaching out to us with a reproduction and suggested fix

New Features

sqlcommenter support for PostgreSQL and MySQL

You can now add custom tags to the query. These tags will be appended to the end of each query, helping the database add metadata/tags to it. This will be especially useful with PlanetScale’s new Database Traffic Control feature

// raw string support
db.select().from().comment("key='val'");
db.select().from().comment("my_first_tag");

// developer friendly dedicated to tags
db.select().from().comment({ key: 'val' });

Example:

db.select().from(comments).comment({ priority: 'high', category: "analytics" });

select "id", "name" from "comments" /*priority='high',category='analytics'*/

The only limitation is that you can't use comments with a prepared statement:

// can't be used
const p = db.select().from().prepare();
// ❌
p.comment({ key: 'val' }).execute();

Bug fixes

• Fixed error message for the defineRelations function
• [BUG]: drizzle-kit push attempts to drop policies in excluded schemas (e.g. cron) despite schemaFilter: ["public"]
• [BUG]: error attempting to drizzle-kit migrate table with char array field generated using drizzle-kit generate
• [BUG]: Ignore Vim *.swp files in drizzle-kit generate
• [BUG]: drizzle-kit pull outputs access method name instead of operator class for ivfflat indexes
• [BUG]: drizzle-kit pull generates not enough data provided to build the relation
• drizzle-kit push fails with Turso/libSQL on table recreation: "cannot commit - no transaction is active"
• [BUG]: Cannot read properties of undefined (reading 'requestLayout') when running drizzle-kit introspect (MySQL)
• [BUG (beta)]: RDS Data API pulling schema error
• [BUG]: drizzle-kit commutative migrations takes only last leaf migration into account

Updates

• Updated migrate() function for mysql dialect to correctly manage multiple databases

• The behavior for reading schema files has been updated. From now only files with the following extensions will be processed: .js .mjs .cjs .jsx .ts .mts .cts .tsx. All other file types will be ignored

New Netlify DB Driver

Note: The Netlify DB driver is developed and maintained by the Netlify team.

Installation:

npm i @netlify/db

Usage example:

import { drizzle } from 'drizzle-orm/netlify-db';

// reads NETLIFY_DB_URL and NETLIFY_DB_DRIVER env vars
const db = drizzle();

const result = await db.execute('select 1');

import { drizzle } from 'drizzle-orm/netlify-db';

const db = drizzle(process.env.DATABASE_URL);

const result = await db.execute('select 1');

import { drizzle } from 'drizzle-orm/netlify-db';

// Explicit client — consumer controls the driver
const db = drizzle({ client: netlifyDbClient });

const result = await db.execute('select 1');

• Fixed sql.identifier(), sql.as() escaping issues. Previously all the values passed to this functions were not properly escaped
  causing a possible SQL Injection (CWE-89) vulnerability

Thanks to @EthanKim88, @0x90sh and @wgoodall01 for reaching out to us with a reproduction and suggested fix

• Fixed pg-native Pool detection in node-postgres transactions breaking in environments with forbidden require() (#5107)

• Fixed pg-native Pool detection in node-postgres transactions
• Allowed subqueries in select fields
• Updated typo algorythm => algorithm
• Fixed $onUpdate not handling SQL values (fixes #2388, tests implemented by L-Mario564 in #2911)
• Fixed pg mappers not handling Date instances in bun-sql:postgresql driver responses for date, timestamp types (fixes #4493)

• fix durable sqlite transaction return value #3746 - thanks @joaocstro

• feat: add $replicas reference #4874

• Fixed invalid usage of .one() in durable-sqlite session
• Fixed spread operator related crash in sqlite blob columns
• Better browser support for sqlite blob columns
• Improved sqlite blob mapping

• Fix wrong DrizzleQueryError export. thanks @nathankleyn

• Fixed types of $client for clients created by drizzle function

await db.$client.[...]

• Added the updated_at column to the neon_auth.users_sync table definition.

• [BUG]: Fixed type issues with joins with certain variations of tsconfig: #4535, #4457

• [BUG]: Drizzle can no longer run on Durable Objects

Error handling

Starting from this version, we’ve introduced a new DrizzleQueryError that wraps all errors from database drivers and provides a set of useful information:

1. A proper stack trace to identify which exact Drizzle query failed
2. The generated SQL string and its parameters
3. The original stack trace from the driver that caused the DrizzleQueryError

Drizzle cache module

Drizzle sends every query straight to your database by default. There are no hidden actions, no automatic caching or invalidation - you’ll always see exactly what runs. If you want caching, you must opt in.

By default, Drizzle uses a explicit caching strategy (i.e. global: false), so nothing is ever cached unless you ask. This prevents surprises or hidden performance traps in your application. Alternatively, you can flip on all caching (global: true) so that every select will look in cache first.

Out first native integration was built together with Upstash team and let you natively use upstash as a cache for your drizzle queries

import { upstashCache } from "drizzle-orm/cache/upstash";
import { drizzle } from "drizzle-orm/...";

const db = drizzle(process.env.DB_URL!, {
  cache: upstashCache({
    // 👇 Redis credentials (optional — can also be pulled from env vars)
    url: '<UPSTASH_URL>',
    token: '<UPSTASH_TOKEN>',
    // 👇 Enable caching for all queries by default (optional)
    global: true,
    // 👇 Default cache behavior (optional)
    config: { ex: 60 }
  })
});

You can also implement your own cache, as Drizzle exposes all the necessary APIs, such as get, put, mutate, etc. You can find full implementation details on the website

import Keyv from "keyv";
export class TestGlobalCache extends Cache {
  private globalTtl: number = 1000;
  // This object will be used to store which query keys were used
  // for a specific table, so we can later use it for invalidation.
  private usedTablesPerKey: Record<string, string[]> = {};
  constructor(private kv: Keyv = new Keyv()) {
    super();
  }
  // For the strategy, we have two options:
  // - 'explicit': The cache is used only when .$withCache() is added to a query.
  // - 'all': All queries are cached globally.
  // The default behavior is 'explicit'.
  override strategy(): "explicit" | "all" {
    return "all";
  }
  // This function accepts query and parameters that cached into key param,
  // allowing you to retrieve response values for this query from the cache.
  override async get(key: string): Promise<any[] | undefined> {
    ...
  }
  // This function accepts several options to define how cached data will be stored:
  // - 'key': A hashed query and parameters.
  // - 'response': An array of values returned by Drizzle from the database.
  // - 'tables': An array of tables involved in the select queries. This information is needed for cache invalidation.
  //
  // For example, if a query uses the "users" and "posts" tables, you can store this information. Later, when the app executes
  // any mutation statements on these tables, you can remove the corresponding key from the cache.
  // If you're okay with eventual consistency for your queries, you can skip this option.
  override async put(
    key: string,
    response: any,
    tables: string[],
    config?: CacheConfig,
  ): Promise<void> {
    ...
  }
  // This function is called when insert, update, or delete statements are executed.
  // You can either skip this step or invalidate queries that used the affected tables.
  //
  // The function receives an object with two keys:
  // - 'tags': Used for queries labeled with a specific tag, allowing you to invalidate by that tag.
  // - 'tables': The actual tables affected by the insert, update, or delete statements,
  //   helping you track which tables have changed since the last cache update.
  override async onMutate(params: {
    tags: string | string[];
    tables: string | string[] | Table<any> | Table<any>[];
  }): Promise<void> {
    ...
  }
}

For more usage example you can check our docs

Fixes

• Fixed incorrect types of schema enums in PostgreSQL

Features

• Added cross join (#1414)
• Added lateral left, inner, cross joins to PostgreSQL, MySQL, Gel, SingleStore
• Added drizzle connection attributes to SingleStore's driver instances

Fixes

• Removed unsupported by dialect full join from MySQL select api
• Forced Gel columns to always have explicit schema & table prefixes due to potential errors caused by lack of such prefix in subquery's selection when there's already a column bearing same name in context
• Added missing export for PgTextBuilderInitial type
• Removed outdated IfNotImported type check from SingleStore driver initializer
• Fixed incorrect type inferrence for insert and update models with non-strict tsconfigs (#2654)
• Fixed invalid spelling of nowait flag (#3554)
• Add join lateral support
• Remove .fullJoin() from MySQL API

Features

Duplicate imports removal

When importing from drizzle-orm using custom loaders, you may encounter issues such as: SyntaxError: The requested module 'drizzle-orm' does not provide an export named 'eq'

This issue arose because there were duplicated exports in drizzle-orm. To address this, we added a set of tests that checks every file in drizzle-orm to ensure all exports are valid. These tests will fail if any new duplicated exports appear.

In this release, we’ve removed all duplicated exports, so you should no longer encounter this issue.

pgEnum and mysqlEnum now can accept both strings and TS enums

If you provide a TypeScript enum, all your types will be inferred as that enum - so you can insert and retrieve enum values directly. If you provide a string union, it will work as before.

enum Test {
  a = 'a',
  b = 'b',
  c = 'c',
}

const tableWithTsEnums = mysqlTable('enums_test_case', {
  id: serial().primaryKey(),
  enum1: mysqlEnum(Test).notNull(),
  enum2: mysqlEnum(Test).default(Test.a),
});

await db.insert(tableWithTsEnums).values([
  { id: 1, enum1: Test.a, enum2: Test.b, enum3: Test.c },
  { id: 2, enum1: Test.a, enum3: Test.c },
  { id: 3, enum1: Test.a },
]);

const res = await db.select().from(tableWithTsEnums);

expect(res).toEqual([
  { id: 1, enum1: 'a', enum2: 'b', enum3: 'c' },
  { id: 2, enum1: 'a', enum2: 'a', enum3: 'c' },
  { id: 3, enum1: 'a', enum2: 'a', enum3: 'b' },
]);

Improvements

• Make inArray accept ReadonlyArray as a value - thanks @Zamiell
• Pass row type parameter to @planetscale/database's execute - thanks @ayrton
• New InferEnum type - thanks @totigm

Issues closed

• Add first-class support for TS native enums
• [FEATURE]: support const enums
• [BUG]: SyntaxError: The requested module 'drizzle-orm' does not provide an export named 'lte'

• bigint, number modes for SQLite, MySQL, PostgreSQL, SingleStore decimal & numeric column types
• Changed behavior of sql-js query preparation to query prebuild instead of db-side prepare due to need to manually free prepared queries, removed .free() method
• Fixed MySQL, SingleStore varchar allowing not specifying length in config
• Fixed MySQL, SingleStore binary, varbinary data\type mismatches
• Fixed numeric\decimal data\type mismatches: #1290, #1453
• Fixed drizzle-studio + AWS Data Api connection issue: #3224
• Fixed isConfig utility function checking types of wrong fields
• Enabled supportBigNumbers in auto-created mysql2 driver instances
• Fixed custom schema tables querying in RQBv1: #4060
• Removed in-driver mapping for postgres types 1231 (numeric[]), 1115 (timestamp[]), 1185 (timestamp_with_timezone[]), 1187 (interval[]), 1182 (date[]), preventing precision loss and data\type mismatches
• Fixed SQLite buffer-mode blob sometimes returning number[]

Updates to neon-http for @neondatabase/serverless@1.0.0 - thanks @jawj

Starting from this version, drizzle-orm will be compatible with both @neondatabase/serverless <1.0 and >1.0

New Features

Added Gel dialect support and gel-js client support

Drizzle is getting a new Gel dialect with its own types and Gel-specific logic. In this first iteration, almost all query-building features have been copied from the PostgreSQL dialect since Gel is fully PostgreSQL-compatible. The only change in this iteration is the data types. The Gel dialect has a different set of available data types, and all mappings for these types have been designed to avoid any extra conversions on Drizzle's side. This means you will insert and select exactly the same data as supported by the Gel protocol.

Drizzle + Gel integration will work only through drizzle-kit pull. Drizzle won't support generate, migrate, or push features in this case. Instead, drizzle-kit is used solely to pull the Drizzle schema from the Gel database, which can then be used in your drizzle-orm queries.

The Gel + Drizzle workflow:

1. Use the gel CLI to manage your schema.
2. Use the gel CLI to generate and apply migrations to the database.
3. Use drizzle-kit to pull the Gel database schema into a Drizzle schema.
4. Use drizzle-orm with gel-js to query the Gel database.

Here is a small example of how to connect to Gel using Drizzle:

// Make sure to install the 'gel' package 
import { drizzle } from "drizzle-orm/gel";
import { createClient } from "gel";

const gelClient = createClient();
const db = drizzle({ client: gelClient });

const result = await db.execute('select 1');

and drizzle-gel schema definition

import { gelTable, uniqueIndex, uuid, smallint, text } from "drizzle-orm/gel-core"
import { sql } from "drizzle-orm"

export const users = gelTable("users", {
    id: uuid().default(sql`uuid_generate_v4()`).primaryKey(),
    age: smallint(),
    email: text().notNull(),
    name: text(),
});

On the drizzle-kit side you can now use dialect: "gel"

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'gel',
});

For a complete Get Started tutorial you can use our new guides:

• Get Started with Drizzle and Gel in a new project
• Get Started with Drizzle and Gel in a existing project

• Remove react from peerDependencies

• To be compatible with latest Neon Auth feature we renamed the pre-defined schema internally, from neon_identity to neon_auth - thanks @pffigueiredo

• Fixed SQLite onConflict clauses being overwritten instead of stacked - #2276
• Added view support to aliasedTable()
• Fixed sql builder prefixing aliased views and tables with their schema

New features

Bun SQL driver support

You can now use the new Bun SQL driver released in Bun v1.2.0 with Drizzle

In version 1.2.0, Bun has issues with executing concurrent statements, which may lead to errors if you try to run several queries simultaneously. We've created a github issue that you can track. Once it's fixed, you should no longer encounter any such errors on Bun's SQL side

import { drizzle } from 'drizzle-orm/bun-sql';

const db = drizzle(process.env.PG_DB_URL!);

const result = await db.select().from(...);

or you can use Bun SQL instance

import { drizzle } from 'drizzle-orm/bun-sql';
import { SQL } from 'bun';

const client = new SQL(process.env.PG_DB_URL!);
const db = drizzle({ client });

const result = await db.select().from(...);

Current Limitations:

• json and jsonb inserts and selects currently perform an additional JSON.stringify on the Bun SQL side. Once this is removed, they should work properly. You can always use custom types and redefine the mappers to and from the database.
• datetime, date, and timestamp will not work properly when using mode: string in Drizzle. This is due to Bun's API limitations, which prevent custom parsers for queries. As a result, Drizzle cannot control the response sent from Bun SQL to Drizzle. Once this feature is added to Bun SQL, it should work as expected.
• array types currently have issues in Bun SQL.

You can check more in Bun docs

You can check more getting started examples in Drizzle docs

WITH now supports INSERT, UPDATE, DELETE and raw sql template

with and insert

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

const sq = db.$with('sq').as(
    db.insert(users).values({ name: 'John' }).returning(),
);

const result = await db.with(sq).select().from(sq);

with and update

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

const sq = db.$with('sq').as(
    db.update(users).set({ age: 25 }).where(eq(users.name, 'John')).returning(),
);
const result = await db.with(sq).select().from(sq);

with and delete

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

const sq = db.$with('sq').as(
  db.delete(users).where(eq(users.name, 'John')).returning(),
);

const result = await db.with(sq).select().from(sq);

with and sql

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

const sq = db.$with('sq', {
  userId: users.id,
  data: {
    name: users.name,
  },
}).as(sql`select * from ${users} where ${users.name} = 'John'`);

const result = await db.with(sq).select().from(sq);

New tables in /neon import

In this release you can use neon_identity schema and users_sync table inside this schema by just importing it from /neon

// "drizzle-orm/neon"
const neonIdentitySchema = pgSchema('neon_identity');

/**
 * Table schema of the `users_sync` table used by Neon Identity.
 * This table automatically synchronizes and stores user data from external authentication providers.
 *
 * @schema neon_identity
 * @table users_sync
 */
export const usersSync = neonIdentitySchema.table('users_sync', {
  rawJson: jsonb('raw_json').notNull(),
  id: text().primaryKey().notNull(),
  name: text(),
  email: text(),
  createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' }),
  deletedAt: timestamp('deleted_at', { withTimezone: true, mode: 'string' }),
});

Utils and small improvements

getViewName util function

import { getViewName } from 'drizzle-orm/sql'

export const user = pgTable("user", {
  id: serial(),
  name: text(),
  email: text(),
});

export const userView = pgView("user_view").as((qb) => qb.select().from(user));

const viewName = getViewName(userView)

Bug fixed and GitHub issue closed

• [FEATURE]: allow INSERT in CTEs (WITH clauses)
• [FEATURE]: Support Raw SQL in CTE Query Builder
• [FEATURE]: include pre-defined database objects related to Neon Identity in drizzle-orm
• [BUG]: $count is undefined on withReplicas
• [FEATURE]: get[Materialized]ViewName, ie getTableName but for (materialized) views.
• [BUG]: $count API error with vercel-postgres
• [BUG]: Cannot use schema.coerce on refining drizzle-zod types
• [FEATURE]: Type Coercion in drizzle-zod
• [BUG]: The inferred type of X cannot be named without a reference to ../../../../../node_modules/drizzle-zod/schema.types.internal.mjs
• [BUG]: drizzle-zod excessively deep and possibly infinite types

• New SingleStore type vector - thanks @mitchwadair
• Fix wrong DROP INDEX statement generation, #3866 - thanks @WaciX
• Typo fixes - thanks @stephan281094

• Fix incorrect deprecation detection for table declarations

New features

USE INDEX, FORCE INDEX and IGNORE INDEX for MySQL

In MySQL, the statements USE INDEX, FORCE INDEX, and IGNORE INDEX are hints used in SQL queries to influence how the query optimizer selects indexes. These hints provide fine-grained control over index usage, helping optimize performance when the default behavior of the optimizer is not ideal.

Use Index

The USE INDEX hint suggests to the optimizer which indexes to consider when processing the query. The optimizer is not forced to use these indexes but will prioritize them if they are suitable.

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
}, () => [usersTableNameIndex]);

const usersTableNameIndex = index('users_name_index').on(users.name);

await db.select()
  .from(users, { useIndex: usersTableNameIndex })
  .where(eq(users.name, 'David'));

Ignore Index

The IGNORE INDEX hint tells the optimizer to avoid using specific indexes for the query. MySQL will consider all other indexes (if any) or perform a full table scan if necessary.

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
}, () => [usersTableNameIndex]);

const usersTableNameIndex = index('users_name_index').on(users.name);

await db.select()
  .from(users, { ignoreIndex: usersTableNameIndex })
  .where(eq(users.name, 'David'));

Force Index

The FORCE INDEX hint forces the optimizer to use the specified index(es) for the query. If the specified index cannot be used, MySQL will not fall back to other indexes; it might resort to a full table scan instead.

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
}, () => [usersTableNameIndex]);

const usersTableNameIndex = index('users_name_index').on(users.name);

await db.select()
  .from(users, { forceIndex: usersTableNameIndex })
  .where(eq(users.name, 'David'));

You can also combine those hints and use multiple indexes in a query if you need

• Closed [FEATURE]: Add more flexible typing for usage with exactOptionalPropertyTypes

Types breaking changes

A few internal types were changed and extra generic types for length of column types were added in this release. It won't affect anyone, unless you are using those internal types for some custom wrappers, logic, etc. Here is a list of all types that were changed, so if you are relying on those, please review them before upgrading

• MySqlCharBuilderInitial
• MySqlVarCharBuilderInitial
• PgCharBuilderInitial
• PgArrayBuilder
• PgArray
• PgVarcharBuilderInitial
• PgBinaryVectorBuilderInitial
• PgBinaryVectorBuilder
• PgBinaryVector
• PgHalfVectorBuilderInitial
• PgHalfVectorBuilder
• PgHalfVector
• PgVectorBuilderInitial
• PgVectorBuilder
• PgVector
• SQLiteTextBuilderInitial

New Features

• Added new function getViewSelectedFields
• Added $inferSelect function to views
• Added InferSelectViewModel type for views
• Added isView function

Validator packages updates

• drizzle-zod has been completely rewritten. You can find detailed information about it here
• drizzle-valibot has been completely rewritten. You can find detailed information about it here
• drizzle-typebox has been completely rewritten. You can find detailed information about it here

Thanks to @L-Mario564 for making more updates than we expected to be shipped in this release. We'll copy his message from a PR regarding improvements made in this release:

• Output for all packages are now unminified, makes exploring the compiled code easier when published to npm.
• Smaller footprint. Previously, we imported the column types at runtime for each dialect, meaning that for example, if you're just using Postgres then you'd likely only have drizzle-orm and drizzle-orm/pg-core in the build output of your app; however, these packages imported all dialects which could lead to mysql-core and sqlite-core being bundled as well even if they're unused in your app. This is now fixed.
• Slight performance gain. To determine the column data type we used the is function which performs a few checks to ensure the column data type matches. This was slow, as these checks would pile up every quickly when comparing all data types for many fields in a table/view. The easier and faster alternative is to simply go off of the column's columnType property.
• Some changes had to be made at the type level in the ORM package for better compatibility with drizzle-valibot.

And a set of new features

• createSelectSchema function now also accepts views and enums.
• New function: createUpdateSchema, for use in updating queries.
• New function: createSchemaFactory, to provide more advanced options and to avoid bloating the parameters of the other schema functions

Bug fixes

• [FEATURE]: publish packages un-minified
• Don't allow unknown keys in drizzle-zod refinement
• [BUG]:drizzle-zod not working with pgSchema
• Add createUpdateSchema to drizzle-zod
• [BUG]:drizzle-zod produces wrong type
• [BUG]:Drizzle-zod:Boolean and Serial types from Schema are defined as enum<unknown> when using CreateInsertSchema and CreateSelectSchema
• [BUG]: Drizzle typebox enum array wrong schema and type
• [BUG]:drizzle-zod not working with pgSchema
• [BUG]: drizzle-zod not parsing arrays correctly
• [BUG]: Drizzle typebox not supporting array
• [FEATURE]: Export factory functions from drizzle-zod to allow usage with extended Zod classes
• [FEATURE]: Add support for new pipe syntax for drizzle-valibot
• [BUG]: drizzle-zod's createInsertSchema() can't handle column of type vector
• [BUG]: drizzle-typebox fails to map geometry column to type-box schema
• [BUG]: drizzle-valibot does not provide types for returned schemas
• [BUG]: Drizzle-typebox types SQLite real field to string
• [BUG]: drizzle-zod: documented usage generates type error with exactOptionalPropertyTypes
• [BUG]: drizzle-zod does not respect/count db type range
• [BUG]: drizzle-zod not overriding optional
• [BUG]:drizzle-zod doesn't accept custom id value
• [FEATURE]: Support for Database Views in Drizzle Zod
• [BUG]: drizzle-valibot return type any
• [BUG]: drizzle-zod Type generation results in undefined types
• [BUG]: GeneratedAlwaysAs
• [FEATURE]: $inferSelect on a view
• [BUG]:Can't infer props from view in schema

New Dialects

🎉 SingleStore dialect is now available in Drizzle

Thanks to the SingleStore team for creating a PR with all the necessary changes to support the MySQL-compatible part of SingleStore. You can already start using it with Drizzle. The SingleStore team will also help us iterate through updates and make more SingleStore-specific features available in Drizzle

import { int, singlestoreTable, varchar } from 'drizzle-orm/singlestore-core';
import { drizzle } from 'drizzle-orm/singlestore';

export const usersTable = singlestoreTable('users_table', {
  id: int().primaryKey(),
  name: varchar({ length: 255 }).notNull(),
  age: int().notNull(),
  email: varchar({ length: 255 }).notNull().unique(),
});

...

const db = drizzle(process.env.DATABASE_URL!);

db.select()...

You can check out our Getting started guides to try SingleStore!

New Drivers

🎉 SQLite Durable Objects driver is now available in Drizzle

You can now query SQLite Durable Objects in Drizzle!

For the full example, please check our Get Started Section

/// <reference types="@cloudflare/workers-types" />
import { drizzle, DrizzleSqliteDODatabase } from 'drizzle-orm/durable-sqlite';
import { DurableObject } from 'cloudflare:workers'
import { migrate } from 'drizzle-orm/durable-sqlite/migrator';
import migrations from '../drizzle/migrations';
import { usersTable } from './db/schema';

export class MyDurableObject1 extends DurableObject {
  storage: DurableObjectStorage;
  db: DrizzleSqliteDODatabase<any>;

  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    this.storage = ctx.storage;
    this.db = drizzle(this.storage, { logger: false });
  }

    async migrate() {
        migrate(this.db, migrations);
    }

  async insert(user: typeof usersTable.$inferInsert) {
        await this.db.insert(usersTable).values(user);
    }

  async select() {
        return this.db.select().from(usersTable);
    }
}

export default {
  /**
   * This is the standard fetch handler for a Cloudflare Worker
   *
   * @param request - The request submitted to the Worker from the client
   * @param env - The interface to reference bindings declared in wrangler.toml
   * @param ctx - The execution context of the Worker
   * @returns The response to be sent back to the client
   */
  async fetch(request: Request, env: Env): Promise<Response> {
    const id: DurableObjectId = env.MY_DURABLE_OBJECT1.idFromName('durable-object');
    const stub = env.MY_DURABLE_OBJECT1.get(id);
    await stub.migrate();

    await stub.insert({
      name: 'John',
      age: 30,
      email: 'john@example.com',
      })
    console.log('New user created!')
  
    const users = await stub.select();
    console.log('Getting all users from the database: ', users)

        return new Response();
    }
}

Bug fixes

• [BUG]: $with is undefined on withReplicas
• [BUG]: Neon serverless driver accepts authToken as a promise, but the $withAuth does not

New Package: drizzle-seed

[!NOTE] drizzle-seed can only be used with drizzle-orm@0.36.4 or higher. Versions lower than this may work at runtime but could have type issues and identity column issues, as this patch was introduced in drizzle-orm@0.36.4

Full Reference

The full API reference and package overview can be found in our official documentation

Basic Usage

In this example we will create 10 users with random names and ids

import { pgTable, integer, text } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";
import { seed } from "drizzle-seed";

const users = pgTable("users", {
  id: integer().primaryKey(),
  name: text().notNull(),
});

async function main() {
  const db = drizzle(process.env.DATABASE_URL!);
  await seed(db, { users });
}

main();

Options

count

By default, the seed function will create 10 entities. However, if you need more for your tests, you can specify this in the seed options object

await seed(db, schema, { count: 1000 });

seed

If you need a seed to generate a different set of values for all subsequent runs, you can define a different number in the seed option. Any new number will generate a unique set of values

await seed(db, schema, { seed: 12345 });

The full API reference and package overview can be found in our official documentation

Features

Added OVERRIDING SYSTEM VALUE api to db.insert()

If you want to force you own values for GENERATED ALWAYS AS IDENTITY columns, you can use OVERRIDING SYSTEM VALUE

As PostgreSQL docs mentions

In an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified value takes precedence

await db.insert(identityColumnsTable).overridingSystemValue().values([
  { alwaysAsIdentity: 2 },
]);

Added .$withAuth() API for Neon HTTP driver

Using this API, Drizzle will send you an auth token to authorize your query. It can be used with any query available in Drizzle by simply adding .$withAuth() before it. This token will be used for a specific query

Examples

const token = 'HdncFj1Nm'

await db.$withAuth(token).select().from(usersTable);
await db.$withAuth(token).update(usersTable).set({ name: 'CHANGED' }).where(eq(usersTable.name, 'TARGET'))

Bug Fixes

• [BUG]: TypeScript error Please install '@neondatabase/serverless' to allow Drizzle ORM to connect to the database

New Features

Support for UPDATE ... FROM in PostgreSQL and SQLite

As the SQLite documentation mentions:

[!NOTE] The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows

Similarly, the PostgreSQL documentation states:

[!NOTE] A table expression allowing columns from other tables to appear in the WHERE condition and update expressions

Drizzle also supports this feature starting from this version

For example, current query:

await db
  .update(users)
  .set({ cityId: cities.id })
  .from(cities)
  .where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')))

Will generate this sql

update "users" set "city_id" = "cities"."id" 
from "cities" 
where ("cities"."name" = $1 and "users"."name" = $2)

-- params: [ 'Seattle', 'John' ]

You can also alias tables that are joined (in PG, you can also alias the updating table too).

const c = alias(cities, 'c');
await db
  .update(users)
  .set({ cityId: c.id })
  .from(c);

Will generate this sql

update "users" set "city_id" = "c"."id" 
from "cities" "c"

In PostgreSQL, you can also return columns from the joined tables.

const updatedUsers = await db
  .update(users)
  .set({ cityId: cities.id })
  .from(cities)
  .returning({ id: users.id, cityName: cities.name });

Will generate this sql

update "users" set "city_id" = "cities"."id" 
from "cities" 
returning "users"."id", "cities"."name"

Support for INSERT INTO ... SELECT in all dialects

As the SQLite documentation mentions:

[!NOTE] The second form of the INSERT statement contains a SELECT statement instead of a VALUES clause. A new entry is inserted into the table for each row of data returned by executing the SELECT statement. If a column-list is specified, the number of columns in the result of the SELECT must be the same as the number of items in the column-list. Otherwise, if no column-list is specified, the number of columns in the result of the SELECT must be the same as the number of columns in the table. Any SELECT statement, including compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may be used in an INSERT statement of this form.

[!CAUTION] To avoid a parsing ambiguity, the SELECT statement should always contain a WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause is present. Without the WHERE clause, the parser does not know if the token "ON" is part of a join constraint on the SELECT, or the beginning of the upsert-clause.

As the PostgreSQL documentation mentions:

[!NOTE] A query (SELECT statement) that supplies the rows to be inserted

And as the MySQL documentation mentions:

[!NOTE] With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables

Drizzle supports the current syntax for all dialects, and all of them share the same syntax. Let's review some common scenarios and API usage. There are several ways to use select inside insert statements, allowing you to choose your preferred approach:

• You can pass a query builder inside the select function.
• You can use a query builder inside a callback.
• You can pass an SQL template tag with any custom select query you want to use

Query Builder

const insertedEmployees = await db
  .insert(employees)
  .select(
    db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
  )
  .returning({
    id: employees.id,
    name: employees.name
  });

const qb = new QueryBuilder();
await db.insert(employees).select(
    qb.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);

Callback

await db.insert(employees).select(
    () => db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);

await db.insert(employees).select(
    (qb) => qb.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);

SQL template tag

await db.insert(employees).select(
    sql`select "users"."name" as "name" from "users" where "users"."role" = 'employee'`
);

await db.insert(employees).select(
    () => sql`select "users"."name" as "name" from "users" where "users"."role" = 'employee'`
);

New Features

• Support more types in like, notLike, ilike and notIlike expressions

Bug and typo fixes

• Fixed typos in repository: thanks @armandsalle, @masto, @wackbyte, @Asher-JH, @MaxLeiter

• Fixed .generated behavior with non-strict tsconfig

• Fix Drizzle ORM for expo-sqlite

• Fixed lack of schema name on columns in sql

• fix: Adjust neon http driver entity kind

• Export PgIntegerBuilderInitial type

• [MySQL] Correct $returningId() implementation to correctly store selected fields

Bug Fixes

• [BUG]: Using sql.placeholder with limit and/or offset for a prepared statement produces TS error - thanks @L-Mario564
• [BUG] If a query I am trying to modify with a dynamic query (....$dynamic()) contains any placeholders, I'm getting an error that says No value for placeholder.... provided - thanks @L-Mario564
• [BUG]: Error thrown when trying to insert an array of new rows using generatedAlwaysAsIdentity() for the id column - thanks @L-Mario564
• [BUG]: Unable to Use BigInt Types with Bun and Drizzle - thanks @L-Mario564

This version of drizzle-orm requires drizzle-kit@0.27.0 to enable all new features

New Features

The third parameter in Drizzle ORM becomes an array

The object API is still available but deprecated

Instead of this

pgTable('users', {
    id: integer().primaryKey(),
}, (t) => ({
    index: index('test').on(t.id),
}));

You can now do this

pgTable('users', {
    id: integer().primaryKey(),
}, (t) => [index('test').on(t.id)]);

Row-Level Security (RLS)

With Drizzle, you can enable Row-Level Security (RLS) for any Postgres table, create policies with various options, and define and manage the roles those policies apply to.

Drizzle supports a raw representation of Postgres policies and roles that can be used in any way you want. This works with popular Postgres database providers such as Neon and Supabase.

In Drizzle, we have specific predefined RLS roles and functions for RLS with both database providers, but you can also define your own logic.

Enable RLS

If you just want to enable RLS on a table without adding policies, you can use .enableRLS()

As mentioned in the PostgreSQL documentation:

If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified. Operations that apply to the whole table, such as TRUNCATE and REFERENCES, are not subject to row security.

import { integer, pgTable } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
	id: integer(),
}).enableRLS();

If you add a policy to a table, RLS will be enabled automatically. So, there’s no need to explicitly enable RLS when adding policies to a table.

Roles

Currently, Drizzle supports defining roles with a few different options, as shown below. Support for more options will be added in a future release.

import { pgRole } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin', { createRole: true, createDb: true, inherit: true });

If a role already exists in your database, and you don’t want drizzle-kit to ‘see’ it or include it in migrations, you can mark the role as existing.

import { pgRole } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin').existing();

Policies

To fully leverage RLS, you can define policies within a Drizzle table.

In PostgreSQL, policies should be linked to an existing table. Since policies are always associated with a specific table, we decided that policy definitions should be defined as a parameter of pgTable

Example of pgPolicy with all available properties

import { sql } from 'drizzle-orm';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin');

export const users = pgTable('users', {
	id: integer(),
}, (t) => [
	pgPolicy('policy', {
		as: 'permissive',
		to: admin,
		for: 'delete',
		using: sql``,
		withCheck: sql``,
	}),
]);

Link Policy to an existing table

There are situations where you need to link a policy to an existing table in your database. The most common use case is with database providers like Neon or Supabase, where you need to add a policy to their existing tables. In this case, you can use the .link() API

import { sql } from "drizzle-orm";
import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, realtimeMessages } from "drizzle-orm/supabase";

export const policy = pgPolicy("authenticated role insert policy", {
  for: "insert",
  to: authenticatedRole,
  using: sql``,
}).link(realtimeMessages);

Migrations

If you are using drizzle-kit to manage your schema and roles, there may be situations where you want to refer to roles that are not defined in your Drizzle schema. In such cases, you may want drizzle-kit to skip managing these roles without having to define each role in your drizzle schema and marking it with .existing().

In these cases, you can use entities.roles in drizzle.config.ts. For a complete reference, refer to the the drizzle.config.ts documentation.

By default, drizzle-kit does not manage roles for you, so you will need to enable this feature in drizzle.config.ts.

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: 'postgresql',
  schema: "./drizzle/schema.ts",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  },
  verbose: true,
  strict: true,
  entities: {
    roles: true
  }
});

In case you need additional configuration options, let's take a look at a few more examples.

You have an admin role and want to exclude it from the list of manageable roles

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  ...
  entities: {
    roles: {
      exclude: ['admin']
    }
  }
});

You have an admin role and want to include it in the list of manageable roles

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  ...
  entities: {
    roles: {
      include: ['admin']
    }
  }
});

If you are using Neon and want to exclude Neon-defined roles, you can use the provider option

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  ...
  entities: {
    roles: {
      provider: 'neon'
    }
  }
});

If you are using Supabase and want to exclude Supabase-defined roles, you can use the provider option

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  ...
  entities: {
    roles: {
      provider: 'supabase'
    }
  }
});

You may encounter situations where Drizzle is slightly outdated compared to new roles specified by your database provider. In such cases, you can use the provider option and exclude additional roles:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  ...
  entities: {
    roles: {
      provider: 'supabase',
      exclude: ['new_supabase_role']
    }
  }
});

RLS on views

With Drizzle, you can also specify RLS policies on views. For this, you need to use security_invoker in the view's WITH options. Here is a small example:

...

export const roomsUsersProfiles = pgView("rooms_users_profiles")
  .with({
    securityInvoker: true,
  })
  .as((qb) =>
    qb
      .select({
        ...getTableColumns(roomsUsers),
        email: profiles.email,
      })
      .from(roomsUsers)
      .innerJoin(profiles, eq(roomsUsers.userId, profiles.id))
  );

Using with Neon

The Neon Team helped us implement their vision of a wrapper on top of our raw policies API. We defined a specific /neon import with the crudPolicy function that includes predefined functions and Neon's default roles.

Here's an example of how to use the crudPolicy function:

import { crudPolicy } from 'drizzle-orm/neon';
import { integer, pgRole, pgTable } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin');

export const users = pgTable('users', {
	id: integer(),
}, (t) => [
	crudPolicy({ role: admin, read: true, modify: false }),
]);

This policy is equivalent to:

import { sql } from 'drizzle-orm';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin');

export const users = pgTable('users', {
	id: integer(),
}, (t) => [
	pgPolicy(`crud-${admin.name}-policy-insert`, {
		for: 'insert',
		to: admin,
		withCheck: sql`false`,
	}),
	pgPolicy(`crud-${admin.name}-policy-update`, {
		for: 'update',
		to: admin,
		using: sql`false`,
		withCheck: sql`false`,
	}),
	pgPolicy(`crud-${admin.name}-policy-delete`, {
		for: 'delete',
		to: admin,
		using: sql`false`,
	}),
	pgPolicy(`crud-${admin.name}-policy-select`, {
		for: 'select',
		to: admin,
		using: sql`true`,
	}),
]);

Neon exposes predefined authenticated and anaonymous roles and related functions. If you are using Neon for RLS, you can use these roles, which are marked as existing, and the related functions in your RLS queries.

// drizzle-orm/neon
export const authenticatedRole = pgRole('authenticated').existing();
export const anonymousRole = pgRole('anonymous').existing();

export const authUid = (userIdColumn: AnyPgColumn) => sql`(select auth.user_id() = ${userIdColumn})`;

For example, you can use the Neon predefined roles and functions like this:

import { sql } from 'drizzle-orm';
import { authenticatedRole } from 'drizzle-orm/neon';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin');

export const users = pgTable('users', {
	id: integer(),
}, (t) => [
	pgPolicy(`policy-insert`, {
		for: 'insert',
		to: authenticatedRole,
		withCheck: sql`false`,
	}),
]);

Using with Supabase

We also have a /supabase import with a set of predefined roles marked as existing, which you can use in your schema. This import will be extended in a future release with more functions and helpers to make using RLS and Supabase simpler.

// drizzle-orm/supabase
export const anonRole = pgRole('anon').existing();
export const authenticatedRole = pgRole('authenticated').existing();
export const serviceRole = pgRole('service_role').existing();
export const postgresRole = pgRole('postgres_role').existing();
export const supabaseAuthAdminRole = pgRole('supabase_auth_admin').existing();

For example, you can use the Supabase predefined roles like this:

import { sql } from 'drizzle-orm';
import { serviceRole } from 'drizzle-orm/supabase';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';

export const admin = pgRole('admin');

export const users = pgTable('users', {
	id: integer(),
}, (t) => [
	pgPolicy(`policy-insert`, {
		for: 'insert',
		to: serviceRole,
		withCheck: sql`false`,
	}),
]);

The /supabase import also includes predefined tables and functions that you can use in your application

// drizzle-orm/supabase

const auth = pgSchema('auth');
export const authUsers = auth.table('users', {
	id: uuid().primaryKey().notNull(),
});

const realtime = pgSchema('realtime');
export const realtimeMessages = realtime.table(
	'messages',
	{
		id: bigserial({ mode: 'bigint' }).primaryKey(),
		topic: text().notNull(),
		extension: text({
			enum: ['presence', 'broadcast', 'postgres_changes'],
		}).notNull(),
	},
);

export const authUid = sql`(select auth.uid())`;
export const realtimeTopic = sql`realtime.topic()`;

This allows you to use it in your code, and Drizzle Kit will treat them as existing databases, using them only as information to connect to other entities

import { foreignKey, pgPolicy, pgTable, text, uuid } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm/sql";
import { authenticatedRole, authUsers } from "drizzle-orm/supabase";

export const profiles = pgTable(
  "profiles",
  {
    id: uuid().primaryKey().notNull(),
    email: text().notNull(),
  },
  (table) => [
    foreignKey({
      columns: [table.id],
	  // reference to the auth table from Supabase
      foreignColumns: [authUsers.id],
      name: "profiles_id_fk",
    }).onDelete("cascade"),
    pgPolicy("authenticated can view all profiles", {
      for: "select",
	  // using predefined role from Supabase
      to: authenticatedRole,
      using: sql`true`,
    }),
  ]
);

Let's check an example of adding a policy to a table that exists in Supabase

import { sql } from "drizzle-orm";
import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, realtimeMessages } from "drizzle-orm/supabase";

export const policy = pgPolicy("authenticated role insert policy", {
  for: "insert",
  to: authenticatedRole,
  using: sql``,
}).link(realtimeMessages);

Bug fixes

• [BUG]: postgres-js driver throws error when using new { client } constructor arguments

New LibSQL driver modules

Drizzle now has native support for all @libsql/client driver variations:

1. @libsql/client - defaults to node import, automatically changes to web if target or platform is set for bundler, e.g. esbuild --platform=browser

import { drizzle } from 'drizzle-orm/libsql';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

1. @libsql/client/node node compatible module, supports :memory:, file, wss, http and turso connection protocols

import { drizzle } from 'drizzle-orm/libsql/node';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

1. @libsql/client/web module for fullstack web frameworks like next, nuxt, astro, etc.

import { drizzle } from 'drizzle-orm/libsql/web';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

1. @libsql/client/http module for http and https connection protocols

import { drizzle } from 'drizzle-orm/libsql/http';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

1. @libsql/client/ws module for ws and wss connection protocols

import { drizzle } from 'drizzle-orm/libsql/ws';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

1. @libsql/client/sqlite3 module for :memory: and file connection protocols

import { drizzle } from 'drizzle-orm/libsql/wasm';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

1. @libsql/client-wasm Separate experimental package for WASM

import { drizzle } from 'drizzle-orm/libsql';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

• Fix issues with importing in several environments after updating the Drizzle driver implementation

We've added approximately 240 tests to check the ESM and CJS builds for all the drivers we have. You can check them here

• Fixed [BUG]: Type Error in PgTransaction Missing $client Property After Upgrading to drizzle-orm@0.35.1
• Fixed [BUG]: New critical Build error drizzle 0.35.0 deploying on Cloudflare

• Updated internal versions for the drizzle-kit and drizzle-orm packages. Changes were introduced in the last minor release, and you are required to upgrade both packages to ensure they work as expected

Important change after 0.34.0 release

Updated the init Drizzle database API

The API from version 0.34.0 turned out to be unusable and needs to be changed. You can read more about our decisions in this discussion

If you still want to use the new API introduced in 0.34.0, which can create driver clients for you under the hood, you can now do so

import { drizzle } from "drizzle-orm/node-postgres";

const db = drizzle(process.env.DATABASE_URL);
// or
const db = drizzle({
  connection: process.env.DATABASE_URL
});
const db = drizzle({
  connection: {
    user: "...",
    password: "...",
    host: "...",
    port: 4321,
    db: "...",
  },
});

// if you need to pass logger or schema
const db = drizzle({
  connection: process.env.DATABASE_URL,
  logger: true,
  schema: schema,
});

in order to not introduce breaking change - we will still leave support for deprecated API until V1 release. It will degrade autocomplete performance in connection params due to DatabaseDriver | ConnectionParams types collision, but that's a decent compromise against breaking changes

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const client = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(client); // deprecated but available

// new version
const db = drizzle({
  client: client,
});

New Features

New .orderBy() and .limit() functions in update and delete statements SQLite and MySQL

You now have more options for the update and delete query builders in MySQL and SQLite

Example

await db.update(usersTable).set({ verified: true }).limit(2).orderBy(asc(usersTable.name));

await db.delete(usersTable).where(eq(usersTable.verified, false)).limit(1).orderBy(asc(usersTable.name));

New drizzle.mock() function

There were cases where you didn't need to provide a driver to the Drizzle object, and this served as a workaround

const db = drizzle({} as any)

Now you can do this using a mock function

const db = drizzle.mock()

There is no valid production use case for this, but we used it in situations where we needed to check types, etc., without making actual database calls or dealing with driver creation. If anyone was using it, please switch to using mocks now

Internal updates

• Upgraded TS in codebase to the version 5.6.3

Bug fixes

• [BUG]: New $count API error with @neondatabase/serverless

• Fixed dynamic imports for CJS and MJS in the /connect module

Breaking changes and migrate guide for Turso users

If you are using Turso and libsql, you will need to upgrade your drizzle.config and @libsql/client package.

1. This version of drizzle-orm will only work with @libsql/client@0.10.0 or higher if you are using the migrate function. For other use cases, you can continue using previous versions(But the suggestion is to upgrade) To install the latest version, use the command:

npm i @libsql/client@latest

1. Previously, we had a common drizzle.config for SQLite and Turso users, which allowed a shared strategy for both dialects. Starting with this release, we are introducing the turso dialect in drizzle-kit. We will evolve and improve Turso as a separate dialect with its own migration strategies.

Before

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  schema: "./schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: "database.db",
  },
  breakpoints: true,
  verbose: true,
  strict: true,
});

After

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "turso",
  schema: "./schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: "database.db",
  },
  breakpoints: true,
  verbose: true,
  strict: true,
});

If you are using only SQLite, you can use dialect: "sqlite"

LibSQL/Turso and Sqlite migration updates

SQLite "generate" and "push" statements updates

Starting from this release, we will no longer generate comments like this:

      '/*\n SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually'
      + '\n Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php'
      + '\n                  https://www.sqlite.org/lang_altertable.html'
      + '\n                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3'
      + "\n\n Due to that we don't generate migration automatically and it has to be done manually"
      + '\n*/'

We will generate a set of statements, and you can decide if it's appropriate to create data-moving statements instead. Here is an example of the SQL file you'll receive now:

PRAGMA foreign_keys=OFF;
--> statement-breakpoint
CREATE TABLE `__new_worker` (
  `id` integer PRIMARY KEY NOT NULL,
  `name` text NOT NULL,
  `salary` text NOT NULL,
  `job_id` integer,
  FOREIGN KEY (`job_id`) REFERENCES `job`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
INSERT INTO `__new_worker`("id", "name", "salary", "job_id") SELECT "id", "name", "salary", "job_id" FROM `worker`;
--> statement-breakpoint
DROP TABLE `worker`;
--> statement-breakpoint
ALTER TABLE `__new_worker` RENAME TO `worker`;
--> statement-breakpoint
PRAGMA foreign_keys=ON;

LibSQL/Turso "generate" and "push" statements updates

Since LibSQL supports more ALTER statements than SQLite, we can generate more statements without recreating your schema and moving all the data, which can be potentially dangerous for production environments.

LibSQL and Turso will now have a separate dialect in the Drizzle config file, meaning that we will evolve Turso and LibSQL independently from SQLite and will aim to support as many features as Turso/LibSQL offer.

With the updated LibSQL migration strategy, you will have the ability to:

• Change Data Type: Set a new data type for existing columns.
• Set and Drop Default Values: Add or remove default values for existing columns.
• Set and Drop NOT NULL: Add or remove the NOT NULL constraint on existing columns.
• Add References to Existing Columns: Add foreign key references to existing columns

You can find more information in the LibSQL documentation

LIMITATIONS

• Dropping foreign key will cause table recreation.

This is because LibSQL/Turso does not support dropping this type of foreign key.

CREATE TABLE `users` (
  `id` integer NOT NULL,
  `name` integer,
  `age` integer PRIMARY KEY NOT NULL
  FOREIGN KEY (`name`) REFERENCES `users1`("id") ON UPDATE no action ON DELETE no action
);

• If the table has indexes, altering columns will cause index recreation: Drizzle-Kit will drop the indexes, modify the columns, and then create the indexes.

• Adding or dropping composite foreign keys is not supported and will cause table recreation.

• Primary key columns can not be altered and will cause table recreation.

• Altering columns that are part of foreign key will cause table recreation.

NOTES

• You can create a reference on any column type, but if you want to insert values, the referenced column must have a unique index or primary key.

CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);
CREATE INDEX i2 ON parent(e);
CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

CREATE TABLE child1(f, g REFERENCES parent(a));                        -- Ok
CREATE TABLE child2(h, i REFERENCES parent(b));                        -- Ok
CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));  -- Ok
CREATE TABLE child4(l, m REFERENCES parent(e));                        -- Error!
CREATE TABLE child5(n, o REFERENCES parent(f));                        -- Error!
CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c));  -- Error!
CREATE TABLE child7(r REFERENCES parent(c));                           -- Error!

NOTE: The foreign key for the table child5 is an error because, although the parent key column has a unique index, the index uses a different collating sequence.

See more: https://www.sqlite.org/foreignkeys.html

A new and easy way to start using drizzle

Current and the only way to do, is to define client yourself and pass it to drizzle

const client = new Pool({ url: '' });
drizzle(client, { logger: true });

But we want to introduce you to a new API, which is a simplified method in addition to the existing one.

Most clients will have a few options to connect, starting with the easiest and most common one, and allowing you to control your client connection as needed.

Let's use node-postgres as an example, but the same pattern can be applied to all other clients

// Finally, one import for all available clients and dialects!
import { drizzle } from 'drizzle-orm/connect'

// Choose a client and use a connection URL — nothing else is needed!
const db1 = await drizzle("node-postgres", process.env.POSTGRES_URL);

// If you need to pass a logger, schema, or other configurations, you can use an object and specify the client-specific URL in the connection
const db2 = await drizzle("node-postgres", {
  connection: process.env.POSTGRES_URL,
  logger: true
});

// And finally, if you need to use full client/driver-specific types in connections, you can use a URL or host/port/etc. as an object inferred from the underlying client connection types
const db3 = await drizzle("node-postgres", {
  connection: {
    connectionString: process.env.POSTGRES_URL,
  },
});

const db4 = await drizzle("node-postgres", {
  connection: {
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_NAME,
    ssl: true,
  },
});

A few clients will have a slightly different API due to their specific behavior. Let's take a look at them:

For aws-data-api-pg, Drizzle will require resourceArn, database, and secretArn, along with any other AWS Data API client types for the connection, such as credentials, region, etc.

drizzle("aws-data-api-pg", {
  connection: {
    resourceArn: "",
    database: "",
    secretArn: "",
  },
});

For d1, the CloudFlare Worker types as described in the documentation here will be required.

drizzle("d1", {
  connection: env.DB // CloudFlare Worker Types
})

For vercel-postgres, nothing is needed since Vercel automatically retrieves the POSTGRES_URL from the .env file. You can check this documentation for more info

drizzle("vercel-postgres")

Note that the first example with the client is still available and not deprecated. You can use it if you don't want to await the drizzle object. The new way of defining drizzle is designed to make it easier to import from one place and get autocomplete for all the available clients

Optional names for columns and callback in drizzle table

We believe that schema definition in Drizzle is extremely powerful and aims to be as close to SQL as possible while adding more helper functions for JS runtime values.

However, there are a few areas that could be improved, which we addressed in this release. These include:

• Unnecessary database column names when TypeScript keys are essentially just copies of them
• A callback that provides all column types available for a specific table.

Let's look at an example with PostgreSQL (this applies to all the dialects supported by Drizzle)

Previously

import { boolean, pgTable, text, uuid } from "drizzle-orm/pg-core";
  
export const ingredients = pgTable("ingredients", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  inStock: boolean("in_stock").default(true),
});

The previous table definition will still be valid in the new release, but it can be replaced with this instead

import { pgTable } from "drizzle-orm/pg-core";

export const ingredients = pgTable("ingredients", (t) => ({
  id: t.uuid().defaultRandom().primaryKey(),
  name: t.text().notNull(),
  description: t.text(),
  inStock: t.boolean("in_stock").default(true),
}));

New casing param in drizzle-orm and drizzle-kit

There are more improvements you can make to your schema definition. The most common way to name your variables in a database and in TypeScript code is usually snake_case in the database and camelCase in the code. For this case, in Drizzle, you can now define a naming strategy in your database to help Drizzle map column keys automatically. Let's take a table from the previous example and make it work with the new casing API in Drizzle

Table can now become:

import { pgTable } from "drizzle-orm/pg-core";

export const ingredients = pgTable("ingredients", (t) => ({
  id: t.uuid().defaultRandom().primaryKey(),
  name: t.text().notNull(),
  description: t.text(),
  inStock: t.boolean().default(true),
}));

As you can see, inStock doesn't have a database name alias, but by defining the casing configuration at the connection level, all queries will automatically map it to snake_case

const db = await drizzle('node-postgres', { connection: '', casing: 'snake_case' })

For drizzle-kit migrations generation you should also specify casing param in drizzle config, so you can be sure you casing strategy will be applied to drizzle-kit as well

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./schema.ts",
  dbCredentials: {
    url: "postgresql://postgres:password@localhost:5432/db",
  },
  casing: "snake_case",
});

New "count" API

Before this release to count entities in a table, you would need to do this:

const res = await db.select({ count: sql`count(*)` }).from(users);
const count = res[0].count;

The new API will look like this:

// how many users are in the database
const count: number = await db.$count(users);

// how many users with the name "Dan" are in the database
const count: number = await db.$count(users, eq(name, "Dan"));

This can also work as a subquery and within relational queries

const users = await db.select({
    ...users,
    postsCount: db.$count(posts, eq(posts.authorId, users.id))
});

const users = await db.query.users.findMany({
    extras: {
        postsCount: db.$count(posts, eq(posts.authorId, users.id))
    }
})

Ability to execute raw strings instead of using SQL templates for raw queries

Previously, you would have needed to do this to execute a raw query with Drizzle

import { sql } from 'drizzle-orm'

db.execute(sql`select * from ${users}`);
// or
db.execute(sql.raw(`select * from ${users}`));

You can now do this as well

db.execute('select * from users')

You can now access the driver client from Drizzle db instance

const client = db.$client;

Breaking changes (for some of postgres.js users)

Bugs fixed for this breaking change

• [BUG]: jsonb always inserted as a json string when using postgres-js
• [BUG]: jsonb type on postgres implement incorrectly

As we are doing with other drivers, we've changed the behavior of PostgreSQL-JS to pass raw JSON values, the same as you see them in the database. So if you are using the PostgreSQL-JS driver and passing data to Drizzle elsewhere, please check the new behavior of the client after it is passed to Drizzle.

We will update it to ensure it does not override driver behaviors, but this will be done as a complex task for everything in Drizzle in other releases

If you were using postgres-js with jsonb fields, you might have seen stringified objects in your database, while drizzle insert and select operations were working as expected.

You need to convert those fields from strings to actual JSON objects. To do this, you can use the following query to update your database:

if you are using jsonb:

update table_name
set jsonb_column = (jsonb_column #>> '{}')::jsonb;

if you are using json:

update table_name
set json_column = (json_column #>> '{}')::json;

We've tested it in several cases, and it worked well, but only if all stringified objects are arrays or objects. If you have primitives like strings, numbers, booleans, etc., you can use this query to update all the fields

if you are using jsonb:

UPDATE table_name
SET jsonb_column = CASE
    -- Convert to JSONB if it is a valid JSON object or array
    WHEN jsonb_column #>> '{}' LIKE '{%' OR jsonb_column #>> '{}' LIKE '[%' THEN
        (jsonb_column #>> '{}')::jsonb
    ELSE
        jsonb_column
END
WHERE
    jsonb_column IS NOT NULL;

if you are using json:

UPDATE table_name
SET json_column = CASE
    -- Convert to JSON if it is a valid JSON object or array
    WHEN json_column #>> '{}' LIKE '{%' OR json_column #>> '{}' LIKE '[%' THEN
        (json_column #>> '{}')::json
    ELSE
        json_column
END
WHERE json_column IS NOT NULL;

If nothing works for you and you are blocked, please reach out to me @AndriiSherman. I will try to help you!

Bug Fixes

• [BUG]: boolean mode not working with prepared statements (bettersqlite) - thanks @veloii
• [BUG]: isTable helper function is not working - thanks @hajek-raven
• [BUG]: Documentation is outdated on inArray and notInArray Methods - thanks @RemiPeruto