--- title: DrizzleORM v0.29.0 release pubDate: 2023-11-09 description: Added new features like unsigned option for bigint in MySQL, improved query builder types, added possibility to specify name for primary keys and foreign keys, read replicas support, set operators support, new MySQL and PostgreSQL proxy drivers, D1 Batch API support. --- import Section from "@mdx/Section.astro"; > 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 ```ts copy {2} const table = mysqlTable('table', { id: bigint('id', { mode: 'number', unsigned: true }), }); ``` Read more in [docs](/docs/column-types/mysql#bigint) ### 🎉 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: ```ts copy {5} 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: ```ts copy {12,13} function withPagination( 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: ```ts copy {2} function withFriends(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](/docs/dynamic-query-building) ### 🎉 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 ```ts copy {5,7} 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](/docs/indexes-constraints#composite-primary-key) ### 🎉 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: ```ts copy {5} 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 ```ts copy 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](/docs/read-replicas) ### 🎉 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`

```ts copy {2,7} // 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) ``` ```ts copy {2} // Builder approach const result = await db.select().from(users).union(db.select().from(customers)); ```

Read more in [docs](/docs/set-operations) ### 🎉 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 ```ts copy {4,9} // 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), ); } ``` Read more in [docs](/docs/get-started-mysql#http-proxy) ### 🎉 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 ```ts copy {3,8} 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)); } ``` Read more in [docs](/docs/get-started-postgresql#http-proxy) ### 🎉 `D1` Batch API support Reference: https://developers.cloudflare.com/d1/platform/client-api/#dbbatch Batch API usage example:

```ts copy {1} 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, ), ]); ``` ```ts 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`: ```ts `db.all()`, `db.get()`, `db.values()`, `db.run()`, `db.query..findMany()`, `db.query.

.findFirst()`, `db.select()...`, `db.update()...`, `db.delete()...`, `db.insert()...`, ``` More usage examples here: [integration-tests/tests/d1-batch.test.ts](https://github.com/drizzle-team/drizzle-orm/blob/beta/integration-tests/tests/d1-batch.test.ts) and in [docs](/docs/batch-api) --- ## 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](https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.20.0)

v0.29.0 Release