DEV Community: Au Quoc Hoa

Ergonomic Code with Elysia #2: Connect database with Drizzle ORM

Au Quoc Hoa — Mon, 28 Jul 2025 06:23:23 +0000

Database is essential for storing data from API. And to avoid cumbersome operations with traditional raw SQL commands, we can use ORM. There are many popular ORMs, in the scope of this article, I will introduce you to Drizzle ORM.

Simply put, Drizzle ORM will bring you convenience, instead of writing a SQL sentence like this:

SELECT * FROM public.posts

you can write it directly in Typescript (for type safety) like this:

const result = await db.query.posts.findMany()
// db.query.posts and result are fully type-safe in typescript

Drizzle also has many other cool features like code-first support, serverless,... find out more on their homepage. https://orm.drizzle.team

Create local Postgres with Docker

Postgresql is extremely popular so I won't introduce it again, the same goes for Docker. If you don't have Docker yet, you can install it here.

Then create the following docker-compose.yml file and run docker compose up -d to initialize a new postgres container.

volumes:
  postgres-data:
    name: postgres-data

services:
  postgres:
    image: "postgres:17.5"
    restart: unless-stopped
    container_name: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: "postgres"
      POSTGRES_PASSWORD: "abc12345"
      POSTGRES_DB: "postgres"
    volumes:
      - postgres-data:/var/lib/postgresql/data

Here, for convenience, I will fill in environment variables like POSTGRES_USER, POSTGRES_PASSWORD into the file. However, in reality, everyone should keep it in a separate env file and not put it on version control.

You can now use any DBMS to access the DB via the URI: postgresql://postgres:abc12345@localhost:5432/postgres

Create environment variables and connect Elysia with DB

We will create a .env file to contain the application's configs, which will be different depending on our future deployment environment. You will want to put this .env file in .gitignore to bypass version control

NODE_ENV="development"
PORT="4000"
DATABASE_URL="postgresql://postgres:abc12345@localhost:5432/postgres"

Because I'm used to working with API on port 4000, I will declare port=4000. NODE_ENV is to determine the environment the code is running in.

In the Elysia application, install the necessary packages for Drizzle

bun add drizzle-orm drizzle-typebox pg @paralleldrive/cuid2

bun add -D drizzle-kit @types/pg

If you encounter a version mismatch in drizzle-typebox please refer to the original documentation for installation.

To be able to store the Post created from the API in the previous post, we will create a schema for it. I will create this file in src/db/schema/app.ts

import { createId } from "@paralleldrive/cuid2";
import { integer, pgSchema, text } from "drizzle-orm/pg-core";

// Application Schema
export const appSchema = pgSchema("app");

export const posts = appSchema.table("posts", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  title: text("title").notNull(),
  content: text("content"),

  incrementId: integer("increment_id").generatedAlwaysAsIdentity(),
});

In my experience, we should not use the db's auto-increment code as the primary key, but generate id from text, I use the library @paralleldrive/cuid2 to do this. At the same time, I still save the auto-increment code in the field increment_id

Then you need to export the schema and type of the schema you just created so that drizzle-kit can use it (I will explain more about how to use drizzle-kit below). We will create this export file at src/db/schema/index.ts

export * from "./app";

import { posts } from "./app";

export const table = {
  posts,
} as const;

export type Table = typeof table;

The next step is to create a Drizzle instance to use in the application, I will create it in src/db/index.ts

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

// Create a PostgreSQL connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Create a Drizzle database instance
export const db = drizzle(pool, { schema });

Update Post service

Now we will edit the Post service created in the previous article, using the database to store data.

import { db } from "@/db";
import { postModel } from "./model";
import { table } from "@/db/schema";
import { eq } from "drizzle-orm";

export abstract class PostService {
  static async getPosts() {
    return await db.query.posts.findMany();
  }

  static async getPostById(id: string) {
    const post = await db.query.posts.findFirst({
      where: eq(table.posts.id, id),
    });
    if (!post) {
      throw new Error("Post not found");
    }
    return post;
  }

  static async createPost({ data }: { data: typeof postModel.body.static }) {
    const posts = await db
      .insert(table.posts)
      .values({
        title: data.title,
        content: data.content,
      })
      .returning();
    return posts[0];
  }

