drizzle-orm

What is drizzle-orm?

Drizzle ORM is a TypeScript-first ORM for Node.js that focuses on type safety, performance, and simplicity. It provides a fluent API for defining and querying your database schema, making it easier to work with SQL databases in a type-safe manner.

What are drizzle-orm's main functionalities?

Schema Definition

Drizzle ORM allows you to define your database schema using a fluent API. This makes it easy to create and manage your database tables with type safety.

const { defineSchema, types } = require('drizzle-orm');

const schema = defineSchema({
  users: {
    id: types.int().primaryKey().autoIncrement(),
    name: types.string().notNull(),
    email: types.string().unique().notNull()
  }
});

Query Building

You can build and execute SQL queries using a fluent API. This example demonstrates how to select users with the name 'John Doe'.

const { select } = require('drizzle-orm');

const users = await select(schema.users)
  .where(schema.users.name.eq('John Doe'))
  .execute();

Type Safety

Drizzle ORM ensures type safety throughout your database operations. This example shows how to insert a new user into the users table with type-checked values.

const { insert } = require('drizzle-orm');

await insert(schema.users).values({
  name: 'Jane Doe',
  email: '[e-mail]'
}).execute();

Migrations

Drizzle ORM supports database migrations, allowing you to evolve your database schema over time. This example demonstrates how to create and drop a 'posts' table.

const { migrate } = require('drizzle-orm');

await migrate(schema, {
  migrations: [
    {
      up: async (db) => {
        await db.schema.createTable('posts', {
          id: types.int().primaryKey().autoIncrement(),
          title: types.string().notNull(),
          content: types.text().notNull(),
          userId: types.int().references(schema.users.id)
        });
      },
      down: async (db) => {
        await db.schema.dropTable('posts');
      }
    }
  ]
});

Other packages similar to drizzle-orm

sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. Compared to Drizzle ORM, Sequelize has a larger community and more extensive documentation but may lack some of the type safety features that Drizzle ORM offers.

typeorm

TypeORM is an ORM for TypeScript and JavaScript (ES7, ES6, ES5). It supports many database systems including MySQL, MariaDB, PostgreSQL, SQLite, and more. TypeORM is known for its extensive feature set and active community. It provides decorators for defining entities and supports advanced features like migrations, caching, and more. Compared to Drizzle ORM, TypeORM offers more features but may be more complex to set up and use.

knex

Knex.js is a SQL query builder for PostgreSQL, MySQL, MariaDB, SQLite3, and Oracle. It features a flexible and powerful API for building SQL queries and managing database schema. While Knex.js is not a full-fledged ORM, it can be used in conjunction with other libraries to provide ORM-like functionality. Compared to Drizzle ORM, Knex.js offers more control over raw SQL queries but lacks the built-in type safety and higher-level abstractions.

README

DrizzleORM

DrizzleORM is an ORM framework for TypeScript. It offers you several levels of Database communication:

• Typesafe Table View approach
• Typesafe Query Builder
• Simple SQL query execution

Drizzle ORM is highly influenced by Exposed and Jetbrains development methodology

Supported Databases

• PostgreSQL

Links

In Progress

Installing

npm install drizzle-orm drizzle-kit

In Progress

yarn add drizzle-orm drizzle-kit
bower install drizzle-orm drizzle-kit

Connecting to database

import { DbConnector } from "drizzle-orm";

// connect via postgresql connection url
const db = await new DbConnector()
	.connectionString("postgres://user:password@host:port/db")
	.connect();

// or by params
const db = await new DbConnector()
	.params({
		host: '0.0.0.0',
		port: 5432,
		user: 'user',
		password: 'password',
		db: 'optional_db_name'
	}).connect();

Project structure

• tables folder
• migrations folder

Create tables

Users Table

---

export const rolesEnum = createEnum({ alias: 'test-enum', values: ['user', 'guest', 'admin'] });

export default class UsersTable extends AbstractTable<UsersTable> {
  public id = this.serial('id').primaryKey();
  public fullName = this.text('full_name');

  public phone = this.varchar('phone', { size: 256 });
  public media = this.jsonb<string[]>('media');
  public decimalField = this.decimal('test', { precision: 100, scale: 2 }).notNull();
  public bigIntField = this.bigint('test1', 'max_bytes_53');
  public role = this.type(rolesEnum, 'name_in_table').notNull();

  public createdAt = this.timestamp('created_at').notNull();

  public createdAtWithTimezone = this.timestamptz('created_at_time_zone');

  public updatedAt = this.timestamp('updated_at').defaultValue(Defaults.CURRENT_TIMESTAMP);
  public isArchived = this.bool('is_archived').defaultValue(false);

