v0.30.4

Compare Source

• Fix bug that generates incorrect syntax when introspect in mysql
• Fix a bug that caused incorrect syntax output when introspect in unsigned columns

v0.30.3

Compare Source

SingleStorepush andgenerate improvements

As SingleStore did not support certain DDL statements before this release, you might encounter an error indicating that some schema changes cannot be applied due to a database issue. Starting from this version, drizzle-kit will detect such cases and initiate table recreation with data transfer between the tables

Bug fixes

• [BUG] If the index name is the same as the generated name, it will be empty and a type error will occur

v0.30.2

Compare Source

• Fix certificates generation utility for Drizzle Studio;[BUG]: [drizzle-kit]: drizzle-kit dependency on drizzle-studio perms error

v0.30.1

Compare Source

New Features

drizzle-kit export

To make drizzle-kit integration with other migration tools, like Atlas much easier, we've prepared a new command calledexport. It will translate your drizzle schema in SQL representation(DDL) statements and outputs to the console

// schema.tsimport{pgTable,serial,text}from'drizzle-orm/pg-core'exportconstusers=pgTable('users',{id:serial('id').primaryKey(),email:text('email').notNull(),name:text('name')});

Running

npx drizzle-kitexport

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-kitexport --sql

v0.30.0

Compare Source

Starting from this update, the PostgreSQL dialect will align with the behavior of all other dialects. It will no longer includeIF 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).

v0.29.1

Compare Source

Fixes

• Forward args correctly when using withReplica feature#​1536. Thanks@​Angelelz
• Fix selectDistinctOn not working with multiple columns#​1466. Thanks@​L-Mario564

New Features/Helpers

🎉 Detailed JSDoc for all query builders in all dialects - thanks@​realmikesolo

You can now access more information, hints, documentation links, etc. while developing and using JSDoc right in your IDE. Previously, we had them only for filter expressions, but now you can see them for all parts of the Drizzle query builder

🎉 New helpers for aggregate functions in SQL - thanks@​L-Mario564

Remember, aggregation functions are often used with the GROUP BY clause of the SELECT statement. So if you are selecting using aggregating functions and other columns in one query,
be sure to use the.groupBy clause

Here is a list of functions and equivalent usingsql template

count

