Drizzle Team•3mo ago

Drizzle/better-auth + Local PSQL

I have the biggest issue trying to setup drizzle with my project and I don't believe it should be this complicated as I am facing currently. Someone with more experience please help me out and explain to me what I am doing wrong. So I am trying to setup better-auth with Drizzle + my local PostgreSQL and I am using the latest version of Next js for my project. I basically start of my initialising my next js project by running the regular command npx create-next-app@latest (app router no src-folder) and go then install drizzle following the docs and run the commands that are shown in the docs/guide. I create my .env file and setup my DATABASE_URL="postgres://postgres:mypassword@localhost:5432/postgres" and move on to creating my db directory and place the index.ts file inside of it which holds this code.

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

export function createClient(connectionString: string) {
  const client = postgres(connectionString);
  return drizzle(client, { schema });
}

export type DbClient = ReturnType<typeof createClient>;

export function createMigrationClient(connectionString: string) {
  const psql = postgres(connectionString, { max: 1 });
  const db = drizzle(psql, { schema });

  return db;
}

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

export function createClient(connectionString: string) {
  const client = postgres(connectionString);
  return drizzle(client, { schema });
}

export type DbClient = ReturnType<typeof createClient>;

export function createMigrationClient(connectionString: string) {
  const psql = postgres(connectionString, { max: 1 });
  const db = drizzle(psql, { schema });

  return db;
}

And I then create my schema.ts file. This is basically just copied over from the docs. Afterwards I move on to create my drizzle.config.ts file but I slightly modify it.

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

What I change here is that I change the schema: './src/db/schema.ts', to schema: './db/schema.ts', for app directory in next js.

21 Replies

JacobOP•3mo ago

From this point on I sort of move into the better-auth installation part of their docs and do the steps that are. I run the command of npx @better-auth/cli generate which gives me the back a auth-schema.ts file. That sort of looks like this

import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("emailVerified").notNull(),
  image: text("image"),
  createdAt: timestamp("createdAt").notNull(),
  updatedAt: timestamp("updatedAt").notNull(),
});

export const session = pgTable("session", {
  id: text("id").primaryKey(),
  expiresAt: timestamp("expiresAt").notNull(),
  ipAddress: text("ipAddress"),
  userAgent: text("userAgent"),
  userId: text("userId")
    .notNull()
    .references(() => user.id),
});

export const account = pgTable("account", {
  id: text("id").primaryKey(),
  accountId: text("accountId").notNull(),
  providerId: text("providerId").notNull(),
  userId: text("userId")
    .notNull()
    .references(() => user.id),
  accessToken: text("accessToken"),
  refreshToken: text("refreshToken"),
  idToken: text("idToken"),
  expiresAt: timestamp("expiresAt"),
  password: text("password"),
});

export const verification = pgTable("verification", {
  id: text("id").primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expiresAt").notNull(),
});

import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("emailVerified").notNull(),
  image: text("image"),
  createdAt: timestamp("createdAt").notNull(),
  updatedAt: timestamp("updatedAt").notNull(),
});

export const session = pgTable("session", {
  id: text("id").primaryKey(),
  expiresAt: timestamp("expiresAt").notNull(),
  ipAddress: text("ipAddress"),
  userAgent: text("userAgent"),
  userId: text("userId")
    .notNull()
    .references(() => user.id),
});

export const account = pgTable("account", {
  id: text("id").primaryKey(),
  accountId: text("accountId").notNull(),
  providerId: text("providerId").notNull(),
  userId: text("userId")
    .notNull()
    .references(() => user.id),
  accessToken: text("accessToken"),
  refreshToken: text("refreshToken"),
  idToken: text("idToken"),
  expiresAt: timestamp("expiresAt"),
  password: text("password"),
});

export const verification = pgTable("verification", {
  id: text("id").primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expiresAt").notNull(),
});

I then try to run this npx drizzle-kit migrate to migrate things over and I get this error.

npx drizzle-kit migrate
  No config path provided, using default 'drizzle.config.ts'
  Reading config file '/home/jacob/github/next/better-auth/drizzle.config.ts'
  Using 'pg' driver for database querying

npx drizzle-kit migrate
  No config path provided, using default 'drizzle.config.ts'
  Reading config file '/home/jacob/github/next/better-auth/drizzle.config.ts'
  Using 'pg' driver for database querying

Darren•3mo ago

did you reference this new schema file in your drizzle config, and did you run npx drizzle-kit generate first, to generate the migration sql, before running drizzle-kit migrate what you saw isnt any error, it just didnt migrate anything

