Usage

Drizzle uses a syntax that closely resembles native SQL. In this chapter, we will compile some common uses of Drizzle to facilitate quick learning for those without a SQL background, while also discussing the best way to integrate Drizzle with Milkio.

BREAD

A guide to basic operations for adding, modifying, deleting, and querying tables.

Insert

Add a new row to a table.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
await drizzle.insert(userTable).values({ name: 'Amber', email: 'amber@example.com', gender: 'female' });

Update

Modify a row based on conditions. Here, we are changing the name of all users named Amber to Kirsi.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
await drizzle.update(userTable).set({ name: 'Kirsi' }).where(eq(userTable.name, 'Amber'));

Delete

Delete a row based on conditions. Here, we are deleting all users named Kirsi.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
await drizzle.delete(userTable).where(eq(userTable.name, 'Kirsi'));

Get a Row

Retrieve a row of data, returning only the first result if there are multiple matches. Here, we are retrieving the user named Reynard.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
const result = await drizzle.query.table.findFirst({
    where: (table, query) => query.eq(table.name, 'Reynard'),
});

Get All Rows and Sort

Retrieve all rows and sort by created_at. Note that for large tables, this may lead to performance issues.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
const result = await drizzle.query.table.findMany({
    orderBy: userTable.created_at,
});

Pagination

Retrieve data in pages instead of returning all data at once, similar to reading where each page contains a specified number of entries. This can effectively reduce server load.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
const args = { page: 1, pageSize: 10 };
const result = await db.query.userTable.findMany({
    orderBy: userTable.created_at,
    offset: (Number(args.page) - 1) * Number(args.pageSize),
    limit: Number(args.pageSize),
});
console.log(result);

Relationships

Database tables often have relationships with each other. For example, a blog post may have many comments, or an order belongs to a user who placed it.

One-to-One

Let’s assume we have a user table and a user info table. The user info table contains the user’s email address, and in the user info table, there is a userId field that records the id of the corresponding user table.

/src/databases/user-table.ts

export const userTable = mysqlTable(
  "user", // Table Name
  {
    id: bigint("id", { mode: "bigint" }).autoincrement().primaryKey(),
    name: varchar("name", { length: 255 }).notNull(),
  },
  t => ({})
)

export const userRelations = relations(userTable, ({ one, many }) => ({
  userInfo: one(userInfoTable),
}));

/src/databases/user-info-table.ts

export const userInfoTable = mysqlTable(
  "userInfo", // Table Name
  {
    id: bigint("id", { mode: "bigint" }).autoincrement().primaryKey(),
    mail: varchar("mail", { length: 255 }).notNull(),
    userId: bigint("userId", { mode: "bigint" }).notNull(),
  },
  t => ({})
)

export const userInfoRelations = relations(userInfoTable, ({ one, many }) => ({
  user: one(userTable),
}));

One-to-Many

Let’s assume we have an article table and a comment table, where one article can have multiple comments. In the comment table, there is an articleId field that records the id of the article it belongs to.

/src/databases/article-table.ts

export const articleTable = mysqlTable(
  "article", // Table name
  {
    id: bigint("id", { mode: "bigint" }).autoincrement().primaryKey(),
    title: varchar("title", { length: 255 }).notNull(),
    content: text("content").notNull(),
  },
  t => ({})
)

export const articleRelations = relations(articleTable, ({ one, many }) => ({
  comment: many(commentTable),
}));

/src/databases/comment-table.ts

export const commentTable = mysqlTable(
  "comment", // Table name
  {
    id: bigint("id", { mode: "bigint" }).autoincrement().primaryKey(),
    comment: varchar("comment", { length: 255 }).notNull(),
    articleId: bigint("articleId", { mode: "bigint" }).notNull(),
  },
  t => ({})
)

export const commentRelations = relations(commentTable, ({ one, many }) => ({
  article: one(articleTable),
}));

Many-to-Many

Let’s consider a scenario with user and organization tables, where a user can join multiple organizations, and an organization can have multiple users. In this case, we need an additional intermediate table to record the relationships between the two tables. It is a common practice to name the intermediate table by adding -to- between the names of the two tables.

/src/databases/user-to-org-table.ts

export const userToOrgTable = mysqlTable(
  "userToOrg", // Table name
  {
    userId: bigint("userId", { mode: "bigint" }).notNull(),
    orgId: bigint("orgId", { mode: "bigint" }).notNull(),
  },
  t => ({})
)

export const userToOrgRelations = relations(userToOrgTable, ({ one, many }) => ({
  user: one(userTable),
  org: one(orgTable),
}));

/src/databases/user-table.ts

export const userTable = mysqlTable(
  "user", // Table name
  {
    id: bigint("id", { mode: "bigint" }).autoincrement().primaryKey(),
    name: varchar("name", { length: 255 }).notNull(),
  },
  t => ({})
)

