Drizzle Team•14mo ago

Next 13, Next-Auth with Neon Postgres and Drizzle vs Prisma 5

I am trying to set up Next-Auth with Neon Postgres and Drizzle, but failing miserably. I set up a repo with several branches, including configurations for: 1) Next 13, Next-Auth, Neon Postgres, Prisma 5 2) Next 13, Next-Auth, PlanetScale MySQL, Prisma 5 3) Next 13, Next-Auth, Neon Postgres, Drizzle The first two work perfectly fine, I am able to authenticate and store users in a database, regardless of whether they are signing up with password, magic links, or OAuth providers. However, I am facing weird issues with the Drizzle branch, particularly with getUserByEmailAction and other similar .select() -based queries, which for some weird reason sometimes return undefined. The logic for all three branches is exactly the same and the code is nearly identical. If anyone would like to have a look and see if they are able to fix it, the code is available here: https://github.com/pjborowiecki/SAASY-LAND-Next-13-Starters-With-Authentication-And-Database-Implemented The branch I am referring to is called next-auth-drizzle-neon-postgres Looking forward to some of you trying and pointing out the issue!

GitHub

GitHub - pjborowiecki/SAASY-LAND-Next-13-Starters-With-Authenticati...

Starter templates for Next.js 13 full-stack projects. Built with Tailwind CSS, ShadCn, authentication, and database. Several branches contain several different configurations, including serverless ...

22 Replies

PioterOP•14mo ago

Ok, I was able to add another branch with a configuration for Next 13, Next-Auth, Drizzle, and MySQL at PlanetScale. All using the exact same logic and nearly identical code. Everything works perfectly fine, which leads me to think there must be something wrong with the way Drizzle and Neon work together in this case. There are no TypeScript errors, no warnings, no linting errors, everything seems to be fine and yet, I am experiencing unexpected behaviour and I cannot figure out why some functions return undefined. Especially that the exact same functions work fine, when using Drizzle with PlanetScale, instead of Neon. I am pretty sure I followed the documentation and that I am using correct syntax. Please, have a look at the code - especially the sign up with password part. It starts with the form and its onSubmit() function, which relies on the signUpWithPasswordAction(), which calls getUserByEmailAction(), which uses a prepared statement defined as follows:

export const psGetUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, sql.placeholder("email")))
  .prepare("psGetUserByEmail")

export const psGetUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, sql.placeholder("email")))
  .prepare("psGetUserByEmail")

So the getUserByEmailAction() is defined as:

export async function getUserByEmailAction(email: string) {
  return await psGetUserByEmail.execute({ email })
}

export async function getUserByEmailAction(email: string) {
  return await psGetUserByEmail.execute({ email })
}

And this is being used in the signUpWithPasswordAction() as follows:

const user = await getUserByEmailAction(email).then((res) => res[0])
if (user) return "exists"

const user = await getUserByEmailAction(email).then((res) => res[0])
if (user) return "exists"

For some weird reason, the user is always undefined, even though I am absolutely certain that at this point the user is already in a database and I can see that user, both in drizzle-kit studio and in the console at neon.tech. Why is this happening? Why does the exact same flow work perfectly well with Planetscale's MySQL? The PlanetScale's branch is called next-auth-drizzle-planetscale-mysql, and the Neon's branch is called next-auth-drizzle-neon-postgres. Thanks!

Angelelz•14mo ago

Seems like you have to debug your app. I would start by making sure in runtime the email I'm passing to the getUserByEmailAction. Either using a debugger or by:

console.log(email)
const users = await getUserByEmailAction(email)
console.log(users)
const user = users[0]
if (user) return "exists"

console.log(email)
const users = await getUserByEmailAction(email)
console.log(users)
const user = users[0]
if (user) return "exists"

PioterOP•14mo ago

Thank you for taking the time to reply. I have of course tried that before asking, and I am certain that my code has no bugs - the exact same logic and nearly identical code works in all other branches, including Drizzle with PlanetScale or both, Neon and PlanetScale, with Prisma. After doing what suggested:

