Releases: drizzle-team/drizzle-orm
0.29.0
Drizzle ORM version
0.29.0
will require a minimum Drizzle Kit version of0.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.usersTa...
0.28.6
Changes
Note:
MySQLdatetime
withmode: 'date'
will now store dates in UTC strings and retrieve data in UTC as well to align with MySQL behavior fordatetime
. If you need a different behavior and want to handledatetime
mapping in a different way, please usemode: 'string'
or Custom Types implementation
Check Fix Datetime mapping for MySQL for implementation details
New Features
🎉 LibSQL
batch api support
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;
}[],
ResultSet,
{
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/libsql-batch.test.ts and in docs
🎉 Add json mode for text in SQLite
Example
const test = sqliteTable('test', {
dataTyped: text('data_typed', { mode: 'json' }).$type<{ a: 1 }>().notNull(),
});
🎉 Add .toSQL()
to Relational Query API calls
Example
const query = db.query.usersTable.findFirst().toSQL();
🎉 Added new PostgreSQL operators for Arrays - thanks @L-Mario564
List of operators and usage examples
arrayContains
, arrayContained
, arrayOverlaps
const contains = await db.select({ id: posts.id }).from(posts)
.where(arrayContains(posts.tags, ['Typescript', 'ORM']));
const contained = await db.select({ id: posts.id }).from(posts)
.where(arrayContained(posts.tags, ['Typescript', 'ORM']));
const overlaps = await db.select({ id: posts.id }).from(posts)
.where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']));
const withSubQuery = await db.select({ id: posts.id }).from(posts)
.where(arrayContains(
posts.tags,
db.select({ tags: posts.tags }).from(posts).where(eq(posts.id, 1)),
));
🎉 Add more SQL operators for where filter function in Relational Queries - thanks @cayter!
Before
import { inArray } from "drizzle-orm/pg-core";
await db.users.findFirst({
where: (table, _) => inArray(table.id, [ ... ])
})
After
await db.users.findFirst({
where: (table, { inArray }) => inArray(table.id, [ ... ])
})
Bug Fixes
- 🐛 Correct where in on conflict in sqlite - Thanks @hanssonduck!
- 🐛 Fix libsql/client type import - Thanks @luisfvieirasilva!
- 🐛 Fix: raw sql query not being mapped properly on RDS - Thanks @boian-ivanov
- 🐛 Fix Datetime mapping for MySQL - thanks @Angelelz
- 🐛 Fix smallserial generating as serial - thanks @L-Mario564
0.28.5
- 🐛 Fixed incorrect OpenTelemetry type import that caused a runtime error
The OpenTelemetry logic currently present in the ORM isn't meant to be used by Drizzle and no stats have ever been collected by Drizzle using drizzle-orm. OpenTelemetry is simply a protocol. If you take a look at the actual code that utilizes it in drizzle-orm, it simply uses the tracer to collect the query stats and doesn't send it anywhere. It was designed for the ORM users to be able to send those stats to their own telemetry consumers.
The important thing is - the OpenTelemetry logic is disabled on the current version. It literally does nothing. We experimented with it at some point in the past, but disabled it before the release.
As to the reason of the issue in the last release: it happened because of an incorrect type import on this line - https://github.com/drizzle-team/drizzle-orm/blob/594e96538e588fee5748e372884dbaf32c331524/drizzle-orm/src/tracing.ts#L1. We've used import { type ... }
syntax instead of import type { ... }
, which resulted in the import '@opentelemetry/api'
line leaking to the runtime.
0.28.4
0.28.3
UPDATE: The 0.28.3
release introduced a bug with types specifically for esm
projects. Everything works as expected with cjs
. If you're experiencing any type-related bugs as described in #1090, please downgrade to 0.28.2
. We are working on releasing 0.28.4 with all the fixes asap
-
🎉 Added SQLite simplified query API
-
🎉 Added
.$defaultFn()
/.$default()
methods to column builders
You can specify any logic and any implementation for a function like cuid()
for runtime defaults. Drizzle won't limit you in the number of implementations you can add.
Note: This value does not affect the
drizzle-kit
behavior, it is only used at runtime indrizzle-orm
import { varchar, mysqlTable } from "drizzle-orm/mysql-core";
import { createId } from '@paralleldrive/cuid2';
const table = mysqlTable('table', {
id: varchar('id', { length: 128 }).$defaultFn(() => createId()),
});
-
🎉 Added
table.$inferSelect
/table._.inferSelect
andtable.$inferInsert
/table._.inferInsert
for more convenient table model type inference -
🛠 Deprecated
InferModel
type in favor of more explicitInferSelectModel
andInferInsertModel
import { InferSelectModel, InferInsertModel } from 'drizzle-orm'
const usersTable = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
verified: boolean('verified').notNull().default(false),
jsonb: jsonb('jsonb').$type<string[]>(),
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});
type SelectUser = typeof usersTable.$inferSelect;
type InsertUser = typeof usersTable.$inferInsert;
type SelectUser2 = InferSelectModel<typeof usersTable>;
type InsertUser2 = InferInsertModel<typeof usersTable>;
- 🛠 Disabled
.d.ts
files bundling - 🐛 Fixed sqlite-proxy and SQL.js response from
.get()
when the result is empty
0.28.2
The community contributions release 🎉
Internal Features and Changes
- Added a set of tests for d1. Thanks to @AdiRishi!
- Fixed issues in internal documentation. Thanks to @balazsorban44 and @pyk!
Bug Fixes
- Resolved the issue of truncating timestamp milliseconds for MySQL. Thanks to @steviec!
- Corrected the type of the get() method for sqlite-based dialects. Issue #565 has been closed. Thanks to @stefanmaric!
- Rectified the sqlite-proxy bug that caused the query to execute twice. Thanks to @mosch!
New packages 🎉
Added a support for Typebox in drizzle-typebox package. Thanks to @Bulbang!
Please check documentation page for more usage examples: https://orm.drizzle.team/docs/typebox
0.28.1
0.28.0
Breaking changes
Removed support for filtering by nested relations
Current example won't work in 0.28.0
:
const usersWithPosts = await db.query.users.findMany({
where: (table, { sql }) => (sql`json_array_length(${table.posts}) > 0`),
with: {
posts: true,
},
});
The table
object in the where
callback won't have fields from with
and extras
. We removed them to be able to build more efficient relational queries, which improved row reads and performance.
If you have used those fields in the where
callback before, there are several workarounds:
- Applying those filters manually on the code level after the rows are fetched;
- Using the core API.
Added Relational Queries mode
config for mysql2
driver
Drizzle relational queries always generate exactly one SQL statement to run on the database and it has certain caveats. To have best in class support for every database out there we've introduced modes.
Drizzle relational queries use lateral joins of subqueries under the hood and for now PlanetScale does not support them.
When using mysql2
driver with regular MySQL database - you should specify mode: "default".
When using mysql2
driver with PlanetScale - you need to specify mode: "planetscale".
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';
const connection = await mysql.createConnection({
uri: process.env.PLANETSCALE_DATABASE_URL,
});
const db = drizzle(connection, { schema, mode: 'planetscale' });
Improved IntelliSense performance for large schemas
We've run the diagnostics on a database schema with 85 tables, 666 columns, 26 enums, 172 indexes and 133 foreign keys. We've optimized internal types which resulted in 430% speed up in IntelliSense.
Improved Relational Queries Permormance and Read Usage
In this release we've fully changed a way query is generated for Relational Queri API.
As a summary we've made current set of changes in query generation startegy:
-
Lateral Joins: In the new version we're utilizing lateral joins, denoted by the "LEFT JOIN LATERAL" clauses, to retrieve specific data from related tables efficiently For MySQL in PlanetScale and SQLite, we've used simple subquery selects, which improved a query plan and overall performance
-
Selective Data Retrieval: In the new version we're retrieving only the necessary data from tables. This targeted data retrieval reduces the amount of unnecessary information fetched, resulting in a smaller dataset to process and faster execution.
-
Reduced Aggregations: In the new version we've reduced the number of aggregation functions (e.g., COUNT, json_agg). By using json_build_array directly within the lateral joins, drizzle is aggregating the data in a more streamlined manner, leading to improved query performance.
-
Simplified Grouping: In the new version the GROUP BY clause is removed, as the lateral joins and subqueries already handle data aggregation more efficiently.
For this drizzle query
const items = await db.query.comments.findMany({
limit,
orderBy: comments.id,
with: {
user: {
columns: { name: true },
},
post: {
columns: { title: true },
with: {
user: {
columns: { name: true },
},
},
},
},
});
Query that Drizzle generates now
select "comments"."id",
"comments"."user_id",
"comments"."post_id",
"comments"."content",
"comments_user"."data" as "user",
"comments_post"."data" as "post"
from "comments"
left join lateral (select json_build_array("comments_user"."name") as "data"
from (select *
from "users" "comments_user"
where "comments_user"."id" = "comments"."user_id"
limit 1) "comments_user") "comments_user" on true
left join lateral (select json_build_array("comments_post"."title", "comments_post_user"."data") as "data"
from (select *
from "posts" "comments_post"
where "comments_post"."id" = "comments"."post_id"
limit 1) "comments_post"
left join lateral (select json_build_array("comments_post_user"."name") as "data"
from (select *
from "users" "comments_post_user"
where "comments_post_user"."id" = "comments_post"."user_id"
limit 1) "comments_post_user") "comments_post_user"
on true) "comments_post" on true
order by "comments"."id"
limit 1
Query generated before:
SELECT "id",
"user_id",
"post_id",
"content",
"user"::JSON,
"post"::JSON
FROM
(SELECT "comments".*,
CASE
WHEN count("comments_post"."id") = 0 THEN '[]'
ELSE json_agg(json_build_array("comments_post"."title", "comments_post"."user"::JSON))::text
END AS "post"
FROM
(SELECT "comments".*,
CASE
WHEN count("comments_user"."id") = 0 THEN '[]'
ELSE json_agg(json_build_array("comments_user"."name"))::text
END AS "user"
FROM "comments"
LEFT JOIN
(SELECT "comments_user".*
FROM "users" "comments_user") "comments_user" ON "comments"."user_id" = "comments_user"."id"
GROUP BY "comments"."id",
"comments"."user_id",
"comments"."post_id",
"comments"."content") "comments"
LEFT JOIN
(SELECT "comments_post".*
FROM
(SELECT "comments_post".*,
CASE
WHEN count("comments_post_user"."id") = 0 THEN '[]'
ELSE json_agg(json_build_array("comments_post_user"."name"))
END AS "user"
FROM "posts" "comments_post"
LEFT JOIN
(SELECT "comments_post_user".*
FROM "users" "comments_post_user") "comments_post_user" ON "comments_post"."user_id" = "comments_post_user"."id"
GROUP BY "comments_post"."id") "comments_post") "comments_post" ON "comments"."post_id" = "comments_post"."id"
GROUP BY "comments"."id",
"comments"."user_id",
"comments"."post_id",
"comments"."content",
"comments"."user") "comments"
LIMIT 1
Possibility to insert rows with default values for all columns
You can now provide an empty object or an array of empty objects, and Drizzle will insert all defaults into the database.
// Insert 1 row with all defaults
await db.insert(usersTable).values({});
// Insert 2 rows with all defaults
await db.insert(usersTable).values([{}, {}]);
0.27.2
🎉 Added support for UNIQUE
constraints in PostgreSQL, MySQL, SQLite
For PostgreSQL, unique constraints can be defined at the column level for single-column constraints, and in the third parameter for multi-column constraints. In both cases, it will be possible to define a custom name for the constraint. Additionally, PostgreSQL will receive the NULLS NOT DISTINCT
option to restrict having more than one NULL value in a table. Reference
Examples that just shows a different unique
usage. Please don't search a real usage for those tables
// single column
const table = pgTable('table', {
id: serial('id').primaryKey(),
name: text('name').notNull().unique(),
state: char('state', { length: 2 }).unique('custom'),
field: char('field', { length: 2 }).unique('custom_field', { nulls: 'not distinct' }),
});
// multiple columns
const table = pgTable('table', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
state: char('state', { length: 2 }),
}, (t) => ({
first: unique('custom_name').on(t.name, t.state).nullsNotDistinct(),
second: unique('custom_name1').on(t.name, t.state),
}));
For MySQL, everything will be the same except for the NULLS NOT DISTINCT
option. It appears that MySQL does not support it
Examples that just shows a different unique
usage. Please don't search a real usage for those tables
// single column
const table = mysqlTable('table', {
id: serial('id').primaryKey(),
name: text('name').notNull().unique(),
state: text('state').unique('custom'),
field: text('field').unique('custom_field'),
});
// multiple columns
const table = mysqlTable('cities1', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
state: text('state'),
}, (t) => ({
first: unique().on(t.name, t.state),
second: unique('custom_name1').on(t.name, t.state),
}));
In SQLite unique constraints are the same as unique indexes. As long as you can specify a name for the unique index in SQLite - we will treat all unique constraints as unique indexes in internal implementation
// single column
const table = sqliteTable('table', {
id: int('id').primaryKey(),
name: text('name').notNull().unique(),
state: text('state').unique('custom'),
field: text('field').unique(),
});
// multiple columns
const table = sqliteTable('table', {
id: int('id').primaryKey(),
name: text('name').notNull(),
state: text('state'),
}, (t) => ({
first: unique().on(t.name, t.state),
second: unique('custom').on(t.name, t.state),
}));
0.27.1
- 🎉 Added support for Neon HTTP driver
import { neon, neonConfig } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
neonConfig.fetchConnectionCache = true;
const sql = neon(process.env.DRIZZLE_DATABASE_URL!);
const db = drizzle(sql);
db.select(...)