drizzle-team / drizzle-orm

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅

Headless ORM for NodeJS, TypeScript and JavaScript 🚀

Website • Documentation • Twitter • Discord

What's Drizzle?

Drizzle is a modern TypeScript ORM developers wanna use in their next project. It is lightweight at only ~7.4kb minified+gzipped, it's tree shakeable with exactly 0 dependencies.

Drizzle supports every PostgreSQL, MySQL and SQLite databases, including serverless ones like Turso, Neon, Xata, PlanetScale, Cloudflare D1, FlyIO LiteFS, Vercel Postgres, Supabase and AWS Data API. No bells and whistles, no rust binaries, no serverless adapters, everything just works out of the box.

Drizzle is serverless-ready by design, it works in every major JavaScript runtime like NodeJS, Bun, Deno, Cloudflare Workers, Supabase functions, any Edge runtime and even in Browsers.
With Drizzle you can be fast out of the box, save time and costs while never introducing any data proxies into your infrastructure.

While you can use Drizzle as a JavaScript library, it shines in the TypeScript. It lets you declare SQL schema and build both relational and SQL-like queries, while keeping the balance between type-safety and extensibility for toolmakers to build on top.

Ecosystem

While Drizzle ORM remains a thin typed layer on top of SQL, we made a set of tools for people to have best possible developer experience.

Drizzle comes with a powerful Drizzle Kit CLI companion for you to have hasstle-free migrations. It can generate SQL migration files for you or apply schema changes directly to the database.

And we have a Drizzle Studio for you to effortlessly browse and manipulate data in your database of choice.

Documentation

Check out the full documentation on the website

Our sponsors ❤️

drizzle-orm's Issues

Support SELECT FOR UPDATE/SHARE

Is there any example with Vercel Edge Runtime?

Is there any example with Vercel Edge Runtime + SQLite?

or

Is there any way to SQLlite drizzle only for Query Builder? (not Database Connect)

ex)

const db = drizzle() // or const db = drizzle(new EmptyDatabase())
const query = db.select(user)

Hi, I'd like to participate in this project

Few years ago I switched from RoR to node.js and found that there is no single decent ORM to use with pleasure.
And long ago I was trying to build my own ORM, porm (Perfect ORM, or for beginning, Postgres ORM), but the problem was inspiration is not endless, it takes time, and no one is interested in using it and developing.

I noticed a post on hashnode from Alex Blockh about such dvizh, and thought: oh, looks like beginning of something cool, if they are building it to actually use it, if they have at least some resources to develop it and promote it on forums/articles, perhaps it has future?

I can't promise to dedicate much time to it, like a hobby project, but I have much experience with ORMs, different ideas.

If you're interested to let me collab, add me to chat where you're discuss it and let's discuss

[BUG]: Autoincrement not in output for PK

What version of drizzle-orm are you using?

0.14.3

Describe the Bug

primaryKey({autoIncrement: true}) does not add AUTO_INCREMENT to output

Add ON CONFLICT to primary keys

[BUG]: Extra colon on reference action sqlite gen

What version of drizzle-orm are you using?

0.14.2

Describe the Bug

When using drizzle-kit with drizzle orm for sql lite the following export occurs causing a syntax error

{
  nodeUrn: text('nodeUrn')
    .notNull()
    .references(() => node.urn, {
      onDelete: 'cascade',
      onUpdate: 'cascade',
    }),
  rcomp: integer('rcomp')
    .notNull()
    .references(() => URNRComponent.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade',
    }),
}

produces

FOREIGN KEY (`src`) REFERENCES node(`urn`) ON UPDATE no action ON DELETE no action,
FOREIGN KEY (`dst`) REFERENCES node(`urn`) ON UPDATE no action ON DELETE no action, // extra ','

drizzle-zod - add buildSelectSchema

Range types support

https://www.postgresql.org/docs/current/rangetypes.html

[FEAT]: Clustered Index with PK

RN there doesn't seem to be a way to create a n-part PK so sql can create a clustered index. you have to create a unique index which creates a less performant non-clustered index.

Any plans to add compound pks?

[FEAT]: Choose Migration Format

Would like a flag to be able to choose migration format. For example, wrangler uses /migrations/[seq]_[name]

This would limit the amount of copy and paste needed for different platforms

MySQL proxy driver

Add colored query logs (toggleable)

