Setting Up Drizzle & Postgres with tRPC and Next.js App

Published from Publish Studio

In this tutorial, let's learn how to connect a Postgres database to a tRPC express backend using Drizzle ORM. I have also created a simple frontend for our finance tracker application. You can copy frontend code from the repo here.

This is part 2, read part 1 here: Let's Build a Full-Stack App with tRPC and Next.js 14

Backend

If you don't have Postgres installed locally, please do or you can also use a hosted database.

Once you have Postgres ready, add DATABASE_URL to your .env:

DATABASE_URL=postgres://postgres:password@localhost:5432/myDB

Setting up db with drizzle

To set up drizzle, start off by installing these packages:

yarn add drizzle-orm pg dotenv
yarn add -D drizzle-kit tsx @types/pg

Now, all you have to do is connect drizzle to the DB. To do that, create src/utils/db.ts file and configure drizzle:

1import { drizzle } from "drizzle-orm/node-postgres";
2import pg from "pg";
3const { Pool } = pg;
4
5export const pool = new Pool({
6  connectionString: process.env.DATABASE_URL,
7  ssl: process.env.NODE_ENV === "production",
8});
9
10export const db = drizzle(pool);

That's it! Our db setup is ready. We can now create tables and interact with our db using drizzle ORM.

Create the first module

Regarding the project structure, there are mainly two types:

1. Modules: Divide features into different modules and keep all related files together. Popular frameworks like NestJs and Angular use this structure.

.
└── feature/
    ├── feature.controller.ts
    ├── feature.routes.ts
    ├── feature.schema.ts
    └── feature.service.ts

2. Separate folders based on the purpose:

.
├── controllers/
│   ├── feature1.controller.ts
│   └── feature2.controller.ts
├── services/
│   ├── feature1.service.ts
│   └── feature2.service.ts
└── models/
    ├── feature1.model.ts
    └── feature2.model.ts

I personally prefer modules because it just makes sense (plz stop using 2nd one).

Now, let's create our first module called transaction. This is our core feature. Start by creating src/modules/transaction/transaction.schema.ts file. This is where we define transaction schema using drizzle.

The great thing about using drizzle to write schemas is it lets us use typescript. So you don't have to learn a new syntax and ensure type safety for your schemas.

To record a transaction (txn), the most basic things we need are:

• txn amount
• txn type - credit or debit
• description - a simple note to refer to later
• tag - a category like shopping/travel/food, and so on.

First, let's create enums for txn type and tag:

1import {
2  pgEnum,
3} from "drizzle-orm/pg-core";
4
5export const txnTypeEnum = pgEnum("txnType", ["Incoming", "Outgoing"]);
6export const tagEnum = pgEnum("tag", [
7  "Food",
8  "Travel",
9  "Shopping",
10  "Investment",
11  "Salary",
12  "Bill",
13  "Others",
14]);

Then, let's create the schema:

1import {
2  integer,
3  pgTable,
4  serial,
5  text,
6  timestamp,
7} from "drizzle-orm/pg-core";
8
9export const transactions = pgTable("transactions", {
10  id: serial("id").primaryKey(),
11  amount: integer("amount").notNull(),
12  txnType: txnTypeEnum("txn_type").notNull(),
13  summary: text("summary"),
14  tag: tagEnum("tag").default("Others"),
15  createdAt: timestamp("created_at").defaultNow(),
16  updatedAt: timestamp("updated_at")
17    .defaultNow()
18    .$onUpdate(() => new Date()),
19});

As you can see, we simply wrote typescript code and created a table!

Run migrations

One final step before we can start interacting with our db is to apply changes to our database so that all the tables will be created. To do that we have to run migrations. Drizzle has this amazing tool called drizzle-kit which handles migrations for us, so all we have to do is run a command.

Before doing that we have to create a file called drizzle.config.ts in the project root, which includes all the information about the database and schemas.

1import "dotenv/config";
2import { defineConfig } from "drizzle-kit";
3
4export default defineConfig({
5  schema: "./src/**/*.schema.ts",
6  out: "./drizzle",
7  dialect: "postgresql",
8  dbCredentials: {
9    url: process.env.DATABASE_URL!,
10    ssl: process.env.NODE_ENV === "production",
11  },
12});

With that ready, run the below command:

yarn dlx drizzle-kit push

That's it! Now we can start interacting with db and write our business logic.

Business logic

Let's add logic to add new transactions.

If you already don't know:

• Service - where we interact with DB and write most of the business logic
• Controller - handle request/response

Create transaction/transaction.service.ts and write logic to add new transactions to db:

1import { TRPCError } from "@trpc/server";
2import { db } from "../../utils/db";
3import { transactions } from "./transaction.schema";
4
5export default class TransactionService {
6  async createTransaction(data: typeof transactions.$inferInsert) {
7    try {
8      return await db.insert(transactions).values(data).returning();
9    } catch (error) {
10      console.log(error);
11
12      throw new TRPCError({
13        code: "INTERNAL_SERVER_ERROR",
14        message: "Failed to create transaction",
15      });
16    }
17  }
18}

Another benefit of using drizzle ORM is it provides type definitions for different CRUD methods like $inferInsert, $inferSelect so there is no need to define the types again. Here, by using typeof transactions.$inferInsert we don't have to provide values for fields like primary key, and fields with default values like createdAt, and updatedAt, so typescript won't throw an error.

Drizzle also has extensions like drizzle-zod which can be used to generate zod schemas. Another headache was prevented by drizzle 🫡. So open transaction.schema.ts and create zod schema for insert operation:

1import { createInsertSchema } from "drizzle-zod";
2
3export const insertUserSchema = createInsertSchema(transactions).omit({
4  id: true,
5  createdAt: true,
6  updatedAt: true,
7});

Let's use this in the controller, create transaction/transaction.controller.ts:

1export default class TransactionController extends TransactionService {
2  async createTransactionHandler(data: typeof transactions.$inferInsert) {
3    return await super.createTransaction(data);
4  }
5}

Now, all that remains is to expose this controller through an endpoint. For that, create transaction/transaction.routes.ts. Since we are using tRPC, to create an endpoint, we have to define a procedure:

1import { publicProcedure, router } from "../../trpc";
2import TransactionController from "./transaction.controller";
3import { insertUserSchema } from "./transaction.schema";
4
5const transactionRouter = router({
6  create: publicProcedure
7    .input(insertUserSchema)
8    .mutation(({ input }) =>
9      new TransactionController().createTransactionHandler(input)
10    ),
11});
12
13export default transactionRouter;

If you remember from part 1, we created a reusable router that can be used to group procedures and publicProcedure which creates an endpoint.

Finally, open src/routes.ts and use the above transactionRouter:

1import transactionRouter from "./modules/transaction/transaction.routes";
2import { router } from "./trpc";
3
4const appRouter = router({
5  transaction: transactionRouter,
6});
7
8export default appRouter;

That's it! The backend is ready. This is the final backend structure:

1.
2├── README.md
3├── drizzle
4│   ├── 0000_true_junta.sql
5│   └── meta
6│       ├── 0000_snapshot.json
7│       └── _journal.json
8├── drizzle.config.ts
9├── package.json
10├── src/
11│   ├── index.ts
12│   ├── modules/
13│   │   └── transaction/
14│   │       ├── transaction.controller.ts
15│   │       ├── transaction.routes.ts
16│   │       ├── transaction.schema.ts
17│   │       └── transaction.service.ts
18│   ├── routes.ts
19│   ├── trpc.ts
20│   └── utils/
21│       ├── db.ts
22│       └── migrate.ts
23├── tsconfig.json
24└── yarn.lock

Challenge for you

Before proceeding to frontend integration, as a challenge, create an endpoint for getting all transactions.

Frontend

It's time to integrate the created endpoints in our frontend. Since this is not a frontend tutorial, I'll let you just copy the code from the repo.

All I've changed is:

• Set up shadcn/ui
• Change src/components/modules/dashboard/index.tsx

Also, as you observe, I'm using a modules-like structure here too. If you also like this structure, you can learn more from my previous projects Publish Studio and My One Post

In part 1, we queried data using built-in tRPC react-query.

1...
2  const { data } = trpc.test.useQuery();
3
4  return (
5    <main className="flex min-h-screen flex-col items-center justify-between p-24">
6      {data}
7    </main>
8  );
9...

So, if you already know react-query, there's isn't much to learn except with tRPC we don't have to create queryFn or mutationFn because we directly call backend methods.

This is how mutations are used:

1...
2  const { mutateAsync: createTxn, isLoading: isCreating } =
3    trpc.transaction.create.useMutation({
4      onSuccess: async () => {
5        form.reset();
6        await utils.transaction.getAll.invalidate();
7      },
8    });
9    
10  const addTransaction = async (data: z.infer<typeof formSchema>) => {
11    try {
12      await createTxn(data);
13    } catch (error) {
14      console.error(error);
15    }
16  };
17...

See In Action

---

I hope you like this tutorial. Feel free to extend the functionality. In the next article, I'll share how to add authentication.

---

The project source code can be found here.

---

Socials