Skip to content

README.md

Latest commit

 

History

History
238 lines (175 loc) · 6.93 KB

README.md

File metadata and controls

238 lines (175 loc) · 6.93 KB
Raw

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.

Changelogs

See full changelogs here

Requirements

Folder Structure

You can use either the JavaScript (.js) or TypeScript (.ts) version of the helper.

NSProject/
├── app/
│   └── assets/
│       └── db/
│           └── your_database.db      <-- Seed database (optional)
│   └── sqlite_helper.js              <-- For JS projects
│   └── sqlite_helper.ts              <-- For TS projects (recomended)

Instructions

  1. Download sqlite_helper.js (or the .ts version) and save it to your app folder.
  2. (Optional) Create a seed database using SQLite Browser.
  3. Place your .db file in app/assets/db/.
  4. Open the helper file and update the config object:
const config = {
  databaseName: "my_app.db", // Your actual database filename
  debug: true,               // Enable for detailed logs in console
  paths: {
    documentsFolder: knownFolders.documents(),
    assetsFolder: "assets/db", // Where the helper looks for the seed file
  },
};
  1. import file sqlite_helper on your module, like : 
    import {
  SQL__select,
  SQL__selectRaw,
  SQL__insert,
  SQL__update,
  SQL__delete,
  SQL__truncate,
  SQL__query,
} from "~/sqlite_helper";
  2. Avaliable methode on sqlite_helper
    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 (supports single row or bulk array) 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 ?
    SQL__transaction(...) for execute multiple operations in one transaction (Bulk) Promise
    SQL__executeBatch(...) for execute multiple raw queries efficiently Promise
    SQL__close() for close database connection void
  3. For details, you can look at the sqlite_helper.js or .ts 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 (or .ts), 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 by ID

I want to update the about field for the user with ID 3.

import { SQL__update } from "~/sqlite_helper";

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

UPDATE Data with WHERE Condition

I want to update the about field where the ID is 3 using a custom string condition.

import { SQL__update } from "~/sqlite_helper";

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

TRANSACTION (For Bulk CRUD)

Use transactions when you need to execute multiple write operations (Insert/Update/Delete) as a single atomic unit. This is much faster than individual calls.

import { SQL__transaction } from "~/sqlite_helper";

await SQL__transaction(async (db) => {
  // All these operations happen in ONE transaction
  // 'db' provides the standard SQLite plugin API (execute, select, etc)
  await db.execute("INSERT INTO users (fullname) VALUES (?)", ["User 1"]);
  await db.execute("INSERT INTO users (fullname) VALUES (?)", ["User 2"]);
  await db.execute("UPDATE users SET about='Batch' WHERE fullname LIKE 'User%'");
});

BULK INSERT (Optimized)

The SQL__insert method is smart: if you pass an array of row objects, it will automatically use a transaction internally for high performance.

import { SQL__insert } from "~/sqlite_helper";

const manyUsers = [
  [{ field: "fullname", value: "User A" }, { field: "about", value: "A" }],
  [{ field: "fullname", value: "User B" }, { field: "about", value: "B" }],
  [{ field: "fullname", value: "User C" }, { field: "about", value: "C" }]
];

// This will automatically use a transaction internally
await SQL__insert("users", manyUsers);

EXECUTE BATCH

import { SQL__executeBatch } from "~/sqlite_helper";

const queries = [
  "DELETE FROM users WHERE archive=1",
  ["INSERT INTO users (fullname) VALUES (?)", ["User X"]],
  "VACUUM"
];

await SQL__executeBatch(queries);

My Nativescript App using SQLite Helper

WA Sender

More info about Sqlite

Sqlite Tutorial by Tutorialspoint