0.23.1

• Fixed an issue with pushing SQLite tables with foreign keys defined using custom names. Previously, such tables were always recreated on each push. In this release, the foreign key name will not affect the detection of differences between the code schema and the database schema. This is because SQLite cannot have foreign key constraint names specified in a DDL create table query, so they should not be used in the diff process

• When dropping a column from an SQLite table, there was an issue with accessing the primary key of an undefined column

• Fixed issues with sequences, #504, #2643

v0.23.0

🎉 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'
  }
});

🎉 Migrations support for 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");

🎉 Migrations support for 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.

🎉 Migrations support for 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!"),
}),

🎉 Migrations support for 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()

🎉 Migrations support for 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.

0.22.8

• 🐛 [BUG]: TypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys') - big thanks to @LavaToaster for the fix!

0.22.6

• 🐛 Fixed drizzle-kit up of snapshots from v6 to v7
• 🐛 [BUG]: extensionsFilters: ['postgis'] still trying to delete spatial_ref_sys - #2464

0.22.5

• 🐛 [BUG]: Recreating pg index in version 0.31(orm) + 0.22(kit) fails - #2470
• 🐛 [BUG]: Drizzle migrator doesn't work with uppercase names when creating indexes - #2457
• 🐛 [BUG]: 'left' column name not escaped in index - #2425
• 🐛 [BUG]: drizzle-kit push TypeError Cannot use 'in' operator to search for 'default' in undefined - #2385
• 🐛 [BUG]: Breaking change in the new "PostgreSQL Indexes API" missing quotes for uppercase column letters - #2413
• 🐛 [BUG]: drizzle-kit migrate fail "applying migrations...error: column "authorid" does not exist" - #2423

0.22.4

• Removed data loss triggers on push when adding a NOT NULL constraint to a column and when removing the default value from a column. These actions will now be performed immediately, and if there are any NULL values in the column, you will receive an error from the database

0.22.3

• 🐛 Fix Cannot use 'in' operator to search for 'default' in undefined error on push and generate

0.22.2

• 🐛 Fixed index-on-expressions sql statement generation if the expression contains a ,. This should fix problems for tsvector indexes, such as:

titleSearchIndex: index('title_search_index').using('gin', sql`to_tsvector('english', ${table.title})`)

0.22.1

Bug fixes

• 🐛 [BUG]: postgis geometry error: type "geometry(point)" does not exist

Improvements

• 🎉 Drizzle Studio now supports raw responses from D1 HTTP. This means that Drizzle Studio now has full support for D1, and all queries should work as expected!

• 🎉 Refactor the d1-http driver to properly show the table row count

0.22.0

New Features

🎉 Full support for indexes in PostgreSQL

The previous Drizzle+PostgreSQL indexes API was incorrect and was not aligned with the PostgreSQL documentation. The good thing is that it was not used in queries, and drizzle-kit didn't support all properties for indexes. This means we can now change the API to the correct one and provide full support for it in drizzle-kit

Previous API

• No way to define SQL expressions inside .on.
• .using and .on in our case are the same thing, so the API is incorrect here.
• .asc(), .desc(), .nullsFirst(), and .nullsLast() should be specified for each column or expression on indexes, but not on an index itself.

// Index declaration reference
index('name')
  .on(table.column1, table.column2, ...) or .onOnly(table.column1, table.column2, ...)
  .concurrently()
  .using(sql``) // sql expression
  .asc() or .desc()
  .nullsFirst() or .nullsLast()
  .where(sql``) // sql expression

Current API

// First example, with `.on()`
index('name')
  .on(table.column1.asc(), table.column2.nullsFirst(), ...) or .onOnly(table.column1.desc().nullsLast(), table.column2, ...)
  .concurrently()
  .where(sql``)
  .with({ fillfactor: '70' })

// Second Example, with `.using()`
index('name')
  .using('btree', table.column1.asc(), sql`lower(${table.column2})`, table.column1.op('text_ops'))
  .where(sql``) // sql expression
  .with({ fillfactor: '70' })

🎉 Support for new types

Drizzle Kit can now handle:

• point and line from PostgreSQL
• vector from the PostgreSQL pg_vector extension
• geometry from the PostgreSQL PostGIS extension

🎉 New param in drizzle.config - extensionsFilters

The PostGIS extension creates a few internal tables in the public schema. This means that if you have a database with the PostGIS extension and use push or introspect, all those tables will be included in diff operations. In this case, you would need to specify tablesFilter, find all tables created by the extension, and list them in this parameter.

We have addressed this issue so that you won't need to take all these steps. Simply specify extensionsFilters with the name of the extension used, and Drizzle will skip all the necessary tables.

Currently, we only support the postgis option, but we plan to add more extensions if they create tables in the public schema.

The postgis option will skip the geography_columns, geometry_columns, and spatial_ref_sys tables

import { defineConfig } from 'drizzle-kit'

export default defaultConfig({
  dialect: "postgresql",
  extensionsFilters: ["postgis"],
})

Improvements

👍 Update zod schemas for database credentials and write tests to all the positive/negative cases

👍 Support full set of SSL params in kit config, provide types from node:tls connection

import { defineConfig } from 'drizzle-kit'

export default defaultConfig({
  dialect: "postgresql",
  dbCredentials: {
    ssl: true, //"require" | "allow" | "prefer" | "verify-full" | options from node:tls
  }
})

import { defineConfig } from 'drizzle-kit'

export default defaultConfig({
  dialect: "mysql",
  dbCredentials: {
    ssl: "", // string | SslOptions (ssl options from mysql2 package)
  }
})

👍 Normilized SQLite urls for libsql and better-sqlite3 drivers

Those drivers have different file path patterns, and Drizzle Kit will accept both and create a proper file path format for each

👍 Updated MySQL and SQLite index-as-expression behavior

In this release MySQL and SQLite will properly map expressions into SQL query. Expressions won't be escaped in string but columns will be

export const users = sqliteTable(
  'users',
  {
    id: integer('id').primaryKey(),
    email: text('email').notNull(),
  },
  (table) => ({
    emailUniqueIndex: uniqueIndex('emailUniqueIndex').on(sql`lower(${table.email})`),
  }),
);

-- before
CREATE UNIQUE INDEX `emailUniqueIndex` ON `users` (`lower("users"."email")`);

-- now
CREATE UNIQUE INDEX `emailUniqueIndex` ON `users` (lower("email"));

Bug Fixes

• [BUG]: multiple constraints not added (only the first one is generated) - #2341
• Drizzle Studio: Error: Connection terminated unexpectedly - #435
• Unable to run sqlite migrations local - #432
• error: unknown option '--config' - #423

How push and generate works for indexes

Limitations

You should specify a name for your index manually if you have an index on at least one expression

Example

index().on(table.id, table.email) // will work well and name will be autogeneretaed
index('my_name').on(table.id, table.email) // will work well

// but

index().on(sql`lower(${table.email})`) // error
index('my_name').on(sql`lower(${table.email})`) // will work well

Push won't generate statements if these fields(list below) were changed in an existing index:

• expressions inside .on() and .using()
• .where() statements
• operator classes .op() on columns

If you are using push workflows and want to change these fields in the index, you would need to:

• Comment out the index
• Push
• Uncomment the index and change those fields
• Push again

For the generate command, drizzle-kit will be triggered by any changes in the index for any property in the new drizzle indexes API, so there are no limitations here.