0.34.1

• Fixed dynamic imports for CJS and MJS in the /connect module

0.34.0

Breaking changes and migrate guide for Turso users

If you are using Turso and libsql, you will need to upgrade your drizzle.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 the migrate 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 common drizzle.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";

export default defineConfig({
  dialect: "sqlite",
  schema: "./schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: "database.db",
  },
  breakpoints: true,
  verbose: true,
  strict: true,
});

After

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  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 use dialect: "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-breakpoint
CREATE TABLE `__new_worker` (
  `id` integer PRIMARY KEY NOT NULL,
  `name` text NOT NULL,
  `salary` text NOT NULL,
  `job_id` integer,
  FOREIGN KEY (`job_id`) REFERENCES `job`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
INSERT INTO `__new_worker`("id", "name", "salary", "job_id") SELECT "id", "name", "salary", "job_id" FROM `worker`;
--> statement-breakpoint
DROP TABLE `worker`;
--> statement-breakpoint
ALTER TABLE `__new_worker` RENAME TO `worker`;
--> statement-breakpoint
PRAGMA 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 the LibSQL documentation

LIMITATIONS

• Dropping foreign key will cause table recreation.

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

CREATE TABLE `users` (
  `id` integer NOT NULL,
  `name` integer,
  `age` integer PRIMARY KEY NOT NULL
  FOREIGN KEY (`name`) REFERENCES `users1`("id") ON UPDATE no action ON 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.

CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);
CREATE INDEX i2 ON parent(e);
CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

CREATE TABLE child1(f, g REFERENCES parent(a));                        -- Ok
CREATE TABLE child2(h, i REFERENCES parent(b));                        -- Ok
CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));  -- Ok
CREATE TABLE child4(l, m REFERENCES parent(e));                        -- Error!
CREATE TABLE child5(n, o REFERENCES parent(f));                        -- Error!
CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c));  -- Error!
CREATE TABLE child7(r REFERENCES 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

A new and easy way to start using drizzle

Current and the only way to do, is to define client yourself and pass it to drizzle

const client = new Pool({ url: '' });
drizzle(client, { logger: true });

But we want to introduce you to a new API, which is a simplified method in addition to the existing one.

Most clients will have a few options to connect, starting with the easiest and most common one, and allowing you to control your client connection as needed.

Let's use node-postgres as an example, but the same pattern can be applied to all other clients

// Finally, one import for all available clients and dialects!
import { drizzle } from 'drizzle-orm/connect'

// Choose a client and use a connection URL — nothing else is needed!
const db1 = await drizzle("node-postgres", process.env.POSTGRES_URL);

// If you need to pass a logger, schema, or other configurations, you can use an object and specify the client-specific URL in the connection
const db2 = await drizzle("node-postgres", {
  connection: process.env.POSTGRES_URL,
  logger: true
});

// And finally, if you need to use full client/driver-specific types in connections, you can use a URL or host/port/etc. as an object inferred from the underlying client connection types
const db3 = await drizzle("node-postgres", {
  connection: {
    connectionString: process.env.POSTGRES_URL,
  },
});

const db4 = await drizzle("node-postgres", {
  connection: {
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_NAME,
    ssl: true,
  },
});

A few clients will have a slightly different API due to their specific behavior. Let's take a look at them:

For aws-data-api-pg, Drizzle will require resourceArn, database, and secretArn, along with any other AWS Data API client types for the connection, such as credentials, region, etc.

drizzle("aws-data-api-pg", {
  connection: {
    resourceArn: "",
    database: "",
    secretArn: "",
  },
});

For d1, the CloudFlare Worker types as described in the documentation here will be required.

drizzle("d1", {
  connection: env.DB // CloudFlare Worker Types
})

For vercel-postgres, nothing is needed since Vercel automatically retrieves the POSTGRES_URL from the .env file. You can check this documentation for more info

drizzle("vercel-postgres")

Note that the first example with the client is still available and not deprecated. You can use it if you don't want to await the drizzle object. The new way of defining drizzle is designed to make it easier to import from one place and get autocomplete for all the available clients

Optional names for columns and callback in drizzle table

We believe that schema definition in Drizzle is extremely powerful and aims to be as close to SQL as possible while adding more helper functions for JS runtime values.

However, there are a few areas that could be improved, which we addressed in this release. These include:

• Unnecessary database column names when TypeScript keys are essentially just copies of them
• A callback that provides all column types available for a specific table.

Let's look at an example with PostgreSQL (this applies to all the dialects supported by Drizzle)

Previously

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

The previous table definition will still be valid in the new release, but it can be replaced with this instead

import { pgTa...

drizzle-kit@0.25.0

Breaking changes and migrate guide for Turso users

If you are using Turso and libsql, you will need to upgrade your drizzle.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 the migrate 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 common drizzle.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";

export default defineConfig({
  dialect: "sqlite",
  schema: "./schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: "database.db",
  },
  breakpoints: true,
  verbose: true,
  strict: true,
});

After

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  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 use dialect: "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-breakpoint
CREATE TABLE `__new_worker` (
  `id` integer PRIMARY KEY NOT NULL,
  `name` text NOT NULL,
  `salary` text NOT NULL,
  `job_id` integer,
  FOREIGN KEY (`job_id`) REFERENCES `job`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
INSERT INTO `__new_worker`("id", "name", "salary", "job_id") SELECT "id", "name", "salary", "job_id" FROM `worker`;
--> statement-breakpoint
DROP TABLE `worker`;
--> statement-breakpoint
ALTER TABLE `__new_worker` RENAME TO `worker`;
--> statement-breakpoint
PRAGMA 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 the LibSQL documentation

LIMITATIONS

• Dropping foreign key will cause table recreation.

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

CREATE TABLE `users` (
  `id` integer NOT NULL,
  `name` integer,
  `age` integer PRIMARY KEY NOT NULL
  FOREIGN KEY (`name`) REFERENCES `users1`("id") ON UPDATE no action ON 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.

CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);
CREATE INDEX i2 ON parent(e);
CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

CREATE TABLE child1(f, g REFERENCES parent(a));                        -- Ok
CREATE TABLE child2(h, i REFERENCES parent(b));                        -- Ok
CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));  -- Ok
CREATE TABLE child4(l, m REFERENCES parent(e));                        -- Error!
CREATE TABLE child5(n, o REFERENCES parent(f));                        -- Error!
CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c));  -- Error!
CREATE TABLE child7(r REFERENCES 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

New casing param in drizzle-orm and drizzle-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 usually snake_case in the database and camelCase 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";

export const ingredients = 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 to snake_case

const db = await drizzle('node-postgres', { connection: '', casing: 'snake_case' })

For drizzle-kit migrations generation you should also specify casing 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";

export default defineConfig({
  dialect: "postgresql",
  schema: "./schema.ts",
  dbCredentials: {
    url: "postgresql://postgres:password@localhost:5432/db",
  },
  casing: "snake_case",
});

drizzle-kit@0.24.2

New Features

🎉 Support for pglite driver

You can now use pglite with all drizzle-kit commands, including Drizzle Studio!

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  driver: "pglite",
  schema: "./schema.ts",
  dbCredentials: {
    url: "local-pg.db",
  },
  verbose: true,
  strict: true,
});

