v0.32.0-beta

[AndriiSherman]

Preview release for drizzle-orm@0.32.0 and drizzle-kit@0.23.0

Current features are preview for 0.32.0 and 0.23.0 versions and are available under generated tag

To install this preview release, you would need to:

npm i drizzle-orm@generated
npm i drizzle-kit@generated -D

This release will create a GitHub discussions post, so feel free to report threr any issues you find

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

🎉 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:

1. 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 to drop the column, push, and then add 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 and push is mostly used for prototyping on a local database, it should be fast to drop and create generated columns. Since these columns are generated, all the data will be restored

2. 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:

1. 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).

2. You can't add a stored generated expression to an existing column for the same reason as above. However, you can add a virtual expression to an existing column.

3. You can't change a stored generated expression in an existing column for the same reason as above. However, you can change a virtual expression.

4. You can't change the generated constraint type from virtual to stored for the same reason as above. However, you can change from stored to virtual.

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 in 0001_name.sql file names;
• supabase and timestamp are equal and will result in 20240627123900_name.sql file names;
• unix will result in unix seconds prefixes 1719481298_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'
  }
});

---

This discussion was created from the release v0.32.0-beta.

[kevinmarrec]

Amazing features 🎉

Correct me if I'm wrong but the snippet for "New migrations flag prefix" should be :

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  migrations: {
    prefix: 'supabase'
  }
});

i.e. not nested in dbCredentials.

[AndriiSherman]

oh, yeah, my bad. Changing that

[abdelfattahradwan]

Any plans to add the bytea type from PostgreSQL? I can define a custom type, but it should be included by default.

[oscar-b]

When updating a table to use identity:

- id: serial('id').primaryKey(),
+ id: integer('id').primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),

npx drizzle-kit push fails:

npx drizzle-kit push --force
drizzle-kit: v0.23.0
drizzle-orm: v0.32.0-5cc2ae0

No config path provided, using default path
Reading config file '/Users/oscar/dev/.../drizzle.config.ts'
Using 'pg' driver for database querying
[✓] Pulling schema from database...
error: relation "users_id_seq" already exists
    at /Users/oscar/dev/.../node_modules/drizzle-kit/bin.cjs:79344:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query (/Users/oscar/dev/.../node_modules/drizzle-kit/bin.cjs:121286:26)
    at async pgPush (/Users/oscar/dev/.../node_modules/drizzle-kit/bin.cjs:124229:13)
    at async _Command.<anonymous> (/Users/oscar/dev/.../node_modules/drizzle-kit/bin.cjs:131485:7) {
  length: 107,
  severity: 'ERROR',
  code: '42P07',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'heap.c',
  line: '1149',
  routine: 'heap_create_with_catalog'
}