Using custom schema for models, with prisma adapter.
Can you specify different database schema when using postgresql with prisma?
For example, I have my user table in auth.users rather than the default public.
5 Replies
need to know the same thing myself!
I've gotten it 'working' with a hacky work-around and I want to find a better way:
// In auth.ts
export const auth = betterAuth({
// Other config...
// Explicitly prefix tables with schema user: { modelName: "better_auth.better_auth_user", // Note the schema prefix // ...fields config }, session: { modelName: "better_auth.better_auth_session", // ...fields config }, account: { modelName: "better_auth.better_auth_account", // ...fields config }, verification: { modelName: "better_auth.better_auth_verification", // ...fields config } }); got it working with direct pg // 1. Setup PostgreSQL connection with schema management import { betterAuth } from 'better-auth'; import { createAuthMiddleware } from 'better-auth/api'; import pg from 'pg'; const { Pool } = pg; // Create database connection pool const pool = new Pool({ connectionString: process.env.DATABASE_URI || '', }); // Configure connection to use better_auth schema pool.on('connect', async (client) => { // Create schema if it doesn't exist await client.query('CREATE SCHEMA IF NOT EXISTS better_auth'); // Set search path to prefer better_auth schema await client.query('SET search_path TO better_auth'); }); // 2. Configure Better Auth with the pool export const auth = betterAuth({ // Basic configuration...
// Database configuration using PostgreSQL with better_auth schema database: pool,
// Simple user configuration with additional fields user: { additionalFields: { firstName: { type: "string", required: false }, lastName: { type: "string", required: false } } },
// Other configuration... }); // 3. Migration // Run the Better Auth CLI with: // npx @better-auth/cli@latest generate --config path/to/auth.ts // npx @better-auth/cli@latest migrate --config path/to/auth.ts
// Explicitly prefix tables with schema user: { modelName: "better_auth.better_auth_user", // Note the schema prefix // ...fields config }, session: { modelName: "better_auth.better_auth_session", // ...fields config }, account: { modelName: "better_auth.better_auth_account", // ...fields config }, verification: { modelName: "better_auth.better_auth_verification", // ...fields config } }); got it working with direct pg // 1. Setup PostgreSQL connection with schema management import { betterAuth } from 'better-auth'; import { createAuthMiddleware } from 'better-auth/api'; import pg from 'pg'; const { Pool } = pg; // Create database connection pool const pool = new Pool({ connectionString: process.env.DATABASE_URI || '', }); // Configure connection to use better_auth schema pool.on('connect', async (client) => { // Create schema if it doesn't exist await client.query('CREATE SCHEMA IF NOT EXISTS better_auth'); // Set search path to prefer better_auth schema await client.query('SET search_path TO better_auth'); }); // 2. Configure Better Auth with the pool export const auth = betterAuth({ // Basic configuration...
// Database configuration using PostgreSQL with better_auth schema database: pool,
// Simple user configuration with additional fields user: { additionalFields: { firstName: { type: "string", required: false }, lastName: { type: "string", required: false } } },
// Other configuration... }); // 3. Migration // Run the Better Auth CLI with: // npx @better-auth/cli@latest generate --config path/to/auth.ts // npx @better-auth/cli@latest migrate --config path/to/auth.ts
also if you're using drizze, this fella figured it out for using a db adapter
ok, I guess for the prisma adapter, your workaround would work. I see it doesn't have any schema parameter
after fighting with this all day, I gave up and just gave my better auth instance its own database/connection string, now everything is operating smooth, clean, and easy. Best practice in most cases too, to give the Auth its own database me thinks ... speration of concerns and all that.
The custom schema was more headache than it was worth