Incorrect INSERT statement execution with @libsql/[email protected]

[y12studio]

When using @libsql/[email protected] in a Node.js v22.9 environment, an INSERT statement within a batch operation throws a SQLITE_ERROR: table users has 2 columns but 1 values were supplied error, despite the table definition clearly requiring two columns (id and name), and the AUTOINCREMENT attribute on the id column. It appears the client isn't correctly handling the auto-incrementing primary key and expects a value for the id column even though it should be automatically generated.

To Reproduce

1. Create a new Node.js project and install the @libsql/client package:

   npm init -y
   npm install npm:@libsql/[email protected]

2. Create a file named main.ts with the following code:

   import { createClient } from "@libsql/client/node";
   
   const client = createClient({
       url: "file:local.db",
   });
   
   await client.batch(
       [
           "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
           {
               sql: "INSERT INTO users VALUES (?)",
               args: ["Iku"],
           },
       ],
       "write",
   );
   
   const result = await client.execute("SELECT * FROM users");
   console.log("result:", result);
   console.log("Users:", result.rows);

3. Run the script:

   node --experimental-strip-types main.ts 

Expected behavior

The INSERT statement should execute successfully, automatically generating an id value, and the SELECT statement should return the inserted row with both id and name populated.

Actual behavior

The script throws the following error:

LibsqlError: SQLITE_ERROR: table users has 2 columns but 1 values were supplied

Environment:

• OS: (e.g., Linux, macOS, Windows) - Output suggests Linux due to the docker container used.
• Node.js version: 22.9.0
• @libsql/client version: 0.14.0

test.sh

#!/bin/bash
docker run --rm -i node:22.9 sh <<\EOF
date
env
mkdir myapp
cd myapp
npm init -y
npm install npm:@libsql/[email protected]
cat <<EOOF > main.ts
import { createClient } from "@libsql/client";
const client = createClient({
    url: "file:local.db",
});
await client.batch(
    [
        "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
        {
            sql: "INSERT INTO users VALUES (?)",
            args: ["Iku"],
        },
    ],
    "write",
);
const result = await client.execute("SELECT * FROM users");
console.log("result:", result);
console.log("Users:", result.rows);
EOOF
echo
echo "==> test 1"
echo
node --experimental-strip-types main.ts
EOF

output

Wed Oct 23 02:49:14 UTC 2024
NODE_VERSION=22.9.0
HOSTNAME=95203c38f566
YARN_VERSION=1.22.22
HOME=/root
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
PWD=/
Wrote to /myapp/package.json:

{
  "name": "myapp",
  "version": "1.0.0",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": ""
}


added 22 packages, and audited 23 packages in 3s

3 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

==> test 1

(node:34) ExperimentalWarning: Type Stripping is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
(node:34) [MODULE_TYPELESS_PACKAGE_JSON] Warning: Module type of file:///myapp/main.ts is not specified and it doesn't parse as CommonJS.
Reparsing as ES module because module syntax was detected. This incurs a performance overhead.
To eliminate this warning, add "type": "module" to /myapp/package.json.
file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:372
        return new LibsqlError(e.message, e.code, e.rawCode, e);
               ^

LibsqlError: SQLITE_ERROR: table users has 2 columns but 1 values were supplied
    at mapSqliteError (file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:372:16)
    at executeStmt (file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:277:15)
    at file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:90:24
    ... 4 lines matching cause stack trace ...
    at async onImport.tracePromise.__proto__ (node:internal/modules/esm/loader:483:26) {
  code: 'SQLITE_ERROR',
  rawCode: 1,
  [cause]: SqliteError: table users has 2 columns but 1 values were supplied
      at convertError (/myapp/node_modules/libsql/index.js:51:12)
      at Database.prepare (/myapp/node_modules/libsql/index.js:119:13)
      at executeStmt (file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:248:28)
      at file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:90:24
      at Array.map (<anonymous>)
      at Sqlite3Client.batch (file:///myapp/node_modules/@libsql/client/lib-esm/sqlite3.js:86:38)
      at file:///myapp/main.ts:5:14
      at ModuleJob.run (node:internal/modules/esm/module_job:262:25)
      at async onImport.tracePromise.__proto__ (node:internal/modules/esm/loader:483:26) {
    code: 'SQLITE_ERROR',
    rawCode: 1
  }
}

Node.js v22.9.0

Additional context

The issue seems to be related to the interaction between the batch operation, the AUTOINCREMENT attribute, and potentially the way the client handles parameterized queries in this context. Explicitly providing a null value for the id column in the INSERT statement might be a workaround, but it shouldn't be necessary given the AUTOINCREMENT specification.

workaround.sh

#!/bin/bash
docker run --rm -i node:22.9 sh <<\EOF
date
env
mkdir myapp
cd myapp
npm init -y
npm install npm:@libsql/[email protected]
cat <<EOOF > main.ts
import { createClient } from "@libsql/client";
const client = createClient({
    url: "file:local.db",
});
await client.batch(
    [
        "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
        {
            sql: "INSERT INTO users VALUES (?,?)",
            args: [null, "Iku"],
        },
    ],
    "write",
);
const result = await client.execute("SELECT * FROM users");
console.log("result:", result);
console.log("Users:", result.rows);
EOOF
echo
echo "==> test 1"
echo
node --experimental-strip-types main.ts
EOF