export const userRelations = relations(userTable, ({ one, many }) => ({
  userToOrgTable: many(userToOrgTable),
}));

/src/databases/org-table.ts

export const orgTable = mysqlTable(
  "org", // Table name
  {
    id: bigint("id", { mode: "bigint" }).autoincrement().primaryKey(),
    name: varchar("name", { length: 255 }).notNull(),
  },
  t => ({})
)

export const orgRelations = relations(orgTable, ({ one, many }) => ({
  userToOrgTable: many(userToOrgTable),
}));

Relationship Queries

In Drizzle, you can easily query associated data using with.

import { useDrizzle } from "/src/uses/drizzle";
import { articleTable } from "/src/databases/article-table.ts";

const drizzle = await useDrizzle();
const result = await drizzle.query.articleTable.findMany({
  where: (table, query) => query.eq(table.title, "Hello World"),
  with: {
    comment: true,
  }
});

Type Safety

In the API section, we learned that Milkio can restrict API parameters based on TypeScript types.

By directly using table types, we can easily ensure that API parameters align with the database table structure. We no longer need to repeatedly define types in controllers as we did in the past.

/src/apps/user/add.ts

import { defineApi } from "milkio";
import { DBInsert } from "milkio-drizzle";
import { userTable } from "../generated/database-schema";

export const api = defineApi({
  meta: {},
  async action(
    params: {
      user: DBInsert<typeof userTable>;
    },
    context
  ) {
    const drizzle = await useDrizzle();
    await drizzle.insert(userTable).values(params.user);
  },
});

Using the DBInsert helper type, we can convert tables into TypeScript types required for insertion.

Additionally, Milkio provides various helper types to better restrict params in APIs.

DBInsert

Type for inserting data, aligned with the type defined in your database table.

DBInsert<typeof userTable>;

DBSelect

Type for selecting data, aligned with the type defined in your database table.

DBSelect<typeof userTable>;

DBPartial

Makes all fields in the database table optional, typically used in update APIs that allow incremental submissions.

DBPartial<typeof userTable>;

DBOmit

Removes fields from the database table, commonly used to prevent certain fields from being updated, such as id, createdAt, updatedAt, deletedAt, and to avoid updating permission-related fields like isAdmin.

DBOmit<typeof userTable, "id" | "createdAt" | "updatedAt" | "deletedAt">;

Type Tags

In the API section, we learned about Typia’s type tags. Can we also add type tags to tables for stricter type constraints?

Drizzle allows us to define the actual type of a table using $type and add type tags. Let’s add type tags to the user table created in the previous section for more stringent type constraints.

export const userTable = mysqlTable(
  "user",
  {
    id: int("id", { unsigned: true }).autoincrement().primaryKey(),
    name: varchar("name", { length: 32 }).$type<string & typia.tags.MaxLength<32>>(),
    avatar: varchar("avatar", { length: 255 }).$type<string & typia.tags.Format<"url">(),
    email: varchar("email", { length: 320 }).unique().$type<string & typia.tags.Format<"email">(),
    gender: varchar("gender", { length: 320 }).unique().$type<"male" | "female" | "other">(),
    createdAt: datetime("createdAt").notNull().default(sql`CURRENT_TIMESTAMP`),
    updatedAt: datetime("updatedAt").notNull().default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),
  },
  (t) => ({})
);

JSON

Both MySQL and PostgreSQL support the JSON type, which means we can store more flexible data types in tables. Our user table can even be rewritten like this:

/src/databases/user-table.ts

export const userTable = mysqlTable(
  "user",
  {
    id: int("id", { unsigned: true }).autoincrement().primaryKey(),

    // If you're using PostgreSQL, `jsonb` is a better choice
    data: json("data").$type<{ name: string, email: string, gender: 'male' | 'female' | 'other' }>().notNull(),

    createdAt: datetime("createdAt").notNull().default(sql`CURRENT_TIMESTAMP`),
    updatedAt: datetime("updatedAt").notNull().default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),
  },
  (t) => ({})
);

Then, we can use the data field to store user data.

import { useDrizzle } from "/src/uses/drizzle";
import { userTable } from "/src/databases/user-table.ts";

const drizzle = await useDrizzle();
await drizzle.insert(userTable).values({data: { name: 'Amber', email: 'amber@example.com', gender: 'female' }});

JSON gives us great flexibility. When we are unsure about the fields a table may have, we can use json for storage. However, if you need to query the data, performance will be much lower compared to querying regular fields unless you establish multi-value indexes. So, please use it judiciously.

What’s Next?

Drizzle has many more useful features. Dive deeper by reading the Drizzle documentation!