Sqlite3-simple NPM

Information

This package is used to make a database in sqlite3 much easier

If you know sqlite3 and you want to simplify some functions this package also works, below it is explained how to directly access the sqlite3 database

Change Log

Version: 0.6.0

Added the Table class with its 6 functions
Now the function createTable() will return an Table class
New functions moveData() and getTables()
The system was modified to handle errors more efficiently
The function insertData() now the data entered can be an array
Now it is imported differently
The package was translated into English (with google translator)

Version: 0.4.0

Changelog added
New function existsTable()
Now the conditions can be more than one
Fixed some errors
More examples were added
New way of handling errors

Example of use

const { Database, Table } = require ("sqlite3-simple");
const db = new Database("./Base.db");

/*
A file will be created next to the one containing this line, creating the file "base.db"
You can put the route you want, like "../folder1/dbs/base.db". Always with the ending ".db"
*/

db.createTable("economy", {id: "123215123", coins: 7000}).then(async (table) => {
     var data = await table.getData({id: "123215123"});
    
     if(data){
        console.log(data);
        if (data[0].coins < 10000) {
            table.updateData({id: "123215123"}, {id: "123215123", coins: data[0].coins + 500});
        }else{
            console.log (`${data[0].id} reached 10,000 coins`);
        }
     }else{
        table.insertData({id: "123215123", coins: 7000});
     }
});

Below are more examples of use

Classes

Database(path)
Path: is the path to the file with the db, example: ../folder1/dbs/base.db
Table(name, Database)
Name: is the name of the table
Database: has to be a Database class
Notes: the table has to be created, this does not create it

Functions

Functions of the Database class
- createTable(table, sample)
  Returns: Promise( Table )
- deleteTable(table)
  Returns: Promise
- getTables()
  Returns: Promise
  Notes: the array it returns contains all the tables in the database
- moveData(oldTable, newTable)
  Notes: used to move data between tables, but the columns have to have the same names (in the case of {id:" 123215123 ", coins: 7000} the columns are id andcoins), and the new column/s will remain with value null
  Definitions
  table: would be the name of the table, for example: economy (Old_table and New_table are also a table)
  sample: an object with the data of "sample", for example {id:" 123215123 ", coins: 7000}
  This is due to the structure of the sqlite databases, this "sample" serves to define that structure once the table is created, because then you cannot insert a number where you put a string.
  If you try to insert {id: 123215123, coins: true} after creating the table with the sample {id: "123215123", coins: 7000} the id pass it to string and that true convert it to a 1 as if they had inserted {id: "123215123", coins: 1}
- insertData(table, data)
  Returns: Promise

- getData(table, condition)  
    `Returns:` promise( array | boolean )

    Example of how the promise return the array:  
    ```js
    [{id: '123215123', coins: 7500}, {id: '484321883', coins: 8400}]
    ```

    `Notes:` if there not any data with that condition, its returns `false`


- updateData(table, condition, data)  
    `Returns:` promise

- insert0UpdateData(table, condition, data)  
    `Returns:` promise(boolean)

    `Notes:` if the data already exists (they comply with the `condition`) it updates it by the new ones, and if they do not exist, it inserts them.  
    And returns:   `false` if it insert and `true` if it update


- deleteData(table, condition)  
    `Returns:` Promise

    ### Definitions
    
    `table:` name of the table on which it will work
    
    `data:` an object or array with the data to be put, for example `{id:" 123215123 ", coins: 7000}` or `[{id: "123215123", coins: 7000}, {id:" 113476821 " , coins: 8000}]` the array can only be used when data is inserted, not when updating
    
    `condition:` an object (or boolean) that tells the program what to search the database, for example: `{id: "123215123"}`. It will be searched in the
    database all "rows" where this "condition" is met, that the id is       "123215123". Another example: `{id: "123215123", coins: 7000}` The condition would be for the id to be "123215123" and it have 7000 coins
    
    `Notes:` if you set the `condition` as `true`: `getData(table, true)` in any of the functions with `condition` it will delete/obtain/edit, all data in the table (except the `getData()` function)
    (the `insert0UpdateData` function will only do it when it is updating)

