drizzle-orm

• 🎉 Added Knex and Kysely adapters! They allow you to manage the schemas and migrations with Drizzle and query the data with your favorite query builder. See documentation for more details:

  • Knex adapter
  • Kysely adapter

• 🎉 Added "type maps" to all entities. You can access them via the special _ property. For example:

  const users = mysqlTable('users', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
  });
  
  type UserFields = typeof users['_']['columns'];
  type InsertUser = typeof users['_']['model']['insert'];
  

  Full documentation on the type maps is coming soon.

• 🎉 Added .$type() method to all column builders to allow overriding the data type. It also replaces the optional generics on columns.

  // Before
  const test = mysqlTable('test', {
    jsonField: json<Data>('json_field'),
  });
  
  // After
  const test = mysqlTable('test', {
    jsonField: json('json_field').$type<Data>(),
  });
  

• ❗ Changed syntax for text-based enum columns:

  // Before
  const test = mysqlTable('test', {
    role: text<'admin' | 'user'>('role'),
  });
  
  // After
  const test = mysqlTable('test', {
    role: text('role', { enum: ['admin', 'user'] }),
  });
  

• 🎉 Allowed passing an array of values into .insert().values() directly without spreading:

  const users = mysqlTable('users', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
  });
  
  await users.insert().values([
    { name: 'John' },
    { name: 'Jane' },
  ]);
  

  The spread syntax is now deprecated and will be removed in one of the next releases.

• 🎉 Added "table creators" to allow for table name customization:

  import { mysqlTableCreator } from 'drizzle-orm/mysql-core';
  
  const mysqlTable = mysqlTableCreator((name) => `myprefix_${name}`);
  
  const users = mysqlTable('users', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
  });
  
  // Users table is a normal table, but its name is `myprefix_users` in runtime
  

• 🎉 Implemented support for selecting/joining raw SQL expressions:

  // select current_date + s.a as dates from generate_series(0,14,7) as s(a);
  const result = await db
    .select({
      dates: sql`current_date + s.a`,
    })
    .from(sql`generate_series(0,14,7) as s(a)`);
  

• 🐛 Fixed a lot of bugs from user feedback on GitHub and Discord (thank you! ❤). Fixes #293 #301 #276 #269 #253 #311 #312

• 🎉 Introduced a standalone query builder that can be used without a DB connection:

  import { queryBuilder as qb } from 'drizzle-orm/pg-core';
  
  const query = qb.select().from(users).where(eq(users.name, 'Dan'));
  const { sql, params } = query.toSQL();
  

• 🎉 Improved WITH ... SELECT subquery creation syntax to more resemble SQL:

  Before:

  const regionalSales = db
    .select({
      region: orders.region,
      totalSales: sql`sum(${orders.amount})`.as<number>('total_sales'),
  })
  .from(orders)
  .groupBy(orders.region)
  .prepareWithSubquery('regional_sales');
  
  await db.with(regionalSales).select(...).from(...);
  

  After:

  const regionalSales = db
    .$with('regional_sales')
    .as(
      db
        .select({
          region: orders.region,
          totalSales: sql<number>`sum(${orders.amount})`.as('total_sales'),
        })
        .from(orders)
        .groupBy(orders.region),
    );
  
  await db.with(regionalSales).select(...).from(...);
  

• 🎉 Added support for HAVING clause

• 🎉 Added support for referencing selected fields in .where(), .having(), .groupBy() and .orderBy() using an optional callback:

  await db
    .select({
      id: citiesTable.id,
      name: sql<string>`upper(${citiesTable.name})`.as('upper_name'),
      usersCount: sql<number>`count(${users2Table.id})::int`.as('users_count'),
    })
    .from(citiesTable)
    .leftJoin(users2Table, eq(users2Table.cityId, citiesTable.id))
    .where(({ name }) => sql`length(${name}) >= 3`)
    .groupBy(citiesTable.id)
    .having(({ usersCount }) => sql`${usersCount} > 0`)
    .orderBy(({ name }) => name);
  

Drizzle ORM 0.21.0 was released 🎉

• Added support for new migration folder structure and breakpoints feature, described in drizzle-kit release section
• Fix onUpdateNow() expression generation for default migration statement

Support for PostgreSQL array types

export const salEmp = pgTable('sal_emp', {
  name: text('name').notNull(),
  payByQuarter: integer('pay_by_quarter').array(),
  schedule: text('schedule').array().array(),
});

export const tictactoe = pgTable('tictactoe', {
  squares: integer('squares').array(3).array(3),
});

drizzle kit will generate

CREATE TABLE sal_emp (
  name            text,
  pay_by_quarter  integer[],
  schedule        text[][]
);

CREATE TABLE tictactoe (
  squares   integer[3][3]
);

Added composite primary key support to PostgreSQL and MySQL

PostgreSQL

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

export const cpkTable = pgTable('table', {
  column1: integer('column1').default(10).notNull(),
  column2: integer('column2'),
  column3: integer('column3'),
}, (table) => ({
  cpk: primaryKey(table.column1, table.column2),
}));

MySQL

import { primaryKey } from 'drizzle-orm/mysql-core';

