sqlite bigint returns are truncated

[tantaman]

Postgres connections have an option to convert bigints to strings (bigIntMode):
https://www.atdatabases.org/docs/pg-options

SQLite needs the same -- otherwise 64bit integers are getting truncated.

[tantaman]

Looks like a deeper issue -- TryGhost/node-sqlite3#922

[tantaman]

better-sqlite3 fixes this -- https://www.npmjs.com/package/better-sqlite3/v/1.3.4

64-bit integer support (invisible until you need it)

but unclear what that swap might break 🤣

[tantaman]

There's a fork of the sqlite3 package that only coerces integers if the exceed max_safe_int.

This is probably a more acceptable fix: juanrgm/node-sqlite3@e25579c

better-sqlite3 converts all things to bigint when enabling 64bit int support (no matter what size the int) which would break pretty much everything.

[ForbesLindesay]

My preference here would be to support a few modes if possible, but it depends how difficult it is to implement. For Postgres, we support bigIntMode: 'string' | 'number' | 'bigint'. This means you can choose when connecting whether you want:

1. Strings for BigInts. This gives you simple compatibility with JSON and older libraries & environments, but it's not a number so unexpected things can happen if you try to do arithmetic on these values.
2. Number for BigInts. This is super easy to use as long as your numbers are smaller than Number.MAX_SAFE_INTEGER. Above that it will lose precision, but remember that Number.MAX_SAFE_INTEGER is much higher than the max value you can store in a column of type INT in the database, so this can still be a useful mode if you are confident your numbers will be smaller than 2^52 but they might be larger than 2^32.
3. JS BigInt for BigInts. This probably makes the most sense in the long term, but it's only recently become widely supported, and it can require extra care when serialising/deserialising to JSON.

My preference would be to support 3 modes, and default to using JS' BigInt type. We can release this as a breaking change.

[hatemjaber]

Can BIGINT values be treated as strings when sending back and forth and when you have database operations the value can be cast to the right type? I'm commenting without any familiarity with the existing code base.

[tantaman]

Yes, you can cast to/from string on the way in and out of the database.

INSERT INTO x VALUES (CAST(? AS BIGINT))
or
SELECT CAST(a as TEXT) FROM x

[tantaman]

and on the receiving end, so that less than works correctly, cast back to BigInt

E.g.,

const rows = db.query(SELECT CAST(a as TEXT) FROM x).map(r => BigInt(r[0])

If you keep it as text then "10" < "2"

[hatemjaber]

I think most of the users will be working with Javascript and will need to keep the BIGINT as a string since JS can only handle to the power of 53 or something along those lines.

[ForbesLindesay]

Update on this issue:

We now have a second SQLite library available that uses a different underlying implementation. For now I think this will still return BIGINTs as number in JavaScript, but it should be relatively easy now for someone to open a PR to add better support for returning them as BigInts in JavaScript. See https://github.com/WiseLibs/better-sqlite3/blob/2194095aa1183e9c21d28eafadeac0d4d4d42625/docs/integer.md for the documentation of the underlying library and https://www.atdatabases.org/docs/sqlite-sync is the documentation for our new @databases/sqlite-sync library.

@hatemjaber Using string for BIGINTs is often the best approach. Especially when those BigInts are just being used as IDs. There are however two cases where you might choose a different approach:

1. If you're using them to store really big numeric values, you might prefer to have them represented using the (relatively new) BigInt data type in JavaScript, which can store the full sized BIGINTs from the database without loss of precision, and still supports mathematical operations.
2. If you want to be able to handle integers that are way bigger than those you can store in an INT, but you know you don't need to be able to handle integers bigger than 2^53, you may want to use the JavaScript number type. The maximum value you can safely store in a JavaScript number is much bigger than the maximum value you can store in an INT, so this is surprisingly useful.

This is why, where possible, we make this a configuration option for @databases

[hatemjaber]

@ForbesLindesay thank you for sharing that. The BigInt data type in JavaScript is new to me. I totally agree with you on the usage cases. I will definitely give this a closer look. Thank you for sharing!