Getting Started with Drizzle ORM: A Beginner's Guide
Tags: javascript, typescript, node, beginners
Published: 2023-10-06 20:27:29

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](https://github.com/drizzle-team/drizzle-orm) 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:

```sh
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:

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

With the dependencies installed, we can move on to drizzle [configuration](https://orm.drizzle.team/kit-docs/conf) so that the paths to the database schema can be defined and in which path the migrations should be generated.

```ts
// 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](https://orm.drizzle.team/kit-docs/config-reference) 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](https://orm.drizzle.team/docs/column-types/sqlite) that are specific to each dialect. Taking the following table as an example:

```ts
// 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:

```ts
// 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](https://orm.drizzle.team/docs/indexes-constraints#indexes), as well as a constraint should be created between these columns and the `user_id` to ensure that they are unique. Like this:

```ts
// 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:

```ts
// 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](https://orm.drizzle.team/kit-docs/commands#generate-migrations), to do so simply execute the following command:

```sh
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](https://orm.drizzle.team/kit-docs/commands#prototype--push) this same migration that was created by running the following command:

```sh
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](https://orm.drizzle.team/docs/quick-sqlite/better-sqlite3). Which may look similar to the following:

```ts
// 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](https://orm.drizzle.team/docs/insert) 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.

```ts
// 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.

```ts
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.

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

If we want to update a specific user, we can use the `.update()` [method](https://orm.drizzle.team/docs/update) 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:

```ts
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](https://orm.drizzle.team/docs/delete) 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.

```ts
// 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:

```ts
// 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](https://orm.drizzle.team/docs/select) 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](https://orm.drizzle.team/docs/rqb) 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:

```ts
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:

```ts
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:

```ts
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.