export const cpkTable = mysqlTable('table', {
  simple: int('simple'),
  columnNotNull: int('column_not_null').notNull(),
  columnDefault: int('column_default').default(100),
}, (table) => ({
  cpk: primaryKey(table.simple, table.columnDefault),
}));

Drizzle Kit 0.17.0 was released 🎉

Breaking changes

Folder structure was migrated to newer version

Before running any new migrations drizzle-kit will ask you to upgrade in a first place

Migration file structure < 0.17.0

📦 <project root>
 └ 📂 migrations
    └ 📂 20221207174503
       ├ 📜 migration.sql
       ├ 📜 snapshot.json
    └ 📂 20230101104503
       ├ 📜 migration.sql
       ├ 📜 snapshot.json

Migration file structure >= 0.17.0

📦 <project root>
 └ 📂 migrations
    └ 📂 meta
      ├ 📜 _journal.json
      ├ 📜 0000_snapshot.json
      ├ 📜 0001_snapshot.json
    └ 📜 0000_icy_stranger.sql
    └ 📜 0001_strange_avengers.sql

Upgrading to 0.17.0

To easily migrate from previous folder structure to new you need to run up command in drizzle kit. It's a great helper to upgrade your migrations to new format on each drizzle kit major update

drizzle-kit up:<dialect> # dialects: `pg`, `mysql`, `sqlite`

# example for pg
drizzle-kit up:pg

New Features

New drizzle-kit command called drop

In a case you think some of migrations were generated in a wrong way or you have made migration simultaneously with other developers you can easily rollback it by running simple command

Warning:
Make sure you are dropping migrations that were not applied to your database

drizzle-kit drop

This command will show you a list of all migrations you have and you'll need just to choose migration you want to drop. After that drizzle-kit will do all the hard work on deleting migration files

New drizzle-kit option --breakpoints for generate and introspect commands

If particular driver doesn't support running multiple quries in 1 execution you can use --breakpoints.

drizzle-kit will generate current sql

CREATE TABLE `users` (
  `id` int PRIMARY KEY NOT NULL,
  `full_name` text NOT NULL,
);
--> statement-breakpoint
CREATE TABLE `table` (
  `id` int PRIMARY KEY NOT NULL,
  `phone` int,
);

Using it drizzle-orm will split all sql files by statements and execute them separately

Add drizzle-kit introspect for MySQL dialect

You can introspect your mysql database using introspect:mysql command

drizzle-kit introspect:mysql --out ./migrations --connectionString mysql://user:[email protected]:3306/database

Support for glob patterns for schema path

Usage example in cli

drizzle-kit generate:pg --out ./migrations --schema ./core/**/*.ts ./database/schema.ts

Usage example in drizzle.config

{
  "out: "./migrations", 
  "schema": ["./core/**/*.ts", "./database/schema.ts"]
}

Bug Fixes and improvements

Postgres dialect

GitHub issue fixes

• [pg] char is undefined during introspection #9
• when unknown type is detected, would be nice to emit a TODO comment instead of undefined #8
• "post_id" integer DEFAULT currval('posts_id_seq'::regclass) generates invalid TS #7
• "ip" INET NOT NULL is not supported #6
• "id" UUID NOT NULL DEFAULT uuid_generate_v4() type is not supported #5
• array fields end up as "undefined" in the schema #4
• timestamp is not in the import statement in schema.ts #3
• generated enums are not camel cased #2

Introspect improvements

• Add support for composite PK's generation;
• Add support for cidr, inet, macaddr, macaddr8, smallserial
• Add interval fields generation in schema, such as minute to second, day to hour, etc.
• Add default values for numerics
• Add default values for enums

MySQL dialect

Migration generation improvements

• Add autoincrement create, delete and update handling
• Add on update current_timestamp handling for timestamps
• Add data type changing, using modify
• Add not null changing, using modify
• Add default drop and create statements
• Fix defaults generation bugs, such as escaping, date strings, expressions, etc

Introspect improvements

• Add autoincrement to all supported types
• Add fsp for time based data types
• Add precision and scale for double
• Make time { mode: "string" } by default
• Add defaults to json, decimal and binary datatypes
• Add enum data type generation

• 🎉 Added support for locking clauses in SELECT (SELECT ... FOR UPDATE):

  PostgreSQL

  await db
    .select()
    .from(users)
    .for('update')
    .for('no key update', { of: users })
    .for('no key update', { of: users, skipLocked: true })
    .for('share', { of: users, noWait: true });
  

  MySQL

  await db.select().from(users).for('update');
  await db.select().from(users).for('share', { skipLocked: true });
  await db.select().from(users).for('update', { noWait: true });
  

• 🎉🐛 Custom column types now support returning SQL from toDriver() method in addition to the driverData type from generic.

• 🎉 Added PostgreSQL network data types:
  • inet
  • cidr
  • macaddr
  • macaddr8

• 🎉 Added { logger: true } shorthand to drizzle() to enable query logging. See logging docs for detailed logging configuration.