  static async updatePost(
    id: string,
    {
      data,
    }: {
      data: typeof postModel.body.static;
    }
  ) {
    const post = await db.query.posts.findFirst({
      where: eq(table.posts.id, id),
    });
    if (!post) {
      throw new Error("Post not found");
    }
    const posts = await db
      .update(table.posts)
      .set({
        title: data.title,
        content: data.content,
      })
      .where(eq(table.posts.id, id))
      .returning();
    return posts[0];
  }

  static async deletePost(id: string) {
    const post = await db.query.posts.findFirst({
      where: eq(table.posts.id, id),
    });
    if (!post) {
      throw new Error("Post not found");
    }
    await db.delete(table.posts).where(eq(table.posts.id, id));
    return post;
  }
}

Okay, now we'll try to create a new post and... failed!

curl http://localhost:4000/post/ \
  --request POST \
  --header 'Content-Type: application/json' \
  --data '{
  "title": "test",
  "content": "test"
}'

Failed query: insert into "app"."posts" ("id", "title", "content", "increment_id") values ($1, $2, $3, default) returning "id", "title", "content", "increment_id"
params: ymhsqaf8rof7wbmgdtcmo8qq,test,test

But don't worry, this is because although we have created the schema for Drizzle to understand the data structure. However, the actual structure on the database has not been created yet.

Code-first with Drizzle-kit

There are usually two ways to create or update a database structure: database first and codebase first (or code-first). Each method has its own advantages and disadvantages, Drizzle supports both methods, you can read more here.

In the scope of this article, I will use option 2 mentioned in the link above to perform migration for quick convenience. If you work with a large codebase, complex project, I recommend you use options 3 or higher to be able to store database schema according to migration versions.

First we need to add the drizzle.config.ts file to the root directory of the project, this is the config file needed for drizzle-kit to work

import type { Config } from "drizzle-kit";

export default {
  schema: "./src/db/schema",
  out: "./drizzle",
  dialect: "postgresql",
  schemaFilter: ["app"],
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Now run bun drizzle-kit push you will notice that the changes have been applied

$ drizzle-kit push
No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/hoaaq/personal-code/ergonomic-code-with-elysia/drizzle.config.ts'
Using 'pg' driver for database querying
[✓] Pulling schema from database...
[✓] Changes applied

Drizzle-kit also has a function that allows visualizing the database visually and beautifully, you can run the command bun drizzle-kit studio and you will see an app page like this

Let's try the API again, everything should work fine now

curl http://localhost:4000/post/   --request POST   --header 'Content-Type: application/json'   --data '{
  "title": "test",
  "content": "test"
}'

{"id":"fzrr5jyz98ssatthtutthj7t","title":"test","content":"test","incrementId":1}

curl http://localhost:4000/post/

[{"id":"fzrr5jyz98ssatthtutthj7t","title":"test","content":"test","incrementId":1}]

But, wait, we're not done yet.

Refactoring code

Let's look back at our model file.

import { t } from "elysia";

export const postModel = {
  body: t.Object({
    title: t.String(),
    content: t.String(),
  }),
};

We are defining a body Post model completely by hand and without any basis. Since we have a database schema, why don’t we use that schema as the basis for the Post model?

The drizzle-typebox library can help us do this easily.

// Model file
import { table } from "@/db/schema";
import {
  createInsertSchema,
  createSelectSchema,
  createUpdateSchema,
} from "drizzle-typebox";
import { t } from "elysia";

const _createPost = createInsertSchema(table.posts);
const _updatePost = createUpdateSchema(table.posts);
const _entityPost = createSelectSchema(table.posts);

export const postModel = {
  create: t.Omit(_createPost, ["id", "incrementId"]),
  update: t.Omit(_updatePost, ["id", "incrementId"]),
  entity: _entityPost,
};

// Service file
// ...
  static async updatePost(
    id: string,
    {
      data,
    }: {
      data: typeof postModel.update.static;
    }
  ): Promise<typeof postModel.entity.static> {
    const post = await db.query.posts.findFirst({
      where: eq(table.posts.id, id),
    });
    if (!post) {
      throw new Error("Post not found");
    }
    const posts = await db
      .update(table.posts)
      .set({
        title: data.title,
        content: data.content,
      })
      .where(eq(table.posts.id, id))
      .returning();
    return posts[0];
  }
// ...

// Controller file
// ...
const updatePost = new Elysia({ name: "update-post" }).put(
  "/:id",
  async ({ params, body }) => {
    return {
      data: await PostService.updatePost(params.id, body),
    };
  },
  {
    response: t.Object({
      data: postModel.entity,
    }),
    body: t.Object({
      data: postModel.update,
    }),
    params: t.Object({
      id: postModel.entity.id,
    }),
  }
);
// ...

