Skip to content

better-sqlite3 parameter binding issues #44

Open
@zareith

Description

@zareith

Hello, thanks for maintaining this library. This is a great concept.

I am trying out the newly added better-sqlite3 in main branch. There appear to be some issues with parameter binding.

If we have a query like:

-- name: InsertTest :exec
insert into test (email, expire_at)
values (?, unixepoch(current_timestamp) + ?) ;

Then it generates an interface like:

export interface InsertTestArgs {
    email: any;
    : any | null;
}

While it is understandable that there is no correct way to infer a name for these parameters, it would be better if the generation failed with an error in such cases rather than silently generating invalid interface.

However, if we try to use named parameters, more problems surface:

insert into test (email, expire_at)
values (@email, unixepoch(current_timestamp) + @expire_in_secs) ;

Now the generated generated interface uses named fields (even if the type is almost always any) but the way it binds parameters is not correct:

export const insertTestQuery = `-- name: InsertTest :exec
insert into test (email, expire_at)
values (?1, unixepoch(current_timestamp) + ?2)`;

export interface InsertTestArgs {
    email: any; 
    expireInSecs: any | null;
}

export async function insertTest(database: Database, args: InsertTestArgs): Promise<void> {
    const stmt = database.prepare(insertTestQuery);
    await stmt.run(args.email, args.expireInSecs);
}

This fails with error:

Uncaught RangeError: Too many parameter values were provided

Because for named parameters better-sqlite3 expects an object of bindings. So the above should be:

export async function insertTest(database: Database, args: InsertTestArgs): Promise<void> {
    const stmt = database.prepare(insertTestQuery);
    await stmt.run({ 1: args.email, 2: args.expireInSecs });
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions