0.29.0

Drizzle ORM version 0.29.0 will require a minimum Drizzle Kit version of 0.20.0, and vice versa. Therefore, when upgrading to a newer version of Drizzle ORM, you will also need to upgrade Drizzle Kit. This may result in some breaking changes throughout the versions, especially if you need to upgrade Drizzle Kit and your Drizzle ORM version is older than <0.28.0

New Features

🎉 MySQL unsigned option for bigint

You can now specify bigint unsigned type

const table = mysqlTable('table', {
  id: bigint('id', { mode: 'number', unsigned: true }),
});

Read more in docs

🎉 Improved query builder types

Starting from 0.29.0 by default, as all the query builders in Drizzle try to conform to SQL as much as possible, you can only invoke most of the methods once. For example, in a SELECT statement there might only be one WHERE clause, so you can only invoke .where() once:

const query = db
  .select()
  .from(users)
  .where(eq(users.id, 1))
  .where(eq(users.name, 'John')); // ❌ Type error - where() can only be invoked once

This behavior is useful for conventional query building, i.e. when you create the whole query at once. However, it becomes a problem when you want to build a query dynamically, i.e. if you have a shared function that takes a query builder and enhances it. To solve this problem, Drizzle provides a special 'dynamic' mode for query builders, which removes the restriction of invoking methods only once. To enable it, you need to call .$dynamic() on a query builder.

Let's see how it works by implementing a simple withPagination function that adds LIMIT and OFFSET clauses to a query based on the provided page number and an optional page size:

function withPagination<T extends PgSelect>(
  qb: T,
  page: number,
  pageSize: number = 10,
) {
  return qb.limit(pageSize).offset(page * pageSize);
}

const query = db.select().from(users).where(eq(users.id, 1));
withPagination(query, 1); // ❌ Type error - the query builder is not in dynamic mode

const dynamicQuery = query.$dynamic();
withPagination(dynamicQuery, 1); // ✅ OK

Note that the withPagination function is generic, which allows you to modify the result type of the query builder inside it, for example by adding a join:

function withFriends<T extends PgSelect>(qb: T) {
  return qb.leftJoin(friends, eq(friends.userId, users.id));
}

let query = db.select().from(users).where(eq(users.id, 1)).$dynamic();
query = withFriends(query);

Read more in docs

🎉 Possibility to specify name for primary keys and foreign keys

There is an issue when constraint names exceed the 64-character limit of the database. This causes the database engine to truncate the name, potentially leading to issues. Starting from 0.29.0, you have the option to specify custom names for both primaryKey() and foreignKey(). We have also deprecated the old primaryKey() syntax, which can still be used but will be removed in future releases

const table = pgTable('table', {
  id: integer('id'),
  name: text('name'),
}, (table) => ({
  cpk: primaryKey({ name: 'composite_key', columns: [table.id, table.name] }),
  cfk: foreignKey({
    name: 'fkName',
    columns: [table.id],
    foreignColumns: [table.name],
  }),
}));

Read more in docs

🎉 Read Replicas Support

You can now use the Drizzle withReplica function to specify different database connections for read replicas and the main instance for write operations. By default, withReplicas will use a random read replica for read operations and the main instance for all other data modification operations. You can also specify custom logic for choosing which read replica connection to use. You have the freedom to make any weighted, custom decision for that. Here are some usage examples:

const primaryDb = drizzle(client);
const read1 = drizzle(client);
const read2 = drizzle(client);

const db = withReplicas(primaryDb, [read1, read2]);

// read from primary
db.$primary.select().from(usersTable);

// read from either read1 connection or read2 connection
db.select().from(usersTable)

// use primary database for delete operation
db.delete(usersTable).where(eq(usersTable.id, 1))

Implementation example of custom logic for selecting read replicas, where the first replica has a 70% chance of being chosen, and the second replica has a 30% chance of being chosen. Note that you can implement any type of random selection for read replicas

const db = withReplicas(primaryDb, [read1, read2], (replicas) => {
    const weight = [0.7, 0.3];
    let cumulativeProbability = 0;
    const rand = Math.random();

    for (const [i, replica] of replicas.entries()) {
      cumulativeProbability += weight[i]!;
      if (rand < cumulativeProbability) return replica;
    }
    return replicas[0]!
});

withReplicas function is available for all dialects in Drizzle ORM

Read more in docs

🎉 Set operators support (UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL)

Huge thanks to @Angelelz for the significant contribution he made, from API discussions to proper type checks and runtime logic, along with an extensive set of tests. This greatly assisted us in delivering this feature in this release

Usage examples:
All set operators can be used in a two ways: import approach or builder approach

Import approach

import { union } from 'drizzle-orm/pg-core'

const allUsersQuery = db.select().from(users);
const allCustomersQuery = db.select().from(customers);

const result = await union(allUsersQuery, allCustomersQuery)

Builder approach

const result = await db.select().from(users).union(db.select().from(customers));

Read more in docs

