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

[galacoder]

Describe the question you have

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
};

[AndriiSherman]

@galacoder I guess it's just some syntax errors you have

Starting from 0.19.0 we have changed syntax for .select(). Please check it Same for inserts/updates/deletes. Please check docs for proper syntax

1. You should specify order by desc in a different way

 return await db.select().from(posts).orderBy(desc(posts.id));

2. Same for second error, you need to write

const result = await db.select().from(posts).where(eq(posts.id, req.input.id)).limit(1);

3. MySQL doesn't have returning. So you need to make an insert+select to get inserted entity (You may say, that Prisma did it in 1 query, but under the hood it's still were 2 queries. Insert+Select)
4. Same for returning in delete. If you need whole entity to be returned after delete, you'll need to get this entity, delete it and then return. This is how MySQL works(Same here with Prisma, it was doing just 2 queries without you knowing it)

[galacoder]

Thank you @AndriiSherman for your help with my code. It's working now thanks to your recommendation!

I had a couple of quick questions regarding Drizzle ORM:

1. I recently discovered that there are some schema limitations with the PlanetScale database compared to traditional databases. I tried to use the Drizzle ORM kit to migrate my schema to .sql, but found out that foreign keys don't work in PlanetScale. I was wondering if there is any documentation or tool available to help convert unsupported schema to a supported format using Drizzle ORM, such as using unique indexes instead of foreign keys?

2. I noticed that in one of Theo's videos, he mentioned using "db:push" in conjunction with Drizzle ORM, but I couldn't find any information on this in the Drizzle ORM documentation. Could you please explain how to use this feature or point me in the direction of any relevant documentation? Thank you very much for your time and assistance!

[galacoder]

@AndriiSherman I tried to convert this mongoose schema to drizzle orm. Could you please give me feedback whether I do it right? Thank you.

Mongoose

const _ = require('lodash');
const mongoose = require('mongoose');
const tools = require('./tools');
const mailSender = require('../mail-sender');
const FileModel = require('./file').Model;
const userGrowth = require('../business/user-growth');
const userStorage = require('../business/user-storage');

let Schema = mongoose.Schema;

let Growth = new Schema({
  _id: false,
  kpi: Number,
  kpiToNextLevel: Number,
  level: Number,
  upgradedAt: Date
});

let VIP = new Schema({
  _id: false,
  type: String,
  willExpireAt: Date,
  iosReceipt: String,
  iosTransactionId: String
});

let Device = new Schema({
  _id: false,
  type: String,
  iosToken: String
});

let UserSchema = new Schema({
  email: {type: String, index: {unique: true, sparse: true}},
  facebookId: {type: String, index: {unique: true, sparse: true}},
  googleId: {type: String, index: {unique: true, sparse: true}},
  username: {type: String, index: {unique: true, sparse: true}},
  usernameUpdated: Boolean,
  firstName: String,
  lastName: String,
  passwordSalt: String,
  passwordHash: String,
  motto: String,
  _avatar: {type: String, ref: 'File'},
  _backdrop: {type: String, ref: 'File'},
  usedFileSize: {type: Number, default: 0}, // file size in kb
  usedPercentage: {type: Number, default: 0}, // the percentage of used file size
  maximumFileSize: {type: Number, default: userStorage.getStorageSize},
  willResetFileSizeAt: {type: Date, default: userStorage.getNextStorageSizeResetDate},
  role: {type: String, default: 'user'}, // admin, user
  enabled: {type: Boolean, default: true},
  growth: Growth,
  vip: VIP,
  devices: [Device],
  requestedFeatures: [String],
  lang: {type: String, default: 'english'},
  triggeredTemplates: [Schema.Types.ObjectId],
  timezone: String,
  inviting: {type: Boolean, default: false}, // the user is being invited, but not registered yet
  inviters: [{type: Schema.Types.ObjectId, ref: 'User'}],
  createdAt: {type: Date, default: Date.now}
});

Drizzle ORM

