Issues with replacing Prisma ORM with Drizzle ORM and Planetscale in TRPC for create-t3-turbo template

[galacoder]

Hi there,

I am trying to replace Prisma in the database layer and trpc of the create-t3-turbo template with Drizzle-ORM, Drizzle-Zod, and Planetscale. I have successfully replaced the Prisma part with Drizzle-ORM, but I am facing issues with the trpc part.

I have gone through all of your example projects but found them outdated. Attached please find the error messages I am getting. I have also searched the internet for a solution, but could not find much information.

I believe many people would benefit from learning how to use Drizzle-ORM with trpc, especially since Prisma has issues running on the edge, whereas Drizzle does not.

Looking forward to hearing from you soon.

Please advise!

trpc.ts

import { getServerSession, type Session } from "@aeonbook/auth";
import { db } from "@ae/db";
import { TRPCError, initTRPC } from "@trpc/server";
import { type CreateNextContextOptions } from "@trpc/server/adapters/next";
import superjson from "superjson";
import { ZodError } from "zod";

type CreateContextOptions = {
  session: Session | null;
};

const createInnerTRPCContext = (opts: CreateContextOptions) => {
  return {
    session: opts.session,
    db,
  };
};

export const createTRPCContext = async (opts: CreateNextContextOptions) => {
  const { req, res } = opts;

  // Get the session from the server using the unstable_getServerSession wrapper function
  const session = await getServerSession({ req, res });

  return createInnerTRPCContext({
    session,
  });
};

const t = initTRPC.context<typeof createTRPCContext>().create({
  transformer: superjson,
  errorFormatter({ shape, error }) {
    return {
      ...shape,
      data: {
        ...shape.data,
        zodError:
          error.cause instanceof ZodError ? error.cause.flatten() : null,
      },
    };
  },
});

/
export const createTRPCRouter = t.router;

 * This is the base piece you use to build new queries and mutations on your
 * tRPC API. It does not guarantee that a user querying is authorized, but you
 * can still access user session data if they are logged in
 */
export const publicProcedure = t.procedure;

const enforceUserIsAuthed = t.middleware(({ ctx, next }) => {
  if (!ctx.session?.user) {
    throw new TRPCError({ code: "UNAUTHORIZED" });
  }
  return next({
    ctx: {
      // infers the `session` as non-nullable
      session: { ...ctx.session, user: ctx.session.user },
    },
  });
});

export const protectedProcedure = t.procedure.use(enforceUserIsAuthed)

post.ts

import { z } from "zod";

import { db, schema } from "@aeonbook/db";

import "@aeonbook/db/";
import { createTRPCRouter, publicProcedure } from "../trpc";
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';
import { eq } from "drizzle-orm/expressions";

const { posts } = schema
// Schema for inserting a post
const insertPostSchema = createInsertSchema(posts);

// Schema for selecting a post
const selectPostSchema = createSelectSchema(posts);

export const postRouter = createTRPCRouter({
  all: publicProcedure.query(async () => {
    return await db.select(posts).orderBy(posts.id.desc());
  }),
  byId: publicProcedure
    .input(z.object({ id: z.string() }))
    .query(async (req) => {
      const result = await db
        .select(posts)
        .where(eq(posts.id, req.input.id))
        .limit(1);
      return result[0];
    }),
  create: publicProcedure
    .input(
      z.object({
        title: z.string().min(1),
        content: z.string().min(1),
      }),
    )
    .mutation(async (req) => {
      const result = await db
        .insert(posts)
        .values({
          title: req.input.title,
          content: req.input.content,
        })
        .returning();
      return result[0];
    }),
  delete: publicProcedure.input(z.string()).mutation(async (req) => {
    const result = await db
      .delete(posts)
      .where(eq(posts.id, req.input))
      .returning();
    return result[0];
  }),
}

schem.ts

import {
  int,
  mysqlTable,
  serial,
  text,
  timestamp,
  uniqueIndex,
  varchar,
} from "drizzle-orm/mysql-core";

// Post Model
export const posts = mysqlTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title"),
  content: text("content"),
});

// User Model
export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
  email: text("email"),
  emailVerified: timestamp("email_verified"),
  image: text("image"),
});

// Account Model
export const accounts = mysqlTable(
  "accounts",
  {
    id: serial("id").primaryKey(),
    userId: varchar("user_id", { length: 255 }),
    type: text("type"),
    provider: varchar("provider", { length: 50 }), // Changed to VARCHAR with length 50
    providerAccountId: varchar("provider_account_id", { length: 100 }), // Changed to VARCHAR with length 100
    refresh_token: text("refresh_token"),
    access_token: text("access_token"),
    expires_at: timestamp("expires_at"),
    token_type: text("token_type"),
    scope: text("scope"),
    id_token: text("id_token"),
    session_state: text("session_state"),
  },
  (accounts) => ({
    userIdIndex: uniqueIndex("user_id_idx").on(accounts.userId),
    providerIndex: uniqueIndex("provider_idx").on(accounts.provider),
    providerAccountIdIndex: uniqueIndex("provider_account_id_idx").on(
      accounts.providerAccountId,
    ),
  }),
);

// Session Model
export const sessions = mysqlTable(
  "sessions",
  {
    id: serial("id").primaryKey(),
    sessionToken: varchar("session_token", { length: 255 }),
    userId: varchar("user_id", { length: 255 }),
    expires: timestamp("expires"),
  },
  (sessions) => ({
    sessionTokenIndex: uniqueIndex("session_token_idx").on(
      sessions.sessionToken,
    ),
  }),
);

// VerificationToken Model
export const verificationTokens = mysqlTable("verificationTokens", {
  identifier: text("identifier"),
  token: text("token"),
  expires: timestamp("expires"),
});

db.ts

import { connect } from "@planetscale/database";
import { drizzle } from "drizzle-orm/planetscale-serverless";

import { accounts, posts, sessions, users, verificationTokens } from "./schema";

// create the connection
const connection = connect({
  host: process.env["DATABASE_HOST"],
  username: process.env["DATABASE_USERNAME"],
  password: process.env["DATABASE_PASSWORD"],
});

const db = drizzle(connection);

const schema = {
  users,
  posts,
  accounts,
  sessions,
  verificationTokens,
}

export {
  db,
  schema
};