  public phoneFullNameIndex = this.index([this.phone, this.fullName]);
  public phoneIndex = this.uniqueIndex(this.phone);

  public tableName(): string {
    return 'users';
  }
}

Cities Table

---

interface CityMeta {
  population: number,
  connection: string,
}

export default class CitiesTable extends AbstractTable<CitiesTable> {
  public id = this.serial('id').primaryKey();

  public foundationDate = this.timestamp('name').notNull();
  public location = this.varchar('page', { size: 256 });

  public userId = this.int('user_id').foreignKey(UsersTable, (table) => table.id, { onUpdate: 'CASCADE' });

  public metadata = this.jsonb<CityMeta>('metadata');

  public tableName(): string {
    return 'cities';
  }
}

User Groups Table

---

export default class UserGroupsTable extends AbstractTable<UserGroupsTable> {
  public id = this.serial('id').primaryKey();

  public name = this.varchar('name');
  public description = this.varchar('description');

  public tableName(): string {
    return 'user_groups';
  }
}

User to User Groups Table

---

Many to many connection between Users and User Groups

export default class UsersToUserGroupsTable extends AbstractTable<UsersToUserGroupsTable> {
  public groupId = this.int('city_id').foreignKey(UserGroupsTable, (table) => table.id, { onDelete: 'CASCADE' });
  public userId = this.int('user_id').foreignKey(UsersTable, (table) => table.id, { onDelete: 'CASCADE' });

  public manyToManyIndex = this.index([this.groupId, this.userId]);

  public tableName(): string {
    return 'users_to_user_groups';
  }
}

CRUD

SELECT

---

const db = await new DbConnector()
  .connectionString('postgresql://postgres@127.0.0.1/drizzle')
  .connect();

const usersTable = new UsersTable(db);

// select all
const allSelect = await usersTable.select().all();

// select first
const firstSelect = await usersTable.select().findOne();

Sorting and Filtering

---

Select all records from Users where phone is "hello"

const eqSelect = await usersTable.select().where(
  eq(usersTable.phone, 'hello')
).all();

Select all records from Users where both phone is "hello" and phone is "hello"

const andSelect = await usersTable.select().where(
  and([
    eq(usersTable.phone, 'hello'),
    eq(usersTable.phone, 'hello')
  ]),
).all();

Select all records from Users where either phone is "hello" or phone is "hello"

const orSelect = await usersTable.select().where(
  or([eq(usersTable.phone, 'hello')]),
).all();

Select all records from Users using LIMIT and OFFSET

const limitOffsetSelect = await usersTable.select().limit(10).offset(10).all();

Select all records from Users where phone contains "hello"

const likeSelect = await usersTable.select().where(
  like(usersTable.phone, '%hello%')
).all();

Select all records from Users where phone equals to some of values from array

const inArraySelect = usersTable.select().where(
  inArray(usersTable.phone, ['hello'])
).all();

Select all records from Users where phone greater(>) than "hello"

const greaterSelect = usersTable.select().where(
  greater(usersTable.phone, 'hello')
).all();

Select all records from Users where phone less(<) than "hello"

const lessSelect = usersTable.select().where(
  less(usersTable.phone, 'hello')
).all();

Select all records from Users where phone greater or equals(>=) than "hello"

const greaterEqSelect = usersTable.select().where(
  greaterEq(usersTable.phone, 'hello')
).all();

Select all records from Users where phone less or equals(<=)

const lessEqSelect = usersTable.select().where(
  lessEq(usersTable.phone, 'hello')
).all();

Select all records from Users where phone is NULL

const isNullSelect = usersTable.select().where(
  isNull(usersTable.phone)
).all();

Select all records from Users where phone not equals to "hello"

const notEqSelect = usersTable.select().where(
  notEq(usersTable.phone, 'hello')
).all();

Select all records from Users ordered by phone in ascending order

const ordered = await usersTable.select().orderBy((table) => table.phone, Order.ASC).all();

Partial Selecting

   const partialSelect = await usersTable.select({
     mappedId: usersTable.id,
     mappedPhone: usersTable.phone,
   }).all();

   // Usage
   const { mappedId, mappedPhone } = partialSelect;

Update

---

Update fullName to newName in Users where phone is "hello"

await usersTable.update()
  .where(eq(usersTable.phone, 'hello'))
  .set({ fullName: 'newName' })
  .execute();

Update fullName to newName in Users where phone is "hello" returning updated User model

await usersTable.update()
  .where(eq(usersTable.phone, 'hello'))
  .set({ fullName: 'newName' })
  .all();

Update fullName to newName in Users where phone is "hello" returning updated User model

await usersTable.update()
  .where(eq(usersTable.phone, 'hello'))
  .set({ fullName: 'newName' })
  .findOne();