By editing the model file, reusing the database schema in Elysia validation models and some small changes in the service, controller files. We have got clearer, more declarative and ergonomic code.

However, using t.Omit and t.Pick will become increasingly complicated if we apply it to large models and repeat this process. So let's use this utility

import { Kind, type TObject } from "@sinclair/typebox";
import {
  createInsertSchema,
  createSelectSchema,
  BuildSchema,
  createUpdateSchema,
} from "drizzle-typebox";

import type { Table } from "drizzle-orm";

type Spread<
  T extends TObject | Table,
  Mode extends "select" | "insert" | "update" | undefined
> = T extends TObject<infer Fields>
  ? {
      [K in keyof Fields]: Fields[K];
    }
  : T extends Table
  ? Mode extends "select"
    ? BuildSchema<"select", T["_"]["columns"], undefined>["properties"]
    : Mode extends "insert"
    ? BuildSchema<"insert", T["_"]["columns"], undefined>["properties"]
    : Mode extends "update"
    ? BuildSchema<"update", T["_"]["columns"], undefined>["properties"]
    : {}
  : {};

/**
 * Spread a Drizzle schema into a plain object
 */
export const spread = <
  T extends TObject | Table,
  Mode extends "select" | "insert" | "update" | undefined
>(
  schema: T,
  mode?: Mode
): Spread<T, Mode> => {
  const newSchema: Record<string, unknown> = {};
  let table;

  switch (mode) {
    case "insert":
    case "update":
    case "select":
      if (Kind in schema) {
        table = schema;
        break;
      }

      table =
        mode === "insert"
          ? createInsertSchema(schema)
          : mode === "update"
          ? createUpdateSchema(schema)
          : createSelectSchema(schema);

      break;

    default:
      if (!(Kind in schema)) throw new Error("Expect a schema");
      table = schema;
  }

  for (const key of Object.keys(table.properties))
    newSchema[key] = table.properties[key];

  return newSchema as any;
};

/**
 * Spread a Drizzle Table into a plain object
 *
 * If `mode` is 'insert', the schema will be refined for insert
 * If `mode` is 'select', the schema will be refined for select
 * If `mode` is undefined, the schema will be spread as is, models will need to be refined manually
 */
export const spreads = <
  T extends Record<string, TObject | Table>,
  Mode extends "select" | "insert" | "update" | undefined
>(
  models: T,
  mode?: Mode
): {
  [K in keyof T]: Spread<T[K], Mode>;
} => {
  const newSchema: Record<string, unknown> = {};
  const keys = Object.keys(models);

  for (const key of keys) newSchema[key] = spread(models[key], mode);

  return newSchema as any;
};

You should place this file in src/db/util.ts

Then, create a singleton database model like this

import { table } from "./schema";
import { spreads } from "./util";

export const dbModel = {
  insert: spreads(
    {
      post: table.posts,
    },
    "insert"
  ),
  update: spreads(
    {
      post: table.posts,
    },
    "update"
  ),
  select: spreads(
    {
      post: table.posts,
    },
    "select"
  ),
} as const;

You should place this file in src/db/model.ts

Then edit the model file as follows

import { dbModel } from "@/db/model";
import { t } from "elysia";
const { post: insertPost } = dbModel.insert;
const { post: updatePost } = dbModel.update;
const { post: selectPost } = dbModel.select;

export const postModel = {
  create: t.Object({
    title: insertPost.title,
    content: insertPost.content,
  }),
  update: t.Object({
    title: updatePost.title,
    content: updatePost.content,
  }),
  entity: t.Object({
    ...selectPost,
    incrementId: t.Number(),
  }),
};

Thanks to the singleton pattern, we can now access the table schema as an Elysia validation model anywhere in the code.

Summary

If you follow the instructions in this article closely, you will end up with code like this repo.

This article has guided you how to connect Elysia application and database easily and effectively, with full notes and practical experience, I hope it is useful for you.

If you have any questions, do not hesitate to comment below.

In the next article, we will implement authentication for the application.

Ergonomic Code with Elysia #1: A very quick start

Au Quoc Hoa — Mon, 14 Jul 2025 07:06:56 +0000

What (or who) is Elysia?

Elysiajs is named after Elysia, a very famous character in the game Honkai Impact 3. The creator of this library is probably a big fan of Elysia, and so am I 😄

