Better Auth•4mo ago

Drizzle ORM Schema

Hello, I was just trying to use better-auth with drizzleORM. But when I did the drizzle-kit generate and drizzle-kit migrate I got this error: #22 8.238 #22 11.07 42 | }); #22 11.07 43 | } #22 11.07 44 | #22 11.07 45 | execute(query, params) { #22 11.07 46 | const c = this.connection; #22 11.07 47 | const localErr = new Error(); #22 11.07 ^ #22 11.07 error: Can't create table api.account (errno: 150 "Foreign key constraint is incorrectly formed") #22 11.07 errno: 1005, #22 11.07 sql: "ALTER TABLE account ADD CONSTRAINT account_user_id_user_id_fk FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE cascade ON UPDATE no action;", #22 11.07 sqlState: "HY000", #22 11.07 sqlMessage: "Can't create table api.account (errno: 150 "Foreign key constraint is incorrectly formed")", #22 11.07 code: "ER_CANT_CREATE_TABLE" #22 11.07 #22 11.07 at execute (/app/node_modules/mysql2/lib/promise/connection.js:47:22) #22 11.07 at <anonymous> (/app/node_modules/drizzle-kit/bin.cjs:79120:40) #22 11.07 at query (/app/node_modules/drizzle-kit/bin.cjs:79119:35) #22 11.07 at <anonymous> (/app/node_modules/drizzle-kit/bin.cjs:82230:22) #22 11.07 #22 DONE 11.3s This are my auth files:

import { betterAuth } from "better-auth";
import { drizzleAdapter } from "better-auth/adapters/drizzle";
import { admin, openAPI, username } from "better-auth/plugins";
import { passkey } from "better-auth/plugins/passkey";

import * as schema from "@/db/auth-schema";
import env from "@/env";
import { db } from "@/lib/db";

export const auth = betterAuth({
  appName: env.APP_NAME,
  basePath: "/api",
  overrideOrigin: true,
  /* database: prismaAdapter(db, {
    provider: "mysql",
  }), */
  database: drizzleAdapter(db, {
    provider: "mysql",
    schema: {
      ...schema,
    },
  }),
  emailAndPassword: {
    enabled: true,
    requireEmailVerification: true,
    minPasswordLength: 8,
    maxPasswordLength: 128,
    autoSignIn: true,
    /* sendResetPassword: async ({ _user, _url, _token }) => {
      // TODO: Send reset password email
    }, */
    resetPasswordTokenExpiresIn: 3600,
  },
  advanced: {
    crossSubDomainCookies: {
      enabled: true,
    },
    defaultCookieAttributes: {
      sameSite: "Lax",
      secure: env.NODE_ENV === "production",
      partitioned: true,
    },
  },
  session: {
    cookieCache: {
      enabled: true,
      maxAge: 5 * 60,
    },
  },
  account: {
    accountLinking: {
      enabled: true,
      trustedProviders: [],
      allowDifferentEmails: false,
    },
  },
  rateLimit: {
    enabled: true,
    window: 10,
    max: 100,
    storage: "memory",
    modelName: "rate_limit",
  },
  onAPIError: {
    throw: true,
    onError: (error, _ctx) => {
      console.error("Auth API Error", error);
    },
    errorURL: "/auth/error",
  },
  plugins: [
    openAPI(),
  ],
});

let _schema: ReturnType<typeof auth.api.generateOpenAPISchema>;
const getSchema = async () => (_schema ??= auth.api.generateOpenAPISchema());

export const OpenAPI = {
  getPaths: (prefix = "/auth/api") =>
    getSchema().then(({ paths }) => {
      const reference: typeof paths = Object.create(null);

      for (const path of Object.keys(paths)) {
        const key = prefix + path;
        reference[key] = paths[path];

        for (const method of Object.keys(paths[path])) {
          const operation = (reference[key] as any)[method];

          operation.tags = ["Better Auth"];
        }
      }

      return reference;
    }) as Promise<any>,
  components: getSchema().then(({ components }) => components) as Promise<any>,
} as const;