export async function signUpWithPasswordAction(
  email: string,
  password: string
) {
  console.log(email)
  const allUsers = await getUserByEmailAction(email)
  console.log("allUsers: ", allUsers)
  const user = allUsers[0]
  console.log("user: ", user)
  if (user) return "exists"

export async function signUpWithPasswordAction(
  email: string,
  password: string
) {
  console.log(email)
  const allUsers = await getUserByEmailAction(email)
  console.log("allUsers: ", allUsers)
  const user = allUsers[0]
  console.log("user: ", user)
  if (user) return "exists"

I am getting the email back, which means the form works fine. Still getting undefined from querying users however, even though I am absolutely certain that the user is in the database at this point and I can clearly see it in my console

Angelelz•14mo ago

My next suggestion would be to try it without the prepared statement. Just do a regular select and see if there's any difference.

PioterOP•14mo ago

I removed the prepared statement, which means the function is now defined as follows:

export async function getUserByEmailAction(email: string) {
  return await db.select().from(users).where(eq(users.email, email))
}

export async function getUserByEmailAction(email: string) {
  return await db.select().from(users).where(eq(users.email, email))
}

Cleared all the cache, etc. Still the same issue: undefined

Revan•14mo ago

what if you try removing the where clause

Angelelz•14mo ago

Also, console.log(db.select().from(users).where(eq(users.email, email)).toSQL()) and let's see the query I can't remember if it's toSQL() or getSQL(), just try one, if not, try the other

PioterOP•14mo ago

Thank you so much for trying to help, I really appreciate it. The output from consol logging .toSQL() is in the image. Here is the otuput from logging .getSQL():

SQL {
  decoder: { mapFromDriverValue: [Function: mapFromDriverValue] },
  shouldInlineParams: false,
  queryChunks: [
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: [Array]
    },
    StringChunk { value: [Array] },
    PgTable {
      id: [PgText],
      name: [PgText],
      surname: [PgText],
      username: [PgText],
      email: [PgText],
      emailVerified: [PgTimestamp],
      emailVerificationToken: [PgText],
      passwordHash: [PgText],
      resetPasswordToken: [PgText],
      resetPasswordTokenExpiry: [PgTimestamp],
      image: [PgText],
      createdAt: [PgTimestamp],
      [Symbol(drizzle:IsAlias)]: false,
      [Symbol(drizzle:ExtraConfigBuilder)]: undefined,
      [Symbol(drizzle:IsDrizzleTable)]: true,
      [Symbol(drizzle:OriginalName)]: 'user',
      [Symbol(drizzle:Name)]: 'user',
      [Symbol(drizzle:Schema)]: undefined,
      [Symbol(drizzle:BaseName)]: 'user',
      [Symbol(drizzle:PgInlineForeignKeys)]: [],
      [Symbol(drizzle:Columns)]: [Object]
    },
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: []
    },
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: [Array]
    },

...

SQL {
  decoder: { mapFromDriverValue: [Function: mapFromDriverValue] },
  shouldInlineParams: false,
  queryChunks: [
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: [Array]
    },
    StringChunk { value: [Array] },
    PgTable {
      id: [PgText],
      name: [PgText],
      surname: [PgText],
      username: [PgText],
      email: [PgText],
      emailVerified: [PgTimestamp],
      emailVerificationToken: [PgText],
      passwordHash: [PgText],
      resetPasswordToken: [PgText],
      resetPasswordTokenExpiry: [PgTimestamp],
      image: [PgText],
      createdAt: [PgTimestamp],
      [Symbol(drizzle:IsAlias)]: false,
      [Symbol(drizzle:ExtraConfigBuilder)]: undefined,
      [Symbol(drizzle:IsDrizzleTable)]: true,
      [Symbol(drizzle:OriginalName)]: 'user',
      [Symbol(drizzle:Name)]: 'user',
      [Symbol(drizzle:Schema)]: undefined,
      [Symbol(drizzle:BaseName)]: 'user',
      [Symbol(drizzle:PgInlineForeignKeys)]: [],
      [Symbol(drizzle:Columns)]: [Object]
    },
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: []
    },
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: [Array]
    },

...

PioterOP•14mo ago