Support UPDATE FROM

Best way to lint columns, indexes, etc.?

What version of drizzle-orm are you using?

0.18.0

Describe the Bug

This isn't a bug, it's a guidance request. As we build our tables some devs make mistakes in nomenclature, creating duplicate column names or missing indexes, etc.

I was wondering if you could add a "lint" parameter to each table creation method like so:

export declare function sqliteTable<TTableName extends string, TColumnsMap extends Record<string, AnySQLiteColumnBuilder>>(
        name: TTableName, 
        columns: TColumnsMap, extraConfig?: (self: BuildColumns<TTableName, TColumnsMap>) => SQLiteTableExtraConfig, 
        lint?: (self: SQLiteTableWithColumns<{name: TTableName;columns: BuildColumns<TTableName, TColumnsMap>;}>) => void): 
    SQLiteTableWithColumns<{ name: TTableName; columns: BuildColumns<TTableName, TColumnsMap>; }>;

that would called like this:

export const lintIssues: { issue: string; tableName: string}[] = [];

export const supplier = sqliteTable('build_supplier', {
	id: integer('build_supplier_id').primaryKey(),
	vendor: text('vendor').notNull(),
}, (supplier) => ({
	uniqueIdx: uniqueIndex('build_supplier_unique_idx').on(supplier.vendor), // unique index
}), (supplier) => ({
        // loop through all columns and make sure none are dupes, adding to lintIssues
        // loop through index names to prevent duplicates
        // check to see if a primary key exists and is named properly
        // etc. etc.
        // it would be too late to _change_ anything in `supplier` table but linting should be possible
}));

It would be nice to also be able to lint at the schema level (multiple tables). :-)

Add VIEW support

• ORM support
• Kit support

---

I could've missed it but it appears the ORM doesn't support views yet? e.g.

CREATE VIEW v_editors
AS 
SELECT * FROM users WHERE role = 'editor';

Would be great to see support for this!

Typesafe PostgreSQL stored procs / function calls

What version of drizzle-orm are you using?

0.18.0

Describe the Bug

This is not a bug but a feature request / question. I noticed that #116 will be adding type-safe views and seems like a fantastic idea. In our projects we heavily rely on PostgreSQL stored procedures and functions. Are there any plans to add support for those objects?

This is a fantastic project and I love the concept of "if you know SQL you know Drizzle!" - being a thin, type-safe, wrapper around SQL is an excellent strategy and I'd love to see other PostgreSQL objects supported.

Thanks!

Add UNION support

Transactions support

For now, can be implemented using a workaround - https://discord.com/channels/1043890932593987624/1071136618209083522/1078116319054016542

Add COLLATE to indexes

https://www.sqlite.org/lang_createindex.html

Remove .default() from serial/smallserial/bigserial builder

Support indexes on function expressions instead of only columns

Add array types support

[BUG]: Method .returning() working incorrect with .get() method

What version of drizzle-orm are you using?

0.17.4

Describe the Bug

Method .returning() is not working properly with method .get()

Full context you can find in these files:

schema.ts
SqliteJson.ts

schema.ts

import { sql } from 'drizzle-orm'
import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core'
import { json } from './SqliteJson'

export const articles = sqliteTable('articles', {
    id: integer('id').primaryKey(),
    slug: text('slug').notNull(),
    title: text('title').notNull(),
    meta_title: text('meta_title').notNull(),
    meta_description: text('meta_description').notNull(),
    blocks: json<{ [key: string]: unknown }>('blocks').notNull(),
    created_at: integer('created_at', { mode: 'timestamp' }).notNull(),
    updated_at: integer('updated_at', { mode: 'timestamp' }).notNull(),
    file_id: text('file_id'),
    hidden: integer('hidden').default(0),
    podcasts: json<{ [url: string]: string }>('podcasts').default(sql`NULL`),
})

SqliteJson.ts

import { ColumnConfig } from 'drizzle-orm'
import { AnySQLiteTable } from 'drizzle-orm/sqlite-core'
import {
    SQLiteColumn,
} from 'drizzle-orm/sqlite-core/columns'
import { ColumnBuilderConfig } from 'drizzle-orm/column-builder'
import { SQLiteColumnBuilder } from 'drizzle-orm/sqlite-core/columns/common';

export class SQLiteJsonBuilder<
    TData,
