0.32.0
Release notes for drizzle-orm@0.32.0
and drizzle-kit@0.23.0
It's not mandatory to upgrade both packages, but if you want to use the new features in both queries and migrations, you will need to upgrade both packages
New Features
🎉 MySQL $returningId()
function
MySQL itself doesn't have native support for RETURNING
after using INSERT
. There is only one way to do it for primary keys
with autoincrement
(or serial
) types, where you can access insertId
and affectedRows
fields. We've prepared an automatic way for you to handle such cases with Drizzle and automatically receive all inserted IDs as separate objects
import { boolean, int, text, mysqlTable } from 'drizzle-orm/mysql-core';
const usersTable = mysqlTable('users', {
id: int('id').primaryKey(),
name: text('name').notNull(),
verified: boolean('verified').notNull().default(false),
});
const result = await db.insert(usersTable).values([{ name: 'John' }, { name: 'John1' }]).$returningId();
// ^? { id: number }[]
Also with Drizzle, you can specify a primary key
with $default
function that will generate custom primary keys at runtime. We will also return those generated keys for you in the $returningId()
call
import { varchar, text, mysqlTable } from 'drizzle-orm/mysql-core';
import { createId } from '@paralleldrive/cuid2';
const usersTableDefFn = mysqlTable('users_default_fn', {
customId: varchar('id', { length: 256 }).primaryKey().$defaultFn(createId),
name: text('name').notNull(),
});
const result = await db.insert(usersTableDefFn).values([{ name: 'John' }, { name: 'John1' }]).$returningId();
// ^? { customId: string }[]
If there is no primary keys -> type will be
{}[]
for such queries
🎉 PostgreSQL Sequences
You can now specify sequences in Postgres within any schema you need and define all the available properties
Example
import { pgSchema, pgSequence } from "drizzle-orm/pg-core";
// No params specified
export const customSequence = pgSequence("name");
// Sequence with params
export const customSequence = pgSequence("name", {
startWith: 100,
maxValue: 10000,
minValue: 100,
cycle: true,
cache: 10,
increment: 2
});
// Sequence in custom schema
export const customSchema = pgSchema('custom_schema');
export const customSequence = customSchema.sequence("name");
🎉 PostgreSQL Identity Columns
Source: As mentioned, the serial
type in Postgres is outdated and should be deprecated. Ideally, you should not use it. Identity columns
are the recommended way to specify sequences in your schema, which is why we are introducing the identity columns
feature
Example
import { pgTable, integer, text } from 'drizzle-orm/pg-core'
export const ingredients = pgTable("ingredients", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
name: text("name").notNull(),
description: text("description"),
});
You can specify all properties available for sequences in the .generatedAlwaysAsIdentity()
function. Additionally, you can specify custom names for these sequences
PostgreSQL docs reference.
🎉 PostgreSQL Generated Columns
You can now specify generated columns on any column supported by PostgreSQL to use with generated columns
Example with generated column for tsvector
Note: we will add
tsVector
column type before latest release
import { SQL, sql } from "drizzle-orm";
import { customType, index, integer, pgTable, text } from "drizzle-orm/pg-core";
const tsVector = customType<{ data: string }>({
dataType() {
return "tsvector";
},
});
export const test = pgTable(
"test",
{
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
content: text("content"),
contentSearch: tsVector("content_search", {
dimensions: 3,
}).generatedAlwaysAs(
(): SQL => sql`to_tsvector('english', ${test.content})`
),
},
(t) => ({
idx: index("idx_content_search").using("gin", t.contentSearch),
})
);
In case you don't need to reference any columns from your table, you can use just sql
template or a string
export const users = pgTable("users", {
id: integer("id"),
name: text("name"),
generatedName: text("gen_name").generatedAlwaysAs(sql`hello world!`),
generatedName1: text("gen_name1").generatedAlwaysAs("hello world!"),
}),
🎉 MySQL Generated Columns
You can now specify generated columns on any column supported by MySQL to use with generated columns
You can specify both stored
and virtual
options, for more info you can check MySQL docs
Also MySQL has a few limitation for such columns usage, which is described here
Drizzle Kit will also have limitations for push
command:
-
You can't change the generated constraint expression and type using
push
. Drizzle-kit will ignore this change. To make it work, you would need todrop the column
,push
, and thenadd a column with a new expression
. This was done due to the complex mapping from the database side, where the schema expression will be modified on the database side and, on introspection, we will get a different string. We can't be sure if you changed this expression or if it was changed and formatted by the database. As long as these are generated columns andpush
is mostly used for prototyping on a local database, it should be fast todrop
andcreate
generated columns. Since these columns aregenerated
, all the data will be restored -
generate
should have no limitations
Example
export const users = mysqlTable("users", {
id: int("id"),
id2: int("id2"),
name: text("name"),
generatedName: text("gen_name").generatedAlwaysAs(
(): SQL => sql`${schema2.users.name} || 'hello'`,
{ mode: "stored" }
),
generatedName1: text("gen_name1").generatedAlwaysAs(
(): SQL => sql`${schema2.users.name} || 'hello'`,
{ mode: "virtual" }
),
}),
In case you don't need to reference any columns from your table, you can use just sql
template or a string
in .generatedAlwaysAs()
🎉 SQLite Generated Columns
You can now specify generated columns on any column supported by SQLite to use with generated columns
You can specify both stored
and virtual
options, for more info you can check SQLite docs
Also SQLite has a few limitation for such columns usage, which is described here
Drizzle Kit will also have limitations for push
and generate
command:
-
You can't change the generated constraint expression with the stored type in an existing table. You would need to delete this table and create it again. This is due to SQLite limitations for such actions. We will handle this case in future releases (it will involve the creation of a new table with data migration).
-
You can't add a
stored
generated expression to an existing column for the same reason as above. However, you can add avirtual
expression to an existing column. -
You can't change a
stored
generated expression in an existing column for the same reason as above. However, you can change avirtual
expression. -
You can't change the generated constraint type from
virtual
tostored
for the same reason as above. However, you can change fromstored
tovirtual
.
New Drizzle Kit features
🎉 Migrations support for all the new orm features
PostgreSQL sequences, identity columns and generated columns for all dialects
🎉 New flag --force
for drizzle-kit push
You can auto-accept all data-loss statements using the push command. It's only available in CLI parameters. Make sure you always use it if you are fine with running data-loss statements on your database
🎉 New migrations
flag prefix
You can now customize migration file prefixes to make the format suitable for your migration tools:
index
is the default type and will result in0001_name.sql
file names;supabase
andtimestamp
are equal and will result in20240627123900_name.sql
file names;unix
will result in unix seconds prefixes1719481298_name.sql
file names;none
will omit the prefix completely;
Example: Supabase migrations format
import { defineConfig } from "drizzle-kit";
export default defineConfig({
dialect: "postgresql",
migrations: {
prefix: 'supabase'
}
});