awaitdb.select({value:count()}).from(users);awaitdb.select({value:count(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`count('*'))`.mapWith(Number)}).from(users);awaitdb.select({value:sql`count(${users.id})`.mapWith(Number)}).from(users);

countDistinct

awaitdb.select({value:countDistinct(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`count(${users.id})`.mapWith(Number)}).from(users);

avg

awaitdb.select({value:avg(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`avg(${users.id})`.mapWith(String)}).from(users);

avgDistinct

awaitdb.select({value:avgDistinct(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`avg(distinct${users.id})`.mapWith(String)}).from(users);

sum

awaitdb.select({value:sum(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`sum(${users.id})`.mapWith(String)}).from(users);

sumDistinct

awaitdb.select({value:sumDistinct(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`sum(distinct${users.id})`.mapWith(String)}).from(users);

max

awaitdb.select({value:max(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`max(${expression})`.mapWith(users.id)}).from(users);

min

awaitdb.select({value:min(users.id)}).from(users);// It's equivalent to writingawaitdb.select({value:sql`min(${users.id})`.mapWith(users.id)}).from(users);

New Packages

🎉 ESLint Drizzle Plugin

For cases where it's impossible to perform type checks for specific scenarios, or where it's possible but error messages would be challenging to understand, we've decided to create an ESLint package with recommended rules. This package aims to assist developers in handling crucial scenarios during development

Big thanks to@​Angelelz for initiating the development of this package and transferring it to the Drizzle Team's npm

Install

[ npm| yarn| pnpm| bun ] install eslint eslint-plugin-drizzle

You can install those packages for typescript support in your IDE

[ npm| yarn| pnpm| bun ] install @​typescript-eslint/eslint-plugin @​typescript-eslint/parser

Usage

Create a.eslintrc.yml file, adddrizzle to theplugins, and specify the rules you want to use. You can find a list of all existing rules below

root:trueparser:'@​typescript-eslint/parser'parserOptions:project:'./tsconfig.json'plugins:  -drizzlerules:'drizzle/enforce-delete-with-where':"error"'drizzle/enforce-update-with-where':"error"

All config

This plugin exports anall config that makes use of all rules (except for deprecated ones).

root:trueextends:  -"plugin:drizzle/all"parser:'@​typescript-eslint/parser'parserOptions:project:'./tsconfig.json'plugins:  -drizzle

At the moment,all is equivalent torecommended

root:trueextends:  -"plugin:drizzle/recommended"parser:'@​typescript-eslint/parser'parserOptions:project:'./tsconfig.json'plugins:  -drizzle

Rules

enforce-delete-with-where: Enforce usingdelete with the.where() clause in the.delete() statement. Most of the time, you don't need to delete all rows in the table and require some kind ofWHERE statements.

Error Message:

Without `.where(...)` you will delete all the rows in a table. If you didn't want to do it, please use `db.delete(...).where(...)` instead. Otherwise you can ignore this rule here

Optionally, you can define adrizzleObjectName in the plugin options that accept astring orstring[]. This is useful when you have objects or classes with a delete method that's not from Drizzle. Such adelete method will trigger the ESLint rule. To avoid that, you can define the name of the Drizzle object that you use in your codebase (like db) so that the rule would only trigger if the delete method comes from this object:

Example, config 1:

"rules": {"drizzle/enforce-delete-with-where": ["error"]}

classMyClass{publicdelete(){return{}}}constmyClassObj=newMyClass();// ---> Will be triggered by ESLint RulemyClassObj.delete()constdb=drizzle(...)// ---> Will be triggered by ESLint Ruledb.delete()

Example, config 2:

"rules": {"drizzle/enforce-delete-with-where": ["error", {"drizzleObjectName": ["db"] }],}

classMyClass{publicdelete(){return{}}}constmyClassObj=newMyClass();// ---> Will NOT be triggered by ESLint RulemyClassObj.delete()constdb=drizzle(...)// ---> Will be triggered by ESLint Ruledb.delete()

enforce-update-with-where: Enforce usingupdate with the.where() clause in the.update() statement. Most of the time, you don't need to update all rows in the table and require some kind ofWHERE statements.

Error Message:

Without `.where(...)` you will update all the rows in a table. If you didn't want to do it, please use `db.update(...).set(...).where(...)` instead. Otherwise you can ignore this rule here

Optionally, you can define adrizzleObjectName in the plugin options that accept astring orstring[]. This is useful when you have objects or classes with a delete method that's not from Drizzle. Such asupdate method will trigger the ESLint rule. To avoid that, you can define the name of the Drizzle object that you use in your codebase (like db) so that the rule would only trigger if the delete method comes from this object:

Example, config 1:

"rules": {"drizzle/enforce-update-with-where": ["error"]}

classMyClass{publicupdate(){return{}}}constmyClassObj=newMyClass();// ---> Will be triggered by ESLint RulemyClassObj.update()constdb=drizzle(...)// ---> Will be triggered by ESLint Ruledb.update()

Example, config 2:

"rules": {"drizzle/enforce-update-with-where": ["error", {"drizzleObjectName": ["db"] }],}

classMyClass{publicupdate(){return{}}}constmyClassObj=newMyClass();// ---> Will NOT be triggered by ESLint RulemyClassObj.update()constdb=drizzle(...)// ---> Will be triggered by ESLint Ruledb.update()

v0.29.0

Compare Source

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';exportdefaultdefineConfig({dialect:'singlestore',out:'./drizzle',schema:'./src/db/schema.ts',dbCredentials:{url:process.env.DATABASE_URL!,},});

You can check out ourGetting 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 ourGet Started Section

import'dotenv/config';import{defineConfig}from'drizzle-kit';exportdefaultdefineConfig({out:'./drizzle',schema:'./src/db/schema.ts',dialect:'sqlite',driver:'durable-sqlite',});

v0.28.1

Compare Source

Bug fixes

• Fixed typos in repository: thanks@​armandsalle,@​masto,@​wackbyte,@​Asher-JH,@​MaxLeiter
• fix: wrong dialect set in mysql/sqlite introspect

v0.28.0

Compare Source

Improvements

• Added an OHM static imports checker to identify unexpected imports within a chain of imports in the drizzle-kit repo. For example, it checks if drizzle-orm is imported before drizzle-kit and verifies if the drizzle-orm import is available in your project.
• Adding more columns to Supabase auth.users table schema - thanks@​nicholasdly

Bug Fixes

• [BUG]: [drizzle-kit]: Fix breakpoints option cannot be disabled - thanks@​klotztech
• [BUG]: drizzle-kit introspect: SMALLINT import missing and incorrect DECIMAL UNSIGNED handling - thanks@​L-Mario564
• Unsigned tinyints preventing migrations - thanks@​L-Mario564
• [BUG]: Can't parse float(8,2) from database (precision and scale and/or unsigned breaks float types) - thanks@​L-Mario564
• [BUG]: PgEnum generated migration doesn't escape single quotes - thanks@​L-Mario564
• [BUG]: single quote not escaped correctly in migration file - thanks@​L-Mario564
• [BUG]: Migrations does not escape single quotes - thanks@​L-Mario564
• [BUG]: Issue with quoted default string values - thanks@​L-Mario564
• [BUG]: SQl commands in wrong roder - thanks@​L-Mario564
• [BUG]: Time with precision in drizzle-orm/pg-core adds double-quotes around type - thanks@​L-Mario564
• [BUG]: Postgres push fails due to lack of quotes - thanks@​L-Mario564
• [BUG]: TypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys') - thanks@​L-Mario564
• [BUG]: drizzle-kit introspect generates CURRENT_TIMESTAMP without sql operator on date column - thanks@​L-Mario564
• [BUG]: Drizzle-kit introspect doesn't pull correct defautl statement - thanks@​L-Mario564
• [BUG]: Problem on MacBook - This statement does not return data. Use run() instead - thanks@​L-Mario564
• [BUG]: Enum column names that are used as arrays are not quoted - thanks@​L-Mario564
• [BUG]: drizzle-kit generate ignores index operators - thanks@​L-Mario564
• dialect param config error message is wrong - thanks@​L-Mario564
• [BUG]: Error setting default enum field values - thanks@​L-Mario564
• [BUG]: drizzle-kit does not respect the order of columns configured in primaryKey() - thanks@​L-Mario564
• [BUG]: Cannot drop Unique Constraint MySQL - thanks@​L-Mario564

v0.27.2

Compare Source

• Fix[BUG]: Undefined properties when using drizzle-kit push
• Fix TypeError: Cannot read properties of undefined (reading 'isRLSEnabled')
• Fix push bugs, when pushing a schema with linked policy to a table fromdrizzle-orm/supabase

v0.27.1

Compare Source

• 🎉 Added support forNeon HTTP driver

import{neon,neonConfig}from'@​neondatabase/serverless';import{drizzle}from'drizzle-orm/neon-http';neonConfig.fetchConnectionCache=true;constsql=neon(process.env.DRIZZLE_DATABASE_URL!);constdb=drizzle(sql);db.select(...)

v0.27.0

Compare Source

Correct behavior when installed in a monorepo (multiple Drizzle instances)

Replacing allinstanceof statements with a customis() 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 useis() instead ofinstanceof 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 support

awaitdb.selectDistinct().from(usersDistinctTable).orderBy(usersDistinctTable.id,usersDistinctTable.name,);

Also,distinct on clause is available for PostgreSQL:

awaitdb.selectDistinctOn([usersDistinctTable.id]).from(usersDistinctTable).orderBy(usersDistinctTable.id,);awaitdb.selectDistinctOn([usersDistinctTable.name],{name:usersDistinctTable.name}).from(usersDistinctTable,).orderBy(usersDistinctTable.name);

bigint andboolean support for SQLite

Contributed by@​MrRahulRamkumar (#​558),@​raducristianpopa (#​411) and@​meech-ward (#​725)

constusers=sqliteTable('users',{bigintCol:blob('bigint',{mode:'bigint'}).notNull(),boolCol:integer('bool',{mode:'boolean'}).notNull(),});

DX improvements

• Added verbose type error when relational queries are used on a database type without a schema generic
• Fixwhere callback in RQB for tables without relations

Various docs improvements

• Fix joins docs typo (#​522) by@​arjunyel
• Add Supabase guide to readme (#​690) by@​saltcod
• Make the column type in sqlite clearer (#​717) by@​shairez

v0.26.2

Compare Source

• Updated internal versions for the drizzle-kit and drizzle-orm packages. Changes were introduced in the last minor release, and you are required to upgrade both packages to ensure they work as expected

v0.26.1

Compare Source

• Fixdata is malformed for views

v0.26.0

Compare Source

While writing this update, we found one bug that may occur with views in MySQL and SQLite, so please use thedrizzle-kit@0.26.1 release

New Features

Checks support indrizzle-kit

You can use drizzle-kit to manage yourcheck constraint defined in drizzle-orm schema definition

For example current drizzle table:

import{sql}from"drizzle-orm";import{check,pgTable}from"drizzle-orm/pg-core";exportconstusers=pgTable("users",(c)=>({id:c.uuid().defaultRandom().primaryKey(),username:c.text().notNull(),age:c.integer(),}),(table)=>({checkConstraint:check("age_check",sql`${table.age} > 21`),}));

will be generated into

CREATETABLEIF NOT EXISTS"users" ("id" uuidPRIMARY KEY DEFAULT gen_random_uuid()NOT NULL,"username"textNOT NULL,"age"integer,CONSTRAINT"age_check"CHECK ("users"."age">21));

The same is supported in all dialects

Limitations

• generate will work as expected for all check constraint changes.
• push will detect only check renames and will recreate the constraint. All other changes to SQL won't be detected and will be ignored.

So, if you want to change the constraint's SQL definition using onlypush, you would need to manually comment out the constraint,push, then put it back with the new SQL definition andpush one more time.

Views support indrizzle-kit

You can use drizzle-kit to manage yourviews defined in drizzle-orm schema definition. It will work with all existing dialects and view options

PostgreSQL

For example current drizzle table:

import{sql}from"drizzle-orm";import{check,pgMaterializedView,pgTable,pgView,}from"drizzle-orm/pg-core";exportconstusers=pgTable("users",(c)=>({id:c.uuid().defaultRandom().primaryKey(),username:c.text().notNull(),age:c.integer(),}),(table)=>({checkConstraint:check("age_check",sql`${table.age} > 21`),}));exportconstsimpleView=pgView("simple_users_view").as((qb)=>qb.select().from(users));exportconstmaterializedView=pgMaterializedView("materialized_users_view").as((qb)=>qb.select().from(users));

will be generated into

CREATETABLEIF NOT EXISTS"users" ("id" uuidPRIMARY KEY DEFAULT gen_random_uuid()NOT NULL,"username"textNOT NULL,"age"integer,CONSTRAINT"age_check"CHECK ("users"."age">21));CREATEVIEW "public"."simple_users_view"AS (select"id","username","age"from"users");CREATE MATERIALIZED VIEW"public"."materialized_users_view"AS (select"id","username","age"from"users");

Views supported in all dialects, but materialized views are supported only in PostgreSQL

Limitations

• generate will work as expected for all view changes
• push limitations:

1. If you want to change the view's SQL definition using onlypush, you would need to manually comment out the view,push, then put it back with the new SQL definition andpush one more time.

Updates for PostgreSQL enums behavior

We've updated enum behavior in Drizzle with PostgreSQL:

• Add value after or before in enum: With this change, Drizzle will now respect the order of values in the enum and allow adding new values after or before a specific one.

• Support for dropping a value from an enum: In this case, Drizzle will attempt to alter all columns using the enum to text, then drop the existing enum and create a new one with the updated set of values. After that, all columns previously using the enum will be altered back to the new enum.

If the deleted enum value was used by a column, this process will result in a database error.

• Support for dropping an enum

• Support for moving enums between schemas

• Support for renaming enums

v0.25.0

Compare Source

Breaking changes and migrate guide for Turso users

If you are using Turso and libsql, you will need to upgrade yourdrizzle.config and@libsql/client package.

1. This version of drizzle-orm will only work with@libsql/client@0.10.0 or higher if you are using themigrate function. For other use cases, you can continue using previous versions(But the suggestion is to upgrade)
   To install the latest version, use the command:

npm i @​libsql/client@latest

2. Previously, we had a commondrizzle.config for SQLite and Turso users, which allowed a shared strategy for both dialects. Starting with this release, we are introducing the turso dialect in drizzle-kit. We will evolve and improve Turso as a separate dialect with its own migration strategies.

Before

import{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({dialect:"sqlite",schema:"./schema.ts",out:"./drizzle",dbCredentials:{url:"database.db",},breakpoints:true,verbose:true,strict:true,});

After

import{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({dialect:"turso",schema:"./schema.ts",out:"./drizzle",dbCredentials:{url:"database.db",},breakpoints:true,verbose:true,strict:true,});

If you are using only SQLite, you can usedialect: "sqlite"

LibSQL/Turso and Sqlite migration updates

SQLite "generate" and "push" statements updates

Starting from this release, we will no longer generate comments like this:

'/*\n SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually'+'\n Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php'+'\n                  https://www.sqlite.org/lang_altertable.html'+'\n                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3'+"\n\n Due to that we don't generate migration automatically and it has to be done manually"+'\n*/'

We will generate a set of statements, and you can decide if it's appropriate to create data-moving statements instead. Here is an example of the SQL file you'll receive now:

PRAGMA foreign_keys=OFF;--> statement-breakpointCREATETABLE `__new_worker` (`id`integerPRIMARY KEYNOT NULL,`name`textNOT NULL,`salary`textNOT NULL,`job_id`integer,FOREIGN KEY (`job_id`)REFERENCES`job`(`id`)ONUPDATE no actionON DELETE no action);--> statement-breakpointINSERT INTO`__new_worker`("id","name","salary","job_id")SELECT"id","name","salary","job_id"FROM`worker`;--> statement-breakpointDROPTABLE`worker`;--> statement-breakpointALTERTABLE`__new_worker` RENAME TO`worker`;--> statement-breakpointPRAGMA foreign_keys=ON;

LibSQL/Turso "generate" and "push" statements updates

Since LibSQL supports more ALTER statements than SQLite, we can generate more statements without recreating your schema and moving all the data, which can be potentially dangerous for production environments.

LibSQL and Turso will now have a separate dialect in the Drizzle config file, meaning that we will evolve Turso and LibSQL independently from SQLite and will aim to support as many features as Turso/LibSQL offer.

With the updated LibSQL migration strategy, you will have the ability to:

• Change Data Type: Set a new data type for existing columns.
• Set and Drop Default Values: Add or remove default values for existing columns.
• Set and Drop NOT NULL: Add or remove the NOT NULL constraint on existing columns.
• Add References to Existing Columns: Add foreign key references to existing columns

You can find more information in theLibSQL documentation

LIMITATIONS

• Dropping foreign key will cause table recreation.

This is because LibSQL/Turso does not support dropping this type of foreign key.

CREATETABLE `users` (`id`integerNOT NULL,`name`integer,`age`integerPRIMARY KEYNOT NULLFOREIGN KEY (`name`)REFERENCES`users1`("id")ONUPDATE no actionON DELETE no action);

• If the table has indexes, altering columns will cause index recreation:
  Drizzle-Kit will drop the indexes, modify the columns, and then create the indexes.

• Adding or dropping composite foreign keys is not supported and will cause table recreation.

• Primary key columns can not be altered and will cause table recreation.

• Altering columns that are part of foreign key will cause table recreation.

NOTES

• You can create a reference on any column type, but if you want to insert values, the referenced column must have a unique index or primary key.

CREATETABLEparent(aPRIMARY KEY, b UNIQUE, c, d, e, f);CREATEUNIQUE INDEXi1ON parent(c, d);CREATEINDEXi2ON parent(e);CREATEUNIQUE INDEXi3ON parent(f COLLATE nocase);CREATETABLEchild1(f, gREFERENCES parent(a));-- OkCREATETABLEchild2(h, iREFERENCES parent(b));-- OkCREATETABLEchild3(j, k,FOREIGN KEY(j, k)REFERENCES parent(c, d));-- OkCREATETABLEchild4(l, mREFERENCES parent(e));-- Error!CREATETABLEchild5(n, oREFERENCES parent(f));-- Error!CREATETABLEchild6(p, q,FOREIGN KEY(p, q)REFERENCES parent(b, c));-- Error!CREATETABLEchild7(rREFERENCES parent(c));-- Error!

NOTE: The foreign key for the table child5 is an error because, although the parent key column has a unique index, the index uses a different collating sequence.

See more:https://www.sqlite.org/foreignkeys.html

Newcasing param indrizzle-orm anddrizzle-kit

There are more improvements you can make to your schema definition. The most common way to name your variables in a database and in TypeScript code is usuallysnake_case in the database andcamelCase in the code. For this case, in Drizzle, you can now define a naming strategy in your database to help Drizzle map column keys automatically. Let's take a table from the previous example and make it work with the new casing API in Drizzle

Table can now become:

import{pgTable}from"drizzle-orm/pg-core";exportconstingredients=pgTable("ingredients",(t)=>({id:t.uuid().defaultRandom().primaryKey(),name:t.text().notNull(),description:t.text(),inStock:t.boolean().default(true),}));

As you can see,inStock doesn't have a database name alias, but by defining the casing configuration at the connection level, all queries will automatically map it tosnake_case

constdb=awaitdrizzle('node-postgres',{connection:'',casing:'snake_case'})

Fordrizzle-kit migrations generation you should also specifycasing param in drizzle config, so you can be sure you casing strategy will be applied to drizzle-kit as well

import{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({dialect:"postgresql",schema:"./schema.ts",dbCredentials:{url:"postgresql://postgres:password@localhost:5432/db",},casing:"snake_case",});