drizzle-kit

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

</br>

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]
);

</br>

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

</br>

New Features

New drizzle-kit command called drop

</br>

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

</br>

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

</br>

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:password@127.0.0.1:3306/database

</br>

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 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'