v0.31.0

Compare Source

Breaking changes

Note:drizzle-orm@0.31.0 can be used withdrizzle-kit@0.22.0 or higher. The same applies to Drizzle Kit. If you run a Drizzle Kit command, it will check and prompt you for an upgrade (if needed). You can check for Drizzle Kit updates.below

PostgreSQL indexes API was changed

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 referenceindex('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'})

New Features

🎉 "pg_vector" extension support

There is no specific code to create an extension inside the Drizzle schema. We assume that if you are using vector types, indexes, and queries, you have a PostgreSQL database with thepg_vector extension installed.

You can now specify indexes forpg_vector and utilizepg_vector functions for querying, ordering, etc.

Let's take a few examples ofpg_vector indexes from thepg_vector docs and translate them to Drizzle

L2 distance, Inner product and Cosine distance

// CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);// CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);// CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);consttable=pgTable('items',{embedding:vector('embedding',{dimensions:3})},(table)=>({l2:index('l2_index').using('hnsw',table.embedding.op('vector_l2_ops'))ip:index('ip_index').using('hnsw',table.embedding.op('vector_ip_ops'))cosine:index('cosine_index').using('hnsw',table.embedding.op('vector_cosine_ops'))}))

L1 distance, Hamming distance and Jaccard distance - added in pg_vector 0.7.0 version

// CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);// CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);// CREATE INDEX ON items USING hnsw (embedding bit_jaccard_ops);consttable=pgTable('table',{embedding:vector('embedding',{dimensions:3})},(table)=>({l1:index('l1_index').using('hnsw',table.embedding.op('vector_l1_ops'))hamming:index('hamming_index').using('hnsw',table.embedding.op('bit_hamming_ops'))bit:index('bit_jaccard_index').using('hnsw',table.embedding.op('bit_jaccard_ops'))}))

For queries, you can use predefined functions for vectors or create custom ones using the SQL template operator.

You can also use the following helpers:

import{l2Distance,l1Distance,innerProduct,cosineDistance,hammingDistance,jaccardDistance}from'drizzle-orm'l2Distance(table.column,[3,1,2])// table.column <-> '[3, 1, 2]'l1Distance(table.column,[3,1,2])// table.column <+> '[3, 1, 2]'innerProduct(table.column,[3,1,2])// table.column <#> '[3, 1, 2]'cosineDistance(table.column,[3,1,2])// table.column <=> '[3, 1, 2]'hammingDistance(table.column,'101')// table.column <~> '101'jaccardDistance(table.column,'101')// table.column <%> '101'

Ifpg_vector has some other functions to use, you can replicate implimentation from existing one we have. Here is how it can be done

exportfunctionl2Distance(column:SQLWrapper|AnyColumn,value:number[]|string[]|TypedQueryBuilder<any>|string,):SQL{if(is(value,TypedQueryBuilder<any>)||typeofvalue==='string'){returnsql`${column} <->${value}`;}returnsql`${column} <->${JSON.stringify(value)}`;}

Name it as you wish and change the operator. This example allows for a numbers array, strings array, string, or even a select query. Feel free to create any other type you want or even contribute and submit a PR

Examples

Let's take a few examples ofpg_vector queries from thepg_vector docs and translate them to Drizzle

import{l2Distance}from'drizzle-orm';// SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;db.select().from(items).orderBy(l2Distance(items.embedding,[3,1,2]))// SELECT embedding <-> '[3,1,2]' AS distance FROM items;db.select({distance:l2Distance(items.embedding,[3,1,2])})// SELECT * FROM items ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;constsubquery=db.select({embedding:items.embedding}).from(items).where(eq(items.id,1));db.select().from(items).orderBy(l2Distance(items.embedding,subquery)).limit(5)// SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;db.select({innerProduct:sql`(${maxInnerProduct(items.embedding,[3,1,2])}) * -1`}).from(items)// and more!