Bug fixes

• mysql-kit: fix GENERATED ALWAYS AS ... NOT NULL - #2824

drizzle-kit@0.24.1

Bug fixes

Big thanks to @L-Mario564 for his PR. It conflicted in most cases with a PR that was merged, but we incorporated some of his logic. Merging it would have caused more problems and taken more time to resolve, so we just took a few things from his PR, like removing "::" mappings in introspect and some array type default handlers

What was fixed

1. The Drizzle Kit CLI was not working properly for the introspect command.
2. Added the ability to use column names with special characters for all dialects.
3. Included PostgreSQL sequences in the introspection process.
4. Reworked array type introspection and added all test cases.
5. Fixed all (we hope) default issues in PostgreSQL, where ::<type> was included in the introspected output.
6. preserve casing option was broken

Tickets that were closed

• [BUG]: invalid schema generation with drizzle-kit introspect:pg
• [BUG][mysql introspection]: TS error when introspect column including colon
• [BUG]: Unhandled defaults when introspecting postgres db
• [BUG]: PostgreSQL Enum Naming and Schema Typing Issue
• [BUG]: drizzle-kit instrospect command generates syntax error on varchar column types
• [BUG]: Introspecting varchar[] type produces syntactically invalid schema.ts
• [BUG]: introspect:pg column not using generated enum name
• [BUG]: drizzle-kit introspect casing "preserve" config not working
• [BUG]: drizzle-kit introspect fails on required param that is defined
• [BUG]: Error when running npx drizzle-kit introspect: "Expected object, received string"
• [BUG]: Missing index names when running introspect command [MYSQL]
• [BUG]: drizzle-kit introspect TypeError: Cannot read properties of undefined (reading 'toLowerCase')
• [BUG]: Wrong column name when using PgEnum.array()
• [BUG]: Incorrect Schema Generated when introspecting extisting pg database
• [⚠️🐞BUG]: index() missing argument after introspection, causes tsc error that fails the build
• [BUG]: drizzle-kit introspect small errors
• [BUG]: Missing bigint import in drizzle-kit introspect

0.33.0

Breaking changes (for some of postgres.js users)

Bugs fixed for this breaking change