Besides, ElysiaJS is a production ready framework built on Bun. With many advantages:

Easy to learn and use, even for beginners
Small bundle, super high performance
E2E type safety

An important note is that in this series I will arrange the knowledge about Elysia in an easy-to-understand order with examples and explanations. However, I also encourage you to go through the official documentation on the home page to understand the theory clearly. In some places I will refer to the official documentation, if necessary.

Since Elysia is built on Bun, it is also recommended that you learn about Bun if possible.

Getting Started

Install Bun on Mac/Linux:

curl -fsSL https://bun.sh/install | bash

or Windows

powershell -c "irm bun.sh/install.ps1 | iex"

Then run script below to install a new Elysia app

bun create elysia app

Change "app" with whatever project name you want to create

Once done, go to your new app directory

cd app

and start the app

bun dev

Navigate to localhost:3000 should greet you with "Hello Elysia".

Controller / Route

To make the Elysia application receive a request from the internet, we need to create a controller (or route).

For example, we will create the following controllers, simulating an application that manages posts.

import { Elysia } from "elysia";

export const PostController = new Elysia({
  name: "post-controller",
  prefix: "/post",
})
  .get("/", () => {
    return "Get all posts";
  })
  .get("/:id", ({ params }) => {
    return `Get post by id ${params.id}`;
  })
  .post("/", () => {
    return "Create a new post";
  })
  .put("/:id", ({ params }) => {
    return `Update post by id ${params.id}`;
  })
  .delete("/:id", ({ params }) => {
    return `Delete post by id ${params.id}`;
  });

You should create this file with the name index.ts and place it in the src/post folder. Standardizing file names and project structure from the beginning will make your project easier to manage and test.

After that, add the routes you just created to the main Elysia instance at src/index.ts, and save. Bun will automatically reload your app with the new codes.

import { Elysia } from "elysia";
import { PostController } from "./post";

const app = new Elysia()
  .use(PostController)
  .get("/", () => "Hello Elysia")
  .listen(3000);

console.log(
  `🦊 Elysia is running at ${app.server?.hostname}:${app.server?.port}`
);

Now you can access the controllers you just created using browser, Postman,... or a simple curl command like this

curl localhost:3000/post

but currently the controllers only log simple text. In a real application we need to use complex logic to handle necessary business, let's add services.

Service / Handler

We call the code that handles business operations as services (or handlers). Services have many different purposes, it can be a simple calculation function, a call to a database or a call to another third-party application,...

In Elysia, service is also an Elysia instance similar to controller (or even other components, this is a special and very cool point of Elysia, we will discuss this issue later)

Now we will assume to create a service to manage posts

export abstract class PostService {
  static posts: { id: number; title: string; content: string }[] = [];

  static async getPosts() {
    return this.posts;
  }

  static async getPostById(id: number) {
    return this.posts.find((post) => post.id === id);
  }

  static async createPost({
    title,
    content,
  }: {
    title: string;
    content: string;
  }) {
    this.posts.push({
      id: this.posts.length + 1,
      title,
      content,
    });
    return this.posts;
  }

  static async updatePost(
    id: number,
    {
      title,
      content,
    }: {
      title: string;
      content: string;
    }
  ) {
    const post = this.posts.find((post) => post.id === id);
    if (!post) {
      throw new Error("Post not found");
    }
    post.title = title;
    post.content = content;
    return post;
  }

  static async deletePost(id: number) {
    const post = this.posts.find((post) => post.id === id);
    if (!post) {
      throw new Error("Post not found");
    }
    this.posts = this.posts.filter((post) => post.id !== id);
    return this.posts;
  }
}

Similar to controller, this file should be named service.ts and be placed in the src/post folder

Change the controller to implement services

import { Elysia } from "elysia";
import { PostService } from "./service";

export const PostController = new Elysia({
  name: "post-controller",
  prefix: "/post",
})
  .get("/", () => {
    return PostService.getPosts();
  })
  .get("/:id", ({ params }) => {
    return PostService.getPostById(Number(params.id));
  })
  .post("/", ({ body }) => {
    return PostService.createPost(body as { title: string; content: string });
  })
  .put("/:id", ({ params, body }) => {
    return PostService.updatePost(
      Number(params.id),
      body as { title: string; content: string }
    );
  })
  .delete("/:id", ({ params }) => {
    return PostService.deletePost(Number(params.id));
  });

Test with Swagger

