More detailed sqlite syntax error messages

[djsavvy]

What is the problem this feature would solve?

Currently, if a query string has a syntax error, it's quite hard to tell what the syntax error actually is.

Consider the following repro script:

import { Database } from 'bun:sqlite';
const db = Database.open(':memory:');
db.run('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE,)');

(The error is the extra comma at the end).

This is the output of bun sqlite_error_repro.tsx:

1 | import { Database } from 'bun:sqlite';
2 | const db = Database.open(':memory:');
3 | db.run('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE,)');
    ^
SQLiteError: near ")": syntax error
 errno: 1

      at run (bun:sqlite:185:11)
      at /Users/savvy/src/XXX/server/sqlite_error_repro.tsx:3:1

Seems harmless enough, but I ran into this error with a much longer, multi-line query, and I had no idea where the syntax error was. In fact, since it was my first time using Bun with sqlite (or at all), I didn't even know where to look. I assumed it was a typescript syntax error and spent a while trying random things.

In contrast, consider what sqlite3 prints when presented with the same line:

SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE,);
Parse error: near ")": syntax error
  E users (id INTEGER PRIMARY KEY, name TEXT UNIQUE,);
                                      error here ---^
sqlite> ^D

What is the feature you are proposing to solve the problem?

More detailed error messages for syntax errors. I see that there is prior art for this w.r.t. runtime errors (#871, closed by #7906). Having the same feature ported over to sqlite syntax errors would be a big QoL improvement.

What alternatives have you considered?

No response

[alabhyajindal]

I'm also facing this issue right now. I was following the guide:

const db = new Database('apps.sqlite')
const query = db.query(`create table foo;`)

I get the error:

6 | const query = db.query(`create table foo;`)
                  ^
SQLiteError: near ";": syntax error
 errno: 1

      at prepare (bun:sqlite:249:19)
      at query (bun:sqlite:256:15)
      at /home/alabhya/Desktop/quickstart/index.ts:6:15

I can't figure out what I'm doing wrong based on the error message.

[Jarred-Sumner]

We store the byteOffset in the SQLiteError object, so all we need to do is add some more text