> extends SQLiteColumnBuilder<
    ColumnBuilderConfig<{ data: TData; driverParam: string }>
> {
    // protected override $sql!: 'SQLiteJsonBuilder';
    build<TTableName extends string>(
        table: AnySQLiteTable<{ name: TTableName }>,
    ): SQLiteJson<TTableName, TData> {
        return new SQLiteJson(table, this.config)
    }
}

export class SQLiteJson<
    TTableName extends string,
    TData,
> extends SQLiteColumn<
    ColumnConfig<{ tableName: TTableName; data: TData; driverParam: string }>
> {
    protected override $sqliteColumnBrand!: 'SQLiteJson'

    constructor(
        table: AnySQLiteTable<{ name: TTableName }>,
        builder: SQLiteJsonBuilder<TData>['config'],
    ) {
        super(table, builder)
    }

    getSQLType(): string {
        return 'json'
    }

    override mapFromDriverValue(value: string): TData {
        if (typeof value === 'string') {
			try {
				return JSON.parse(value);
			} catch (e) {
				return value as unknown as TData;
			}
		}
		return value;
    }

    override mapToDriverValue(value: TData): string {
        return JSON.stringify(value)
    }
}

export const json = <T>(name: string) => {
    return new SQLiteJsonBuilder<T>(name)
}

Code with bug

const newArticle = {
    title: 'no-img2',
    meta_title: 'biba',
    meta_description: 'sadasd',
    blocks: {
        props: {},
    },
    slug: 'cvbcvbc',
    created_at: new Date(),
    updated_at: new Date(),
}

const query = database
    .insert(articles)
    .values(newArticle)
    .returning()

const result = query.get()
console.log(result);

Log:

{
  id: undefined,
  slug: undefined,
  title: undefined,
  meta_title: undefined,
  meta_description: undefined,
  blocks: undefined,
  created_at: Invalid Date,
  updated_at: Invalid Date,
  file_id: undefined,
  hidden: undefined,
  podcasts: undefined
}

But when I changed const result = query.get() to const result = query.all() it seems to be working well

Log with .all() method:

[
  {
    id: 26,
    slug: 'cvbcvbc',
    title: 'no-img2',
    meta_title: 'biba',
    meta_description: 'sadasd',
    blocks: { props: {} },
    created_at: 2023-02-03T08:57:41.430Z,
    updated_at: 2023-02-03T08:57:41.430Z,
    file_id: null,
    hidden: 0,
    podcasts: null
  }
]

Support MariaDB

Add composite primary keys

[BUG]: references doesn't seem to add the CONSTRAINT <name>

What version of drizzle-orm are you using?

0.14.3

Describe the Bug

As described: https://github.com/drizzle-team/drizzle-orm/blob/54453c9e5c531c04ae0bf2a6604ad9bcc31ce856/drizzle-orm-sqlite/README.md#automatic-sql-migrations-generation-with-drizzle-kit

Output for FK should name a constraint. Instead just the FK is defined (for example):

FOREIGN KEY ("nodeUrn") REFERENCES node("urn") ON UPDATE cascade ON DELETE cascade

instead of

CONSTRAINT fk_some_name FOREIGN KEY ("nodeUrn") REFERENCES node("urn") ON UPDATE cascade ON DELETE cascade

Without CONSTRAINT when run against sqlite3, the table doesn't seem to register the reference

Drizzle Kit 0.17.0 release

https://github.com/drizzle-team/drizzle-orm/blob/migration-folders/changelogs/drizzle-orm/0.21.0.md

Composite types support

https://www.postgresql.org/docs/current/rowtypes.html

Add SQLite WASM driver support

PostgreSQL proxy driver

CREATE DOMAIN support

https://www.postgresql.org/docs/current/sql-createdomain.html

creating tables our of schema

Hi,

I could not figure out how to get the example to run. I received an error 'no such table users'.

const UserTable = sqliteTable('users', {
  id: integer('id').primaryKey(),
  fullName: text('full_name'),
  createdAt: integer('created_at', { mode: 'timestamp' }),
});

const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite);

// error here
const users = db.select(UserTable).all();
console.log(users);

How do I initialize the database with schema and/or convert UserTable to sql and manually initialize?

I saw the documentation for obtaining sql from expression.

const query = db.select(UserTable).toSQL();

Support partial array column updates

Add insert ignore in MySQL

Add placeholder support to operator functions

