drizzle-team/drizzle-orm (drizzle-orm)
### [`v0.36.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.36.0)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.3...0.36.0)
> This version of `drizzle-orm` requires `drizzle-kit@0.27.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 as `Neon` and `Supabase`.
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.
```ts
import { integer, pgTable } from 'drizzle-orm/pg-core';
export const users = 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.
```ts
import { pgRole } from 'drizzle-orm/pg-core';
export const admin = 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.
```ts
import { pgRole } from 'drizzle-orm/pg-core';
export const admin = 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 of `pgTable`
**Example of pgPolicy with all available properties**
```ts
import { sql } from 'drizzle-orm';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';
export const admin = pgRole('admin');
export const users = 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 like `Neon` or `Supabase`, where you need to add a policy
to their existing tables. In this case, you can use the `.link()` API
```ts
import { sql } from "drizzle-orm";
import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, realtimeMessages } from "drizzle-orm/supabase";
export const policy = 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 use `entities.roles` in `drizzle.config.ts`. For a complete reference, refer to the the [`drizzle.config.ts`](https://orm.drizzle.team/docs/drizzle-config-file) documentation.
By default, `drizzle-kit` does not manage roles for you, so you will need to enable this feature in `drizzle.config.ts`.
```ts {12-14}
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
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 an `admin` role and want to exclude it from the list of manageable roles**
```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
...
entities: {
roles: {
exclude: ['admin']
}
}
});
```
**You have an `admin` role and want to include it in the list of manageable roles**
```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
...
entities: {
roles: {
include: ['admin']
}
}
});
```
**If you are using `Neon` and want to exclude Neon-defined roles, you can use the provider option**
```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
...
entities: {
roles: {
provider: 'neon'
}
}
});
```
**If you are using `Supabase` and want to exclude Supabase-defined roles, you can use the provider option**
```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
...
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 the `provider` option and `exclude` additional roles:
```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
...
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 use `security_invoker` in the view's WITH options. Here is a small example:
```ts {5}
...
export const roomsUsersProfiles = 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 the `crudPolicy` function that includes predefined functions and Neon's default roles.
Here's an example of how to use the `crudPolicy` function:
```ts
import { crudPolicy } from 'drizzle-orm/neon';
import { integer, pgRole, pgTable } from 'drizzle-orm/pg-core';
export const admin = pgRole('admin');
export const users = pgTable('users', {
id: integer(),
}, (t) => [
crudPolicy({ role: admin, read: true, modify: false }),
]);
```
This policy is equivalent to:
```ts
import { sql } from 'drizzle-orm';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';
export const admin = pgRole('admin');
export const users = 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 predefined `authenticated` and `anaonymous` roles and related functions. If you are using `Neon` for RLS, you can use these roles, which are marked as existing, and the related functions in your RLS queries.
```ts
// drizzle-orm/neon
export const authenticatedRole = pgRole('authenticated').existing();
export const anonymousRole = pgRole('anonymous').existing();
export const authUid = (userIdColumn: AnyPgColumn) => sql`(select auth.user_id() = ${userIdColumn})`;
```
For example, you can use the `Neon` predefined roles and functions like this:
```ts
import { sql } from 'drizzle-orm';
import { authenticatedRole } from 'drizzle-orm/neon';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';
export const admin = pgRole('admin');
export const users = 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 and `Supabase` simpler.
```ts
// drizzle-orm/supabase
export const anonRole = pgRole('anon').existing();
export const authenticatedRole = pgRole('authenticated').existing();
export const serviceRole = pgRole('service_role').existing();
export const postgresRole = pgRole('postgres_role').existing();
export const supabaseAuthAdminRole = pgRole('supabase_auth_admin').existing();
```
For example, you can use the `Supabase` predefined roles like this:
```ts
import { sql } from 'drizzle-orm';
import { serviceRole } from 'drizzle-orm/supabase';
import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core';
export const admin = pgRole('admin');
export const users = 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
```ts
// drizzle-orm/supabase
const auth = pgSchema('auth');
export const authUsers = auth.table('users', {
id: uuid().primaryKey().notNull(),
});
const realtime = pgSchema('realtime');
export const realtimeMessages = realtime.table(
'messages',
{
id: bigserial({ mode: 'bigint' }).primaryKey(),
topic: text().notNull(),
extension: text({
enum: ['presence', 'broadcast', 'postgres_changes'],
}).notNull(),
},
);
export const authUid = sql`(select auth.uid())`;
export const realtimeTopic = 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
```ts
import { foreignKey, pgPolicy, pgTable, text, uuid } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm/sql";
import { authenticatedRole, authUsers } from "drizzle-orm/supabase";
export const profiles = pgTable(
"profiles",
{
id: uuid().primaryKey().notNull(),
email: text().notNull(),
},
(table) => [
foreignKey({
columns: [table.id],
// reference to the auth table from Supabase
foreignColumns: [authUsers.id],
name: "profiles_id_fk",
}).onDelete("cascade"),
pgPolicy("authenticated can view all profiles", {
for: "select",
// using predefined role from Supabase
to: authenticatedRole,
using: sql`true`,
}),
]
);
```
Let's check an example of adding a policy to a table that exists in `Supabase`
```ts
import { sql } from "drizzle-orm";
import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, realtimeMessages } from "drizzle-orm/supabase";
export const policy = pgPolicy("authenticated role insert policy", {
for: "insert",
to: authenticatedRole,
using: sql``,
}).link(realtimeMessages);
```
### Bug fixes
- [\[BUG\]: postgres-js driver throws error when using new { client } constructor arguments ](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3176)
### [`v0.35.3`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.3)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.2...0.35.3)
### New LibSQL driver modules
Drizzle now has native support for all `@libsql/client` driver variations:
1. `@libsql/client` - defaults to node import, automatically changes to web if target or platform is set for bundler, e.g. `esbuild --platform=browser`
```ts
import { drizzle } from 'drizzle-orm/libsql';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
2. `@libsql/client/node` node compatible module, supports :memory:, file, wss, http and turso connection protocols
```ts
import { drizzle } from 'drizzle-orm/libsql/node';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
3. `@libsql/client/web` module for fullstack web frameworks like next, nuxt, astro, etc.
```ts
import { drizzle } from 'drizzle-orm/libsql/web';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
4. `@libsql/client/http` module for http and https connection protocols
```ts
import { drizzle } from 'drizzle-orm/libsql/http';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
5. `@libsql/client/ws` module for ws and wss connection protocols
```ts
import { drizzle } from 'drizzle-orm/libsql/ws';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
6. `@libsql/client/sqlite3` module for :memory: and file connection protocols
```ts
import { drizzle } from 'drizzle-orm/libsql/wasm';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
7. `@libsql/client-wasm` Separate experimental package for WASM
```ts
import { drizzle } from 'drizzle-orm/libsql';
const db = drizzle({ connection: {
url: process.env.DATABASE_URL,
authToken: process.env.DATABASE_AUTH_TOKEN
}});
```
### [`v0.35.2`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.2)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.1...0.35.2)
- Fix issues with importing in several environments after updating the Drizzle driver implementation
We've added approximately 240 tests to check the ESM and CJS builds for all the drivers we have. You can check them [here](https://redirect.github.com/drizzle-team/drizzle-orm/tree/main/integration-tests/js-tests/driver-init)
- Fixed [\[BUG\]: Type Error in PgTransaction Missing $client Property After Upgrading to drizzle-orm@0.35.1](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3140)
- Fixed [\[BUG\]: New critical Build error drizzle 0.35.0 deploying on Cloudflare ](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3137)
### [`v0.35.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.1)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.0...0.35.1)
- 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.35.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.0)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.34.1...0.35.0)
### Important change after 0.34.0 release
#### Updated the init Drizzle database API
The API from version 0.34.0 turned out to be unusable and needs to be changed. You can read more about our decisions in [this discussion](https://redirect.github.com/drizzle-team/drizzle-orm/discussions/3097)
If you still want to use the new API introduced in 0.34.0, which can create driver clients for you under the hood, you can now do so
```ts
import { drizzle } from "drizzle-orm/node-postgres";
const db = drizzle(process.env.DATABASE_URL);
// or
const db = drizzle({
connection: process.env.DATABASE_URL
});
const db = drizzle({
connection: {
user: "...",
password: "...",
host: "...",
port: 4321,
db: "...",
},
});
// if you need to pass logger or schema
const db = drizzle({
connection: process.env.DATABASE_URL,
logger: true,
schema: schema,
});
```
in order to not introduce breaking change - we will still leave support for deprecated API until V1 release.
It will degrade autocomplete performance in connection params due to `DatabaseDriver` | `ConnectionParams` types collision,
but that's a decent compromise against breaking changes
```ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
const client = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(client); // deprecated but available
// new version
const db = drizzle({
client: client,
});
```
### New Features
#### New .orderBy() and .limit() functions in update and delete statements SQLite and MySQL
You now have more options for the `update` and `delete` query builders in MySQL and SQLite
**Example**
```ts
await db.update(usersTable).set({ verified: true }).limit(2).orderBy(asc(usersTable.name));
await db.delete(usersTable).where(eq(usersTable.verified, false)).limit(1).orderBy(asc(usersTable.name));
```
#### New `drizzle.mock()` function
There were cases where you didn't need to provide a driver to the Drizzle object, and this served as a workaround
```ts
const db = drizzle({} as any)
```
Now you can do this using a mock function
```ts
const db = drizzle.mock()
```
There is no valid production use case for this, but we used it in situations where we needed to check types, etc., without making actual database calls or dealing with driver creation. If anyone was using it, please switch to using mocks now
### Internal updates
- Upgraded TS in codebase to the version 5.6.3
### Bug fixes
- [\[BUG\]: New $count API error with @neondatabase/serverless](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3081)
### [`v0.34.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.34.1)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.34.0...0.34.1)
- Fixed dynamic imports for CJS and MJS in the `/connect` module
### [`v0.34.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.34.0)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.33.0...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:
```bash
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**
```ts
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**
```ts
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:
```sql
'/*\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:
```sql
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](https://redirect.github.com/tursodatabase/libsql/blob/main/libsql-sqlite3/doc/libsql_extensions.md#altering-columns)
##### LIMITATIONS
- Dropping foreign key will cause table recreation.
This is because LibSQL/Turso does not support dropping this type of foreign key.
```sql
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.
```sql
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
```ts
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
```ts
// 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.
```ts
drizzle("aws-data-api-pg", {
connection: {
resourceArn: "",
database: "",
secretArn: "",
},
});
```
For `d1`, the CloudFlare Worker types as described in the [documentation](https://developers.cloudflare.com/d1/get-started/) here will be required.
```ts
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](https://vercel.com/docs/storage/vercel-postgres/quickstart) for more info
```ts
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**
```ts
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
```ts
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("in_stock").default(true),
}));
```
#### 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:
```ts
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`
```ts
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
```ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
dialect: "postgresql",
schema: "./schema.ts",
dbCredentials: {
url: "postgresql://postgres:password@localhost:5432/db",
},
casing: "snake_case",
});
```
#### New "count" API
Before this release to count entities in a table, you would need to do this:
```ts
const res = await db.select({ count: sql`count(*)` }).from(users);
const count = res[0].count;
```
The new API will look like this:
```ts
// how many users are in the database
const count: number = await db.$count(users);
// how many users with the name "Dan" are in the database
const count: number = await db.$count(users, eq(name, "Dan"));
```
This can also work as a subquery and within relational queries
```ts
const users = await db.select({
...users,
postsCount: db.$count(posts, eq(posts.authorId, users.id))
});
const users = await db.query.users.findMany({
extras: {
postsCount: db.$count(posts, eq(posts.authorId, users.id))
}
})
```
#### Ability to execute raw strings instead of using SQL templates for raw queries
Previously, you would have needed to do this to execute a raw query with Drizzle
```ts
import { sql } from 'drizzle-orm'
db.execute(sql`select * from ${users}`);
// or
db.execute(sql.raw(`select * from ${users}`));
```
You can now do this as well
```ts
db.execute('select * from users')
```
#### You can now access the driver client from Drizzle db instance
```ts
const client = db.$client;
```
### [`v0.33.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.33.0)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.32.2...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](https://redirect.github.com/drizzle-team/drizzle-orm/issues/724)
- [\[BUG\]: jsonb type on postgres implement incorrectly](https://redirect.github.com/drizzle-team/drizzle-orm/issues/1511)
> 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:**
```sql
update table_name
set jsonb_column = (jsonb_column #>> '{}')::jsonb;
```
**if you are using json:**
```sql
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:**
```sql
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:**
```sql
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](https://redirect.github.com/AndriiSherman). I will try to help you!
#### Bug Fixes
- [\[BUG\]: boolean mode not working with prepared statements (bettersqlite)](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2568) - thanks [@veloii](https://redirect.github.com/veloii)
- [\[BUG\]: isTable helper function is not working](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2672) - thanks [@hajek-raven](https://redirect.github.com/hajek-raven)
- [\[BUG\]: Documentation is outdated on inArray and notInArray Methods](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2690) - thanks [@RemiPeruto](https://redirect.github.com/RemiPeruto)
### [`v0.32.2`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.32.2)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.32.1...0.32.2)
- Fix AWS Data API type hints bugs in RQB
- Fix set transactions in MySQL bug - thanks [@roguesherlock](https://redirect.github.com/roguesherlock)
- Add forwaring dependencies within useLiveQuery, fixes [#2651](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2651) - thanks [@anstapol](https://redirect.github.com/anstapol)
- Export additional types from SQLite package, like `AnySQLiteUpdate` - thanks [@veloii](https://redirect.github.com/veloii)
### [`v0.32.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.32.1)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.32.0...0.32.1)
- Fix typings for indexes and allow creating indexes on 3+ columns mixing columns and expressions - thanks [@lbguilherme](https://redirect.github.com/lbguilherme)!
- Added support for "limit 0" in all dialects - closes [#2011](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2011) - thanks [@sillvva](https://redirect.github.com/sillvva)!
- Make inArray and notInArray accept empty list, closes [#1295](https://redirect.github.com/drizzle-team/drizzle-orm/issues/1295) - thanks [@RemiPeruto](https://redirect.github.com/RemiPeruto)!
- fix typo in lt typedoc - thanks [@dalechyn](https://redirect.github.com/dalechyn)!
- fix wrong example in README.md - thanks [@7flash](https://redirect.github.com/7flash)!
### [`v0.32.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.32.0)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.4...0.32.0)
### Release notes for `drizzle-orm@0.32.0` and `drizzle-kit@0.23.0`
> It's not mandatory to upgrade both packages, but if you want to use the new features in both queries and migrations, you will need to upgrade both packages
#### New Features
##### 🎉 MySQL `$returningId()` function
MySQL itself doesn't have native support for `RETURNING` after using `INSERT`. There is only one way to do it for `primary keys` with `autoincrement` (or `serial`) types, where you can access `insertId` and `affectedRows` fields. We've prepared an automatic way for you to handle such cases with Drizzle and automatically receive all inserted IDs as separate objects
```ts
import { boolean, int, text, mysqlTable } from 'drizzle-orm/mysql-core';
const usersTable = mysqlTable('users', {
id: int('id').primaryKey(),
name: text('name').notNull(),
verified: boolean('verified').notNull().default(false),
});
const result = await db.insert(usersTable).values([{ name: 'John' }, { name: 'John1' }]).$returningId();
// ^? { id: number }[]
```
Also with Drizzle, you can specify a `primary key` with `$default` function that will generate custom primary keys at runtime. We will also return those generated keys for you in the `$returningId()` call
```ts
import { varchar, text, mysqlTable } from 'drizzle-orm/mysql-core';
import { createId } from '@paralleldrive/cuid2';
const usersTableDefFn = mysqlTable('users_default_fn', {
customId: varchar('id', { length: 256 }).primaryKey().$defaultFn(createId),
name: text('name').notNull(),
});
const result = await db.insert(usersTableDefFn).values([{ name: 'John' }, { name: 'John1' }]).$returningId();
// ^? { customId: string }[]
```
> If there is no primary keys -> type will be `{}[]` for such queries
##### 🎉 PostgreSQL Sequences
You can now specify sequences in Postgres within any schema you need and define all the available properties
##### **Example**
```ts
import { pgSchema, pgSequence } from "drizzle-orm/pg-core";
// No params specified
export const customSequence = pgSequence("name");
// Sequence with params
export const customSequence = pgSequence("name", {
startWith: 100,
maxValue: 10000,
minValue: 100,
cycle: true,
cache: 10,
increment: 2
});
// Sequence in custom schema
export const customSchema = pgSchema('custom_schema');
export const customSequence = customSchema.sequence("name");
```
##### 🎉 PostgreSQL Identity Columns
[Source](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial): As mentioned, the `serial` type in Postgres is outdated and should be deprecated. Ideally, you should not use it. `Identity columns` are the recommended way to specify sequences in your schema, which is why we are introducing the `identity columns` feature
##### **Example**
```ts
import { pgTable, integer, text } from 'drizzle-orm/pg-core'
export const ingredients = pgTable("ingredients", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
name: text("name").notNull(),
description: text("description"),
});
```
You can specify all properties available for sequences in the `.generatedAlwaysAsIdentity()` function. Additionally, you can specify custom names for these sequences
PostgreSQL docs [reference](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY).
##### 🎉 PostgreSQL Generated Columns
You can now specify generated columns on any column supported by PostgreSQL to use with generated columns
##### **Example** with generated column for `tsvector`
> Note: we will add `tsVector` column type before latest release
```ts
import { SQL, sql } from "drizzle-orm";
import { customType, index, integer, pgTable, text } from "drizzle-orm/pg-core";
const tsVector = customType<{ data: string }>({
dataType() {
return "tsvector";
},
});
export const test = pgTable(
"test",
{
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
content: text("content"),
contentSearch: tsVector("content_search", {
dimensions: 3,
}).generatedAlwaysAs(
(): SQL => sql`to_tsvector('english', ${test.content})`
),
},
(t) => ({
idx: index("idx_content_search").using("gin", t.contentSearch),
})
);
```
In case you don't need to reference any columns from your table, you can use just `sql` template or a `string`
```ts
export const users = pgTable("users", {
id: integer("id"),
name: text("name"),
generatedName: text("gen_name").generatedAlwaysAs(sql`hello world!`),
generatedName1: text("gen_name1").generatedAlwaysAs("hello world!"),
}),
```
##### 🎉 MySQL Generated Columns
You can now specify generated columns on any column supported by MySQL to use with generated columns
You can specify both `stored` and `virtual` options, for more info you can check [MySQL docs](https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html)
Also MySQL has a few limitation for such columns usage, which is described [here](https://dev.mysql.com/doc/refman/8.4/en/alter-table-generated-columns.html)
Drizzle Kit will also have limitations for `push` command:
1. You can't change the generated constraint expression and type using `push`. Drizzle-kit will ignore this change. To make it work, you would need to `drop the column`, `push`, and then `add a column with a new expression`. This was done due to the complex mapping from the database side, where the schema expression will be modified on the database side and, on introspection, we will get a different string. We can't be sure if you changed this expression or if it was changed and formatted by the database. As long as these are generated columns and `push` is mostly used for prototyping on a local database, it should be fast to `drop` and `create` generated columns. Since these columns are `generated`, all the data will be restored
2. `generate` should have no limitations
##### **Example**
```ts
export const users = mysqlTable("users", {
id: int("id"),
id2: int("id2"),
name: text("name"),
generatedName: text("gen_name").generatedAlwaysAs(
(): SQL => sql`${schema2.users.name} || 'hello'`,
{ mode: "stored" }
),
generatedName1: text("gen_name1").generatedAlwaysAs(
(): SQL => sql`${schema2.users.name} || 'hello'`,
{ mode: "virtual" }
),
}),
```
In case you don't need to reference any columns from your table, you can use just `sql` template or a `string` in `.generatedAlwaysAs()`
##### 🎉 SQLite Generated Columns
You can now specify generated columns on any column supported by SQLite to use with generated columns
You can specify both `stored` and `virtual` options, for more info you can check [SQLite docs](https://www.sqlite.org/gencol.html)
Also SQLite has a few limitation for such columns usage, which is described [here](https://www.sqlite.org/gencol.html)
Drizzle Kit will also have limitations for `push` and `generate` command:
1. You can't change the generated constraint expression with the stored type in an existing table. You would need to delete this table and create it again. This is due to SQLite limitations for such actions. We will handle this case in future releases (it will involve the creation of a new table with data migration).
2. You can't add a `stored` generated expression to an existing column for the same reason as above. However, you can add a `virtual` expression to an existing column.
3. You can't change a `stored` generated expression in an existing column for the same reason as above. However, you can change a `virtual` expression.
4. You can't change the generated constraint type from `virtual` to `stored` for the same reason as above. However, you can change from `stored` to `virtual`.
#### New Drizzle Kit features
##### 🎉 Migrations support for all the new orm features
PostgreSQL sequences, identity columns and generated columns for all dialects
##### 🎉 New flag `--force` for `drizzle-kit push`
You can auto-accept all data-loss statements using the push command. It's only available in CLI parameters. Make sure you always use it if you are fine with running data-loss statements on your database
##### 🎉 New `migrations` flag `prefix`
You can now customize migration file prefixes to make the format suitable for your migration tools:
- `index` is the default type and will result in `0001_name.sql` file names;
- `supabase` and `timestamp` are equal and will result in `20240627123900_name.sql` file names;
- `unix` will result in unix seconds prefixes `1719481298_name.sql` file names;
- `none` will omit the prefix completely;
##### **Example**: Supabase migrations format
```ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
dialect: "postgresql",
migrations: {
prefix: 'supabase'
}
});
```
### [`v0.31.4`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.4)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.3...0.31.4)
- Mark prisma clients package as optional - thanks [@Cherry](https://redirect.github.com/Cherry)
### [`v0.31.3`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.3)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.2...0.31.3)
##### Bug fixed
- 🛠️ Fixed RQB behavior for tables with same names in different schemas
- 🛠️ Fixed \[BUG]: Mismatched type hints when using RDS Data API - [#2097](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2097)
##### New Prisma-Drizzle extension
```ts
import { PrismaClient } from '@prisma/client';
import { drizzle } from 'drizzle-orm/prisma/pg';
import { User } from './drizzle';
const prisma = new PrismaClient().$extends(drizzle());
const users = await prisma.$drizzle.select().from(User);
```
For more info, check docs: https://orm.drizzle.team/docs/prisma
### [`v0.31.2`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.2)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.1...0.31.2)
- 🎉 Added support for TiDB Cloud Serverless driver:
```ts
import { connect } from '@tidbcloud/serverless';
import { drizzle } from 'drizzle-orm/tidb-serverless';
const client = connect({ url: '...' });
const db = drizzle(client);
await db.select().from(...);
```
### [`v0.31.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.1)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.0...0.31.1)
### New Features
#### Live Queries 🎉
> ### For a full explanation about Drizzle + Expo welcome to [discussions](https://redirect.github.com/drizzle-team/drizzle-orm/discussions/2447)
As of `v0.31.1` Drizzle ORM now has native support for Expo SQLite Live Queries!
We've implemented a native `useLiveQuery` React Hook which observes necessary database changes and automatically re-runs database queries. It works with both SQL-like and Drizzle Queries:
```tsx
import { useLiveQuery, drizzle } from 'drizzle-orm/expo-sqlite';
import { openDatabaseSync } from 'expo-sqlite/next';
import { users } from './schema';
import { Text } from 'react-native';
const expo = openDatabaseSync('db.db', { enableChangeListener: true }); // <-- enable change listeners
const db = drizzle(expo);
const App = () => {
// Re-renders automatically when data changes
const { data } = useLiveQuery(db.select().from(users));
// const { data, error, updatedAt } = useLiveQuery(db.query.users.findFirst());
// const { data, error, updatedAt } = useLiveQuery(db.query.users.findMany());
return {JSON.stringify(data)};
};
export default App;
```
We've intentionally not changed the API of ORM itself to stay with conventional React Hook API, so we have `useLiveQuery(databaseQuery)` as opposed to `db.select().from(users).useLive()` or `db.query.users.useFindMany()`
We've also decided to provide `data`, `error` and `updatedAt` fields as a result of hook for concise explicit error handling following practices of `React Query` and `Electric SQL`
### [`v0.31.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.0)
[Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.30.10...0.31.0)
#### Breaking changes
> Note: `drizzle-orm@0.31.0` can be used with `drizzle-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](#drizzle-kit-updates-drizzle-kit0220)
##### 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.
```ts
// Index declaration reference
index('name')
.on(table.column1, table.column2, ...) or .onOnly(table.column1, table.column2, ...)
.concurrently()
.using(sql``) // sql expression
.asc() or .desc()
.nullsFirst() or .nullsLast()
.where(sql``) // sql expression
```
Current API
```ts
// 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 the `pg_vector` extension installed.
You can now specify indexes for `pg_vector` and utilize `pg_vector` functions for querying, ordering, etc.
Let's take a few examples of `pg_vector` indexes from the `pg_vector` docs and translate them to Drizzle
##### L2 distance, Inner product and Cosine distance
```ts
// 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);
const table = 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
```ts
// 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);
const table = 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:
```ts
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'
```
If `pg_vector` has some other functions to use, you can replicate implimentation from existing one we have. Here is how it can be done
```ts
export function l2Distance(
column: SQLWrapper | AnyColumn,
value: number[] | string[] | TypedQueryBuilder | string,
): SQL {
if (is(value, TypedQueryBuilder) || typeof value === 'string') {
return sql`${column} <-> ${value}`;
}
return sql`${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 of `pg_vector` queries from the `pg_vector` docs and translate them to Drizzle
```ts
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;
const subquery = 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 use `point` and `line` from [PostgreSQL Geometric Types](https://www.postgresql.org/docs/current/datatype-geometric.html)
Type `point` has 2 modes for mappings from the database: `tuple` and `xy`.
- `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
```ts
const items = pgTable('items', {
point: point('point'),
pointObj: point('point_xy', { mode: 'xy' }),
});
```
Type `line` has 2 modes for mappings from the database: `tuple` and `abc`.
- `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 equation `Ax + By + C = 0`. So, the database Line{1,2,3} will be typed as `{ a: 1, b: 2, c: 3 }` with drizzle.
```ts
const items = 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 the `postgis` extension installed.
`geometry` type from postgis extension:
```ts
const items = 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**
Type `geometry` has 2 modes for mappings from the database: `tuple` and `xy`.
- `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 the `geometry(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 from [drizzle-kit@0.22.0](https://redirect.github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.22.0)
#### New Features
##### 🎉 Support for new types
Drizzle Kit can now handle:
- `point` and `line` from PostgreSQL
- `vector` from the PostgreSQL `pg_vector` extension
- `geometry` from the PostgreSQL `PostGIS` extension
##### 🎉 New param in drizzle.config - `extensionsFilters`
The PostGIS extension creates a few internal tables in the `public` schema. This means that if you have a database with the PostGIS extension and use `push` or `introspect`, all those tables will be included in `diff` operations. In this case, you would need to specify `tablesFilter`, find all tables created by the extension, and list them in this parameter.
We have addressed this issue so that you won't need to take all these steps. Simply specify `extensionsFilters` with the name of the extension used, and Drizzle will skip all the necessary tables.
Currently, we only support the `postgis` option, but we plan to add more extensions if they create tables in the `public` schema.
The `po
Configuration
📅 Schedule: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).
🚦 Automerge: Disabled by config. Please merge this manually once you are satisfied.
♻ Rebasing: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.
🔕 Ignore: Close this PR and you won't be reminded about this update again.
[ ] If you want to rebase/retry this PR, check this box
This PR contains the following updates:
^0.28.6->^0.36.0Release Notes
drizzle-team/drizzle-orm (drizzle-orm)
### [`v0.36.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.36.0) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.3...0.36.0) > This version of `drizzle-orm` requires `drizzle-kit@0.27.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 as `Neon` and `Supabase`. 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. ```ts import { integer, pgTable } from 'drizzle-orm/pg-core'; export const users = 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. ```ts import { pgRole } from 'drizzle-orm/pg-core'; export const admin = 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. ```ts import { pgRole } from 'drizzle-orm/pg-core'; export const admin = 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 of `pgTable` **Example of pgPolicy with all available properties** ```ts import { sql } from 'drizzle-orm'; import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core'; export const admin = pgRole('admin'); export const users = 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 like `Neon` or `Supabase`, where you need to add a policy to their existing tables. In this case, you can use the `.link()` API ```ts import { sql } from "drizzle-orm"; import { pgPolicy } from "drizzle-orm/pg-core"; import { authenticatedRole, realtimeMessages } from "drizzle-orm/supabase"; export const policy = 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 use `entities.roles` in `drizzle.config.ts`. For a complete reference, refer to the the [`drizzle.config.ts`](https://orm.drizzle.team/docs/drizzle-config-file) documentation. By default, `drizzle-kit` does not manage roles for you, so you will need to enable this feature in `drizzle.config.ts`. ```ts {12-14} // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ 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 an `admin` role and want to exclude it from the list of manageable roles** ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ ... entities: { roles: { exclude: ['admin'] } } }); ``` **You have an `admin` role and want to include it in the list of manageable roles** ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ ... entities: { roles: { include: ['admin'] } } }); ``` **If you are using `Neon` and want to exclude Neon-defined roles, you can use the provider option** ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ ... entities: { roles: { provider: 'neon' } } }); ``` **If you are using `Supabase` and want to exclude Supabase-defined roles, you can use the provider option** ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ ... 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 the `provider` option and `exclude` additional roles: ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ ... 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 use `security_invoker` in the view's WITH options. Here is a small example: ```ts {5} ... export const roomsUsersProfiles = 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 the `crudPolicy` function that includes predefined functions and Neon's default roles. Here's an example of how to use the `crudPolicy` function: ```ts import { crudPolicy } from 'drizzle-orm/neon'; import { integer, pgRole, pgTable } from 'drizzle-orm/pg-core'; export const admin = pgRole('admin'); export const users = pgTable('users', { id: integer(), }, (t) => [ crudPolicy({ role: admin, read: true, modify: false }), ]); ``` This policy is equivalent to: ```ts import { sql } from 'drizzle-orm'; import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core'; export const admin = pgRole('admin'); export const users = 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 predefined `authenticated` and `anaonymous` roles and related functions. If you are using `Neon` for RLS, you can use these roles, which are marked as existing, and the related functions in your RLS queries. ```ts // drizzle-orm/neon export const authenticatedRole = pgRole('authenticated').existing(); export const anonymousRole = pgRole('anonymous').existing(); export const authUid = (userIdColumn: AnyPgColumn) => sql`(select auth.user_id() = ${userIdColumn})`; ``` For example, you can use the `Neon` predefined roles and functions like this: ```ts import { sql } from 'drizzle-orm'; import { authenticatedRole } from 'drizzle-orm/neon'; import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core'; export const admin = pgRole('admin'); export const users = 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 and `Supabase` simpler. ```ts // drizzle-orm/supabase export const anonRole = pgRole('anon').existing(); export const authenticatedRole = pgRole('authenticated').existing(); export const serviceRole = pgRole('service_role').existing(); export const postgresRole = pgRole('postgres_role').existing(); export const supabaseAuthAdminRole = pgRole('supabase_auth_admin').existing(); ``` For example, you can use the `Supabase` predefined roles like this: ```ts import { sql } from 'drizzle-orm'; import { serviceRole } from 'drizzle-orm/supabase'; import { integer, pgPolicy, pgRole, pgTable } from 'drizzle-orm/pg-core'; export const admin = pgRole('admin'); export const users = 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 ```ts // drizzle-orm/supabase const auth = pgSchema('auth'); export const authUsers = auth.table('users', { id: uuid().primaryKey().notNull(), }); const realtime = pgSchema('realtime'); export const realtimeMessages = realtime.table( 'messages', { id: bigserial({ mode: 'bigint' }).primaryKey(), topic: text().notNull(), extension: text({ enum: ['presence', 'broadcast', 'postgres_changes'], }).notNull(), }, ); export const authUid = sql`(select auth.uid())`; export const realtimeTopic = 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 ```ts import { foreignKey, pgPolicy, pgTable, text, uuid } from "drizzle-orm/pg-core"; import { sql } from "drizzle-orm/sql"; import { authenticatedRole, authUsers } from "drizzle-orm/supabase"; export const profiles = pgTable( "profiles", { id: uuid().primaryKey().notNull(), email: text().notNull(), }, (table) => [ foreignKey({ columns: [table.id], // reference to the auth table from Supabase foreignColumns: [authUsers.id], name: "profiles_id_fk", }).onDelete("cascade"), pgPolicy("authenticated can view all profiles", { for: "select", // using predefined role from Supabase to: authenticatedRole, using: sql`true`, }), ] ); ``` Let's check an example of adding a policy to a table that exists in `Supabase` ```ts import { sql } from "drizzle-orm"; import { pgPolicy } from "drizzle-orm/pg-core"; import { authenticatedRole, realtimeMessages } from "drizzle-orm/supabase"; export const policy = pgPolicy("authenticated role insert policy", { for: "insert", to: authenticatedRole, using: sql``, }).link(realtimeMessages); ``` ### Bug fixes - [\[BUG\]: postgres-js driver throws error when using new { client } constructor arguments ](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3176) ### [`v0.35.3`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.3) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.2...0.35.3) ### New LibSQL driver modules Drizzle now has native support for all `@libsql/client` driver variations: 1. `@libsql/client` - defaults to node import, automatically changes to web if target or platform is set for bundler, e.g. `esbuild --platform=browser` ```ts import { drizzle } from 'drizzle-orm/libsql'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` 2. `@libsql/client/node` node compatible module, supports :memory:, file, wss, http and turso connection protocols ```ts import { drizzle } from 'drizzle-orm/libsql/node'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` 3. `@libsql/client/web` module for fullstack web frameworks like next, nuxt, astro, etc. ```ts import { drizzle } from 'drizzle-orm/libsql/web'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` 4. `@libsql/client/http` module for http and https connection protocols ```ts import { drizzle } from 'drizzle-orm/libsql/http'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` 5. `@libsql/client/ws` module for ws and wss connection protocols ```ts import { drizzle } from 'drizzle-orm/libsql/ws'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` 6. `@libsql/client/sqlite3` module for :memory: and file connection protocols ```ts import { drizzle } from 'drizzle-orm/libsql/wasm'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` 7. `@libsql/client-wasm` Separate experimental package for WASM ```ts import { drizzle } from 'drizzle-orm/libsql'; const db = drizzle({ connection: { url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }}); ``` ### [`v0.35.2`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.2) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.1...0.35.2) - Fix issues with importing in several environments after updating the Drizzle driver implementation We've added approximately 240 tests to check the ESM and CJS builds for all the drivers we have. You can check them [here](https://redirect.github.com/drizzle-team/drizzle-orm/tree/main/integration-tests/js-tests/driver-init) - Fixed [\[BUG\]: Type Error in PgTransaction Missing $client Property After Upgrading to drizzle-orm@0.35.1](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3140) - Fixed [\[BUG\]: New critical Build error drizzle 0.35.0 deploying on Cloudflare ](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3137) ### [`v0.35.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.1) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.35.0...0.35.1) - 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.35.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.35.0) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.34.1...0.35.0) ### Important change after 0.34.0 release #### Updated the init Drizzle database API The API from version 0.34.0 turned out to be unusable and needs to be changed. You can read more about our decisions in [this discussion](https://redirect.github.com/drizzle-team/drizzle-orm/discussions/3097) If you still want to use the new API introduced in 0.34.0, which can create driver clients for you under the hood, you can now do so ```ts import { drizzle } from "drizzle-orm/node-postgres"; const db = drizzle(process.env.DATABASE_URL); // or const db = drizzle({ connection: process.env.DATABASE_URL }); const db = drizzle({ connection: { user: "...", password: "...", host: "...", port: 4321, db: "...", }, }); // if you need to pass logger or schema const db = drizzle({ connection: process.env.DATABASE_URL, logger: true, schema: schema, }); ``` in order to not introduce breaking change - we will still leave support for deprecated API until V1 release. It will degrade autocomplete performance in connection params due to `DatabaseDriver` | `ConnectionParams` types collision, but that's a decent compromise against breaking changes ```ts import { drizzle } from "drizzle-orm/node-postgres"; import { Pool } from "pg"; const client = new Pool({ connectionString: process.env.DATABASE_URL }); const db = drizzle(client); // deprecated but available // new version const db = drizzle({ client: client, }); ``` ### New Features #### New .orderBy() and .limit() functions in update and delete statements SQLite and MySQL You now have more options for the `update` and `delete` query builders in MySQL and SQLite **Example** ```ts await db.update(usersTable).set({ verified: true }).limit(2).orderBy(asc(usersTable.name)); await db.delete(usersTable).where(eq(usersTable.verified, false)).limit(1).orderBy(asc(usersTable.name)); ``` #### New `drizzle.mock()` function There were cases where you didn't need to provide a driver to the Drizzle object, and this served as a workaround ```ts const db = drizzle({} as any) ``` Now you can do this using a mock function ```ts const db = drizzle.mock() ``` There is no valid production use case for this, but we used it in situations where we needed to check types, etc., without making actual database calls or dealing with driver creation. If anyone was using it, please switch to using mocks now ### Internal updates - Upgraded TS in codebase to the version 5.6.3 ### Bug fixes - [\[BUG\]: New $count API error with @neondatabase/serverless](https://redirect.github.com/drizzle-team/drizzle-orm/issues/3081) ### [`v0.34.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.34.1) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.34.0...0.34.1) - Fixed dynamic imports for CJS and MJS in the `/connect` module ### [`v0.34.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.34.0) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.33.0...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: ```bash 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** ```ts 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** ```ts 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: ```sql '/*\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: ```sql 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](https://redirect.github.com/tursodatabase/libsql/blob/main/libsql-sqlite3/doc/libsql_extensions.md#altering-columns) ##### LIMITATIONS - Dropping foreign key will cause table recreation. This is because LibSQL/Turso does not support dropping this type of foreign key. ```sql 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. ```sql 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 ```ts 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 ```ts // 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. ```ts drizzle("aws-data-api-pg", { connection: { resourceArn: "", database: "", secretArn: "", }, }); ``` For `d1`, the CloudFlare Worker types as described in the [documentation](https://developers.cloudflare.com/d1/get-started/) here will be required. ```ts 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](https://vercel.com/docs/storage/vercel-postgres/quickstart) for more info ```ts 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** ```ts 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 ```ts 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("in_stock").default(true), })); ``` #### 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: ```ts 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` ```ts 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 ```ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "postgresql", schema: "./schema.ts", dbCredentials: { url: "postgresql://postgres:password@localhost:5432/db", }, casing: "snake_case", }); ``` #### New "count" API Before this release to count entities in a table, you would need to do this: ```ts const res = await db.select({ count: sql`count(*)` }).from(users); const count = res[0].count; ``` The new API will look like this: ```ts // how many users are in the database const count: number = await db.$count(users); // how many users with the name "Dan" are in the database const count: number = await db.$count(users, eq(name, "Dan")); ``` This can also work as a subquery and within relational queries ```ts const users = await db.select({ ...users, postsCount: db.$count(posts, eq(posts.authorId, users.id)) }); const users = await db.query.users.findMany({ extras: { postsCount: db.$count(posts, eq(posts.authorId, users.id)) } }) ``` #### Ability to execute raw strings instead of using SQL templates for raw queries Previously, you would have needed to do this to execute a raw query with Drizzle ```ts import { sql } from 'drizzle-orm' db.execute(sql`select * from ${users}`); // or db.execute(sql.raw(`select * from ${users}`)); ``` You can now do this as well ```ts db.execute('select * from users') ``` #### You can now access the driver client from Drizzle db instance ```ts const client = db.$client; ``` ### [`v0.33.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.33.0) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.32.2...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](https://redirect.github.com/drizzle-team/drizzle-orm/issues/724) - [\[BUG\]: jsonb type on postgres implement incorrectly](https://redirect.github.com/drizzle-team/drizzle-orm/issues/1511) > 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:** ```sql update table_name set jsonb_column = (jsonb_column #>> '{}')::jsonb; ``` **if you are using json:** ```sql 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:** ```sql 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:** ```sql 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](https://redirect.github.com/AndriiSherman). I will try to help you! #### Bug Fixes - [\[BUG\]: boolean mode not working with prepared statements (bettersqlite)](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2568) - thanks [@veloii](https://redirect.github.com/veloii) - [\[BUG\]: isTable helper function is not working](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2672) - thanks [@hajek-raven](https://redirect.github.com/hajek-raven) - [\[BUG\]: Documentation is outdated on inArray and notInArray Methods](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2690) - thanks [@RemiPeruto](https://redirect.github.com/RemiPeruto) ### [`v0.32.2`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.32.2) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.32.1...0.32.2) - Fix AWS Data API type hints bugs in RQB - Fix set transactions in MySQL bug - thanks [@roguesherlock](https://redirect.github.com/roguesherlock) - Add forwaring dependencies within useLiveQuery, fixes [#2651](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2651) - thanks [@anstapol](https://redirect.github.com/anstapol) - Export additional types from SQLite package, like `AnySQLiteUpdate` - thanks [@veloii](https://redirect.github.com/veloii) ### [`v0.32.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.32.1) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.32.0...0.32.1) - Fix typings for indexes and allow creating indexes on 3+ columns mixing columns and expressions - thanks [@lbguilherme](https://redirect.github.com/lbguilherme)! - Added support for "limit 0" in all dialects - closes [#2011](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2011) - thanks [@sillvva](https://redirect.github.com/sillvva)! - Make inArray and notInArray accept empty list, closes [#1295](https://redirect.github.com/drizzle-team/drizzle-orm/issues/1295) - thanks [@RemiPeruto](https://redirect.github.com/RemiPeruto)! - fix typo in lt typedoc - thanks [@dalechyn](https://redirect.github.com/dalechyn)! - fix wrong example in README.md - thanks [@7flash](https://redirect.github.com/7flash)! ### [`v0.32.0`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.32.0) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.4...0.32.0) ### Release notes for `drizzle-orm@0.32.0` and `drizzle-kit@0.23.0` > It's not mandatory to upgrade both packages, but if you want to use the new features in both queries and migrations, you will need to upgrade both packages #### New Features ##### 🎉 MySQL `$returningId()` function MySQL itself doesn't have native support for `RETURNING` after using `INSERT`. There is only one way to do it for `primary keys` with `autoincrement` (or `serial`) types, where you can access `insertId` and `affectedRows` fields. We've prepared an automatic way for you to handle such cases with Drizzle and automatically receive all inserted IDs as separate objects ```ts import { boolean, int, text, mysqlTable } from 'drizzle-orm/mysql-core'; const usersTable = mysqlTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), verified: boolean('verified').notNull().default(false), }); const result = await db.insert(usersTable).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); // ^? { id: number }[] ``` Also with Drizzle, you can specify a `primary key` with `$default` function that will generate custom primary keys at runtime. We will also return those generated keys for you in the `$returningId()` call ```ts import { varchar, text, mysqlTable } from 'drizzle-orm/mysql-core'; import { createId } from '@paralleldrive/cuid2'; const usersTableDefFn = mysqlTable('users_default_fn', { customId: varchar('id', { length: 256 }).primaryKey().$defaultFn(createId), name: text('name').notNull(), }); const result = await db.insert(usersTableDefFn).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); // ^? { customId: string }[] ``` > If there is no primary keys -> type will be `{}[]` for such queries ##### 🎉 PostgreSQL Sequences You can now specify sequences in Postgres within any schema you need and define all the available properties ##### **Example** ```ts import { pgSchema, pgSequence } from "drizzle-orm/pg-core"; // No params specified export const customSequence = pgSequence("name"); // Sequence with params export const customSequence = pgSequence("name", { startWith: 100, maxValue: 10000, minValue: 100, cycle: true, cache: 10, increment: 2 }); // Sequence in custom schema export const customSchema = pgSchema('custom_schema'); export const customSequence = customSchema.sequence("name"); ``` ##### 🎉 PostgreSQL Identity Columns [Source](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial): As mentioned, the `serial` type in Postgres is outdated and should be deprecated. Ideally, you should not use it. `Identity columns` are the recommended way to specify sequences in your schema, which is why we are introducing the `identity columns` feature ##### **Example** ```ts import { pgTable, integer, text } from 'drizzle-orm/pg-core' export const ingredients = pgTable("ingredients", { id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }), name: text("name").notNull(), description: text("description"), }); ``` You can specify all properties available for sequences in the `.generatedAlwaysAsIdentity()` function. Additionally, you can specify custom names for these sequences PostgreSQL docs [reference](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY). ##### 🎉 PostgreSQL Generated Columns You can now specify generated columns on any column supported by PostgreSQL to use with generated columns ##### **Example** with generated column for `tsvector` > Note: we will add `tsVector` column type before latest release ```ts import { SQL, sql } from "drizzle-orm"; import { customType, index, integer, pgTable, text } from "drizzle-orm/pg-core"; const tsVector = customType<{ data: string }>({ dataType() { return "tsvector"; }, }); export const test = pgTable( "test", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), content: text("content"), contentSearch: tsVector("content_search", { dimensions: 3, }).generatedAlwaysAs( (): SQL => sql`to_tsvector('english', ${test.content})` ), }, (t) => ({ idx: index("idx_content_search").using("gin", t.contentSearch), }) ); ``` In case you don't need to reference any columns from your table, you can use just `sql` template or a `string` ```ts export const users = pgTable("users", { id: integer("id"), name: text("name"), generatedName: text("gen_name").generatedAlwaysAs(sql`hello world!`), generatedName1: text("gen_name1").generatedAlwaysAs("hello world!"), }), ``` ##### 🎉 MySQL Generated Columns You can now specify generated columns on any column supported by MySQL to use with generated columns You can specify both `stored` and `virtual` options, for more info you can check [MySQL docs](https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html) Also MySQL has a few limitation for such columns usage, which is described [here](https://dev.mysql.com/doc/refman/8.4/en/alter-table-generated-columns.html) Drizzle Kit will also have limitations for `push` command: 1. You can't change the generated constraint expression and type using `push`. Drizzle-kit will ignore this change. To make it work, you would need to `drop the column`, `push`, and then `add a column with a new expression`. This was done due to the complex mapping from the database side, where the schema expression will be modified on the database side and, on introspection, we will get a different string. We can't be sure if you changed this expression or if it was changed and formatted by the database. As long as these are generated columns and `push` is mostly used for prototyping on a local database, it should be fast to `drop` and `create` generated columns. Since these columns are `generated`, all the data will be restored 2. `generate` should have no limitations ##### **Example** ```ts export const users = mysqlTable("users", { id: int("id"), id2: int("id2"), name: text("name"), generatedName: text("gen_name").generatedAlwaysAs( (): SQL => sql`${schema2.users.name} || 'hello'`, { mode: "stored" } ), generatedName1: text("gen_name1").generatedAlwaysAs( (): SQL => sql`${schema2.users.name} || 'hello'`, { mode: "virtual" } ), }), ``` In case you don't need to reference any columns from your table, you can use just `sql` template or a `string` in `.generatedAlwaysAs()` ##### 🎉 SQLite Generated Columns You can now specify generated columns on any column supported by SQLite to use with generated columns You can specify both `stored` and `virtual` options, for more info you can check [SQLite docs](https://www.sqlite.org/gencol.html) Also SQLite has a few limitation for such columns usage, which is described [here](https://www.sqlite.org/gencol.html) Drizzle Kit will also have limitations for `push` and `generate` command: 1. You can't change the generated constraint expression with the stored type in an existing table. You would need to delete this table and create it again. This is due to SQLite limitations for such actions. We will handle this case in future releases (it will involve the creation of a new table with data migration). 2. You can't add a `stored` generated expression to an existing column for the same reason as above. However, you can add a `virtual` expression to an existing column. 3. You can't change a `stored` generated expression in an existing column for the same reason as above. However, you can change a `virtual` expression. 4. You can't change the generated constraint type from `virtual` to `stored` for the same reason as above. However, you can change from `stored` to `virtual`. #### New Drizzle Kit features ##### 🎉 Migrations support for all the new orm features PostgreSQL sequences, identity columns and generated columns for all dialects ##### 🎉 New flag `--force` for `drizzle-kit push` You can auto-accept all data-loss statements using the push command. It's only available in CLI parameters. Make sure you always use it if you are fine with running data-loss statements on your database ##### 🎉 New `migrations` flag `prefix` You can now customize migration file prefixes to make the format suitable for your migration tools: - `index` is the default type and will result in `0001_name.sql` file names; - `supabase` and `timestamp` are equal and will result in `20240627123900_name.sql` file names; - `unix` will result in unix seconds prefixes `1719481298_name.sql` file names; - `none` will omit the prefix completely; ##### **Example**: Supabase migrations format ```ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "postgresql", migrations: { prefix: 'supabase' } }); ``` ### [`v0.31.4`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.4) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.3...0.31.4) - Mark prisma clients package as optional - thanks [@Cherry](https://redirect.github.com/Cherry) ### [`v0.31.3`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.3) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.2...0.31.3) ##### Bug fixed - 🛠️ Fixed RQB behavior for tables with same names in different schemas - 🛠️ Fixed \[BUG]: Mismatched type hints when using RDS Data API - [#2097](https://redirect.github.com/drizzle-team/drizzle-orm/issues/2097) ##### New Prisma-Drizzle extension ```ts import { PrismaClient } from '@prisma/client'; import { drizzle } from 'drizzle-orm/prisma/pg'; import { User } from './drizzle'; const prisma = new PrismaClient().$extends(drizzle()); const users = await prisma.$drizzle.select().from(User); ``` For more info, check docs: https://orm.drizzle.team/docs/prisma ### [`v0.31.2`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.2) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.1...0.31.2) - 🎉 Added support for TiDB Cloud Serverless driver: ```ts import { connect } from '@tidbcloud/serverless'; import { drizzle } from 'drizzle-orm/tidb-serverless'; const client = connect({ url: '...' }); const db = drizzle(client); await db.select().from(...); ``` ### [`v0.31.1`](https://redirect.github.com/drizzle-team/drizzle-orm/releases/tag/0.31.1) [Compare Source](https://redirect.github.com/drizzle-team/drizzle-orm/compare/0.31.0...0.31.1) ### New Features #### Live Queries 🎉 > ### For a full explanation about Drizzle + Expo welcome to [discussions](https://redirect.github.com/drizzle-team/drizzle-orm/discussions/2447) As of `v0.31.1` Drizzle ORM now has native support for Expo SQLite Live Queries! We've implemented a native `useLiveQuery` React Hook which observes necessary database changes and automatically re-runs database queries. It works with both SQL-like and Drizzle Queries: ```tsx import { useLiveQuery, drizzle } from 'drizzle-orm/expo-sqlite'; import { openDatabaseSync } from 'expo-sqlite/next'; import { users } from './schema'; import { Text } from 'react-native'; const expo = openDatabaseSync('db.db', { enableChangeListener: true }); // <-- enable change listeners const db = drizzle(expo); const App = () => { // Re-renders automatically when data changes const { data } = useLiveQuery(db.select().from(users)); // const { data, error, updatedAt } = useLiveQuery(db.query.users.findFirst()); // const { data, error, updatedAt } = useLiveQuery(db.query.users.findMany()); returnConfiguration
📅 Schedule: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).
🚦 Automerge: Disabled by config. Please merge this manually once you are satisfied.
♻ Rebasing: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.
🔕 Ignore: Close this PR and you won't be reminded about this update again.
This PR was generated by Mend Renovate. View the repository job log.