[Drizzle-Adapter] Inconsistent table naming (plural vs singular)

[SSardorf]

Question 💬

Am I missing something or is the new Drizzle Adapter for NextAuth inconsistent in its naming? https://authjs.dev/reference/adapter/drizzle

Requires you to define four tables: "users", "accounts", "sessions", "verificationToken".

Why are the first three plural, but verificationToken is singular? In the other adapters, all four are singular.

Is there any way to change this locally, or am I bound to use the above naming convention?

Related to #7165

How to reproduce ☕️

Visit https://authjs.dev/reference/adapter/drizzle

Contributing 🙌🏽

Yes, I am willing to help answer this question in a PR

[adam4nj]

Its just convention, I guess. but changing table names depends on schema declared in createTables() which leads down to a MinimumSchema type based on which you can further extend. Maybe try it out and see what happens

[SSardorf]

Its just convention, I guess. but changing table names depends on schema declared in createTables() which leads down to a MinimumSchema type based on which you can further extend. Maybe try it out and see what happens

What's the convention here though? We're using both plural and singular by requiring the following table names: "users", "accounts", "sessions", "verificationToken". The first three is plural, the last one is singular - So which convention are we following here?

I propose that we keep them all singular, exactly like we do with all the other adapters

[balazsorban44]

Probably a typo. We can change it as we are still below 1.0.0. Doesn't mean much, as you can now pass your own tables since #8266 as pointed out.

Want to open a PR? Need to change in a few places:

https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/index.ts https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/lib/mysql.ts https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/lib/pg.ts https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/lib/sqlite.ts

[SSardorf]

Probably a typo. We can change it as we are still below 1.0.0. Doesn't mean much, as you can now pass your own tables since #8266 as pointed out.

Want to open a PR? Need to change in a few places:

https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/index.ts https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/lib/mysql.ts https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/lib/pg.ts https://github.com/nextauthjs/next-auth/blob/main/packages/adapter-drizzle/src/lib/sqlite.ts

Sure, I'll open a PR

[DaviPolita]

How about column names? Some tables mix camelCase and snake_case. Postgres convention is snake_case, mysql too I believe.

Maybe I'm being nitpicky, but I noticed this after updating the tables names due the last update.

[arnemolland]

How about column names? Some tables mix camelCase and snake_case. Postgres convention is snake_case, mysql too I believe.

Maybe I'm being nitpicky, but I noticed this after updating the tables names due the last update.

You're right, and the other adapters allow mapping column names. I've patched the drizzle adapter to user snake_case, I'll see if I can get a PR up.

[arnemolland]

How about column names? Some tables mix camelCase and snake_case. Postgres convention is snake_case, mysql too I believe. Maybe I'm being nitpicky, but I noticed this after updating the tables names due the last update.

You're right, and the other adapters allow mapping column names. I've patched the drizzle adapter to user snake_case, I'll see if I can get a PR up.

Until then, you could apply the following patch via e.g. pnpm patch

diff --git a/lib/pg.js b/lib/pg.js
index f3d333f0ba5ab12c807f507b7b52925a0489b136..6760746cda05aafc483c5e574fa1d94839722e2b 100644
--- a/lib/pg.js
+++ b/lib/pg.js
@@ -5,16 +5,16 @@ export function createTables(pgTable) {
         id: text("id").notNull().primaryKey(),
         name: text("name"),
         email: text("email").notNull(),
-        emailVerified: timestamp("emailVerified", { mode: "date" }),
+        emailVerified: timestamp("email_verified", { mode: "date" }),
         image: text("image"),
     });
     const accounts = pgTable("account", {
-        userId: text("userId")
+        userId: text("user_id")
             .notNull()
             .references(() => users.id, { onDelete: "cascade" }),
         type: text("type").$type().notNull(),
         provider: text("provider").notNull(),
-        providerAccountId: text("providerAccountId").notNull(),
+        providerAccountId: text("provider_account_id").notNull(),
         refresh_token: text("refresh_token"),
         access_token: text("access_token"),
         expires_at: integer("expires_at"),
@@ -26,13 +26,13 @@ export function createTables(pgTable) {
         compoundKey: primaryKey(account.provider, account.providerAccountId),
     }));
     const sessions = pgTable("session", {
-        sessionToken: text("sessionToken").notNull().primaryKey(),
-        userId: text("userId")
+        sessionToken: text("session_token").notNull().primaryKey(),
+        userId: text("user_id")
             .notNull()
             .references(() => users.id, { onDelete: "cascade" }),
         expires: timestamp("expires", { mode: "date" }).notNull(),
     });
-    const verificationTokens = pgTable("verificationToken", {
+    const verificationTokens = pgTable("verification_token", {
         identifier: text("identifier").notNull(),
         token: text("token").notNull(),
         expires: timestamp("expires", { mode: "date" }).notNull(),
diff --git a/src/lib/pg.ts b/src/lib/pg.ts
index ec33d7cf9662e09713c1b719d6c75ec4630ec6b1..4d117a88d8ba0a7f6b15eb8a8c7c76a84962c38c 100644
--- a/src/lib/pg.ts
+++ b/src/lib/pg.ts
@@ -16,19 +16,19 @@ export function createTables(pgTable: PgTableFn) {
     id: text("id").notNull().primaryKey(),
     name: text("name"),
     email: text("email").notNull(),
-    emailVerified: timestamp("emailVerified", { mode: "date" }),
+    emailVerified: timestamp("email_verified", { mode: "date" }),
     image: text("image"),
   })

   const accounts = pgTable(
     "account",
     {
-      userId: text("userId")
+      userId: text("user_id")
         .notNull()
         .references(() => users.id, { onDelete: "cascade" }),
       type: text("type").$type<AdapterAccount["type"]>().notNull(),
       provider: text("provider").notNull(),
-      providerAccountId: text("providerAccountId").notNull(),
+      providerAccountId: text("provider_account_id").notNull(),
       refresh_token: text("refresh_token"),
       access_token: text("access_token"),
       expires_at: integer("expires_at"),
@@ -43,15 +43,15 @@ export function createTables(pgTable: PgTableFn) {
   )

   const sessions = pgTable("session", {
-    sessionToken: text("sessionToken").notNull().primaryKey(),
-    userId: text("userId")
+    sessionToken: text("session_token").notNull().primaryKey(),
+    userId: text("user_id")
       .notNull()
       .references(() => users.id, { onDelete: "cascade" }),
     expires: timestamp("expires", { mode: "date" }).notNull(),
   })

   const verificationTokens = pgTable(
-    "verificationToken",
+    "verification_token",
     {
       identifier: text("identifier").notNull(),
       token: text("token").notNull(),