Theo's Typesafe Cult•9mo ago

Drizzle and Create-t3

I am facing difficulty setting up Drizzle (PlanetScale MySQL) within create-t3-app setup. Can someone please direct me towards a working code snippet or repo.

Solution:

I was able to solve this, adding "db:studio": "dotenv drizzle-kit studio" in package.json worked along with re installing mysql2 again. Thank you for your help @kershnerd happy

Jump to solution

15 Replies

peternovak•9mo ago

I was just about to post about the same thing when I saw your post @Bhan Singh ! I used the standard 7.20.2 implementation of T3 (including everything + drizzle) and pushed the schema to Planetscale. When trying to log in I got this error. With a few modifications I got a similar codebase working on Neon using postgres, but somehow not on Planetscale.

jeff.kershner•9mo ago

Mine worked out of the box with no custom changes. I know you said you pushed your schema but can you confirm the tables actually exist on planet scale? I will create a new project in the next hour to confirm as well. It worked for me with a fresh install of a new planetscale database, brand new t3 app (7.20.2). I am using bun and NextAuth with a GoogleProvider. Does your drizzle-kit studio work and show your tables? Add this to your package.json:

"db:studio": "dotenv drizzle-kit studio",

"db:studio": "dotenv drizzle-kit studio",

And make sure you install mysql2 package:

bunx add mysql2

bunx add mysql2

Make sure your planetscale database string in your .env ends with:

?ssl={"rejectUnauthorized":true}

?ssl={"rejectUnauthorized":true}

No other custom changes were made on my side. Did you guys make any changes or do anything different?

peternovak•9mo ago

I am using Yarn and the default Discord provider. Just recreated my database in planetscale, used db:push and tried to log in. I got the same error then trying to log in, but got these tables in planetscale

peternovak•9mo ago

I am connecting the planetscale by logging in through their CLI and using: pscale connect drizzle main --port 3309 In my .env: DATABASE_URL='mysql://root@127.0.0.1:3309/drizzle' No changes after installing the mysql2 package as I could see

Bhan Singh•9mo ago

My connection works for db:push to planetscale but fails when spinning drizzle studio locally with "Invalid Environment variables" error. I tried adding db-studio as you suggested but it failed with "db:studio@dotenv drizzle-kit studio isn't supported by any available resolver." I am using pnpm.

jeff.kershner•9mo ago

Your DATABSE_URL is wrong... Planetscale should give you this connection string (you might have to click on prisma in planetscale to see it). It should be something like this (replaced parts of my connection string with xxxx for obvious reasons):

mysql://t2xxxxxxxxxxosmqy:pscale_pw_IwDsnyxxxxxxxx9eEGwLMe9zfM@aws.connect.psdb.cloud/rtc?ssl={"rejectUnauthorized":true}

mysql://t2xxxxxxxxxxosmqy:pscale_pw_IwDsnyxxxxxxxx9eEGwLMe9zfM@aws.connect.psdb.cloud/rtc?ssl={"rejectUnauthorized":true}

If you go to your planetscale dashboard and click on settings -> passwords and click on Connect with Prisma (I know you are not using prisma, but that is the connect string you need). But you have to replace the sslaccept=strict with ssl={"rejectUnauthorized":true}

jeff.kershner•9mo ago

Should look like this

jeff.kershner•9mo ago

@Bhan Singh Interesting that you are getting invalid environment variables. What variables do you have? For database, you should only have DATABASE_URL. What auth provider are you using? Whatever auth provider you are using, you will need to make sure they are in your .env and env.mjs (in two places - in the server object and in the runtimeEnv). If either of you are still having trouble, send me your github repo (if its public) and I can try it locally. Your repo will not contain your .env file (at least it shouldn't), so it would be safe sharing.

Bozic0909•9mo ago

I had same issue, for me it was not the problem with env variables values itself, but it was problem with loading variables, and i solved problem by using dotenv library like this on the top of drizzle.config.ts

dotenv.config({
    path: ".env",
})

dotenv.config({
    path: ".env",
})

jeff.kershner•9mo ago

@Bozic0909 You shouldn't have to do that if you are running dotenv in your studio command like this (in your package.json):

"db:studio": "dotenv drizzle-kit studio",

"db:studio": "dotenv drizzle-kit studio",

My entire drizzle.config.ts look like this:

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

import { env } from "~/env.mjs";

export default {
  schema: "./src/server/db/schema.ts",
  driver: "mysql2",
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },
  tablesFilter: ["rtc_*"],
} satisfies Config;

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

import { env } from "~/env.mjs";

export default {
  schema: "./src/server/db/schema.ts",
  driver: "mysql2",
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },
  tablesFilter: ["rtc_*"],
} satisfies Config;

Bhan Singh•9mo ago

You are right. I am only using DATABASE_URL as my config variable. I am using NextAuth Discord Provider (Boilerplate with T3) but I believe its validation is not set to required in env.mjs. Here's my github repo "https://github.com/Bhan1709/drizzle-nextjs" (It's just t3 create with drizzle out of the box tbh). Thing is, if my creds were wrong, db:push should have failed as well, but it doesn't, the only issue arises with drizzle studio.

Bhan Singh•9mo ago

Attaching the console log for reference

Bhan Singh•9mo ago

I am assuming the issue is with env.mjs runtime env section, so would like to know how I can correct that still maintaining the intended use of this section.

Solution

Bhan Singh•9mo ago

I was able to solve this, adding "db:studio": "dotenv drizzle-kit studio" in package.json worked along with re installing mysql2 again. Thank you for your help @kershnerd happy

cje•9mo ago

sorry for this issue. we were on the verge of just making dotenv the default, but drizzle team will soon release an update where that won't be necessary anymore

Gaming

Programming

Drizzle and Create-t3