Are dynamic schema names using @vercel/postgres possible?

Question

I have the following code and it seems to be throwing me the following error:

async function isUser(email: string, password: string) {
    await createTable();

    console.log("Database", env.DATABASE);

    const hashed = await Bun.password.hash(password);
    const { rows } = await sql<
        User<"email" | "password">
    >`SELECT email, password FROM ${env.DATABASE}.users WHERE email = ${email}`;
    const userFound: User<"email" | "password"> | undefined = rows[0];

    return userFound && (await Bun.password.verify(hashed, userFound.password));
}

NeonDbError: db error: ERROR: syntax error at or near "$1"

Caused by:
    ERROR: syntax error at or near "$1"
    at execute (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.js:4713:70)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async createTable (webpack-internal:///(rsc)/./src/server/database.ts:10:5)
    at async Object.isUser (webpack-internal:///(rsc)/./src/server/database.ts:68:5)
    at async Object.authorize (webpack-internal:///(rsc)/./src/app/api/auth/[...nextauth]/route.ts:39:37)
    at async Object.callback (webpack-internal:///(rsc)/./node_modules/next-auth/core/routes/callback.js:291:20)
    at async AuthHandler (webpack-internal:///(rsc)/./node_modules/next-auth/core/index.js:298:38)
    at async NextAuthRouteHandler (webpack-internal:///(rsc)/./node_modules/next-auth/next/index.js:50:30)
    at async NextAuth._args$ (webpack-internal:///(rsc)/./node_modules/next-auth/next/index.js:85:24)
    at async /home/cyrus/Projects/PLMR/dynamic-qr-code-generator/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:6:62609 {
  code: '42601',
  sourceError: undefined
}

My guess is that it's perceiving the schema name that I'm trying to pass in as an argument when it can't be parsed that way. If that is the case, is there any better way I can approach this? Any advice helps.

Griffon Nivernais · Answer

Did a bit of Googling and apparently you CAN do this by creating the query manually using sql.query. You CAN miss out on the protection it gives you if you do not setup parameters as $1, $2, ... in the query, so make sure to do that and pass them in as an argument array to avoid SQL injection attacks (or to follow best practice where possible if only server-ended):const query = `SELECT email, password FROM ${env.DATABASE}.users WHERE email = $1`;
const { rows } = sql.query(query, [email]);

// ...

Are dynamic schema names using @vercel/postgres possible?

1 Reply