Rich Prins Posts - Answer Overflow

Rich Prins

Explore posts from servers

•Created by Rich Prins on 3/28/2025 in #help

pgSchemas and BetterAuth

I am trying to integrate better auth and drizzle into a new Elysia app, and had some questions regarding separating schemas in my db. I want to put the users, accounts, verifications, and sessions on an auth schema and the rest on a public schema. Can someone give me a sanity check to make sure I am doing this right?

//drizzle.config.ts
import type { Config } from "drizzle-kit";
import * as dotenv from "dotenv";
dotenv.config();
const { DATABASE_URL } = process.env;
if (!DATABASE_URL) {
  throw new Error("No url");
}
export default {
  schema: "./src/lib/database/schema",
  out: "./src/lib/database/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: DATABASE_URL,
  },
  schemaFilter: ["auth", "public"],
  tablesFilter: [
    "auth.users",
    "auth.sessions",
    "auth.accounts",
    "auth.verifications",
    "public.*",
  ],
} satisfies Config;


//schema.ts

export const authSchema = pgSchema("auth");
export const publicSchema = pgSchema("public");

// Auth Tables

export const users = authSchema.table("users", {
  // column defs
});


// better auth instance


export const auth = betterAuth({
  database: drizzleAdapter(db, {
    provider: "pg",
    usePlural: true,
    schema: {
      ...schema,
      users: schema.users,
      // other auth tables
    }
  }),
  
});

//drizzle.config.ts
import type { Config } from "drizzle-kit";
import * as dotenv from "dotenv";
dotenv.config();
const { DATABASE_URL } = process.env;
if (!DATABASE_URL) {
  throw new Error("No url");
}
export default {
  schema: "./src/lib/database/schema",
  out: "./src/lib/database/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: DATABASE_URL,
  },
  schemaFilter: ["auth", "public"],
  tablesFilter: [
    "auth.users",
    "auth.sessions",
    "auth.accounts",
    "auth.verifications",
    "public.*",
  ],
} satisfies Config;


//schema.ts

export const authSchema = pgSchema("auth");
export const publicSchema = pgSchema("public");

// Auth Tables

export const users = authSchema.table("users", {
  // column defs
});


// better auth instance


export const auth = betterAuth({
  database: drizzleAdapter(db, {
    provider: "pg",
    usePlural: true,
    schema: {
      ...schema,
      users: schema.users,
      // other auth tables
    }
  }),
  
});

1 replies

DTDrizzle Team

•Created by Rich Prins on 5/22/2024 in #help

question about partial query for joins

I am running a query for 'table1', and have an inner join for the user related to 'table1' by userId, but I wanted to limit the columns for user, becuase as my query stands now, I am leaking password hashes and that's obv unsafe. Can someone point me in the right direction? It's late and I have been digging in the docs for something to alleviate this but I am trying not to resolve to using raw sql template. Here is my query as it stands:

    const result = await db
        .select()
        .from(candidateProfileTable)
        .where(eq(candidateProfileTable.id, candidateId))
        .innerJoin(userTable, eq(candidateProfileTable.userId, userTable.id))
        .innerJoin(disciplineTable, eq(candidateProfileTable.disciplineId, disciplineTable.id))
        .innerJoin(regionTable, eq(candidateProfileTable.regionId, regionTable.id))
        .leftJoin(subRegionTable, eq(regionTable.id, subRegionTable.regionId));

    const result = await db
        .select()
        .from(candidateProfileTable)
        .where(eq(candidateProfileTable.id, candidateId))
        .innerJoin(userTable, eq(candidateProfileTable.userId, userTable.id))
        .innerJoin(disciplineTable, eq(candidateProfileTable.disciplineId, disciplineTable.id))
        .innerJoin(regionTable, eq(candidateProfileTable.regionId, regionTable.id))
        .leftJoin(subRegionTable, eq(regionTable.id, subRegionTable.regionId));

3 replies

Gaming

Programming