import {
  boolean,
  date,
  int,
  json,
  mysqlTable,
  serial,
  varchar,
  uniqueIndex,
  index,
} from "drizzle-orm/mysql-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
const userGrowth = require('../business/user-growth');
const userStorage = require('../business/user-storage');

/**
 * User model.
 */

// Growth schema
interface Growth {
  kpi: number;
  kpiToNextLevel: number;
  level: number;
  upgradedAt: Date;
}

// VIP schema
interface VIP {
  type: string;
  willExpireAt: Date;
  iosReceipt: string;
  iosTransactionId: string;
}

// Device schema
interface Device {
  type: string;
  iosToken: string;
}

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }),
  facebookId: varchar("facebookId", { length: 255 }),
  googleId: varchar("googleId", { length: 255 }),
  username: varchar("username", { length: 255 }),
  usernameUpdated: boolean("usernameUpdated"),
  firstName: varchar("firstName", { length: 255 }),
  lastName: varchar("lastName", { length: 255 }),
  passwordSalt: varchar("passwordSalt", { length: 255 }),
  passwordHash: varchar("passwordHash", { length: 255 }),
  motto: varchar("motto", { length: 255 }),
  avatar: varchar("avatar", { length: 255 }),
  backdrop: varchar("backdrop", { length: 255 }),
  usedFileSize: int("usedFileSize").default(0), // file size in kb
  usedPercentage: int("usedPercentage").default(0), // the percentage of used file size
  maximumFileSize: int("maximumFileSize").default(userStorage.getStorageSize),
  willResetFileSizeAt: date("willResetFileSizeAt").default(userStorage.getNextStorageSizeResetDate),
  role: varchar("role", { length: 255 }).default("user"), // admin, user
  enabled: boolean("enabled").default(true),
  growth: json("growth").$type<Growth>(),
  vip: json("vip").$type<VIP>(),
  devices: json("devices").$type<Device[]>(),
  requestedFeatures: json("requestedFeatures").$type<string[]>(),
  lang: varchar("lang", { length: 255 }).default("english"),
  triggeredTemplates: json("triggeredTemplates").$type<string[]>(),
  timezone: varchar("timezone", { length: 255 }),
  inviting: boolean("inviting").default(false),
  inviters: json("inviters").$type<string[]>(),
  createdAt: date("createdAt").defaultNow(),
}, (users) => ({
  emailIndex: uniqueIndex("email_idx").on(users.email),
  facebookIdIndex: uniqueIndex("facebook_id_idx").on(users.facebookId),
  googleIdIndex: uniqueIndex("google_id_idx").on(users.googleId),
  usernameIndex: uniqueIndex("username_idx").on(users.username),
  avatarIndex: index("avatar_idx").on(users.avatar),
  backdropIndex: index("backdrop_idx").on(users.backdrop),
}));

// Schema for inserting a user
const insertUserSchema = createInsertSchema(users);
// Schema for selecting a user
const selectUserSchema = createSelectSchema(users)

[KeshScripts]

I'm battling with same issue. Can I get the full repo link.

[spookyuser]

I think the problem, which i also have, is that json with types is not validated as those types by this plugin, soo with this schema

export const projects = pgTable("projects", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  ownerId: text("owner_id").notNull(),
  input: json("input").$type<Array<Array<any>>>().notNull(),
});

Zod checks that input is just json so when you try and pass a zod validated object to values it just thinks it's getting json and doesn't know the type

so for example this trpc route

  create: protectedProcedure
    .input(schema.apiCreateProject)
    .mutation(async ({ input, ctx }) => {
      return await ctx.db.insert(schema.projects).values(input);
    }),

sees input as invalid even though it's literally being validated by zod 2 lines before bc if i exapnd it to

input: input.input,
ownerId: input.ownerId,
title: input.title,

The type error occurs on the first line only

And i have no idea what this means but i'm assuming it means json isn't compatible with my json type [][]

[Angelelz]

Seems like the original issue was addressed