🎉 New PostgreSQL types:point,line

You can now usepoint andline fromPostgreSQL Geometric Types

Typepoint has 2 modes for mappings from the database:tuple andxy.

• tuple will be accepted for insert and mapped on select to a tuple. So, the database Point(1,2) will be typed as [1,2] with drizzle.

• xy will be accepted for insert and mapped on select to an object with x, y coordinates. So, the database Point(1,2) will be typed as{ x: 1, y: 2 } with drizzle

constitems=pgTable('items',{point:point('point'),pointObj:point('point_xy',{mode:'xy'}),});

Typeline has 2 modes for mappings from the database:tuple andabc.

• tuple will be accepted for insert and mapped on select to a tuple. So, the database Line{1,2,3} will be typed as [1,2,3] with drizzle.

• abc will be accepted for insert and mapped on select to an object with a, b, and c constants from the equationAx + By + C = 0. So, the database Line{1,2,3} will be typed as{ a: 1, b: 2, c: 3 } with drizzle.

constitems=pgTable('items',{line:line('line'),lineObj:point('line_abc',{mode:'abc'}),});

🎉 Basic "postgis" extension support

There is no specific code to create an extension inside the Drizzle schema. We assume that if you are using postgis types, indexes, and queries, you have a PostgreSQL database with thepostgis extension installed.

geometry type from postgis extension:

constitems=pgTable('items',{geo:geometry('geo',{type:'point'}),geoObj:geometry('geo_obj',{type:'point',mode:'xy'}),geoSrid:geometry('geo_options',{type:'point',mode:'xy',srid:4000}),});

mode
Typegeometry has 2 modes for mappings from the database:tuple andxy.

• tuple will be accepted for insert and mapped on select to a tuple. So, the database geometry will be typed as [1,2] with drizzle.
• xy will be accepted for insert and mapped on select to an object with x, y coordinates. So, the database geometry will be typed as{ x: 1, y: 2 } with drizzle

type

The current release has a predefined type:point, which is thegeometry(Point) type in the PostgreSQL PostGIS extension. You can specify any string there if you want to use some other type

Drizzle Kit updates:drizzle-kit@0.22.0

Release notes here are partially duplicated fromdrizzle-kit@0.22.0

New Features

🎉 Support for new types

Drizzle Kit can now handle:

• point andline from PostgreSQL
• vector from the PostgreSQLpg_vector extension
• geometry from the PostgreSQLPostGIS extension

🎉 New param in drizzle.config -extensionsFilters

The PostGIS extension creates a few internal tables in thepublic schema. This means that if you have a database with the PostGIS extension and usepush orintrospect, all those tables will be included indiff operations. In this case, you would need to specifytablesFilter, 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 specifyextensionsFilters with the name of the extension used, and Drizzle will skip all the necessary tables.

Currently, we only support thepostgis option, but we plan to add more extensions if they create tables in thepublic schema.

Thepostgis option will skip thegeography_columns,geometry_columns, andspatial_ref_sys tables

