Releases: drizzle-team/drizzle-orm
0.38.2
New features
USE INDEX
, FORCE INDEX
and IGNORE INDEX
for MySQL
In MySQL, the statements USE INDEX, FORCE INDEX, and IGNORE INDEX are hints used in SQL queries to influence how the query optimizer selects indexes. These hints provide fine-grained control over index usage, helping optimize performance when the default behavior of the optimizer is not ideal.
Use Index
The USE INDEX
hint suggests to the optimizer which indexes to consider when processing the query. The optimizer is not forced to use these indexes but will prioritize them if they are suitable.
export const users = mysqlTable('users', {
id: int('id').primaryKey(),
name: varchar('name', { length: 100 }).notNull(),
}, () => [usersTableNameIndex]);
const usersTableNameIndex = index('users_name_index').on(users.name);
await db.select()
.from(users, { useIndex: usersTableNameIndex })
.where(eq(users.name, 'David'));
Ignore Index
The IGNORE INDEX
hint tells the optimizer to avoid using specific indexes for the query. MySQL will consider all other indexes (if any) or perform a full table scan if necessary.
export const users = mysqlTable('users', {
id: int('id').primaryKey(),
name: varchar('name', { length: 100 }).notNull(),
}, () => [usersTableNameIndex]);
const usersTableNameIndex = index('users_name_index').on(users.name);
await db.select()
.from(users, { ignoreIndex: usersTableNameIndex })
.where(eq(users.name, 'David'));
Force Index
The FORCE INDEX
hint forces the optimizer to use the specified index(es) for the query. If the specified index cannot be used, MySQL will not fall back to other indexes; it might resort to a full table scan instead.
export const users = mysqlTable('users', {
id: int('id').primaryKey(),
name: varchar('name', { length: 100 }).notNull(),
}, () => [usersTableNameIndex]);
const usersTableNameIndex = index('users_name_index').on(users.name);
await db.select()
.from(users, { forceIndex: usersTableNameIndex })
.where(eq(users.name, 'David'));
You can also combine those hints and use multiple indexes in a query if you need
[email protected]
New Features
drizzle-kit export
To make drizzle-kit integration with other migration tools, like Atlas much easier, we've prepared a new command called export
. It will translate your drizzle schema in SQL representation(DDL) statements and outputs to the console
// schema.ts
import { pgTable, serial, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: serial('id').primaryKey(),
email: text('email').notNull(),
name: text('name')
});
Running
npx drizzle-kit export
will output this string to console
CREATE TABLE "users" (
"id" serial PRIMARY KEY NOT NULL,
"email" text NOT NULL,
"name" text
);
By default, the only option for now is --sql
, so the output format will be SQL DDL statements. In the future, we will support additional output formats to accommodate more migration tools
npx drizzle-kit export --sql
0.38.1
0.38.0
Types breaking changes
A few internal types were changed and extra generic types for length of column types were added in this release. It won't affect anyone, unless you are using those internal types for some custom wrappers, logic, etc. Here is a list of all types that were changed, so if you are relying on those, please review them before upgrading
MySqlCharBuilderInitial
MySqlVarCharBuilderInitial
PgCharBuilderInitial
PgArrayBuilder
PgArray
PgVarcharBuilderInitial
PgBinaryVectorBuilderInitial
PgBinaryVectorBuilder
PgBinaryVector
PgHalfVectorBuilderInitial
PgHalfVectorBuilder
PgHalfVector
PgVectorBuilderInitial
PgVectorBuilder
PgVector
SQLiteTextBuilderInitial
New Features
- Added new function
getViewSelectedFields
- Added
$inferSelect
function to views - Added
InferSelectViewModel
type for views - Added
isView
function
Validator packages updates
drizzle-zod
has been completely rewritten. You can find detailed information about it heredrizzle-valibot
has been completely rewritten. You can find detailed information about it heredrizzle-typebox
has been completely rewritten. You can find detailed information about it here
Thanks to @L-Mario564 for making more updates than we expected to be shipped in this release. We'll copy his message from a PR regarding improvements made in this release:
- Output for all packages are now unminified, makes exploring the compiled code easier when published to npm.
- Smaller footprint. Previously, we imported the column types at runtime for each dialect, meaning that for example, if you're just using Postgres then you'd likely only have drizzle-orm and drizzle-orm/pg-core in the build output of your app; however, these packages imported all dialects which could lead to mysql-core and sqlite-core being bundled as well even if they're unused in your app. This is now fixed.
- Slight performance gain. To determine the column data type we used the is function which performs a few checks to ensure the column data type matches. This was slow, as these checks would pile up every quickly when comparing all data types for many fields in a table/view. The easier and faster alternative is to simply go off of the column's columnType property.
- Some changes had to be made at the type level in the ORM package for better compatibility with drizzle-valibot.
And a set of new features
createSelectSchema
function now also accepts views and enums.- New function:
createUpdateSchema
, for use in updating queries. - New function:
createSchemaFactory
, to provide more advanced options and to avoid bloating the parameters of the other schema functions
Bug fixes
- [FEATURE]: publish packages un-minified
- Don't allow unknown keys in drizzle-zod refinement
- [BUG]:drizzle-zod not working with pgSchema
- Add createUpdateSchema to drizzle-zod
- [BUG]:drizzle-zod produces wrong type
- [BUG]:Drizzle-zod:Boolean and Serial types from Schema are defined as enum when using CreateInsertSchema and CreateSelectSchema
- [BUG]: Drizzle typebox enum array wrong schema and type
- [BUG]:drizzle-zod not working with pgSchema
- [BUG]: drizzle-zod not parsing arrays correctly
- [BUG]: Drizzle typebox not supporting array
- [FEATURE]: Export factory functions from drizzle-zod to allow usage with extended Zod classes
- [FEATURE]: Add support for new pipe syntax for drizzle-valibot
- [BUG]: drizzle-zod's createInsertSchema() can't handle column of type vector
- [BUG]: drizzle-typebox fails to map geometry column to type-box schema
- [BUG]: drizzle-valibot does not provide types for returned schemas
- [BUG]: Drizzle-typebox types SQLite real field to string
- [BUG]: drizzle-zod: documented usage generates type error with exactOptionalPropertyTypes
- [BUG]: drizzle-zod does not respect/count db type range
- [BUG]: drizzle-zod not overriding optional
- [BUG]:drizzle-zod doesn't accept custom id value
- [FEATURE]: Support for Database Views in Drizzle Zod
- [BUG]: drizzle-valibot return type any
- [BUG]: drizzle-zod Type generation results in undefined types
- [BUG]: GeneratedAlwaysAs
- [FEATURE]: $inferSelect on a view
- [BUG]:Can't infer props from view in schema
[email protected]
Starting from this update, the PostgreSQL dialect will align with the behavior of all other dialects. It will no longer include IF NOT EXISTS
, $DO
, or similar statements, which could cause incorrect DDL statements to not fail when an object already exists in the database and should actually fail.
This change marks our first step toward several major upgrades we are preparing:
- An updated and improved migration workflow featuring commutative migrations, a revised folder structure, and enhanced collaboration capabilities for migrations.
- Better support for Xata migrations.
- Compatibility with CockroachDB (achieving full compatibility will only require removing serial fields from the migration folder).
[email protected]
- Fix SingleStore generate migrations command
0.37.0
New Dialects
🎉 SingleStore
dialect is now available in Drizzle
Thanks to the SingleStore team for creating a PR with all the necessary changes to support the MySQL-compatible part of SingleStore. You can already start using it with Drizzle. The SingleStore team will also help us iterate through updates and make more SingleStore-specific features available in Drizzle
import { int, singlestoreTable, varchar } from 'drizzle-orm/singlestore-core';
import { drizzle } from 'drizzle-orm/singlestore';
export const usersTable = singlestoreTable('users_table', {
id: int().primaryKey(),
name: varchar({ length: 255 }).notNull(),
age: int().notNull(),
email: varchar({ length: 255 }).notNull().unique(),
});
...
const db = drizzle(process.env.DATABASE_URL!);
db.select()...
You can check out our Getting started guides to try SingleStore!
New Drivers
🎉 SQLite Durable Objects
driver is now available in Drizzle
You can now query SQLite Durable Objects in Drizzle!
For the full example, please check our Get Started Section
/// <reference types="@cloudflare/workers-types" />
import { drizzle, DrizzleSqliteDODatabase } from 'drizzle-orm/durable-sqlite';
import { DurableObject } from 'cloudflare:workers'
import { migrate } from 'drizzle-orm/durable-sqlite/migrator';
import migrations from '../drizzle/migrations';
import { usersTable } from './db/schema';
export class MyDurableObject1 extends DurableObject {
storage: DurableObjectStorage;
db: DrizzleSqliteDODatabase<any>;
constructor(ctx: DurableObjectState, env: Env) {
super(ctx, env);
this.storage = ctx.storage;
this.db = drizzle(this.storage, { logger: false });
}
async migrate() {
migrate(this.db, migrations);
}
async insert(user: typeof usersTable.$inferInsert) {
await this.db.insert(usersTable).values(user);
}
async select() {
return this.db.select().from(usersTable);
}
}
export default {
/**
* This is the standard fetch handler for a Cloudflare Worker
*
* @param request - The request submitted to the Worker from the client
* @param env - The interface to reference bindings declared in wrangler.toml
* @param ctx - The execution context of the Worker
* @returns The response to be sent back to the client
*/
async fetch(request: Request, env: Env): Promise<Response> {
const id: DurableObjectId = env.MY_DURABLE_OBJECT1.idFromName('durable-object');
const stub = env.MY_DURABLE_OBJECT1.get(id);
await stub.migrate();
await stub.insert({
name: 'John',
age: 30,
email: '[email protected]',
})
console.log('New user created!')
const users = await stub.select();
console.log('Getting all users from the database: ', users)
return new Response();
}
}
Bug fixes
[email protected]
New Dialects
🎉 SingleStore
dialect is now available in Drizzle
Thanks to the SingleStore team for creating a PR with all the necessary changes to support the MySQL-compatible part of SingleStore. You can already start using it with Drizzle. The SingleStore team will also help us iterate through updates and make more SingleStore-specific features available in Drizzle
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
dialect: 'singlestore',
out: './drizzle',
schema: './src/db/schema.ts',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
You can check out our Getting started guides to try SingleStore!
New Drivers
🎉 SQLite Durable Objects
driver is now available in Drizzle
You can now query SQLite Durable Objects in Drizzle!
For the full example, please check our Get Started Section
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
out: './drizzle',
schema: './src/db/schema.ts',
dialect: 'sqlite',
driver: 'durable-sqlite',
});
0.36.4
New Package: drizzle-seed
Note
drizzle-seed
can only be used with [email protected]
or higher. Versions lower than this may work at runtime but could have type issues and identity column issues, as this patch was introduced in [email protected]
Full Reference
The full API reference and package overview can be found in our official documentation
Basic Usage
In this example we will create 10 users with random names and ids
import { pgTable, integer, text } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";
import { seed } from "drizzle-seed";
const users = pgTable("users", {
id: integer().primaryKey(),
name: text().notNull(),
});
async function main() {
const db = drizzle(process.env.DATABASE_URL!);
await seed(db, { users });
}
main();
Options
count
By default, the seed
function will create 10 entities.
However, if you need more for your tests, you can specify this in the seed options object
await seed(db, schema, { count: 1000 });
seed
If you need a seed to generate a different set of values for all subsequent runs, you can define a different number
in the seed
option. Any new number will generate a unique set of values
await seed(db, schema, { seed: 12345 });
The full API reference and package overview can be found in our official documentation
Features
Added OVERRIDING SYSTEM VALUE
api to db.insert()
If you want to force you own values for GENERATED ALWAYS AS IDENTITY
columns, you can use OVERRIDING SYSTEM VALUE
As PostgreSQL docs mentions
In an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified value takes precedence
await db.insert(identityColumnsTable).overridingSystemValue().values([
{ alwaysAsIdentity: 2 },
]);
Added .$withAuth()
API for Neon HTTP driver
Using this API, Drizzle will send you an auth token to authorize your query. It can be used with any query available in Drizzle by simply adding .$withAuth()
before it. This token will be used for a specific query
Examples
const token = 'HdncFj1Nm'
await db.$withAuth(token).select().from(usersTable);
await db.$withAuth(token).update(usersTable).set({ name: 'CHANGED' }).where(eq(usersTable.name, 'TARGET'))
Bug Fixes
0.36.3
New Features
Support for UPDATE ... FROM
in PostgreSQL and SQLite
As the SQLite documentation mentions:
Note
The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database.
The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table
against other tables in the database in order to help compute which rows need updating and what
the new values should be on those rows
Similarly, the PostgreSQL documentation states:
Note
A table expression allowing columns from other tables to appear in the WHERE condition and update expressions
Drizzle also supports this feature starting from this version
For example, current query:
await db
.update(users)
.set({ cityId: cities.id })
.from(cities)
.where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')))
Will generate this sql
update "users" set "city_id" = "cities"."id"
from "cities"
where ("cities"."name" = $1 and "users"."name" = $2)
-- params: [ 'Seattle', 'John' ]
You can also alias tables that are joined (in PG, you can also alias the updating table too).
const c = alias(cities, 'c');
await db
.update(users)
.set({ cityId: c.id })
.from(c);
Will generate this sql
update "users" set "city_id" = "c"."id"
from "cities" "c"
In PostgreSQL, you can also return columns from the joined tables.
const updatedUsers = await db
.update(users)
.set({ cityId: cities.id })
.from(cities)
.returning({ id: users.id, cityName: cities.name });
Will generate this sql
update "users" set "city_id" = "cities"."id"
from "cities"
returning "users"."id", "cities"."name"
Support for INSERT INTO ... SELECT
in all dialects
As the SQLite documentation mentions:
Note
The second form of the INSERT statement contains a SELECT statement instead of a VALUES clause.
A new entry is inserted into the table for each row of data returned by executing the SELECT statement.
If a column-list is specified, the number of columns in the result of the SELECT must be the same as
the number of items in the column-list. Otherwise, if no column-list is specified, the number of
columns in the result of the SELECT must be the same as the number of columns in the table.
Any SELECT statement, including compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses,
may be used in an INSERT statement of this form.
Caution
To avoid a parsing ambiguity, the SELECT statement should always contain a WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause is present. Without the WHERE clause, the parser does not know if the token "ON" is part of a join constraint on the SELECT, or the beginning of the upsert-clause.
As the PostgreSQL documentation mentions:
Note
A query (SELECT statement) that supplies the rows to be inserted
And as the MySQL documentation mentions:
Note
With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables
Drizzle supports the current syntax for all dialects, and all of them share the same syntax. Let's review some common scenarios and API usage.
There are several ways to use select inside insert statements, allowing you to choose your preferred approach:
- You can pass a query builder inside the select function.
- You can use a query builder inside a callback.
- You can pass an SQL template tag with any custom select query you want to use
Query Builder
const insertedEmployees = await db
.insert(employees)
.select(
db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
)
.returning({
id: employees.id,
name: employees.name
});
const qb = new QueryBuilder();
await db.insert(employees).select(
qb.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);
Callback
await db.insert(employees).select(
() => db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);
await db.insert(employees).select(
(qb) => qb.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);
SQL template tag
await db.insert(employees).select(
sql`select "users"."name" as "name" from "users" where "users"."role" = 'employee'`
);
await db.insert(employees).select(
() => sql`select "users"."name" as "name" from "users" where "users"."role" = 'employee'`
);