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';
}
}
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();
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)
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();
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 }));
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();
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";
}
}
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);
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!
Oldest comments (3)
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.
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.
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!
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.