0.23.0

• 🎉 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

0.22.0

• 🎉 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(...);

0.21.1

• 🎉 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);

0.21.0

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

0.20.3

• 🎉 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.

0.20.2

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

0.20.1

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

0.20.0

• 🎉 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.

0.19.1

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

0.19.0

• 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.