You can type curl command to test. But, to make life easier, just add Swagger library, Elysia already has config, we don't need to do much, just install this library

bun add @elysiajs/swagger

Then add this plugin to your application

import { Elysia } from "elysia";
import { PostController } from "./post";
import { swagger } from "@elysiajs/swagger";

const app = new Elysia()
  .use(swagger())
  .use(PostController)
  .get("/", () => "Hello Elysia")
  .listen(3000);

console.log(
  `🦊 Elysia is running at ${app.server?.hostname}:${app.server?.port}`
);

Then go to http://localhost:3000/swagger, you will see a beautiful swagger page with all your routes. You can test your new routes on that page

Type Integrity

You may have noticed that in routes with param ':id' Elysia has automatically interpolated the id type as string. However, in routes with body there is still no type.

And if you pass in numeric data in the title or content attribute (which should be text), the application still accepts it and does not report an error, which is not true in reality.

Therefore Elysia provides Elysia.t, which allows you to build schemas to validate types and data at both runtime and compile-time. This schema is similar to Zod schema.

For example, bind the create post route to title and content only being string.

//Another codes...
.post(
    "/",
    ({ body }) => {
      return PostService.createPost(body);
    },
    {
      body: t.Object({
        title: t.String(),
        content: t.String(),
      }),
    }
  )
//Another codes...

As you may have noticed, Elysia has now interpolated the body type and we don’t need to cast it anymore. Also, if you try to pass in a title or content with numeric data, you’ll get a validation error.

Model and split code

It is not good to keep rewriting the same schema in every case. For example, you can see that the body of create and update service is the same, so we will create a model and reuse that schema.

Actually, you can totally make an application without a Model. But trust me, creating a model will help you a lot, especially after connecting to the database which I will guide you in the next articles.

import { t } from "elysia";

export const postModel = {
  body: t.Object({
    title: t.String(),
    content: t.String(),
  }),
};

You should name this file model.tsand place in src/post

Now you can change service create to like this

//...
static async createPost({ data }: { data: typeof postModel.body.static }) {
    this.posts.push({
      id: this.posts.length + 1,
      ...data,
    });
    return this.posts;
  }
//...

and controller like this

//...
.post(
    "/",
    ({ body }) => {
      return PostService.createPost({ data: body });
    },
    {
      body: postModel.body,
    }
  )
//...

You can change the same for the update part and we have got a simple application.

But wait, if we stop here, organizing the code in the controller with many chaining methods will look quite messy.

I have a suggestion, which is also the way I usually organize, split the child routes inside the controller into separate Elysia instances with easy-to-understand names and add them inside the main controller, like this

import { Elysia } from "elysia";
import { PostService } from "./service";
import { postModel } from "./model";

const getPosts = new Elysia({ name: "get-posts" }).get("/", () => {
  return PostService.getPosts();
});

const getPostById = new Elysia({ name: "get-post-by-id" }).get(
  "/:id",
  ({ params }) => {
    return PostService.getPostById(Number(params.id));
  }
);

const createPost = new Elysia({ name: "create-post" }).post(
  "/",
  ({ body }) => {
    return PostService.createPost({ data: body });
  },
  {
    body: postModel.body,
  }
);

const updatePost = new Elysia({ name: "update-post" }).put(
  "/:id",
  ({ params, body }) => {
    return PostService.updatePost(Number(params.id), { data: body });
  },
  {
    body: postModel.body,
  }
);

const deletePost = new Elysia({ name: "delete-post" }).delete(
  "/:id",
  ({ params }) => {
    return PostService.deletePost(Number(params.id));
  }
);

export const PostController = new Elysia({
  name: "post-controller",
  prefix: "/post",
})
  .use(getPosts)
  .use(getPostById)
  .use(createPost)
  .use(updatePost)
  .use(deletePost);

It now looks nice and clean, let's say if you need to modify code in a certain route you can easily locate where to modify

Summary

If you follow all the instructions above you will have a directory tree that looks like this

├── README.md
├── bun.lockb*
├── package.json
├── src/
│   ├── index.ts
│   └── post/
│       ├── index.ts
│       ├── model.ts
│       └── service.ts
└── tsconfig.json

In this part, we have only learned the very basic parts of Elysia to know how to start a web server and call it.

In fact, to be able to handle business logic, applications always need at least 1 accompanying database. In the next part, we will build and connect this application to the database, so stay tuned.

The entire code of this series will be stored in this repo.