• 🎉 Implemented support for WITH clause (docs). Example usage:

  const sq = db
    .select()
    .from(users)
    .prepareWithSubquery('sq');
  
  const result = await db
    .with(sq)
    .select({
      id: sq.id,
      name: sq.name,
      total: sql<number>`count(${sq.id})::int`(),
    })
    .from(sq)
    .groupBy(sq.id, sq.name);
  

• 🐛 Fixed various bugs with selecting/joining of subqueries.

• ❗ Renamed .subquery('alias') to .as('alias').

• ❗ sql`query`.as<type>() is now sql<type>`query`(). Old syntax is still supported, but is deprecated and will be removed in one of the next releases.

Changelog

• Add char data type support for postgresql by @AlexandrLi in #177
• Adding new section with New Contributors for release notes. Took this template from bun release notes pattern

New Contributors

• @AlexandrLi made their first contribution in #177

• Implemented selecting and joining a subquery. Example usage:

  const sq = db
    .select({
      categoryId: courseCategoriesTable.id,
      category: courseCategoriesTable.name,
      total: sql`count(${courseCategoriesTable.id})`.as<number>(),
    })
    .from(courseCategoriesTable)
    .groupBy(courseCategoriesTable.id, courseCategoriesTable.name)
    .subquery('sq');
  

  After that, just use the subquery instead of a table as usual.

• ❗ Replaced db.select(table).fields({ ... }) syntax with db.select({ ... }).from(table) to look more like its SQL counterpart.

• Improved join result types for partial selects (refer to the docs page for more information)
• Renamed import paths for Postgres.js and SQL.js drivers to avoid bundling errors:
  • drizzle-orm/postgres.js -> drizzle-orm/postgres-js
  • drizzle-orm/sql.js -> drizzle-orm/sql-js

• Fix #158 issue. Method .returning() was working incorrectly with .get() method in sqlite dialect
• Fix SQLite Proxy driver mapping bug
• Add test cases for SQLite Proxy driver
• Add additional example for SQLite Proxy Server setup to handle .get() as well

Fix circular dependency for query building on all pg and mysql drivers

Moved all aws data api typings specific logic to dialect from sql to prevent circular dependency issues

We have released Planetscale Serverless driver support

Usage example:

import { drizzle } from 'drizzle-orm/planetscale-serverless';
import { connect } from '@planetscale/database';

// create the connection
const connection = connect({
  host: process.env['DATABASE_HOST'],
  username: process.env['DATABASE_USERNAME'],
  password: process.env['DATABASE_PASSWORD'],
});

const db = drizzle(connection);

We have released SQLite Proxy Driver

Perfect way to setup custom logic for database calls instead of predefined drivers

Should work well with serverless apps 🚀

// Custom Proxy HTTP driver
  const db = drizzle(async (sql, params, method) => {
    try {
      const rows = await axios.post('http://localhost:3000/query', { sql, params, method });

      return { rows: rows.data };
    } catch (e: any) {
      console.error('Error from sqlite proxy server: ', e.response.data)
      return { rows: [] };
    }
  });

For more example you can check full documentation

We have released AWS Data API support for PostgreSQL

Connection example

import { drizzle, migrate } from 'drizzle-orm/aws-data-api/pg';

const rdsClient = new RDSDataClient({});

const db = drizzle(rdsClient, {
  database: '',
  secretArn: '',
  resourceArn: '',
});

await migrate(db, { migrationsFolder: '' });

Note:
All drizzle pg data types are working well with data api, except of interval. This type is not yet mapped in proper way

• Fixed package.json require path in drizzle-orm/version

• Added feature showcase section to README

❗ All ORM packages are now merged into drizzle-orm

Starting from release 0.17.0 and onwards, all dialect-specific packages are merged into drizzle-orm. Legacy ORM packages will be archived.

Import paths changes

PostgreSQL

• import { ... } from 'drizzle-orm-pg' -> import { ... } from 'drizzle-orm/pg-core'
• import { ... } from 'drizzle-orm-pg/node' -> import { ... } from 'drizzle-orm/node-postgres'
• import { ... } from 'drizzle-orm-pg/neondb' -> import { ... } from 'drizzle-orm/neon'
• import { ... } from 'drizzle-orm-pg/postgres.js' -> import { ... } from 'drizzle-orm/postgres.js'

MySQL

• import { ... } from 'drizzle-orm-mysql' -> import { ... } from 'drizzle-orm/mysql-core'
• import { ... } from 'drizzle-orm-mysql/mysql2' -> import { ... } from 'drizzle-orm/mysql2'

SQLite

• import { ... } from 'drizzle-orm-sqlite' -> import { ... } from 'drizzle-orm/sqlite-core'
• import { ... } from 'drizzle-orm-sqlite/better-sqlite3' -> import { ... } from 'drizzle-orm/better-sqlite3'
• import { ... } from 'drizzle-orm-sqlite/d1' -> import { ... } from 'drizzle-orm/d1'
• import { ... } from 'drizzle-orm-sqlite/bun' -> import { ... } from 'drizzle-orm/bun-sqlite'
• import { ... } from 'drizzle-orm-sqlite/sql.js' -> import { ... } from 'drizzle-orm/sql.js'