DEV Community

Cover image for DrizzleORM v0.11 | 18 months into development
Alex Blokh
Alex Blokh

Posted on • Originally published at alexblokh.hashnode.dev

DrizzleORM v0.11 | 18 months into development

DrizzleORM - is an open source TypeScript ORM, supports PostgreSQL and about to have MySQL and SQLite support in couple of weeks. We've decided it's time to share it with public.

With drizzle you have a fully typed SQL schema in-code which benefits you in multiple different major ways, which I'll cover later

// declaring enum in database
export const popularityEnum = createEnum({ alias: 'popularity', values: ['unknown', 'known', 'popular'] });

export class CountriesTable extends PgTable<CountriesTable> {
  id = this.serial("id").primaryKey();
  name = this.varchar("name", { size: 256 })

  // declaring index
  nameIndex = this.uniqueIndex(this.name)

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

export class CitiesTable extends PgTable<CitiesTable> {
  id = this.serial("id").primaryKey();
  name = this.varchar("name", { size: 256 })
  countryId = this.int("country_id").foreignKey(CountriesTable, (country) => country.id)

  // declaring enum column in table
  popularity = this.type(popularityEnum, "popularity")

  public tableName(): string {
    return 'cities';
  }
}
Enter fullscreen mode Exit fullscreen mode

This is quick start example of how you connect to the database and make your first query with typed result

import { drizzle, PgTable } from 'drizzle-orm'

export class UsersTable extends PgTable<UsersTable> {
  public id = this.serial('id').primaryKey();
  public fullName = this.text('full_name');
  public phone = this.varchar('phone', { size: 256 });

  public tableName(): string {
    return 'users';
  }
}
export type User = InferType<UsersTable>

const db = await drizzle.connect("postgres://user:password@host:port/db");
const usersTable = new UsersTable(db);

const users: User[] = await usersTable.select().execute();
Enter fullscreen mode Exit fullscreen mode

This is how you use WHERE statement with filters, run partial select queries, use limit/offset and orderBy

await table.select().where(
  eq(table.id, 42)
).execute();

// you can combine filters with eq(...) or or(...)
await table.select().where(
  and([eq(table.id, 42), eq(table.name, "Dan")])
).execute();

await table.select().where(
  or([eq(table.id, 42), eq(table.id, 1)])
).execute();

// partial select
const result = await table.select({
     mapped1: table.id,
     mapped2: table.name,
}).execute();
const { mapped1, mapped2 } = result[0];

// limit offset & order by
await table.select().limit(10).offset(10).execute()
await table.select().orderBy((table) => table.name, Order.ASC)
await table.select().orderBy((table) => table.name, Order.DESC)
Enter fullscreen mode Exit fullscreen mode

This is how you run inserts, updates and deletes

const result = await usersTable.insert({
  name: "Andrew",
  createdAt: new Date(),
}).execute();

const result = await usersTable.insertMany([{
  name: "Andrew",
  createdAt: new Date(),
}, {
  name: "Dan",
  createdAt: new Date(),
}]).execute();

await usersTable.update()
  .where(eq(usersTable.name, 'Dan'))
  .set({ name: 'Mr. Dan' })
  .execute();

await usersTable.delete()
  .where(eq(usersTable.name, 'Dan'))
  .execute();
Enter fullscreen mode Exit fullscreen mode

One of the most powerful features we have in our ORM are fully typed joins, compiler won't let you make a mistake

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

const result = await citiesTable.select()
  .leftJoin(usersTable, (cities, users) => eq(cities.userId, users.id))
  .where((cities, users) => eq(cities.id, 1))
  .execute();

const citiesWithUsers: { city: City, user: User }[] = result.map((city, user) => ({ city, user }));
Enter fullscreen mode Exit fullscreen mode

Here's a many to many relationship example

export class UsersTable extends PgTable<UsersTable> {
  id = this.serial("id").primaryKey();
    name = this.varchar("name");
}

export class ChatGroupsTable extends PgTable<ChatGroupsTable> {
  id = this.serial("id").primaryKey();
}

export class ManyToManyTable extends PgTable<ManyToManyTable> {
  userId = this.int('user_id').foreignKey(UsersTable, (table) => table.id, { onDelete: 'CASCADE' });
  groupId = this.int('group_id').foreignKey(ChatGroupsTable, (table) => table.id, { onDelete: 'CASCADE' });
}

...
const usersTable = new UsersTable(db);
const chatGroupsTable = new ChatGroupsTable(db);
const manyToManyTable = new ManyToManyTable(db);

// querying user group with id 1 and all the participants(users)
const usersWithUserGroups = await manyToManyTable.select()
  .leftJoin(usersTable, (manyToMany, users) => eq(manyToManyTable.userId, users.id))
  .leftJoin(chatGroupsTable, (manyToMany, _users, chatGroups) => eq(manyToManyTable.groupId, chatGroups.id))
  .where((manyToMany, _users, userGroups) => eq(userGroups.id, 1))
  .execute();
Enter fullscreen mode Exit fullscreen mode

Last but not least are migrations. We've implemented a CLI tool for automatic migrations generation, which does handle renames and deletes by prompting you to resolve.

For a typescript schema below

import { PgTable } from "drizzle-orm";

export class UsersTable extends PgTable <UsersTable> {
  public id = this.serial("id").primaryKey();
  public fullName = this.varchar("full_name", { size: 256 });