JacobOP•3mo ago

I bascially took what ever was in the auth-schema.ts and and placed it inside of the schema.ts file. I don't really use the other file. It's only usecase was to generate the info shown above. Yes I did run those commands and when I run the npx drizzle-kit generate it says No schema changes, nothing to migrate because I have already ran it before. This only changes in case I delete the drizzle directory inside of my project.

JacobOP•3mo ago

Also if I open up pgadmin to see what's inside the db I get 2 schemas. One named public (which I'm used to seeing from other projects) and a schema named "drizzle". Should this be the case?

Darren•3mo ago

yes, drizzle keeps track of migrations so whe nu ran generate it didnt generate a sql file in drizzle/migrations?

JacobOP•3mo ago

Do you mean inside pgadmin itself?

Darren•3mo ago

no in your project folder

JacobOP•3mo ago

Oh yeah. As I said earlier it does generate that directory

JacobOP•3mo ago

Darren•3mo ago

ok and whats in that sql file

JacobOP•3mo ago

CREATE TABLE IF NOT EXISTS "account" (
    "id" text PRIMARY KEY NOT NULL,
    "accountId" text NOT NULL,
    "providerId" text NOT NULL,
    "userId" text NOT NULL,
    "accessToken" text,
    "refreshToken" text,
    "idToken" text,
    "expiresAt" timestamp,
    "password" text
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "session" (
    "id" text PRIMARY KEY NOT NULL,
    "expiresAt" timestamp NOT NULL,
    "ipAddress" text,
    "userAgent" text,
    "userId" text NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "user" (
    "id" text PRIMARY KEY NOT NULL,
    "name" text NOT NULL,
    "email" text NOT NULL,
    "emailVerified" boolean NOT NULL,
    "image" text,
    "createdAt" timestamp NOT NULL,
    "updatedAt" timestamp NOT NULL,
    CONSTRAINT "user_email_unique" UNIQUE("email")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "verification" (
    "id" text PRIMARY KEY NOT NULL,
    "identifier" text NOT NULL,
    "value" text NOT NULL,
    "expiresAt" timestamp NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "session" ADD CONSTRAINT "session_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "account" (
    "id" text PRIMARY KEY NOT NULL,
    "accountId" text NOT NULL,
    "providerId" text NOT NULL,
    "userId" text NOT NULL,
    "accessToken" text,
    "refreshToken" text,
    "idToken" text,
    "expiresAt" timestamp,
    "password" text
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "session" (
    "id" text PRIMARY KEY NOT NULL,
    "expiresAt" timestamp NOT NULL,
    "ipAddress" text,
    "userAgent" text,
    "userId" text NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "user" (
    "id" text PRIMARY KEY NOT NULL,
    "name" text NOT NULL,
    "email" text NOT NULL,
    "emailVerified" boolean NOT NULL,
    "image" text,
    "createdAt" timestamp NOT NULL,
    "updatedAt" timestamp NOT NULL,
    CONSTRAINT "user_email_unique" UNIQUE("email")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "verification" (
    "id" text PRIMARY KEY NOT NULL,
    "identifier" text NOT NULL,
    "value" text NOT NULL,
    "expiresAt" timestamp NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "account" ADD CONSTRAINT "account_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "session" ADD CONSTRAINT "session_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Darren•3mo ago

ok now in your db whats in the drizzle migrations table

JacobOP•3mo ago

Darren•3mo ago

so you are saying that none of these tables are created in public

JacobOP•3mo ago

No they are created. I can see the tables. I am curious to why it's giving me the error in my terminal when I run the command npx drizzle-kit migrate

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/jacob/github/next/better-auth/drizzle.config.ts'
Using 'pg' driver for database querying

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/jacob/github/next/better-auth/drizzle.config.ts'
Using 'pg' driver for database querying

Darren•3mo ago

thats not an error its telling you you didnt pass in a config file so its using default one it can find

JacobOP•3mo ago

What does that mean exactly.

Darren•3mo ago

the config file is how to connect to your database the one you made at the top in teh command line you didnt pass it as an argument so it searched your project for one and used the one it found

JacobOP•3mo ago

So where can I find more info about how to do that exactly because I mostly just copied over code from the docs. I now need to understand exactly how it works Basically what your saying is that my index.ts file should be passed into my drizzle.config.ts file right?

Darren•3mo ago

no you do not need to do anything everything worked as it should

JacobOP•3mo ago

Okej then Thanks Darren 😄

Gaming

Programming

Drizzle/better-auth + Local PSQL