sqlite-js-wrapper v1.3.0

sqlite-js-wrapper

Tiny SQLite helper library built on top of HTML5/Web SQL (DRAFT) API.

This library helps you to build sqlite query in an eloquent way.

Supported platforms

Why i need this tiny library

Installation

Using npm:

npm install --save sqlite-js-wrapper

Using yarn

yarn add sqlite-js-wrapper

Features

It supports various sqlite syntax. Here is the feature list

Chaining functions

createTable->columns supported properties

Examples

First create database in usual way

const db = SQLite.openDatabase(
    { name: 'test.db', location: 'default' },
    succ => console.log('DB Created: '),
    err => console.log('Err:', err)
  );

Init wrapper using database object:

const sw = new SQLiteWrapper(db);

Drop old tables if exists

sw.dropTable('user');
sw.dropTable('score');

Create user and score tables

sw.createTable('user', [
{
  columnName: 'id',
  dataType: 'integer',
  primaryKey: true,
  autoIncrement: true,
},
{
  columnName: 'name',
  dataType: 'text',
  notNull: true,
  unique: true,
},
{
  columnName: 'team',
  dataType: 'text',
  default: 'gala',
  notNull: true,
},
]);

// Result is: true

sw.createTable('score', [
{
  columnName: 'id',
  dataType: 'integer',
  primaryKey: true,
  autoIncrement: true,
},
{
  columnName: 'game',
  dataType: 'integer',
  notNull: true,
},
{
  columnName: 'userId',
  dataType: 'integer',
  notNull: true,
},
{
  columnName: 'score',
  dataType: 'integer',
  notNull: true,
},
]);

// Result is: true

Sample data

const users = [
    { id: 1, name: 'user1' },
    { id: 2, name: 'user2', team: 'madrid' },
    { id: 3, name: 'user3', team: 'barca' },
    { id: 4, name: 'user4', team: 'arsenal' },
    { id: 5, name: 'user5', team: 'barca' },
    { id: 6, name: 'user6', team: 'gala' },
];

const scores = [
    { game: 1, userId: 1, score: 5 },
    { game: 2, userId: 2, score: 2 },
    { game: 3, userId: 3, score: 4 },
    { game: 4, userId: 1, score: 8 },
    { game: 1, userId: 2, score: 3 },
    { game: 2, userId: 4, score: 1 },
    { game: 3, userId: 2, score: 2 },
    { game: 4, userId: 3, score: 4 },
    { game: 5, userId: 1, score: 3 },
    { game: 5, userId: 2, score: 1 },
    { game: 6, userId: 3, score: 5 },
    { game: 6, userId: 4, score: 2 },
    { game: 7, userId: 3, score: 2 },
    { game: 7, userId: 1, score: 1 },
    { game: 8, userId: 2, score: 4 },
    { game: 8, userId: 4, score: 3 },
];

Insert data to tables

sw.insert('user', users);

// Result: true

sw.insert('score', scores);

// Result: true

Insert single record and get insertId

const { insertId } = sw.insert('score', { game: 9, userId: 1, score: 4 });

// Result: InsertId: 17

Update

const rowsAffected = sw
.table('user')
.where('team', 'gala')
.update({ team: 'galatasaray' });

// rowsAffected: 2

Delete records id between 4, 6

const rowsDeleted = sw
.table('user')
.whereBetween('id', [4, 6])
.delete();

// rowsDeleted: 3

Select all records from user table

sw.table('user').select()

/*
  Result:
  {
     "data":[
        {
           "team":"galatasaray",
           "name":"user1",
           "id":1
        },
        {
           "team":"madrid",
           "name":"user2",
           "id":2
        },
        {
           "team":"barca",
           "name":"user3",
           "id":3
        },
        {
           "team":"arsenal",
           "name":"user4",
           "id":4
        },
        {
           "team":"barca",
           "name":"user5",
           "id":5
        },
        {
           "team":"galatasaray",
           "name":"user6",
           "id":6
        }
     ],
     "rowsAffected":0,
     "length":6
  }
 */

Select team names and remove duplicates

const teams = sw
  .table('user')
  .distinct()
  .select('team');
const teamArray = teams.data.map(x => x.team)

// Result: ["galatasaray", "madrid", "barca", "arsenal"]

Complex query using join, where, groupBy, having, orderBy

This query return the users and total scores with user name sorted descendant by sumOfScore where sums bigger than 12 and team is not equal to arsenal

const maxScoreList = sw
  .table('score', 'S')
  .join('user', 'U', j => {
    j.on('U.id', 'S.userId');
    j.whereIn('U.id', [1, 2, 3, 4, 5]);
  })
  .where('U.team', 'arsenal', '!=')
  .groupBy(['userId'])
  .having('sumOfScore > 12')
  .orderBy('sumOfScore', 'DESC')
  .select('U.name, SUM(S.score) as sumOfScore');

/*
  Result:
  {
     "data":[
        {
           "sumOfScore":21,
           "name":"user1"
        },
        {
           "sumOfScore":15,
           "name":"user3"
        }
     ],
     "rowsAffected":0,
     "length":2
  } 
 */

Processing results

There are 2 ways to get the results from functions

• First is using await/async

const records = await sw.table('user').select();

• Second is using .then()

sw.table('score')
    .select()
    .then(result => console.log(result));

Error handling

• Using await/async

try {
  await sw.table('tableNotExists').select();
} catch (err) {
  console.log(err);
}

• Using .then()

sw.table('tableNotExists')
    .select()
    .then(() => {})
    .catch(err => console.log(err));

Feedback

All bugs, feature requests, feedback, etc., are welcome.

Donation

If this project help you reduce time to develop, you can give me a cup of coffee ☕️ :)