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",
});