Inserting multiple rows with a single query via the sql.file method.

[virtuallyunknown]

Greetings.

I really like this library, however, one thing I don't like is using tagged templates, because that way you can't have auto-formatting and code-completions/suggestions in VSCode. So the way I go about this instead is to create an .sql file next to my .ts file, then use sql.file(...), for example:

-- file: teams/insert.sql
INSERT INTO teams (id, name, logo)
    VALUES ($1, $2, $3);

// file teams/team.ts
import { sql } from '../../services/db.js';

export class Teams {
    public static async insert() {
        await sql.file(new URL('insert.sql', import.meta.url), [1, 'Team A', 'team_a.png']);
    }
}

This works, but what if you wanted to insert multiple rows at the same time? The documentation says to use an array of values, and sure enough it works with the tagged template, however sql.file no longer works. Example:

const teams = [
    [1, 'Team A', 'team_a.png'],
    [2, 'Team B', 'team_b.png'],
];

// ✅ works
await sql`INSERT INTO teams (id, name, logo) VALUES ${sql(teams)}`;

// ❌ error
await sql.file(new URL('insert.sql', import.meta.url), teams);

// ❌ error
await sql.file(new URL('insert.sql', import.meta.url), [teams]);

I've done some debugging trying to replicate the args that the tagged version sends to the database, but I couldn't get it right. I think the library does some extra work behind the scenes to modify the statement to match the amount of rows you pass in, however the file method doesn't.

So with all of that in mind, I do have two quick questions:

1. Is there a workaround or solution to this current problem?
2. Are there any known limitations on what you can achieve using the file method as compared to tagged templates, because if they are I might have to reconsider my strategy of using file everywhere.

Cheers!