dynamic table name

how is that my code breaks:

const { tableName } = params;
const data = await dbPool.execute(sql`SELECT * FROM ${tableName}`);

const { tableName } = params;
const data = await dbPool.execute(sql`SELECT * FROM ${tableName}`);

but if i do this it works:

const data = await dbPool.execute(sql`SELECT * FROM account`);

const data = await dbPool.execute(sql`SELECT * FROM account`);

Error: Syntax error at or near "$1"
Location: 
- serverless/index.js:1336:74 (in a module at ./node_modules/.pnpm/@[email protected])
- ./src/routes/(api)/api/[table]/+server.ts:13:16
- hooks.server.ts:9:10 (at ./src)

Details:
- Error Code: 42601
- Position: 15
- File: scan.l
- Line: 1188
- Routine: scanner_yyerror

Stack trace details are available in the following modules:
- @sveltejs/kit (at ./node_modules/.pnpm/@[email protected][email protected][email protected])
- @neondatabase/serverless (at ./node_modules/.pnpm/@[email protected])

Error: Syntax error at or near "$1"
Location: 
- serverless/index.js:1336:74 (in a module at ./node_modules/.pnpm/@[email protected])
- ./src/routes/(api)/api/[table]/+server.ts:13:16
- hooks.server.ts:9:10 (at ./src)

Details:
- Error Code: 42601
- Position: 15
- File: scan.l
- Line: 1188
- Routine: scanner_yyerror

Stack trace details are available in the following modules:
- @sveltejs/kit (at ./node_modules/.pnpm/@[email protected][email protected][email protected])
- @neondatabase/serverless (at ./node_modules/.pnpm/@[email protected])

7 Replies

MrMendezOP•15mo ago

turns out that using the

await dbPool.execute(sql`SELECT * FROM ${sql.raw(tableName)}`);

await dbPool.execute(sql`SELECT * FROM ${sql.raw(tableName)}`);

works, but what would be the best way for dynamic queries?

Luxaritas•15mo ago

When you pass a value in an interpolation, it converts it into a parameter of a prepared query. Table names are an invalid location for parameters in prepared queries To provide more guidance on best practices, it might be helpful to get some more info on your use case for dynamic table names in the query

MrMendezOP•15mo ago

my use case is to have an endpoint where you pass a tableName and get all the rows in the table also you can pass an array of fields and get those fields too

Luxaritas•15mo ago

As far as “Best practices” go, coupling your API that closely to the schema is generally not desirable If you really want to do that, you need to make sure you validate the table names are actual tables in your DB before inserting them into the query, otherwise you are opening the opportunity for a sql injection (or just in general allowing access to tables that you may intend to gave behind access control, etc)

Angelelz•15mo ago

I'd suggest you create a map like this:

const tableMap: Record<string, MySqlTable> = {
  table1,
  table2,
}

const tableMap: Record<string, MySqlTable> = {
  table1,
  table2,
}

And then create the query like this:

if (tableMap[tableName]) {
  await dbPool.select().from(tableMap[tableName]);
}

if (tableMap[tableName]) {
  await dbPool.select().from(tableMap[tableName]);
}

Luxaritas•15mo ago

That’s a great approach

Ontropy•5mo ago

hey, i was facing a similar issue. i am working on a multiplayer game, which has rooms, so a certain amount of players in a particular room can only interact with each other. now each room will be storing some coordinates and data, which could go up to be in a few 100 thousands. initially i had been putting in all the data in one huge table, and separating using room name while querying. to eliminate the separation part each time for such a huge database, i was thinking i could make tables for each of the rooms on the fly. sort of like CREATE TABLE ${ roomName } what would be the appropriate approach for this? if go with the single huge table approach, i fear the table would go into many millions in a month and the queries would start to slow.

Gaming

Programming

dynamic table name