I'm working on a Discord bot using TypeScript and SQLite3 as the database. When querying a Users table with:

SELECT * FROM Users;

I encounter a strange issue: the returned Discord ID has 3 numbers replaced, leading to an incorrect value.

For example:

• Actual Discord ID: 1248626823638552701
• Returned Discord ID: 1248626823638552600

You can see that only the last 3 digits differ.

Here's the schema for my Users table:

CREATE TABLE IF NOT EXISTS Users (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    id TEXT NOT NULL UNIQUE,         -- Discord ID stored as TEXT
    xp INTEGER DEFAULT 0,
    level INTEGER DEFAULT 0,
    balance REAL DEFAULT 0.00,
    market_items TEXT DEFAULT NULL,  -- JSON array of item IDs
    last_level_up TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The id field, which holds the Discord ID, is stored as TEXT to avoid any integer overflow issues, since Discord IDs are large numbers.

Despite storing the correct ID in the database, querying it results in incorrect values with slight differences. The data stored in SQLite is correct, but the issue occurs during retrieval.

• Custom Singleton Class for Query Handling
• Event for Creating Users
• Example of it Being Used

If needed I can provide the bot I am working on.

What I Tried

• Data Type Verification: I confirmed that the id field in the Users table is defined as TEXT to prevent any integer overflow issues due to the large size of Discord IDs.

• Direct Database Inspection: I used a SQLite browser to inspect the database directly and verified that the correct Discord IDs are indeed stored in the table.

• Console Logging: I added console logs in my TypeScript code to output the retrieved IDs immediately after querying the database. The logs show the incorrect IDs with altered last digits.

• Type Casting: I attempted to cast the retrieved IDs to BigInt in TypeScript to handle large integer values without loss of precision.

• Updating SQLite3 Library: I made sure that I'm using the latest version of the sqlite3 library to rule out any bugs related to data retrieval.

• Alternative Query Methods: I tried different query methods, such as using parameterized queries and fetching the data in different formats (e.g., as arrays instead of objects).

What I Expected

I expected that when I query the Users table, the Discord IDs retrieved would exactly match the IDs stored in the database, without any alterations to the digits. Specifically, the retrieved ID should be 1248626823638552701, the same as the one stored, rather than 1248626823638552600.

EDIT

I have gotten it to work, turns out I was not properly casting it as a string. Thanks to:

• https://stackoverflow.com/a/78957916/19972576
• https://stackoverflow.com/a/78959216/19972576

Trying to parse each column as JSON is the cause of this.

const parsed = JSON.parse(row[key as keyof typeof row]);
(row as any)[key] = parsed;

The original query will return row = { id: "1248626823638552701", ... }, but JSON.parse("1248626823638552701") is 1248626823638552600.