Sqlite Node module outputs wrong column

Question

I am making a simple Discord bot with Node and I'm having an issue with Sqlite module. My sqlite database definition looks like this:

create table rooms
(
    id integer not null
        constraint rooms_pk
            primary key autoincrement,
    channel_snowflake integer not null,
    owner_snowflake integer not null
);

create unique index rooms_channel_snowflake_uindex
    on rooms (channel_snowflake);

create unique index rooms_id_uindex
    on rooms (id);

create table room_users
(
    room_id integer not null
        constraint room_users_rooms_id_fk
            references rooms
            on update restrict on delete cascade,
    user_snowflake integer not null
);

And I have the following data in my database:

I need to execute this query to get IDs of the channel owner and its members:

select owner_snowflake, room_users.user_snowflake
from rooms
         left join room_users on rooms.id = room_users.room_id
where channel_snowflake = '702042586092667000'

When I execute it in the database, I get the following (corrrect) result:

But when I try to execute the same query in Node, I get different results:
Here is my code:

rows = await db.all(`select owner_snowflake, room_users.user_snowflake
                     from rooms
                       left join room_users on rooms.id = room_users.room_id
                     where channel_snowflake = '702042586092667000'`);
console.log(rows);

And the results I get are as follows:

[
  { owner_snowflake: 255371734771826700, user_snowflake: 1 },
  { owner_snowflake: 255371734771826700, user_snowflake: 423 },
  { owner_snowflake: 255371734771826700, user_snowflake: 5675 }
]

As you can see, the results are different and Sqlite module outputs channel_snowflake instead of owner_snowflake.
Am I missing something? How can I fix this?

Thank you!

Changing a row in the database seems to have fixed the issue. Is it somehow connected with Sqlite caching data? — Mikhail Stefantsev, Apr 21 '20 at 08:45
It's probably related to those numbers being too large to represent exactly in javascript. -- Indeed, entering 255371734771826688 into a javascript console gives back 255371734771826700. — Shawn, Apr 21 '20 at 11:55

score 0 · Answer 1 · answered Apr 21 '20 at 12:08

0

Apparently, the issue is that these snowflake IDs are too big for JavaScript to be stored without losing precision. (See this post for more info.)

Changing the type of the IDs to "text" solves the issue

answered Apr 21 '20 at 12:08

Mikhail Stefantsev

59
1
6

Sqlite Node module outputs wrong column

1 Answers1