sqlite-js-wrapper - npm Package Compare versions

Comparing version 1.0.2 to 1.1.0

index.js

@@ -326,19 +326,46 @@ const SQLiteJSWrapper = function(db) {
 
-  this.createTable = (tableName, columns) => {
-    let query = '';
-    for (let i = 0; i < columns.length; i++) {
-      if (i === columns.length - 1) {
-        query += `"${columns[i].name}" ${columns[i].dataType} ${columns[i].isNotNull ? 'NOT NULL ' : ''}${
-          columns[i].options
-          }`;
-      } else {
-        query += `"${columns[i].name}" ${columns[i].dataType} ${columns[i].isNotNull ? 'NOT NULL ' : ''}${
-          columns[i].options
-          },`;
-      }
+  this.createTable = async (tableName, columns, withRowId = false) => {
+    const primaryKeys = columns.filter(x => x.primaryKey).map(x => x.columnName);
+    let colStr = columns
+      .map(col => {
+        const quote = typeof col.default === 'string' ? "'" : '';
+        return [
+          col.columnName,
+          col.dataType || '',
+          col.primaryKey && primaryKeys.length === 1 ? 'PRIMARY KEY' : '',
+          col.autoIncrement && col.primaryKey && primaryKeys.length === 1 ? 'AUTOINCREMENT' : null,
+          col.notNull ? 'NOT NULL' : null,
+          col.unique ? 'UNIQUE' : null,
+          col.default ? `DEFAULT ${quote}${col.default}${quote}` : null,
+          col.option || null,
+        ]
+          .filter(x => x !== null)
+          .join(' ');
+      })
+      .join(', ');
+
+    if (primaryKeys && primaryKeys.length > 1) {
+      colStr += `, PRIMARY KEY (${primaryKeys.join(', ')})`;
     }
-    this.query(`CREATE TABLE IF NOT EXISTS ${tableName} (${query})`);
+
+    return new Promise((resolve, reject) => {
+      this.query(`CREATE TABLE IF NOT EXISTS ${tableName} (${colStr}) ${withRowId ? '[WITHOUT ROWID]' : ''}`)
+        .then(() => resolve(true))
+        .catch(err => {
+          reject(err);
+        });
+    });
   };
+
+  this.dropTable = tableName => {
+    return new Promise((resolve, reject) => {
+      this.query(`DROP TABLE IF EXISTS ${tableName}`)
+        .then(() => resolve(true))
+        .catch(err => {
+          reject(err);
+        });
+    });
+  };
 };
 
 export default SQLiteJSWrapper;

package.json

 {
   "name": "sqlite-js-wrapper",
-  "version": "1.0.2",
-  "description": "Tiny SQLite helper library built on top of HTML5/Web SQL (DRAFT) API",
+  "version": "1.1.0",
+  "description": "Tiny SQLite helper library (query builder) built on top of HTML5/Web SQL (DRAFT) API",
   "main": "index.js",
@@ -33,3 +33,11 @@ "scripts": {
     "js",
-    "wrapper"
+    "wrapper",
+    "helper",
+    "sqlite3",
+    "javascript",
+    "storage",
+    "react",
+    "native",
+    "database",
+    "cordova"
   ],
@@ -36,0 +44,0 @@ "author": "Seçkin KUZOLUK",

README.md

@@ -38,3 +38,4 @@ # sqlite-js-wrapper
 | insert        | Insert single object or array of objects to the table given
-| createTable   | Creates table using column argument
+| 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
 | 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)
@@ -59,2 +60,286 @@
 
-<strong>Examples are coming soon...</strong>
+## 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
+```javascript
+const db = SQLite.openDatabase(
+    { name: 'test.db', location: 'default' },
+    succ => console.log('DB Created: '),
+    err => console.log('Err:', err)
+  );
+```
+
+#### Init wrapper using database object:
+```javascript
+const sw = new SQLiteWrapper(db);
+```
+#### Drop old tables if exists
+```javascript
+sw.dropTable('user');
+sw.dropTable('score');
+```
+
+#### Create user and score tables
+```javascript
+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
+```javascript
+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
+```javascript
+sw.insert('user', users);
+
+// Result: true
+
+```
+```javascript
+sw.insert('score', scores);
+
+// Result: true
+
+```
+#### Insert single record and get insertId
+```javascript
+const { insertId } = sw.insert('score', { game: 9, userId: 1, score: 4 });
+
+// Result: InsertId: 17
+```
+
+#### Update
+```javascript
+const rowsAffected = sw
+.table('user')
+.where('team', 'gala')
+.update({ team: 'galatasaray' });
+
+// rowsAffected: 2
+```
+
+#### Delete records id between 4, 6
+```javascript
+const rowsDeleted = sw
+.table('user')
+.whereBetween('id', [4, 6])
+.delete();
+
+// rowsDeleted: 3
+```
+
+#### Select all records from user table
+```javascript
+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
+```javascript
+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
+
+```javascript
+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 <strong>await/async</strong>
+
+```javascript
+const records = await sw.table('user').select();
+```
+
+* Second is using <strong>.then()</strong>
+```javascript
+sw.table('score')
+    .select()
+    .then(result => console.log(result));
+```
+
+#### Error handling
+* Using <strong>await/async</strong>
+```javascript
+try {
+  await sw.table('tableNotExists').select();
+} catch (err) {
+  console.log(err);
+}
+```
+* Using <strong>.then()</strong>
+```javascript
+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 ☕️ :)

No alert changes

Improved metrics

Total package byte prevSize

23585

increased by40.54%

Lines of code

369

increased by6.96%

Number of lines in readme file

344

increased by483.05%

No dependency changes