Need some clarification on storage format

Which version are you using?

    "@nano-sql/adapter-sqlite": "^2.0.8",
    "@nano-sql/core": "^2.3.7",

Describe the bug

Consider this db setup given:

    await nSQL()
        .createDatabase({
            id: 'temp',
            mode: new SQLite('./temp.bin'),
            // warnOnSlowQuery: true,
            tables: [{
                name: 'posts',
                model: {
                    'slug:string': {pk: true},
                    'published_at:date': {notNull: true},
                    'html:string': {notNull: true},
                },
            }],
        });

I opened the temp.bin DB to look how the data is stored, and I got a table with a name b64b-2c9a and the schema

id TEXT UNIQUE
data TEXT

which is quite ineffective.

Expected behavior My expectation was nSQL will create a table with name posts and all the columns I've described, i.e. the slug PK field with auto unique index. However, the serialized JSON data field was created.

Is this an intended behavior or am I doing something wrong?

Hello Tomiv,

This is expected behavior, there are a few reasons it's setup like this.

First, nanoSQL's database abstraction layer expects a minimal set of features that are consistent across different database backends. This way even a simple key value store can be used as a database adapter in nanoSQL. The downside is advanced features of databases like MySQL & SQLite aren't used because nanoSQL only needs a key value abstraction to work.

nanoSQL is built in such a way that if you're using Local Storage, SQLite, MongoDB, IndexedDB or Redis you get exactly the same features. The only thing that differs is the environment and performance.

Second, there are several things nanoSQL allows that SQLite just doesn't support, for example you can't remove columns from an existing table. Of course we could implement a query that creates a new table that receives a copy of all the rows in the old table on every alter table but this creates new problems... how do we communicate to the end user of the SQLite adapter that alter table commands are expensive the larger their tables get?

If nanoSQL went down this road, you'd end up with a table of supported features and caveats for every database adapter. Some adapters would support some features and others not at all. If you look at query builders like Knex.js this is exactly what you see, this isn't what I wanted to use nanoSQL for at all.

Specifically regarding the database name in SQLite, b64b-2c9a, that's the randomly generated table ID. This is done to make table rename queries easier for nanoSQL to track and prevent conflicts. You can use a show tables query in nanoSQL to get an object containing the table names and their associated ids.

Edit: You can add a simple function to the createDatabase call that will tell nanoSQL not to create the Ids for the tables. If you do this you lose the ability to rename your tables after they're created. https://github.com/ClickSimply/Nano-SQL/issues/117

only-cliches / Nano-SQL

Need some clarification on storage format #179