Delete

Delete user where phone is "hello"

await usersTable.delete()
      .where(eq(usersTable.phone, 'hello'))
      .execute();

Delete user where phone is "hello" returning updated User model

await usersTable.delete()
      .where(eq(usersTable.phone, 'hello'))
      .all();

Delete user where phone is "hello" returning updated User model

await usersTable.delete()
      .where(eq(usersTable.phone, 'hello'))
      .findOne();

Insert

Insert user with required fields

await usersTable.insert({
  test: 1,
  createdAt: new Date(),
}).execute();

Insert user with required fields and get all rows as array

const user = await usersTable.insert({
  test: 1,
  createdAt: new Date(),
}).all();

Insert user with required fields and get inserted entity

const user = await usersTable.insert({
  test: 1,
  createdAt: new Date(),
}).findOne();

Insert many users with required fields and get all inserted entities

const users = await usersTable.insertMany([{
      test: 1,
      createdAt: new Date(),
    }, {
      test: 2,
      createdAt: new Date(),
    }]).all();

Insert many users with required fields and get all inserted entities. If such user already exists - update phone field

await usersTable.insertMany([{
      test: 1,
      createdAt: new Date(),
    }, {
      test: 2,
      createdAt: new Date(),
    }])
      .onConflict(
        (table) => table.phoneIndex,
        { phone: 'confilctUpdate' },
      ).all();

Joins

Join One-To-Many Tables

Join Cities with Users and map to city object with full user

const usersTable = new UsersTable(db);
const citiesTable = new CitiesTable(db);

 const userWithCities = await citiesTable.select()
      .where(eq(citiesTable.id, 1))
      .leftJoin(UsersTable,
        (city) => city.userId,
        (users) => users.id)
      .execute();

const citiesWithUserObject = userWithCities.map((city, user) => ({ ...city, user }));

Join Many-To-Many Tables

Join User Groups with Users, using many-to-many table and map response to get user object with groups array

    const usersWithUserGroups = await usersToUserGroupsTable.select()
      .where(eq(userGroupsTable.id, 1))
      .leftJoin(UsersTable,
        (userToGroup) => userToGroup.userId,
        (users) => users.id)
      .leftJoin(UsersToUserGroupsTable, UserGroupsTable,
        (userToGroup) => userToGroup.groupId,
        (users) => users.id)
      .execute();

    const userGroupWithUsers = usersWithUserGroups.group({
      one: (_, dbUser, dbUserGroup) => dbUser!,
      many: (_, dbUser, dbUserGroup) => dbUserGroup!,
    });

    const userWithGroups: ExtractModel<UsersTable> & { groups: ExtractModel<UserGroupsTable>[] } = {
      ...userGroupWithUsers.one,
      groups: userGroupWithUsers.many,
    };

Join User Groups with Users, using many-to-many table and map response to get user group object with users array

    const usersWithUserGroups = await usersToUserGroupsTable.select()
      .where(eq(userGroupsTable.id, 1))
      .leftJoin(UsersTable,
        (userToGroup) => userToGroup.userId,
        (users) => users.id)
      .leftJoin(UsersToUserGroupsTable, UserGroupsTable,
        (userToGroup) => userToGroup.groupId,
        (users) => users.id)
      .execute();

    const userGroupWithUsers = usersWithUserGroups.group({
      one: (_, dbUser, dbUserGroup) => dbUserGroup!,
      many: (_, dbUser, dbUserGroup) => dbUser!,
    });

    const userWithGroups: ExtractModel<UserGroupsTable> & { users: ExtractModel<UsersTable>[] } = {
      ...userGroupWithUsers.one,
      users: userGroupWithUsers.many,
    };

Join using partial field select

Join Cities with Users getting only needed fields form request

await citiesTable.select({
      id: citiesTable.id,
      userId: citiesTable.userId,
    })
    .where(eq(citiesTable.id, 1))
    .leftJoin(UsersTable,
        (city) => city.userId,
        (users) => users.id,
        {
          id: usersTable.id,
        })
    .execute();

const citiesWithUserObject = userWithCities.map((city, user) => ({ ...city, user }));

Migrations

To run migrations generated by drizzle-kit you could use Migrator class

Provide drizzle-kit config path

await drizzle.migrator(db).migrate('src/drizzle.config.yaml');

Another possibility is to provide object with path to folder with migrations

await drizzle.migrator(db).migrate({ migrationFolder: 'drizzle' });

Raw query usage

If you have some complex queries to execute and drizzle-orm can't handle them yet, then you could use rawQuery execution

Execute custom raw query

const res: QueryResult<any> = await db.session().execute('SELECT * FROM users WHERE user.id = $1', [1]);