• [BUG]: jsonb always inserted as a json string when using postgres-js
• [BUG]: jsonb type on postgres implement incorrectly

As we are doing with other drivers, we've changed the behavior of PostgreSQL-JS to pass raw JSON values, the same as you see them in the database. So if you are using the PostgreSQL-JS driver and passing data to Drizzle elsewhere, please check the new behavior of the client after it is passed to Drizzle.

We will update it to ensure it does not override driver behaviors, but this will be done as a complex task for everything in Drizzle in other releases

If you were using postgres-js with jsonb fields, you might have seen stringified objects in your database, while drizzle insert and select operations were working as expected.

You need to convert those fields from strings to actual JSON objects. To do this, you can use the following query to update your database:

if you are using jsonb:

update table_name
set jsonb_column = (jsonb_column #>> '{}')::jsonb;

if you are using json:

update table_name
set json_column = (json_column #>> '{}')::json;

We've tested it in several cases, and it worked well, but only if all stringified objects are arrays or objects. If you have primitives like strings, numbers, booleans, etc., you can use this query to update all the fields

if you are using jsonb:

UPDATE table_name
SET jsonb_column = CASE
    -- Convert to JSONB if it is a valid JSON object or array
    WHEN jsonb_column #>> '{}' LIKE '{%' OR jsonb_column #>> '{}' LIKE '[%' THEN
        (jsonb_column #>> '{}')::jsonb
    ELSE
        jsonb_column
END
WHERE
    jsonb_column IS NOT NULL;

if you are using json:

UPDATE table_name
SET json_column = CASE
    -- Convert to JSON if it is a valid JSON object or array
    WHEN json_column #>> '{}' LIKE '{%' OR json_column #>> '{}' LIKE '[%' THEN
        (json_column #>> '{}')::json
    ELSE
        json_column
END
WHERE json_column IS NOT NULL;

If nothing works for you and you are blocked, please reach out to me @AndriiSherman. I will try to help you!

Bug Fixes

• [BUG]: boolean mode not working with prepared statements (bettersqlite) - thanks @veloii
• [BUG]: isTable helper function is not working - thanks @hajek-raven
• [BUG]: Documentation is outdated on inArray and notInArray Methods - thanks @RemiPeruto

drizzle-kit@0.24.0

Breaking changes (for SQLite users)

Fixed Composite primary key order is not consistent by removing sort in SQLite and to be consistent with the same logic in PostgreSQL and MySQL

The issue that may arise for SQLite users with any driver using composite primary keys is that the order in the database may differ from the Drizzle schema.

• If you are using push, you MAY be prompted to update your table with a new order of columns in the composite primary key. You will need to either change it manually in the database or push the changes, but this may lead to data loss, etc.

• If you are using generate, you MAY also be prompted to update your table with a new order of columns in the composite primary key. You can either keep that migration or skip it by emptying the SQL migration file.

If nothing works for you and you are blocked, please reach out to me @AndriiSherman. I will try to help you!

Bug fixes

• [BUG] When using double type columns, import is not inserted - thanks @Karibash
• [BUG] A number value is specified as the default for a column of type char - thanks @Karibash
• [BUG]: Array default in migrations are wrong - thanks @L-Mario564
• [FEATURE]: Simpler default array fields - thanks @L-Mario564
• [BUG]: drizzle-kit generate succeeds but generates invalid SQL for default([]) - Postgres - thanks @L-Mario564
• [BUG]: Incorrect type for array column default value - thanks @L-Mario564
• [BUG]: error: column is of type integer[] but default expression is of type integer - thanks @L-Mario564
• [BUG]: Default value in array generating wrong migration file - thanks @L-Mario564
• [BUG]: enum as array, not possible? - thanks @L-Mario564

0.32.2

• Fix AWS Data API type hints bugs in RQB
• Fix set transactions in MySQL bug - thanks @roguesherlock
• Add forwaring dependencies within useLiveQuery, fixes #2651 - thanks @anstapol
• Export additional types from SQLite package, like AnySQLiteUpdate - thanks @veloii

drizzle-kit@0.23.2

• Fixed a bug in PostgreSQL with push and introspect where the schemaFilter object was passed. It was detecting enums even in schemas that were not defined in the schemaFilter.
• Fixed the drizzle-kit up command to work as expected, starting from the sequences release.

0.32.1

• Fix typings for indexes and allow creating indexes on 3+ columns mixing columns and expressions - thanks @lbguilherme!
• Added support for "limit 0" in all dialects - closes #2011 - thanks @sillvva!
• Make inArray and notInArray accept empty list, closes #1295 - thanks @RemiPeruto!
• fix typo in lt typedoc - thanks @dalechyn!
• fix wrong example in README.md - thanks @7flash!