  public fullNameIndex = this.index(this.fullName);

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

export class AuthOtpTable extends PgTable <AuthOtpTable> {
  public id = this.serial("id").primaryKey();
  public phone = this.varchar("phone", { size: 256 });
  public userId = this.int("user_id").foreignKey(UsersTable, (t) => t.id);

  public tableName(): string {
    return "auth_otp";
  }
}
Enter fullscreen mode Exit fullscreen mode

It will automatically generate SQL migration

CREATE TABLE IF NOT EXISTS auth_otp (
    "id" SERIAL PRIMARY KEY,
    "phone" character varying(256),
    "user_id" INT
);

CREATE TABLE IF NOT EXISTS users (
    "id" SERIAL PRIMARY KEY,
    "full_name" character varying(256)
);

DO $$ BEGIN
 ALTER TABLE auth_otp ADD CONSTRAINT auth_otp_user_id_fkey FOREIGN KEY ("user_id") REFERENCES users(id);
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS users_full_name_index ON users (full_name);
Enter fullscreen mode Exit fullscreen mode

Feel free to give it a try - https://www.npmjs.com/package/drizzle-orm

If you have any questions or feature request - reach me out on tweeter

thanks!

Latest comments (3)

Collapse
 
langstra profile image
Wybren Kortstra • Edited

Hey there, thanks for the article and great work on the orm. I was wondering what the use case is here? From a learning perspective it is great, obviously. However, there are quite some great (and less great) orms out there.
Personally I like mikro-orm, because of the way the define entities, do transaction and compute changes before flushing them. In comparison, this feels a little more like a query builder than an orm.
Wondering about your take on it and where you expect this project is heading. And also interested in your take on other orms.

Collapse
 
_alexblokh profile image
Alex Blokh • Edited

Hey @langstra, thanks for your feedback!
The most important thing is we're not trying to abstract away from SQL, we're building abstraction on SQL, which is very important. The main goal is if you know how to solve problem in SQL you don't have to find out how to solve problem in lets say mikro-orm, cause mikro-orm enforces you to define SQL schema with annotations, abstracting SQL away from you and you now becoming a mikro-orm developer or prisma developer, you now have to solve business challenges in the domain of both mikro-orm and SQL as opposed to just solving them in SQL domain.

this feels a little more like a query builder than an orm

Yes, exactly, there're two main parts to the system. Schema definition and fully typed, flexible query builder. The goal was to again let you do everything you'd wanna do in SQL, but safe thanks to ground top strongly typed API.
The third part of the system which is an opt-in one - are migrations. We've built a CLI for you to generate migrations completely automatically, resolving renames and deletes by simply prompting you. And you have an opt-in solution to run generated migrations with us too, or you can run them however you want. We keep you in control and don't interfere which is very hard to achieve design wise.

This tool was also designed to be an opt-in solution by itself, so let's say you have a project done with knex, all you need to do is wrap knex in our session, define tables you want to be typed and you're ready to go, you can have knex and drizzle-orm side by side.

There're so much I would love to share, especially comparisons with other ORMs and stuff, both architectural and performance wise.

I'm writing a lot of stuff currently and we're about to drop a major redesign with fixes for most of the design flaws and support for MySQL and SQLite

So stay tuned!

Collapse
 
langstra profile image
Wybren Kortstra

The point about becoming an x-orm developer is a good point. Really interested in what you are about to drop in. Also would love to read more about it.