Getting Started with Drizzle ORM: A Beginner's Guide

In today's article I will give you some information related to the creation and application of migrations, as well as the definition of table schemas and how to interact with the database itself using Drizzle ORM.

Introduction

In the past I had used Drizzle multiple times but had never written a specific article for it that could serve as a guide for new users. Addressing different topics, with examples and links to documentation.

What will be covered

• Configuring Migrations using Drizzle Kit
• Data modeling using Drizzle ORM
• Defining relationships between tables
• Definition of indexes and constraints
• Interaction with the database

Prerequisites

It is expected that you have a basic knowledge of Node.js and that you have used an ORM or Query Builder in the past. As well as basic knowledge about relational databases.

Getting started

First we need to install the necessary dependencies:

npm install drizzle-orm better-sqlite3

As you may have noticed in the previous command, in today's article we are going to use the SQLite dialect, so that as many people as possible can try it out without having a process running.

Additionally, we need to install the following dependencies for the development environment:

npm install --dev drizzle-kit @types/better-sqlite3

With the dependencies installed, we can move on to drizzle configuration so that the paths to the database schema can be defined and in which path the migrations should be generated.

// drizzle.config.ts
import type { Config } from "drizzle-kit";

export default {
  schema: "./schema.ts",
  out: "./migrations",
  driver: "better-sqlite",
  dbCredentials: {
    url: "./local.db",
  },
  verbose: true,
  strict: true,
} satisfies Config;

The default file name is drizzle.config.ts, it is worth mentioning that the file name may be different, however when running drizzle-kit the --config= flag must be specified with the file path configuration.

Speaking of the configuration file, the database schema paths, the migration path, which driver should be used and the SQLite database path were defined. Last but not least, the verbose and strict properties are ideal if you want to have a prompt with more information when migrations are applied.

Table schemas

With this we can now move on to the next point which is the definition of the schema of the database tables. Drizzle contains a collection of primitives that are specific to each dialect. Taking the following table as an example:

// schema.ts
import {
  sqliteTable,
  integer,
  text,
} from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  username: text("username").unique().notNull(),
});

In the code above we have a table called users that contains two columns. We have the id column whose data type is integer, which is an auto-incrementable primary key. Just like the username column, which has data type text, it must be unique and not null.

This time we will create a second table, which must contain a one-to-many relationship. Let's take into account the following table:

// schema.ts
import {
  unique,
  sqliteTable,
  integer,
  text,
} from "drizzle-orm/sqlite-core";

// ...

export const tasks = sqliteTable(
  "tasks",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    name: text("name").notNull(),
    start: integer("start", { mode: "timestamp" }).notNull(),
    end: integer("end", { mode: "timestamp" }).notNull(),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
  });

In the code snippet above we have a table called tasks that has the following five columns:

• id which is the primary key
• name which has data type text and cannot be null
• start and end, both columns are timestamps and cannot be null
• user_id which is the foreign key that references a user

Now let's take into account the following use case:

"We will often query taking into account the start and end columns, just as these columns must be unique taking into account the user_id."

Taking into account the case indicated in the previous paragraph, the ideal would be to define that the start and end columns should be indexed, as well as a constraint should be created between these columns and the user_id to ensure that they are unique. Like this:

// schema.ts
import {
  unique,
  sqliteTable,
  integer,
  text,
} from "drizzle-orm/sqlite-core";

// ...

export const tasks = sqliteTable(
  "tasks",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    name: text("name").notNull(),
    start: integer("start", { mode: "timestamp" }).notNull(),
    end: integer("end", { mode: "timestamp" }).notNull(),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
  },
  (table) => ({
    startIndex: index("start_index").on(table.start),
    endIndex: index("end_index").on(table.end),
    timeUniqueConstraint: unique("time_unique_constraint").on(
      table.start,
      table.end,
      table.userId
    ),
  })
);

Not forgetting to mention that in the definition of the foreign key we have specified that when the user is deleted, all rows related to the user must be removed.

Table relations

With both tables defined, we need to specify the relationships between them. I had just mentioned that the relationship would be one-to-many, we can define this as follows:

// schema.ts
import { relations } from "drizzle-orm";

// ...

export const userRelations = relations(users, ({ many }) => ({
  tasks: many(tasks),
}));

export const tasksRelations = relations(tasks, ({ one }) => ({
  user: one(users, {
    fields: [tasks.userId],
    references: [users.id],
  }),
}));

In the code snippet above we specify the relationships between the tables and to which columns of each the keys should be mapped.