...
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: []
    },
    StringChunk { value: [Array] }
  ]
}

...
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    undefined,
    StringChunk { value: [Array] },
    SQL {
      decoder: [Object],
      shouldInlineParams: false,
      queryChunks: []
    },
    StringChunk { value: [Array] }
  ]
}

Angelelz•14mo ago

and you are sure there's no typo in the email?

PioterOP•14mo ago

Absolutely . I tried several times

Angelelz•14mo ago

That query is correct, I think we can safely say drizzle is not the problem You said the problem is Neon?

PioterOP•14mo ago

This problem only occurs when I use Drizzle with Neon. The exact same logic works fine when using Drizzle and PlanetScale, or Neon and Prisma 5, or PlanetScale and Prisma 5. So, perhaps, the problem is with the @Neondatabase/serverless package?
This is how I connect:

import * as auth from "@/db/schemas/auth.schema"
import { env } from "@/env.mjs"
import { neon, neonConfig } from "@neondatabase/serverless"
import { drizzle } from "drizzle-orm/neon-http"

neonConfig.fetchConnectionCache = true

const schema = { ...auth }
const sql = neon(env.DATABASE_URL)

export const db = drizzle(sql, {
  schema,
})

import * as auth from "@/db/schemas/auth.schema"
import { env } from "@/env.mjs"
import { neon, neonConfig } from "@neondatabase/serverless"
import { drizzle } from "drizzle-orm/neon-http"

neonConfig.fetchConnectionCache = true

const schema = { ...auth }
const sql = neon(env.DATABASE_URL)

export const db = drizzle(sql, {
  schema,
})

There is no indication of any connection issues when running the app though, the user gets added to the database, I just can't get it back, as it returns undefined, for some reason. The email is 100% stored correctly in the database, as seen in the screenshot with Drizzle Studio. Also attempted to fill and send t he form several times, constantly getting undefined

Angelelz•14mo ago

Hmmm... You have caching going on?

neonConfig.fetchConnectionCache = true

Have you tried setting that to false? I'm not a neon user, so I don't know haw it works

PioterOP•14mo ago

Still the same issue, when neonConfig.fetchConnectionCache is set to false. I followed the guide on Drizzle's website, regarding the Neon connection: https://orm.drizzle.team/docs/quick-postgresql/neon

Neon - DrizzleORM

Drizzle ORM | %s

Angelelz•14mo ago

What environment are you running this from? Node, serverless, worker threads?

PioterOP•14mo ago

I am using Next.js 13 with the app router with the intention of hosting it on Vercel

Angelelz•14mo ago

I just saw this in the docs:

Drizzle ORM natively supports both Neon Serverless(opens in a new tab) driver with drizzle-orm/neon-serverless package and postgres or pg drivers to access Neon database, as of their official nodejs docs

https://orm.drizzle.team/docs/quick-postgresql/neon

Neon - DrizzleORM

Drizzle ORM | %s

Angelelz•14mo ago

So your next step would be to try and import drizzle from either: "drizzle-orm/neon-serverless", "drizzle-orm/postgres-js" or "drizzle-orm/node-postgres"

Angelelz•14mo ago

This might be worth the read: https://neon.tech/docs/serverless/serverless-driver

Neon

Neon serverless driver - Neon Docs

The Neon serverless driver is a low-latency Postgres driver for JavaScript and TypeScript that allows you to query data from serverless and edge environments over HTTP or WebSockets in place of TCP. Y...

PioterOP•13mo ago

If anyone stumbles upon similar problem, the cause turned out to be Next.js cache. Since Next 14, adding noStore() to my server actions involving Drizzle calls to Neon’s Postgres database (as described in the official Next 14 guide here) solved the problem.

Learn Next.js | Next.js by Vercel - The React Framework

Next.js by Vercel is the full-stack React framework for the web.

Yogen9•13mo ago

I am facing similar kind of issue with Nest.js, Passport google Oauth2.0. and DrizzleORM, i am getting Error: Client network socket disconnected before secure TLS connection was established error.

Gaming

Programming

Next 13, Next-Auth with Neon Postgres and Drizzle vs Prisma 5