Socket
Socket
Sign inDemoInstall

sqlite-js-wrapper

Package Overview
Dependencies
0
Maintainers
1
Versions
5
Alerts
File Explorer

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

sqlite-js-wrapper

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


Version published
Maintainers
1
Weekly downloads
2
increased by100%

Weekly downloads

Readme

Source

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

BrowserMobileNodeJS
Any js library supports ES6 module (React, VueJS...)React Native, Cordova (JS based others)Try and inform me please :)

Why i need this tiny library

I need to use sqlite in a project and i did some research. I found some libraries, for example websqlite and TypeORM.

TypeORM has model support and more complex than i need (at least for my project). Also force you to use typescript. Websqlite also is a good library but i want to query sqlite database and get result format in a different way (Promises and result as object). At the same time i want the library to be handy and easy to read. That's why i decided to implement one.

This is a fast imlementation took one workday. So if you commit bugs or issues i will be grateful

Hope you enjoy it!

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

FeatureDescription
queryExecutes single raw query
queryMultiExecutes multiple raw query
sqlBatchExecutes multiple raw query using plugin-specific API calls support
insertInsert single object or array of objects to the table given
createTableCreates table using column argument and returns true or throws error
dropTableDrops table if exists given as first argument and returns true or throws error
isTableExistsCheck if a table exists or not
tableThe magic! Takes table name as an argument then let you to chain with other functions such as (select, delete, update, where, whereIn, whereRaw, whereBetween, distinct, join, orderBy, groupBy, having)

Chaining functions

FunctionDescription
selectReturn the data from the query builder.

Arguments: fields (string default is '*'. It takes comma separated string), limit (int), offset (int)

deleteDeletes the records.

Arguments: limit (int), offset (int)

updateUpdates the matching records.

Arguments: data (object), limit (int), offset (int)

whereAdds where condition to the query builder.

Arguments: field (string), value, operator (string default is '=' example: =,<,>, like ...), andOr (string default is 'AND')

whereInAdds where condition to filter the records that matches with the array given.

Arguments: field (string), valueArray, andOr (string default is 'AND')

whereBetweenAdds where condition to filter the records that between the array given.

Arguments: field (string), valueArray (two item only), andOr (string default is 'AND')

whereRawAdds raw where condition to write complex where clause.

Arguments: condition (string), andOr (string default is 'AND')

distinctRemoves duplicates from result set.

Takes no argument

joinJoins the table to another. This is a little more complex. See the examples below

Arguments: joinTable (string), joinTableAlias (string), joinCallback (function (j){}), joinType (string default is 'INNER')

orderByAdds order by clause to the query. It can be used multiple times

Arguments: field (string), type (string default is 'ASC' values are ASC, DESC)

groupByGroups the query using the array given.

Arguments: groupByArray (array of string)

havingAdds raw having clause to a grouped query.

Arguments: havingStr (string)

existsCheck if records exists or not

createTable->columns supported properties

keyvaluerequired
columnNamestring
dataTypestring (null, integer, real, text, blob)
primaryKeyboolean
autoIncrementboolean
notNullboolean
uniqueboolean
defaultstring
optionstring (extra attribs such as "CHECK" when needed )

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 ☕️ :)

Keywords

FAQs

Last updated on 05 Aug 2019

Did you know?

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc