discord.js - Imagine an app•2w ago

Store Snowflakes as SQLite integer?

Problem I have a large number of discord snowflakes to store in SQLite via Sequelize. I'd like to store them as 8-byte integers rather than 18-byte strings, but I'm running up against https://github.com/sequelize/sequelize/pull/17154. Attempted Solution Defining the following mapping results in precision loss. It seems that sequelize does a lossy conversion to a JS number before my mapping is used.

{
    type: DataTypes.BIGINT,
    get() {
        const val = this.getDataValue(colName); // number, not bigint :(
        return val ? val.toString() : null;
    },
    set(val) {
        const bigint = BigInt(val)
        this.setDataValue(colName, bigint);
    }
}

{
    type: DataTypes.BIGINT,
    get() {
        const val = this.getDataValue(colName); // number, not bigint :(
        return val ? val.toString() : null;
    },
    set(val) {
        const bigint = BigInt(val)
        this.setDataValue(colName, bigint);
    }
}

SQLite Dialect Mappings? The following line from https://sequelize.org/docs/v6/other-topics/extending-data-types/ seems relevant:

After creating this new datatype, you need to map this datatype in each database dialect and make some adjustments.

However, it only shows postgres mappings--no SQLite. Both Claude Code and I are at a loss to figure out correct SQLite mappings. Rejected Alternatives I don't want to run a separate database service for this. I don't want to store the values as opaque BLOBs that I cannot inspect in the DB. Context https://github.com/snoggles/loggles/blob/main/src/db/snowflake.js (currently using strings, sadly) Guidance Request Storing discord snowflakes in SQLite seems like it should be a common task. Are there existing examples of storing them as SQLite numbers--or at least using sequelize to create SQLite dialect mappings?

7 Replies

d.js toolkit•2w ago

Amgelo•2w ago

1. what does that have to do with djs? 2. why a bigint and not a string? you're not going to do any math operations with it, at most maybe get the timestamp and you could store that in the db to avoid computation if it's really that expensive for your case

SnogglesOP•2w ago

Thanks for the response. 1. Discord.js recommends Sequelize and discord snowflakes are a common thing to store. I know it's more of a Sequelize question, but the Slack link on the Sequelize website is dead, so I figured I try here first, since I doubt I'm the first to encounter this after following the discord.js guide. 2. String is more than twice the storage space as Integer. My discord.js bot is designed to store things, so I'll have a lot of these--and limited disk space. Computation is a non-issue. I'd even be happy to have the ORM pull back ALL numbers as BigInt if that's an easy path forward.

Inky•2w ago

You could just make a function in SQLite that converts the blob to a string so that you can fetch it as a readable string via sql query

SnogglesOP•2w ago

Yes, that'd be a workaround for a workaround, but rather than creating a pile of workarounds, I was hoping to more simply store the integer as an integer since the docs seem to suggest that this should be possible for the ORM.

Inky•2w ago

It’s an ORM problem I don’t think I’ve seen an ORM that gets BigInts properly (w/o treating all ints as a BigInt)

SnogglesOP•2w ago

treating all ints as a BigInt would be acceptable My understanding is that Sequelize has no problem mapping to BigInt from other DB engines that specify integer precision in the schema, but SQLite only has a variable width integer type, so Sequelize needs some hinting via dialect specific mappings. Surely someone has written one before...

Gaming

Programming

Store Snowflakes as SQLite integer?

Did you find this page helpful?