Functions of the Table class
- insertData(data) Returns: Promise
- getData(condition) Returns: Promise( data | boolean )
  Example of how it return the data:
```
[{id: '123215123', coins: 7500}, {id: '484321883', coins: 8400}]
```
  Notes: if it cannot find any data with that condition, its return false

- updateData(condition, data)  
    `Returns:` Promise


- insert0UpdateData (condition, data)  
    `Returns:` Promise(boolean)  
    `false` if it insert and` true` if it update

    `Notes:` if the data already exists (they comply with the `condition`) it updates them by the new ones, and if they do not exist, it inserts them


- deleteData(condition)  
    `Returns:` Promise

- delete()  
    `Returns:` Promise

    `Notes:` deletes the table

    ### Definitions

    `data:` an object or array with the data to be put, for example `{id:" 123215123 ", coins: 7000}` or `[{id: "123215123", coins: 7000}, {id:" 113476821 " , coins: 8000}]` the array can only be used when data is inserted, not when updating
    
    `condition:` an object (or boolean) that tells the program what to search the database, for example: `{id: "123215123"}`. It will be searched in the database all "rows" where this "condition" is met, that the id is "123215123". Another example: `{id: "123215123", coins: 7000}` The condition would be for the id to be "123215123" and it have 7000 coins
    
    `Notes:` if you set the `condition` as `true`: `getData(table, true)` in any of the functions with `condition` it will delete/obtain/edit, all data in the table (except the `getData()` function)
    (the `insert0UpdateData` function will only do it when it is updating)

Other examples of use

Here is an example of cooldown quite useful, to an api, for example

const { Database } = require("sqlite3-simple");
const db = new Database("./base.db");

db.createTable("cooldown", { ip: "string", ends: (Date.now() + 10000) });

async function has_cooldown(ip_object) {
    var data = await db.getData("cooldown", ip_object);
    if(data){
        if(data[0].ends > Date.now()){
            return true; //has cooldown
        }else{
            return false; //doesn't have cooldown
        }
    }else{
        return false; //doesn't have cooldown
    }
}


var ips_samples = [
    {ip: "176.178.197.128"},
    {ip: "204.8.234.130"},
    {ip: "120.99.71.11"}
];

var index = 0;
setInterval(async () => {
    if(await has_cooldown(ips_samples[index])){
        console.log(`1 ${ips_samples[index].ip} has cooldown`);
    }else{
        console.log(`2 ${ips_samples[index].ip} hasn't cooldown`);

        db.insertData("cooldown", {ip: ips_samples[index].ip, ends: (Date.now() + 10000)}).then(() => {

            console.log(`3 now ${ips_samples[index].ip} has cooldown`);
            
            setTimeout(() => {
                db.deleteData("cooldown", {ip: ips_samples[index].ip});
            }, Math.random() * 15000);

        });
    }
    

    (index === (ips_samples.length - 1))?index = 0:index++;
}, Math.random() * 5000)

Another example:

const { Database, Table } = require("sqlite3-simple");
const db = new Database("./Base.db");

db.getTables().then(async tables => {
    console.log(tables);
    
    await db.createTable("economy", {id: "some string", coins: 1});

    await db.getTables().then(console.log);

    var table = new Table("economy", db);
    await table.insertData([
        {id: "12325123", coins: 7000}, 
        {id: "14788436", coins: 7000}, 
        {id: "75183149", coins: 7000}
    ]);
    await table.getData(true).then(console.log);

    await table.delete();

    await db.getTables().then(console.log);
});

And here something for those who already know sqlite3 or for some other reason, if you want to directly access the db of sqlite use db.sqlite3db
Here is an example:

const { Database } = require("sqlite3-simple");
const db = new Database("./Base.db");

db.sqlite3db.all(`SELECT * FROM economy`, (err, rows) => {
    if(err) throw err;
    console.log(rows);
});

sqlite database simple facil base datos

5 years ago

5 years ago

5 years ago

5 years ago

5 years ago