import { betterAuth } from "better-auth";
import { drizzleAdapter } from "better-auth/adapters/drizzle";
import { admin, openAPI, username } from "better-auth/plugins";
import { passkey } from "better-auth/plugins/passkey";

import * as schema from "@/db/auth-schema";
import env from "@/env";
import { db } from "@/lib/db";

export const auth = betterAuth({
  appName: env.APP_NAME,
  basePath: "/api",
  overrideOrigin: true,
  /* database: prismaAdapter(db, {
    provider: "mysql",
  }), */
  database: drizzleAdapter(db, {
    provider: "mysql",
    schema: {
      ...schema,
    },
  }),
  emailAndPassword: {
    enabled: true,
    requireEmailVerification: true,
    minPasswordLength: 8,
    maxPasswordLength: 128,
    autoSignIn: true,
    /* sendResetPassword: async ({ _user, _url, _token }) => {
      // TODO: Send reset password email
    }, */
    resetPasswordTokenExpiresIn: 3600,
  },
  advanced: {
    crossSubDomainCookies: {
      enabled: true,
    },
    defaultCookieAttributes: {
      sameSite: "Lax",
      secure: env.NODE_ENV === "production",
      partitioned: true,
    },
  },
  session: {
    cookieCache: {
      enabled: true,
      maxAge: 5 * 60,
    },
  },
  account: {
    accountLinking: {
      enabled: true,
      trustedProviders: [],
      allowDifferentEmails: false,
    },
  },
  rateLimit: {
    enabled: true,
    window: 10,
    max: 100,
    storage: "memory",
    modelName: "rate_limit",
  },
  onAPIError: {
    throw: true,
    onError: (error, _ctx) => {
      console.error("Auth API Error", error);
    },
    errorURL: "/auth/error",
  },
  plugins: [
    openAPI(),
  ],
});

let _schema: ReturnType<typeof auth.api.generateOpenAPISchema>;
const getSchema = async () => (_schema ??= auth.api.generateOpenAPISchema());

export const OpenAPI = {
  getPaths: (prefix = "/auth/api") =>
    getSchema().then(({ paths }) => {
      const reference: typeof paths = Object.create(null);

      for (const path of Object.keys(paths)) {
        const key = prefix + path;
        reference[key] = paths[path];

        for (const method of Object.keys(paths[path])) {
          const operation = (reference[key] as any)[method];

          operation.tags = ["Better Auth"];
        }
      }

      return reference;
    }) as Promise<any>,
  components: getSchema().then(({ components }) => components) as Promise<any>,
} as const;

db.ts:

import { drizzle } from "drizzle-orm/mysql2";
import "dotenv/config";

import env from "@/env";

export const db = drizzle(env.DATABASE_URL);

import { drizzle } from "drizzle-orm/mysql2";
import "dotenv/config";

import env from "@/env";

export const db = drizzle(env.DATABASE_URL);

3 Replies

mayivtOP•4mo ago

auth-schema.ts:

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

export const user = mysqlTable("user", {
    id: varchar('id', { length: 36 }).primaryKey(),
    name: text('name').notNull(),
    email: varchar('email', { length: 255 }).notNull().unique(),
    emailVerified: boolean('email_verified').$defaultFn(() => false).notNull(),
    image: text('image'),
    createdAt: timestamp('created_at').$defaultFn(() => /* @__PURE__ */ new Date()).notNull(),
    updatedAt: timestamp('updated_at').$defaultFn(() => /* @__PURE__ */ new Date()).notNull()
});

export const session = mysqlTable("session", {
    id: varchar('id', { length: 36 }).primaryKey(),
    expiresAt: timestamp('expires_at').notNull(),
    token: varchar('token', { length: 255 }).notNull().unique(),
    createdAt: timestamp('created_at').notNull(),
    updatedAt: timestamp('updated_at').notNull(),
    ipAddress: text('ip_address'),
    userAgent: text('user_agent'),
    userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' })
});

