Kysely•5mo ago

How to type tables with dynamic names?

The MS SQL dialect only supports global temporary objects, that forces us to add random table suffix to prevent collisions between sessions. Is it possible to type the table contents as a second query? Conceptually my code runs like this:

const tableName = `##TMP_${someRandomHash(6)}`;
const stream = await db
  .connection()
  .execute(async (con) => {
    await sql`
      SELECT colA, colB INTO ${sql.raw(tableName)}
      FROM SomeTable;
    `.execute(con);

    return con.withTables<{
      [tableName]: {
        colA: string;
        colB: string;
      };
    }>()
      .selectFrom(tableName)
      .select(["colA", "colB"])
      .stream();
  });

for await (const row of stream) {
  // do stuff here
}

const tableName = `##TMP_${someRandomHash(6)}`;
const stream = await db
  .connection()
  .execute(async (con) => {
    await sql`
      SELECT colA, colB INTO ${sql.raw(tableName)}
      FROM SomeTable;
    `.execute(con);

    return con.withTables<{
      [tableName]: {
        colA: string;
        colB: string;
      };
    }>()
      .selectFrom(tableName)
      .select(["colA", "colB"])
      .stream();
  });

for await (const row of stream) {
  // do stuff here
}

My current road blocks: 1. .withTables() does not accept object type with dynamic keys [tableName] where it shows this error

A computed property name in a type literal must refer to an expression whose type is a literal type or a 'unique symbol' type.

, 2. A raw SQL approach via sql literal does not provide a .stream() method.

Solution:

Hey 👋 A workaround would be to use the same table name in .withTables and in the queries, and implement a plugin that on-the-fly adds the random suffix to all references to that table....

Jump to solution

3 Replies

koskimas•5mo ago

does not accept object type with dynamic keys

Of course it doesn't. The key is known at runtime while types need to be known at compile time. How would typescript know what the result of someRandomHash(6) is going to be when you run the code? Please explain to me how you think that could ever work?

Solution

Igal•5mo ago

Hey 👋 A workaround would be to use the same table name in .withTables and in the queries, and implement a plugin that on-the-fly adds the random suffix to all references to that table.

vicary•5mo ago

Looks like .withPlugin() for that session would work, thanks for the answer!

Gaming

Programming

How to type tables with dynamic names?