import{defineConfig}from'drizzle-kit'exportdefaultdefaultConfig({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'exportdefaultdefaultConfig({dialect:"postgresql",dbCredentials:{ssl:true,//"require" | "allow" | "prefer" | "verify-full" | options from node:tls}})

import{defineConfig}from'drizzle-kit'exportdefaultdefaultConfig({dialect:"mysql",dbCredentials:{ssl:"",// string | SslOptions (ssl options from mysql2 package)}})

Normilized SQLite urls forlibsql andbetter-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

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

-- beforeCREATEUNIQUE INDEX `emailUniqueIndex`ON`users` (`lower("users"."email")`);-- nowCREATEUNIQUE 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

Howpush andgenerate 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 autogeneretaedindex('my_name').on(table.id,table.email)// will work well// butindex().on(sql`lower(${table.email})`)// errorindex('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 usingpush 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 thegenerate 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.

v0.30.6

Compare Source

Bug fixes

• [BUG]: d1 push locally is not working - thanks@​mabels and@​RomanNabukhotnyi
• [BUG] Cloudflare D1: drizzle-kit push is not working (error 7500 SQLITE_AUTH) - thanks@​mabels and@​RomanNabukhotnyi

v0.30.5

Compare Source

New Features

AddedGel dialect support andgel-js client support

Drizzle is getting a newGel dialect with its own types and Gel-specific logic. In this first iteration, almost all query-building features have been copied from thePostgreSQL dialect since Gel is fully PostgreSQL-compatible. The only change in this iteration is the data types. The Gel dialect has a different set of available data types, and all mappings for these types have been designed to avoid any extra conversions on Drizzle's side. This means you will insert and select exactly the same data as supported by the Gel protocol.

Drizzle + Gel integration will work only throughdrizzle-kit pull. Drizzle won't supportgenerate,migrate, orpush features in this case. Instead, drizzle-kit is used solely to pull the Drizzle schema from the Gel database, which can then be used in yourdrizzle-orm queries.

The Gel + Drizzle workflow:

1. Use thegel CLI to manage your schema.
2. Use thegel CLI to generate and apply migrations to the database.
3. Use drizzle-kit to pull the Gel database schema into a Drizzle schema.
4. Use drizzle-orm with gel-js to query the Gel database.

On the drizzle-kit side you can now usedialect: "gel"

// drizzle.config.tsimport{defineConfig}from'drizzle-kit';exportdefaultdefineConfig({dialect:'gel',});

For a complete Get Started tutorial you can use our new guides:

• Get Started with Drizzle and Gel in a new project
• Get Started with Drizzle and Gel in a existing project

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

• Fix SingleStore generate migrations command

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

• Fix:[BUG]: When using RLS policies and Views, the view is the last clause generated

v0.27.0

Compare Source

This version ofdrizzle-jit requiresdrizzle-orm@0.36.0 to enable all new features

New Features

Row-Level Security (RLS)

With Drizzle, you can enable Row-Level Security (RLS) for any Postgres table, create policies with various options, and define and manage the roles those policies apply to.

Drizzle supports a raw representation of Postgres policies and roles that can be used in any way you want. This works with popular Postgres database providers such asNeon andSupabase.

In Drizzle, we have specific predefined RLS roles and functions for RLS with both database providers, but you can also define your own logic.

Enable RLS

If you just want to enable RLS on a table without adding policies, you can use.enableRLS()

As mentioned in the PostgreSQL documentation:

If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.
Operations that apply to the whole table, such as TRUNCATE and REFERENCES, are not subject to row security.

import{integer,pgTable}from'drizzle-orm/pg-core';exportconstusers=pgTable('users',{id:integer(),}).enableRLS();

If you add a policy to a table, RLS will be enabled automatically. So, there’s no need to explicitly enable RLS when adding policies to a table.

Roles

Currently, Drizzle supports defining roles with a few different options, as shown below. Support for more options will be added in a future release.

import{pgRole}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin',{createRole:true,createDb:true,inherit:true});

If a role already exists in your database, and you don’t want drizzle-kit to ‘see’ it or include it in migrations, you can mark the role as existing.

import{pgRole}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin').existing();

Policies

To fully leverage RLS, you can define policies within a Drizzle table.

In PostgreSQL, policies should be linked to an existing table. Since policies are always associated with a specific table, we decided that policy definitions should be defined as a parameter ofpgTable

Example of pgPolicy with all available properties

import{sql}from'drizzle-orm';import{integer,pgPolicy,pgRole,pgTable}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin');exportconstusers=pgTable('users',{id:integer(),},(t)=>[pgPolicy('policy',{as:'permissive',to:admin,for:'delete',using:sql``,withCheck:sql``,}),]);

Link Policy to an existing table

There are situations where you need to link a policy to an existing table in your database.
The most common use case is with database providers likeNeon orSupabase, where you need to add a policy
to their existing tables. In this case, you can use the.link() API

import{sql}from"drizzle-orm";import{pgPolicy}from"drizzle-orm/pg-core";import{authenticatedRole,realtimeMessages}from"drizzle-orm/supabase";exportconstpolicy=pgPolicy("authenticated role insert policy",{for:"insert",to:authenticatedRole,using:sql``,}).link(realtimeMessages);

Migrations

If you are using drizzle-kit to manage your schema and roles, there may be situations where you want to refer to roles that are not defined in your Drizzle schema. In such cases, you may want drizzle-kit to skip managing these roles without having to define each role in your drizzle schema and marking it with.existing().

In these cases, you can useentities.roles indrizzle.config.ts. For a complete reference, refer to the thedrizzle.config.ts documentation.

By default,drizzle-kit does not manage roles for you, so you will need to enable this feature indrizzle.config.ts.

// drizzle.config.tsimport{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({dialect:'postgresql',schema:"./drizzle/schema.ts",dbCredentials:{url:process.env.DATABASE_URL!},verbose:true,strict:true,entities:{roles:true}});

In case you need additional configuration options, let's take a look at a few more examples.

You have anadmin role and want to exclude it from the list of manageable roles

// drizzle.config.tsimport{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({  ...entities:{    roles:{exclude:['admin']}}});

You have anadmin role and want to include it in the list of manageable roles

// drizzle.config.tsimport{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({  ...entities:{    roles:{include:['admin']}}});

If you are usingNeon and want to exclude Neon-defined roles, you can use the provider option

// drizzle.config.tsimport{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({  ...entities:{    roles:{provider:'neon'}}});

If you are usingSupabase and want to exclude Supabase-defined roles, you can use the provider option

// drizzle.config.tsimport{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({  ...entities:{    roles:{provider:'supabase'}}});

You may encounter situations where Drizzle is slightly outdated compared to new roles specified by your database provider.
In such cases, you can use theprovider option andexclude additional roles:

// drizzle.config.tsimport{defineConfig}from"drizzle-kit";exportdefaultdefineConfig({  ...entities:{    roles:{provider:'supabase',exclude:['new_supabase_role']}}});

RLS on views

With Drizzle, you can also specify RLS policies on views. For this, you need to usesecurity_invoker in the view's WITH options. Here is a small example:

...exportconstroomsUsersProfiles=pgView("rooms_users_profiles").with({securityInvoker:true,}).as((qb)=>qb.select({        ...getTableColumns(roomsUsers),email:profiles.email,}).from(roomsUsers).innerJoin(profiles,eq(roomsUsers.userId,profiles.id)));

Using with Neon

The Neon Team helped us implement their vision of a wrapper on top of our raw policies API. We defined a specific
/neon import with thecrudPolicy function that includes predefined functions and Neon's default roles.

Here's an example of how to use thecrudPolicy function:

import{crudPolicy}from'drizzle-orm/neon';import{integer,pgRole,pgTable}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin');exportconstusers=pgTable('users',{id:integer(),},(t)=>[crudPolicy({role:admin,read:true,modify:false}),]);

This policy is equivalent to:

import{sql}from'drizzle-orm';import{integer,pgPolicy,pgRole,pgTable}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin');exportconstusers=pgTable('users',{id:integer(),},(t)=>[pgPolicy(`crud-${admin.name}-policy-insert`,{for:'insert',to:admin,withCheck:sql`false`,}),pgPolicy(`crud-${admin.name}-policy-update`,{for:'update',to:admin,using:sql`false`,withCheck:sql`false`,}),pgPolicy(`crud-${admin.name}-policy-delete`,{for:'delete',to:admin,using:sql`false`,}),pgPolicy(`crud-${admin.name}-policy-select`,{for:'select',to:admin,using:sql`true`,}),]);

Neon exposes predefinedauthenticated andanaonymous roles and related functions. If you are usingNeon for RLS, you can use these roles, which are marked as existing, and the related functions in your RLS queries.

// drizzle-orm/neonexportconstauthenticatedRole=pgRole('authenticated').existing();exportconstanonymousRole=pgRole('anonymous').existing();exportconstauthUid=(userIdColumn:AnyPgColumn)=>sql`(select auth.user_id() =${userIdColumn})`;

For example, you can use theNeon predefined roles and functions like this:

import{sql}from'drizzle-orm';import{authenticatedRole}from'drizzle-orm/neon';import{integer,pgPolicy,pgRole,pgTable}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin');exportconstusers=pgTable('users',{id:integer(),},(t)=>[pgPolicy(`policy-insert`,{for:'insert',to:authenticatedRole,withCheck:sql`false`,}),]);

Using with Supabase

We also have a/supabase import with a set of predefined roles marked as existing, which you can use in your schema.
This import will be extended in a future release with more functions and helpers to make using RLS andSupabase simpler.

// drizzle-orm/supabaseexportconstanonRole=pgRole('anon').existing();exportconstauthenticatedRole=pgRole('authenticated').existing();exportconstserviceRole=pgRole('service_role').existing();exportconstpostgresRole=pgRole('postgres_role').existing();exportconstsupabaseAuthAdminRole=pgRole('supabase_auth_admin').existing();

For example, you can use theSupabase predefined roles like this:

import{sql}from'drizzle-orm';import{serviceRole}from'drizzle-orm/supabase';import{integer,pgPolicy,pgRole,pgTable}from'drizzle-orm/pg-core';exportconstadmin=pgRole('admin');exportconstusers=pgTable('users',{id:integer(),},(t)=>[pgPolicy(`policy-insert`,{for:'insert',to:serviceRole,withCheck:sql`false`,}),]);

The/supabase import also includes predefined tables and functions that you can use in your application

// drizzle-orm/supabaseconstauth=pgSchema('auth');exportconstauthUsers=auth.table('users',{id:uuid().primaryKey().notNull(),});constrealtime=pgSchema('realtime');exportconstrealtimeMessages=realtime.table('messages',{id:bigserial({mode:'bigint'}).primaryKey(),topic:text().notNull(),extension:text({enum:['presence','broadcast','postgres_changes'],}).notNull(),},);exportconstauthUid=sql`(select auth.uid())`;exportconstrealtimeTopic=sql`realtime.topic()`;

This allows you to use it in your code, and Drizzle Kit will treat them as existing databases,
using them only as information to connect to other entities

import{foreignKey,pgPolicy,pgTable,text,uuid}from"drizzle-orm/pg-core";import{sql}from"drizzle-orm/sql";import{authenticatedRole,authUsers}from"drizzle-orm/supabase";exportconstprofiles=pgTable("profiles",{id:uuid().primaryKey().notNull(),email:text().notNull(),},(table)=>[foreignKey({columns:[table.id],// reference to the auth table from SupabaseforeignColumns:[authUsers.id],name:"profiles_id_fk",}).onDelete("cascade"),pgPolicy("authenticated can view all profiles",{for:"select",// using predefined role from Supabaseto:authenticatedRole,using:sql`true`,}),]);

Let's check an example of adding a policy to a table that exists inSupabase

import{sql}from"drizzle-orm";import{pgPolicy}from"drizzle-orm/pg-core";import{authenticatedRole,realtimeMessages}from"drizzle-orm/supabase";exportconstpolicy=pgPolicy("authenticated role insert policy",{for:"insert",to:authenticatedRole,using:sql``,}).link(realtimeMessages);

Bug fixes

• [BUG]: Studio + mysql default mode, wrong format related timezone
• [BUG]: Drizzle Studio CORS error
• [BUG]: TIMESTAMPS showing up incorrectly on drizzle studio

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