The users table can contain several tasks but a task must only be associated with one user. And in this way, the contraint that was created in the tasks table between the start, end and user_id columns is also formalized.

Migrations

With the database tables defined and the relationships between them specified, we can now create the first migration, to do so simply execute the following command:

npm run drizzle-kit generate:sqlite

The above command takes into account the drizzle.config.ts file that we had created at the beginning of the article and it is in this command that the --config= flag must be specified if another name is given to the file.

The expected behavior is that a folder called /migrations is created with the newly created migration.

If it was successful, we can now apply this same migration that was created by running the following command:

npm run drizzle-kit push:sqlite

The expected behavior is that the migration that will be applied will be shown in the terminal and a prompt asking whether we want to apply these same changes. For this same reason, the verbose and strict properties were added to the Drizzle configuration file.

Database Client

With the migrations pulled into the database, we can move on to the next step, which involves creating the database client. Which may look similar to the following:

// db.ts
import {
  drizzle,
  type BetterSQLite3Database,
} from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";

import * as schema from "./schema";

const sqlite = new Database("local.db");

export const db: BetterSQLite3Database<typeof schema> = drizzle(sqlite, {
  schema,
});

Taking into account the code snippet above, one thing worth highlighting is the import of chemas from the database that can be used to have intellisense in the text editor. You will soon feel the benefits of this last point.

Using the .insert() method we can define which table we want to add a new row to, and using the .values() method we can define the data to be inserted. This data can be an Object if we want to add just one row or an Array if we want to add multiple rows.

// single row
await db.insert(users).values({ username: "Foo" });

// multiple rows
await db
  .insert(users)
  .values([
    { username: "Bar" },
    { username: "Baz" }
  ]);

In the example above, what is returned from Promise is just some metadata, such as changed rows, etc. If you want the datums of the inserted rows to be returned, you can use the .returning() method.

await db
  .insert(users)
  .values({ username: "Foo" })
  .returning();

If we follow the schema of the users table, an error is expected to occur with the insertion of the user called Foo, this is because it was added in a previous example to this one.

To do this, we can use the .onConflictDoNothing() method if we do not want an error to be thrown when a conflict occurs, this is because it had been specified that the username must be unique.

await db
  .insert(users)
  .values({ username: "Foo" })
  .onConflictDoNothing();

If we want to update a specific user, we can use the .update() method where we specify which table the update should be made on. Just as we should take advantage of the .set() method to define which columns should be changed and which row using the .where() method. This way:

await db
  .update(users)
  .set({ username: "Baz" })
  .where(eq(users.username, "Buzz Lightyear"))
  .returning();

On the other hand, if we want to delete a row we can take advantage of the .delete() method where we must specify which table this operation should be performed on.

One thing that must be taken into account is that if the .where() method is not used, all rows in the table are removed.

// delete one row
await db.delete(users).where(eq(users.username, "Bar");

// clear table
await db.delete(users);

To obtain all the rows of a table, it can be done in the following ways:

// SQL-like way (most common)
await db.select().from(users);

// Drizzle query
await db.query.users.findMany();

In the code snippet above, we can use method chaining in order to specify which columns can be selected (in the example above, all of them) and which table this should be done to. While the second approach offers a very similar experience to other ORM's.

Using the query examples from now on, if we want to obtain a row taking into account a column and specific datum we can do it as follows:

await db.query.users.findFirst({
  where: (user, { eq }) => eq(user.username, "Bar"),
});

Another interesting point is that using this API we can also query datums of relationships from other tables, such as obtaining the user and their tasks. This way:

await db.query.users.findFirst({
  where: (user, { eq }) => eq(user.username, "Bar"),
  with: {
    tasks: true,
  }
});

If we want something more granular, we can select which columns from the users table and the tasks table should be returned in the query, as follows:

await db.query.users.findFirst({
  where: (user, { eq }) => eq(user.username, "Bar"),
  columns: {
    username: true
  },
  with: {
    tasks: {
      columns: {
        id: true,
        name: true
      }
    }
  }
});

In the previous example, from the users table we selected the username column while from the tasks table we selected the id and name columns.

With this I conclude the article, the objective was to give an overview of some things that I did not cover in other articles in which I used Drizzle and felt it was necessary. At least to try to help you in the first twenty or thirty minutes of use.

Conclusion

I hope you found this article helpful, whether you're using the information in an existing project or just giving it a try for fun.

Please let me know if you notice any mistakes in the article by leaving a comment.

Comments

[Budi 🇺🇦🇵🇸]

Great overview! Curious, what's the benefit of using indices?