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
Browser | Mobile | NodeJS |
---|
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
Feature | Description |
---|
query | Executes single raw query |
queryMulti | Executes multiple raw query |
sqlBatch | Executes multiple raw query using plugin-specific API calls support |
insert | Insert single object or array of objects to the table given |
createTable | Creates table using column argument and returns true or throws error |
dropTable | Drops table if exists given as first argument and returns true or throws error |
isTableExists | Check if a table exists or not |
table | The 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
Function | Description |
---|
select | Return the data from the query builder. Arguments: fields (string default is '*'. It takes comma separated string), limit (int), offset (int) |
delete | Deletes the records. Arguments: limit (int), offset (int) |
update | Updates the matching records. Arguments: data (object), limit (int), offset (int) |
where | Adds where condition to the query builder. Arguments: field (string), value, operator (string default is '=' example: =,<,>, like ...), andOr (string default is 'AND') |
whereIn | Adds where condition to filter the records that matches with the array given. Arguments: field (string), valueArray, andOr (string default is 'AND') |
whereBetween | Adds where condition to filter the records that between the array given. Arguments: field (string), valueArray (two item only), andOr (string default is 'AND') |
whereRaw | Adds raw where condition to write complex where clause. Arguments: condition (string), andOr (string default is 'AND') |
distinct | Removes duplicates from result set. Takes no argument |
join | Joins 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') |
orderBy | Adds order by clause to the query. It can be used multiple times Arguments: field (string), type (string default is 'ASC' values are ASC, DESC) |
groupBy | Groups the query using the array given. Arguments: groupByArray (array of string) |
having | Adds raw having clause to a grouped query. Arguments: havingStr (string) |
exists | Check if records exists or not |
createTable->columns supported properties
key | value | required |
---|
columnName | string | ✅ |
dataType | string (null, integer, real, text, blob) | |
primaryKey | boolean | |
autoIncrement | boolean | |
notNull | boolean | |
unique | boolean | |
default | string | |
option | string (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,
},
]);
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,
},
]);
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);
sw.insert('score', scores);
Insert single record and get insertId
const { insertId } = sw.insert('score', { game: 9, userId: 1, score: 4 });
Update
const rowsAffected = sw
.table('user')
.where('team', 'gala')
.update({ team: 'galatasaray' });
Delete records id between 4, 6
const rowsDeleted = sw
.table('user')
.whereBetween('id', [4, 6])
.delete();
Select all records from user table
sw.table('user').select()
Select team names and remove duplicates
const teams = sw
.table('user')
.distinct()
.select('team');
const teamArray = teams.data.map(x => x.team)
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');
Processing results
There are 2 ways to get the results from functions
- First is using await/async
const records = await sw.table('user').select();
sw.table('score')
.select()
.then(result => console.log(result));
Error handling
try {
await sw.table('tableNotExists').select();
} catch (err) {
console.log(err);
}
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 ☕️ :)