Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅
APACHE-2.0 License
Bot releases are visible (Hide)
Published by github-actions[bot] about 1 year ago
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
LibSQL
batch api supportBatch 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
Example
const test = sqliteTable('test', {
dataTyped: text('data_typed', { mode: 'json' }).$type<{ a: 1 }>().notNull(),
});
.toSQL()
to Relational Query API callsExample
const query = db.query.usersTable.findFirst().toSQL();
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)),
));
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, [ ... ])
})
Published by github-actions[bot] about 1 year ago
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.
Published by github-actions[bot] about 1 year ago
⚠ If you are facing a Cannot find package '@opentelemetry/api'
error, please update to 0.28.5
, it's fixed there.
Published by github-actions[bot] about 1 year ago
🎉 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
and table.$inferInsert
/ table._.inferInsert
for more convenient table model type inference
🛠 Deprecated InferModel
type in favor of more explicit InferSelectModel
and InferInsertModel
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>;
.d.ts
files bundling.get()
when the result is emptyPublished by github-actions[bot] about 1 year ago
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
Published by github-actions[bot] about 1 year ago
Published by github-actions[bot] about 1 year ago
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:
mode
config for mysql2
driverDrizzle 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' });
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.
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
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([{}, {}]);
Published by github-actions[bot] over 1 year ago
UNIQUE
constraints in PostgreSQL, MySQL, SQLiteFor 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),
}));
Published by github-actions[bot] over 1 year ago
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(...)
Published by github-actions[bot] over 1 year ago
Replacing all instanceof
statements with a custom is()
function allowed us to handle multiple Drizzle packages interacting properly.
It also fixes one of our biggest Discord tickets: maximum call stack exceeded
🎉
You should now use is()
instead of instanceof
to check if specific objects are instances of specific Drizzle types. It might be useful if you are building something on top of the Drizzle API.
import { is, Column } from 'drizzle-orm'
if (is(value, Column)) {
// value's type is narrowed to Column
}
distinct
clause supportawait db.selectDistinct().from(usersDistinctTable).orderBy(
usersDistinctTable.id,
usersDistinctTable.name,
);
Also, distinct on
clause is available for PostgreSQL:
await db.selectDistinctOn([usersDistinctTable.id]).from(usersDistinctTable).orderBy(
usersDistinctTable.id,
);
await db.selectDistinctOn([usersDistinctTable.name], { name: usersDistinctTable.name }).from(
usersDistinctTable,
).orderBy(usersDistinctTable.name);
bigint
and boolean
support for SQLiteContributed by @MrRahulRamkumar (#558), @raducristianpopa (#411) and @meech-ward (#725)
const users = sqliteTable('users', {
bigintCol: blob('bigint', { mode: 'bigint' }).notNull(),
boolCol: integer('bool', { mode: 'boolean' }).notNull(),
});
where
callback in RQB for tables without relationsPublished by github-actions[bot] over 1 year ago
Published by github-actions[bot] over 1 year ago
Published by github-actions[bot] over 1 year ago
Published by github-actions[bot] over 1 year ago
Published by github-actions[bot] over 1 year ago
Published by github-actions[bot] over 1 year ago
The documentation has been completely reworked and updated with additional examples and explanations. You can find it here: https://orm.drizzle.team.
Furthermore, the entire documentation has been made open source, allowing you to edit and add any information you deem important for the community.
Visit https://github.com/drizzle-team/drizzle-orm-docs to access the open-sourced documentation.
Additionally, you can create specific documentation issues in this repository
Introducing our first helper built on top of Drizzle Core API syntax: the Relational Queries! 🎉
With Drizzle RQ you can do:
where
statements, allowing you to define custom conditions beyond the predefined ones available in the Drizzle Core API.Most importantly, regardless of the size of your query, Drizzle will always generate a SINGLE optimized query.
This efficiency extends to the usage of Prepared Statements, which are fully supported within the Relational Query Builder.
For more info: Prepared Statements in Relational Query Builder
Example of setting one-to-many relations
As you can observe,
relations
are a distinct concept that coexists alongside the main Drizzle schema. You have the flexibility to opt-in or opt-out of them at any time without affecting thedrizzle-kit
migrations or the logic for Core API's types and runtime.
import { integer, serial, text, pgTable } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
});
export const usersConfig = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const posts = pgTable('posts', {
id: serial('id').primaryKey(),
content: text('content').notNull(),
authorId: integer('author_id').notNull(),
});
export const postsConfig = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));
Example of querying you database
Step 1: Provide all tables and relations to drizzle
function
drizzle
import depends on the database driver you're using
import * as schema from './schema';
import { drizzle } from 'drizzle-orm/...';
const db = drizzle(client, { schema });
await db.query.users.findMany(...);
If you have schema in multiple files
import * as schema1 from './schema1';
import * as schema2 from './schema2';
import { drizzle } from 'drizzle-orm/...';
const db = drizzle(client, { schema: { ...schema1, ...schema2 } });
await db.query.users.findMany(...);
Step 2: Query your database with Relational Query Builder
Select all users
const users = await db.query.users.findMany();
Select first users
.findFirst()
will add limit 1 to the query
const user = await db.query.users.findFirst();
Select all users
Get all posts with just id
, content
and include comments
const posts = await db.query.posts.findMany({
columns: {
id: true,
content: true,
},
with: {
comments: true,
}
});
Select all posts excluding content
column
const posts = await db.query.posts.findMany({
columns: {
content: false,
},
});
For more examples you can check full docs for Relational Queries
You can now specify the configuration not only in the .json
format but also in .ts
and .js
formats.
TypeScript example
import { Config } from "drizzle-kit";
export default {
schema: "",
connectionString: process.env.DB_URL,
out: "",
breakpoints: true
} satisfies Config;
JavaScript example
/** @type { import("drizzle-kit").Config } */
export default {
schema: "",
connectionString: "",
out: "",
breakpoints: true
};
drizzle-kit push:mysql
You can now push your MySQL schema directly to the database without the need to create and manage migration files. This feature proves to be particularly useful for rapid local development and when working with PlanetScale databases.
By pushing the MySQL schema directly to the database, you can streamline the development process and avoid the overhead of managing migration files. This allows for more efficient iteration and quick deployment of schema changes during local development.
For this feature, you need to create a drizzle.config.[ts|js|json]
file. We recommend using .ts
or .js
files as they allow you to easily provide the database connection information as secret variables
You'll need to specify schema
and connectionString
(or db
, port
, host
, password
, etc.) to make drizzle-kit push:mysql
work
drizzle.config.ts
example
import { Config } from "src";
export default {
schema: "./schema.ts",
connectionString: process.env.DB_URL,
} satisfies Config;
Run drizzle-kit push:mysql
If Drizzle detects any potential data-loss
issues during a migration, it will prompt you to approve whether the data should be truncated or not in order to ensure a successful migration
Approve or reject the action that Drizzle needs to perform in order to push your schema changes to the database.
Done ✅
Published by github-actions[bot] over 1 year ago
import { drizzle } from 'drizzle-orm/vercel-postgres';
import { sql } from "@vercel/postgres";
const db = drizzle(sql);
db.select(...)
Published by github-actions[bot] over 1 year ago
pg
imports in ESM mode (#505)Published by github-actions[bot] over 1 year ago
"sideEffects": false
to package.json (#515)Published by github-actions[bot] over 1 year ago
exports
field