export const account = mysqlTable("account", {
    id: varchar('id', { length: 36 }).primaryKey(),
    accountId: text('account_id').notNull(),
    providerId: text('provider_id').notNull(),
    userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' }),
    accessToken: text('access_token'),
    refreshToken: text('refresh_token'),
    idToken: text('id_token'),
    accessTokenExpiresAt: timestamp('access_token_expires_at'),
    refreshTokenExpiresAt: timestamp('refresh_token_expires_at'),
    scope: text('scope'),
    password: text('password'),
    createdAt: timestamp('created_at').notNull(),
    updatedAt: timestamp('updated_at').notNull()
});

export const verification = mysqlTable("verification", {
    id: varchar('id', { length: 36 }).primaryKey(),
    identifier: text('identifier').notNull(),
    value: text('value').notNull(),
    expiresAt: timestamp('expires_at').notNull(),
    createdAt: timestamp('created_at').$defaultFn(() => /* @__PURE__ */ new Date()),
    updatedAt: timestamp('updated_at').$defaultFn(() => /* @__PURE__ */ new Date())
});

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

export const user = mysqlTable("user", {
    id: varchar('id', { length: 36 }).primaryKey(),
    name: text('name').notNull(),
    email: varchar('email', { length: 255 }).notNull().unique(),
    emailVerified: boolean('email_verified').$defaultFn(() => false).notNull(),
    image: text('image'),
    createdAt: timestamp('created_at').$defaultFn(() => /* @__PURE__ */ new Date()).notNull(),
    updatedAt: timestamp('updated_at').$defaultFn(() => /* @__PURE__ */ new Date()).notNull()
});

export const session = mysqlTable("session", {
    id: varchar('id', { length: 36 }).primaryKey(),
    expiresAt: timestamp('expires_at').notNull(),
    token: varchar('token', { length: 255 }).notNull().unique(),
    createdAt: timestamp('created_at').notNull(),
    updatedAt: timestamp('updated_at').notNull(),
    ipAddress: text('ip_address'),
    userAgent: text('user_agent'),
    userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' })
});

export const account = mysqlTable("account", {
    id: varchar('id', { length: 36 }).primaryKey(),
    accountId: text('account_id').notNull(),
    providerId: text('provider_id').notNull(),
    userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' }),
    accessToken: text('access_token'),
    refreshToken: text('refresh_token'),
    idToken: text('id_token'),
    accessTokenExpiresAt: timestamp('access_token_expires_at'),
    refreshTokenExpiresAt: timestamp('refresh_token_expires_at'),
    scope: text('scope'),
    password: text('password'),
    createdAt: timestamp('created_at').notNull(),
    updatedAt: timestamp('updated_at').notNull()
});

export const verification = mysqlTable("verification", {
    id: varchar('id', { length: 36 }).primaryKey(),
    identifier: text('identifier').notNull(),
    value: text('value').notNull(),
    expiresAt: timestamp('expires_at').notNull(),
    createdAt: timestamp('created_at').$defaultFn(() => /* @__PURE__ */ new Date()),
    updatedAt: timestamp('updated_at').$defaultFn(() => /* @__PURE__ */ new Date())
});

I just did the npx @better-auth/cli@latest generate npx drizzle-kit generate npx drizzle-kit migrate and got the error

OrwaDotDev•4mo ago

could be a size mismatch between session/account userId foreign key and the actual user.id column. id => is varchar(36) userId => text try:

userId: varchar('user_id', { length: 36 }).notNull().references(() => user.id, { onDelete: 'cascade' })

userId: varchar('user_id', { length: 36 }).notNull().references(() => user.id, { onDelete: 'cascade' })

mayivtOP•4mo ago

it worked, thanks :)

Gaming

Programming

Drizzle ORM Schema

Did you find this page helpful?