blog/sqlite.md at main · andrewitsover/blog · GitHub
//blob/show" data-turbo-transient="true" />
Skip to content
Search or jump to...
Search code, repositories, users, issues, pull requests...
-->
Search
Clear
Search syntax tips
Provide feedback
--><br>We read every piece of feedback, and take your input very seriously.
Include my email address so I can be contacted
Cancel
Submit feedback
Saved searches
Use saved searches to filter your results more quickly
-->
Name
Query
To see all available qualifiers, see our documentation.
Cancel
Create saved search
Sign in
//blob/show;ref_cta:Sign up;ref_loc:header logged out"}"<br>Sign up
Appearance settings
Resetting focus
You signed in with another tab or window. Reload to refresh your session.<br>You signed out in another tab or window. Reload to refresh your session.<br>You switched accounts on another tab or window. Reload to refresh your session.
Dismiss alert
{{ message }}
andrewitsover
blog
Public
Notifications<br>You must be signed in to change notification settings
Fork
Star
FilesExpand file tree
main
/sqlite.md
Copy path
Blame<br>More file actions
Blame<br>More file actions
Latest commit
History<br>History<br>History
72 lines (46 loc) · 2.47 KB
main
/sqlite.md
Top
File metadata and controls<br>Preview
Code
Blame
72 lines (46 loc) · 2.47 KB
Raw<br>Copy raw file<br>Download raw file
Edit and raw actions
Bun and NodeJS return different results for the same SQLite query
Until the other day, I didn't even think this was possible. The same language, the same database, and the same query returning different results.
Take this SQL:
select 3 / 2;
The result turns out to be 1 because SQLite and most other databases treat this as integer arithmetic. Both bun and node return the result as 1 also:
Here is the node version:
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');<br>const statement = db.prepare('select 3 / 2 as result');<br>const result = statement.get();
console.log(result); // { result: 1 }
Now, let's pass the 2 in as a parameter instead using node:
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');<br>const statement = db.prepare('select 3 / ? as result');<br>const result = statement.get(2);
console.log(result); // { result: 1.5 }
The result has changed to 1.5.
Let's try it with bun:
import { Database } from 'bun:sqlite';
const db = new Database(':memory:');<br>const statement = db.prepare('select 3 / ? as result');<br>const result = statement.get(2);
console.log(result); // { result: 1 }
The same query, the same database, and the same language, but different results.
Why is this the case? It turns out that node chooses to bind all number parameters using sqlite3_bind_double regardless of whether they are mathemetical integers or not. Bun, on the other hand, decides to use sqlite3_bind_int or the 64-bit version of this if the value is actually an integer.
Who is right? One argument from the node camp is that
JavaScript doesn't have an integer type so all numbers should be bound to statements as doubles
Everyone knows this right? All the smart people do at least. JavaScript doesn't have an integer type! It is true, but does JavaScript have the concept of integers? This is a different question. The answer is yes.
const shift = 5 1;
What is 5? It is very specifically an integer in this context, so the debate cannot end there. When you ask somebody what the 2 is in this code, what will they say?
const result = statement.get(2);
"It is a number". "What kind of number?", you ask. "An integer".
It still seems entirely up for debate as to what the correct approach is. The fact that taking a number out of the statement and turning it into a parameter results in a different outcome is maybe the best case for binding integers as integers.
You can’t perform that action at this time.