Question: NPM Install Fails (Peer Deps)

What version of drizzle-orm are you using?

[email protected]

Question

Just started playing around around with this lib and wow! Great job! I have a strong affinity for type-safe dx-first tools (I'm the author of Type Route ) and this checks a lot of boxes. Really well done!

I'm evualuating this tool for a new project I'm working on. I see the peer deps for drizzle-orm-sqlite specify "@cloudflare/workers-types": ">=3 <4". Just curious why <4 is necessary?

Add support for `having`

Support STRICT TABLE in SQLite

Fix numeric type options in Postgres

Add view support

• Orm
• Kit

Introspect snapshot equality issue

SQLite support in drizzle-zod

drizzle-zod MySQL support

AWS Data API MySQL

Where to have discussions?

Seems discussions tab for this repo is closed, and the website is 404 for me, so wondering where to have discussions.

for myself, I'm primarily a devops dev the past 10 years, and need to get back into web site and app frameworks; I've built a few tiny apps over the years on cloudflare workers and kv, such as a billing an invoicing system a few years ago, however have spent a week researching my modern options... seems it is astro for web sites, remix for web apps; however for database, ideally I'd like to keep the entire stack cloudflare native, for performance, capability, and affordability reasons.... ideally there would be an orm + headless-crm + api-generator combo for cloudflare, but that combo isn't there yet, so been evaluating prisma, planetscale, fauna, kysely, trpc; and now found drizzle, which seems great, and meets my requirements for selecting cloudflare. Fauna has great features, however its pricing has always been the bottleneck for me.

I do imagine however that prisma will eventually bring out d1 support in a year or two, in which case, what is drizzle's plan or positioning then?

happy for you to move my post to the appropriate discussion forum

Support WITH RECURSIVE

Have possibility to use values/keys from fields object in .where/.orderBy/.groupBy

For example in this query I want to have a possibility to access medianRevenue or medianTalkTime fields in where/orderby/groupby

Example

.where((fields) => eq(fields.medianRevenue, 15))

Add knex driver support

Build intermediary state, query API for query composition like Ecto

Have a look into Ecto db library in Elixir, it could influence/inspire you to where the take the API to the next level. One of the powers of ecto is to ability to compose queries and then run them on Repo.one or Repo.all. Also, typeorm decorator API could be better way to declare the schema but this one isn't that important.

Ecto Query API: https://hexdocs.pm/ecto/Ecto.Query.html#module-query-prefix

I couldn't find a query composition api in the docs so wanted to mention this ;)

Better esm (ECMAScript module) import support

In Node.js if you are using ES modules (by either having an .mjs / .mts file, or adding "type": "module" in the package.json), importing the various drizzle-orm modules has to be done differently than when using commonjs, and from how it is shown in the docs.

As an example, in order to get the imports to work if using node-postgres, the imports need to look like this when using esm:

import { pgTable } from 'drizzle-orm/pg-core/index.js';
import { drizzle } from 'drizzle-orm/node-postgres/index.js';
import { eq } from 'drizzle-orm/expressions.js';
import { migrate } from 'drizzle-orm/node-postgres/migrator/index.js';
import pg from 'pg';
const { Pool } = pg;

A way to resolve this would be to declare all the package entry points in the package.js using the exports field. To test this solution out I modified the package.json to add an exports field with the entry points for the imports I showed above. This is what I added:

"exports": {
    ".": "./index.js",
    "./expressions": "./expressions.js",
    "./pg-core": "./pg-core/index.js",
    "./node-postgres": "./node-postgres/index.js",
    "./node-postgres/migrator": "./node-postgres/migrator/index.js"
}

These changes allowed me to change the imports to how they are documented in the docs (besides for pg), like this:

import { pgTable } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq } from 'drizzle-orm/expressions';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import pg from 'pg';
const { Pool } = pg;

I may be able to help with a PR for this if this is something you would want to have done. I am not sure what all the entry points are that you want to have exposed though. If you want to be less strict though, you can set the exports with wildcards as seen in that Node.js docs link I posted above.

If this does get fixed, you may want to also consider updating your docs to show how to make importing Pool from pg work when using esm, e.g.

import pg from 'pg';
const { Pool } = pg;

I was using drizzle-orm v0.17.6 and Node.js v18.13.0.

Thanks for all you guys do! This project is amazing, and all your hard work is very appreciated!