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
andend
, both columns are timestamps and cannot be null -
user_id
which is the foreign key that references auser
Now let's take into account the following use case:
"We will often query taking into account the
start
andend
columns, just as these columns must be unique taking into account theuser_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.
Top comments (2)
Great overview! Curious, what's the benefit of using indices?
Indices allow for faster lookups on columns which may be queried a lot. The use case described here that a lot of the times we will query with the start and end timestamps is a reason to setup indices for those columns. However indices should be consciously used as it might lead to other operations like inserts being a bit slower as the database needs to build the tree for indices.