Unveiling the Power of Drizzle ORM: Key Features that Skyrocketed My Productivity

Inspired by a few of my favorite tech YouTubers, namely Theo and Josh, I was intrigued to try out Drizzle ORM after hearing their rave reviews. Having since incorporated Drizzle into several projects, I've discovered numerous features that have not only enhanced my productivity but have also made the development process more enjoyable. Want to know why Drizzle has made such a difference? Stay with me as I dive into its game-changing features.

1) Great Structured Documentation with a lot of examples

Beyond the very funny marketing landing page (see blogpost cover), Drizzle also finally launched a highly anticipated documentation. What I particularly like is that the documentation is well structured and broken down logically.

First there is Drizzle ORM:

Drizzle ORM is a TypeScript ORM for SQL databases designed with maximum type safety in mind.

And then there is Drizzle Kit :

Drizzle Kit - is a CLI companion for automatic SQL migrations generation and rapid prototyping

For each of these they have the most important topics displayed nicely in a sidebar on the left, and then a table of contents for this particular page on the right sidebar.

2) Easy to remember SQL Query Syntax

When adopting a relatively new library, it can be a struggle to remember the specific API syntax. If you know SQL though, you already know Drizzle.

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

Check out some of these examples:

Simple Select

Drizzle Syntax:

const result = await db.select().from(users);

SQL Syntax:

select * from 'users';

Simple Select with Filters

Drizzle Syntax:

await db.select().from(users).where(eq(users.id, 42));

SQL Syntax:

select * from 'users' where 'id' = 42;

Select Distinct

Drizzle Syntax:

await db.selectDistinct().from(users).orderBy(usersTable.id, usersTable.name);

SQL Syntax:

select distinct "id", "name" from "users" order by "users"."id", "users"."name";

3) Support for Complex SQL Queries

"While most ORMs manage to handle the simpler examples adequately, Drizzle takes it a step further. It truly excels in dealing with complex TypeScript joins, navigating terrain where many others struggle. Moreover, Drizzle shines by offering advanced database features, such as transactions, thereby enhancing its functionality and flexibility

Smart Type Inference on Joins

In the following there's a real life query from one of my projects, that has a lot of conditions, filters and all sort of things going on that I use for server side filtered table:

export async function getTournaments({
    userId,
    timeframe = undefined,
    limit = undefined,
    sort = "asc",
    from = undefined,
    to = undefined,
    organizerId = undefined,
    eventId = undefined,
    minBuyIn = undefined,
    maxBuyIn = undefined,
    minGuarantee = undefined,
    maxGuarantee = undefined,
}: {
    userId: string
    timeframe?: string
    limit?: number
    sort?: "asc" | "desc"
    from?: string
    to?: string
    organizerId?: string
    eventId?: string
    minBuyIn?: number
    maxBuyIn?: number
    minGuarantee?: number
    maxGuarantee?: number
}) {

    const now = new Date().toUTCString()

    let query = db.select({
        tournamentId: tournaments.id,
        tournamentStartAt: tournaments.startAt,
        organizerName: organizers.name,
        organizerId: organizers.id,
        eventName: events.name,
        eventId: events.id,
        tournamentName: tournaments.name,
        buyIn: tournaments.buyIn,
        tournamentGuarantee: tournaments.guarantee,
        buyInCurrency: buyInCurrency.symbol,
        guaranteeCurrency: guaranteeCurrency.symbol,
        userId: userTournaments.userId

    }).from(tournaments)
        .leftJoin(events, eq(tournaments.eventId, events.id))
        .leftJoin(organizers, eq(events.organizerId, organizers.id))
        .leftJoin(buyInCurrency, eq(tournaments.buyInCurrencyId, buyInCurrency.id))
        .leftJoin(guaranteeCurrency, eq(tournaments.guaranteeCurrencyId, guaranteeCurrency.id))
        .leftJoin(userTournaments, eq(tournaments.id, userTournaments.tournamentId))

    if (limit) query = query.limit(limit)

    if (sort === "desc") query = query.orderBy(desc(tournaments.startAt))

    if (sort === "asc") query = query.orderBy(asc(tournaments.startAt))

    let whereCondition;

    if (timeframe === "my-tournaments") {
        whereCondition = eq(userTournaments.userId, userId);
    } else if (timeframe === "upcoming") {
        whereCondition = and(
            isNotNull(tournaments.startAt),
            gte(tournaments.startAt, now),
            isNull(userTournaments.userId)
        );
    } else if (timeframe === "past") {
        whereCondition = and(
            isNotNull(tournaments.startAt),
            lt(tournaments.startAt, now),
            isNull(userTournaments.userId)
        );
    } else {
        whereCondition = isNotNull(userTournaments.userId);
    }

    // Add from and to filters
    if (from && to) {
        const fromDate = new Date(from).toISOString();

        const toEndOfDay = new Date(to);
        toEndOfDay.setHours(23, 59, 59, 999);
        const toDate = new Date(to);
        toDate.setHours(23, 59, 59, 999);

        whereCondition = and(
            whereCondition,
            between(tournaments.startAt, fromDate, toDate.toISOString())
        );
    }

    // Add filter for organizerId
    if (organizerId) {
        whereCondition = and(
            whereCondition,
            eq(organizers.id, Number(organizerId)),
        )
    }

    // Add filter for eventId
    if (eventId) {
        whereCondition = and(
            whereCondition,
            eq(events.id, Number(eventId)),
        )
    }

    // Add filter for minBuyIn
    if (minBuyIn) {
        whereCondition = and(
            whereCondition,
            gte(tournaments.buyIn, minBuyIn.toString())
        )
    }

    // Add filter for maxBuyIn
    if (maxBuyIn) {
        whereCondition = and(
            whereCondition,
            lte(tournaments.buyIn, maxBuyIn.toString())
        )
    }

    // Add filter for minGuarantee
    if (minGuarantee) {
        whereCondition = and(
            whereCondition,
            gte(tournaments.guarantee, minGuarantee.toString())
        )
    }

    // Add filter for maxGuarantee
    if (maxGuarantee) {
        whereCondition = and(
            whereCondition,
            lte(tournaments.guarantee, maxGuarantee.toString())
        )
    }


    query = query.where(whereCondition);

    // console.log(query.toSQL())

    const data = await query

    return data
}