🎉 New MySQL Proxy Driver

A new driver has been released, allowing you to create your own implementation for an HTTP driver using a MySQL database. You can find usage examples in the ./examples/mysql-proxy folder

You need to implement two endpoints on your server that will be used for queries and migrations(Migrate endpoint is optional and only if you want to use drizzle migrations). Both the server and driver implementation are up to you, so you are not restricted in any way. You can add custom mappings, logging, and much more

You can find both server and driver implementation examples in the ./examples/mysql-proxy folder

// Driver
import axios from 'axios';
import { eq } from 'drizzle-orm/expressions';
import { drizzle } from 'drizzle-orm/mysql-proxy';
import { migrate } from 'drizzle-orm/mysql-proxy/migrator';
import { cities, users } from './schema';

async function main() {
  const db = drizzle(async (sql, params, method) => {
    try {
      const rows = await axios.post(`${process.env.REMOTE_DRIVER}/query`, {
        sql,
        params,
        method,
      });

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

  await migrate(db, async (queries) => {
    try {
      await axios.post(`${process.env.REMOTE_DRIVER}/migrate`, { queries });
    } catch (e) {
      console.log(e);
      throw new Error('Proxy server cannot run migrations');
    }
  }, { migrationsFolder: 'drizzle' });

  await db.insert(cities).values({ id: 1, name: 'name' });

  await db.insert(users).values({
    id: 1,
    name: 'name',
    email: 'email',
    cityId: 1,
  });

  const usersToCityResponse = await db.select().from(users).leftJoin(
    cities,
    eq(users.cityId, cities.id),
  );
}

🎉 New PostgreSQL Proxy Driver

Same as MySQL you can now implement your own http driver for PostgreSQL database. You can find usage examples in the ./examples/pg-proxy folder

You need to implement two endpoints on your server that will be used for queries and migrations (Migrate endpoint is optional and only if you want to use drizzle migrations). Both the server and driver implementation are up to you, so you are not restricted in any way. You can add custom mappings, logging, and much more

You can find both server and driver implementation examples in the ./examples/pg-proxy folder

import axios from 'axios';
import { eq } from 'drizzle-orm/expressions';
import { drizzle } from 'drizzle-orm/pg-proxy';
import { migrate } from 'drizzle-orm/pg-proxy/migrator';
import { cities, users } from './schema';

async function main() {
  const db = drizzle(async (sql, params, method) => {
    try {
      const rows = await axios.post(`${process.env.REMOTE_DRIVER}/query`, { sql, params, method });

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

  await migrate(db, async (queries) => {
    try {
      await axios.post(`${process.env.REMOTE_DRIVER}/query`, { queries });
    } catch (e) {
      console.log(e);
      throw new Error('Proxy server cannot run migrations');
    }
  }, { migrationsFolder: 'drizzle' });

  const insertedCity = await db.insert(cities).values({ id: 1, name: 'name' }).returning();
  const insertedUser = await db.insert(users).values({ id: 1, name: 'name', email: 'email', cityId: 1 });
  const usersToCityResponse = await db.select().from(users).leftJoin(cities, eq(users.cityId, cities.id));
}

🎉 D1 Batch API support

Reference: https://developers.cloudflare.com/d1/platform/client-api/#dbbatch

Batch API usage example:

const batchResponse = await db.batch([
  db.insert(usersTable).values({ id: 1, name: 'John' }).returning({
    id: usersTable.id,
  }),
  db.update(usersTable).set({ name: 'Dan' }).where(eq(usersTable.id, 1)),
  db.query.usersTable.findMany({}),
  db.select().from(usersTable).where(eq(usersTable.id, 1)),
  db.select({ id: usersTable.id, invitedBy: usersTable.invitedBy }).from(
    usersTable,
  ),
]);

Type for batchResponse in this example would be:

type BatchResponse = [
  {
    id: number;
  }[],
  D1Result,
  {
    id: number;
    name: string;
    verified: number;
    invitedBy: number | null;
  }[],
  {
    id: number;
    name: string;
    verified: number;
    invitedBy: number | null;
  }[],
  {
    id: number;
    invitedBy: number | null;
  }[],
];

All possible builders that can be used inside db.batch:

`db.all()`,
`db.get()`,
`db.values()`,
`db.run()`,
`db.query.<table>.findMany()`,
`db.query.<table>.findFirst()`,
`db.select()...`,
`db.update()...`,
`db.delete()...`,
`db.insert()...`,

More usage examples here: integration-tests/tests/d1-batch.test.ts and in docs

---

Drizzle Kit 0.20.0

1. New way to define drizzle.config using defineConfig function
2. Possibility to access Cloudflare D1 with Drizzle Studio using wrangler.toml file
3. Drizzle Studio is migrating to https://local.drizzle.studio/
4. bigint unsigned support
5. primaryKeys and foreignKeys now can have custom names
6. Environment variables are now automatically fetched
7. Some bug fixes and improvements

You can read more about drizzle-kit updates here