Drizzle ORM

TypeScript ORM for SQL

ORMSQL
drizzle-team/drizzle-ormdrizzle-orm

README

Drizzle ORM npm

npmnpm bundle sizeDiscordLicense
If you know SQL, you know Drizzle ORM

Drizzle ORM is a TypeScript ORM for SQL databases designed with maximum type safety in mind. It comes with a drizzle-kit CLI companion for automatic SQL migrations generation. Drizzle ORM is meant to be a library, not a framework. It stays as an opt-in solution all the time at any levels.

The ORM main philosophy is "If you know SQL, you know Drizzle ORM". We follow the SQL-like syntax whenever possible, are strongly typed ground top and fail at compile time, not in runtime.

Drizzle ORM is being battle-tested on production projects by multiple teams 🚀 Give it a try and let us know if you have any questions or feedback on Discord.

Feature list


- Full type safety
- Smart automated migrations generation
- No ORM learning curve
- SQL-like syntax for table definitions and queries
- Best in class fully typed joins
- Fully typed partial and non-partial selects of any complexity
- Auto-inferring of TS types for DB models for selections and insertions separately
- Zero dependencies

Supported databases


| Database    | Status | |
|:------------|:-------:|:---|
| PostgreSQL  | ✅ | Docs|
| MySQL       | ✅      |Docs|
| SQLite      | ✅      |Docs|
| DynamoDB    | ⏳      |            |
| MS SQL      | ⏳      |            |
| CockroachDB | ⏳      |            |

Installation


  1. ```bash
  2. npm install drizzle-orm
  3. npm install -D drizzle-kit
  4. ```

Feature showcase (PostgreSQL)


Note: don't forget to install pg and @types/pg packages for this example to work.


  1. ```typescript
  2. import { eq } from 'drizzle-orm/expressions';
  3. import { drizzle } from 'drizzle-orm/node-postgres';
  4. import { InferModel, integer, pgTable, serial, text, timestamp, varchar } from 'drizzle-orm/pg-core';
  5. import { sql } from 'drizzle-orm/sql';
  6. import { Pool } from 'pg';

  8. export const users = pgTable('users', {
  9.   id: serial('id').primaryKey(),
  10.   fullName: text('full_name').notNull(),
  11.   phone: varchar('phone', { length: 20 }).notNull(),
  12.   role: text<'user' | 'admin'>('role').default('user').notNull(),
  13.   cityId: integer('city_id').references(() => cities.id),
  14.   createdAt: timestamp('created_at').defaultNow().notNull(),
  15.   updatedAt: timestamp('updated_at').defaultNow().notNull(),
  16. });

  18. export type User = InferModel<typeof users>;
  19. export type NewUser = InferModel<typeof users, 'insert'>;

  21. export const cities = pgTable('cities', {
  22.   id: serial('id').primaryKey(),
  23.   name: text('name').notNull(),
  24. });

  26. export type City = InferModel<typeof cities>;
  27. export type NewCity = InferModel<typeof cities, 'insert'>;

  29. const pool = new Pool({
  30.   connectionString: 'postgres://user:password@host:port/db',
  31. });

  33. const db = drizzle(pool);

  35. // Insert
  36. const newUser: NewUser = {
  37.   fullName: 'John Doe',
  38.   phone: '+123456789',
  39. };
  40. const insertedUsers /* : User */ = await db.insert(users).values(newUser).returning();
  41. const insertedUser = insertedUsers[0]!;

  43. const newCity: NewCity = {
  44.   name: 'New York',
  45. };
  46. const insertedCities /* : City */ = await db.insert(cities).values(newCity).returning();
  47. const insertedCity = insertedCities[0]!;

  49. // Update
  50. const updateResult /* : { updated: Date }[] */ = await db.update(users)
  51.   .set({ cityId: insertedCity.id, updatedAt: new Date() })
  52.   .where(eq(users.id, insertedUser.id))
  53.   .returning({ updated: users.updatedAt });

  55. // Select
  56. const allUsers /* : User[] */ = await db.select(users);

  58. // Select custom fields
  59. const upperCaseNames /* : { id: number; name: string }[] */ = await db.select(users)
  60.   .fields({
  61.     id: users.id,
  62.     name: sql`upper(${users.fullName})`.as<string>(),
  63.   });

  65. // Joins
  66. // You wouldn't BELIEVE how SMART the result type is! 😱
  67. const allUsersWithCities = await db.select(users)
  68.   .fields({
  69.     user: {
  70.       id: users.id,
  71.       name: users.fullName,
  72.     },
  73.     cityId: cities.id,
  74.     cityName: cities.name,
  75.   })
  76.   .leftJoin(cities, eq(users.cityId, cities.id));

  78. // Delete
  79. const deletedNames /* : { name: string }[] */ = await db.delete(users)
  80.   .where(eq(users.id, insertedUser.id))
  81.   .returning({ name: users.fullName });
  82. ```

See full docs for further reference:
- PostgreSQL
- MySQL
- SQLite