With all of this complicated logic Drizzle still gives me perfect type annotations.

Even with partial select, when you expect any joined table to be potentially have null types for every field, Drizzle shines:

4) Transaction & Prepared Statement Support

When I work with a relational database, I normalize my tables and keep everything neatly organised. However, this approach can introduce complexities when it comes to populating these tables. Let's explore this issue using a practical example from my SaaS, BacklinkGPT.com.

In the process of creating a record for my campaign backlink prospect table, a sequence of insertions must take place first. Here are the necessary steps:

• Insert the website into the websites table of a and get the corresponding id to link it to a backlink prospect
• Insert the contact into the contacts table and get the corresponding id to link it to the backlink prospect
• Insert the backlink prospect with the just inserted website_id , contact_id and other backlink prospect info
• Finally, insert the backlink_prospect_id as well as the campign_id to finally get the campaign_backlink_prospect_id

Being a big fan of Supabase, I tried to do this first with the Supabase SDK and wasn't happy with the implementation. However, the implementation fell short of my expectations. Whenever an insert operation failed, I found myself writing extensive logic to undo the changes.

What would truly solve this issue is the ability to use transactions to manage these operations:

SQL transaction is a grouping of one or more SQL statements that interact with a database. A transaction in its entirety can commit to a database as a single logical unit or rollback (become undone) as a single logical unit.

Unfortunately, as of now you can only do this with Supabase by calling a supabase function with the .rpc method. While this could technically be a great solution, I did not like to have this complex logic stored in a postgres function with no version control.

Luckily, Drizzle ORM supports transactions, and it was quite easy to implement with their excellent docs:

const { backlinkProspectId, campaignBacklinkProspectId } = await db.transaction(async (tx) => {
  
    const website = await tx.insert(websites).values(formData.website)
        .onConflictDoUpdate({
            target: [websites.url, websites.createdBy],
            set: {
                ...formData.website
            },
            where: eq(websites.createdBy, formData.website.createdBy)
        }).returning()

    console.log(`Created website with id ${website[0].id}`);


    const contact = await tx.insert(contacts).values({
        ...formData.contact
    }).returning()


    console.log(`Created contact with id ${contact[0].id}`);

    const backlinkProspect = await tx.insert(backlinkProspects).values({
        ...formData.backlinkProspect,
        websiteId: website[0].id,
        contactId: contact[0].id
    }).onConflictDoUpdate(
        {
            target: [backlinkProspects.websiteId, backlinkProspects.createdBy, backlinkProspects.contactId],
            set: {
                ...formData.backlinkProspect
            },
            where: eq(backlinkProspects.createdBy, formData.backlinkProspect.createdBy)
        }
    ).returning()

    const backlinkProspectId = backlinkProspect[0].id;

    console.log(`Created backlink prospect with id ${backlinkProspectId}`);

    const SCampaignsBacklinkProspects = createInsertSchema(campaignsBacklinkProspects)

    const parsedCampaignBacklinkProspect = SCampaignsBacklinkProspects.parse({
        id: uuidv4(),
        createdBy: formData.backlinkProspect.createdBy,
        campaignId: campaignUuid,
        backlinkProspectId: backlinkProspectId
    })

    console.log(parsedCampaignBacklinkProspect);

    const campaignBacklinkProspect = await tx.insert(campaignsBacklinkProspects).values(parsedCampaignBacklinkProspect).onConflictDoUpdate({
        target: [campaignsBacklinkProspects.campaignId, campaignsBacklinkProspects.backlinkProspectId, campaignsBacklinkProspects.createdBy],
        set: {
            ...parsedCampaignBacklinkProspect
        }
    }).returning()


    const campaignBacklinkProspectId = campaignBacklinkProspect[0].id;

    console.log(`Created campaign backlink prospect with id ${campaignBacklinkProspectId}`);

    revalidatePath(`/campaigns/[campaignId]/backlink-prospects`);

    return { campaignBacklinkProspectId, backlinkProspectId };
});

Prepared Statements

Something I have not tried yet, but would love to see the performance benefits of are prepared statements:

I had a very interesting chat with Alex Blokh who's part of the Drizzle Team:

Sadly, I figured that not all Databases (e.g. Neon) can currently leverage these, but I am sure that with time there might be an opportunity:

5) No more manual creation of zod models or types

By far this is my favourite feature, as it makes you so much more productive as a developer. The power behind Drizzle reminds me a bit about Theo's video: