SQLite Helper Nativescript

This helper can helping you when you using SQLite in Nativescript

Dependency

Before you using this helper, you must be install plugin nativescript-community/sqlite, cause this helper running on this plugin.

Requirement

• Nativescript 8 or newer
• nativescript-community/sqlite

Instructions

1. Download file sqlite_helper.js and save that file here : \YOUR_NATIVESCRIPT_PROJECT\app
2. Create .db file using SQLite Browser
3. And after that put the your_database.db in \YOUR_NATIVESCRIPT_PROJECT\app
4. Then in the sqlite_helper.js file, find openOrCreate("your_database.db") code and change with your database name.
5. Still in the sqlite_helper.js file, find showError variable then set it to true if you want to see all errors that occur during development on your sqlite.
6. import file sql_helper.js on your module, like :

   import { SQL__select, SQL__selectRaw, SQL__insert, SQL__update, SQL__delete, SQL__truncate, SQL__query } from "~/sqlite_helper";

7. Avaliable methode on sql_helper.js

   Method	Description	Return
   SQL__select(...)	for get data from table	Array
   SQL__selectRaw(...)	for get data from table, same like SQL_select, but here you can execute simple or advance query, like JOIN Query or etc	Array
   SQL__insert(...)	for insert data to table	void
   SQL__update(...)	for update data to table	void
   SQL__delete(...)	for delete data row from table	void
   SQL__truncate(...)	for clear all data on the table	void
   SQL__query(...)	for execute raw query like Create new Table or Etc	?

8. For details, you can look at the sqlite_helper.js file directly

Sample Code

TABLE

Assummed I have a users table like this :

CREATE TABLE "users" (
	"id"	INTEGER NOT NULL UNIQUE,
	"fullname"	TEXT NOT NULL,
	"about"	TEXT DEFAULT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
)

CREATE new TABLE USERS

Before you can do something, make sure you already create the table. for create table in SQLite, you can use method SQL_query from sqlite_helper.js, example like this :

import { SQL__query } from "~/sqlite_helper";

SQL__query(`CREATE TABLE IF NOT EXISTS "users" (
	"id"	INTEGER NOT NULL UNIQUE,
	"fullname"	TEXT NOT NULL,
	"about"	TEXT DEFAULT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
)`);

When you make create table query, make sure you use IF NOT EXISTS in your query. This is useful to avoid double execution of your query.

GET all USERS

SQL__select(tableName)

I want to get all user data from the table

import { SQL__select } from "~/sqlite_helper";

SQL__select("users").then((res) => {
   console.log(res);
   console.log(res.length);
});

GET USER where FULLNAME is JOHN DUO

SQL__select(tableName, fields, conditionalQuery)

I want to get all user data from table by fullname is john duo

import { SQL__select } from "~/sqlite_helper";

SQL__select("users", "*", "WHERE fullname='john duo'").then((res) => {
   console.log(res);
   console.log(res.length);
});

CREATE new USER

SQL__insert(tableName, data)

I want to create new user with fullname is Kang Cahya and about is Designer

import { SQL__insert } from "~/sqlite_helper";

SQL__insert("users", [
   { field: "fullname", value: "Kang Cahya" },
   { field: "about", value: "Designer" }
]);

UPDATE data USER by ID

SQL__update(tableName, data, id, conditionalQuery)

I want to update field ABOUT by user ID number 3

import { SQL__insert } from "~/sqlite_helper";

SQL__update("users", [{ field: "about", value: "Tester" }], 3);

UPDATE data USER with WHERE condition

SQL__update(tableName, data, id, conditionalQuery)

I want to update field about by user ID number 3

import { SQL__insert } from "~/sqlite_helper";

SQL__update("users", [{ field: "about", value: "Tester" }], null, "WHERE id='3'");

More info about Sqlite

Sqlite Tutorial by Tutorialspoint