expo-sqlite-query-helper

expo-sqlite Query Helper 🦮

SQLite query helper library for expo-sqlite

Installation

Yarn

yarn add expo-sqlite-query-helper

NPM

npm install --save expo-sqlite-query-helper

Usage

import { useEffect } from 'react';
import Database, { createTable, insert } from 'expo-sqlite-query-helper';

const App = () => {
    Database('myDatabase.db');
    useEffect(() => {
        createTable('user', {
            name: 'TEXT',
            email: 'TEXT'
        }).then(({ row, rowAffected, insertID, lastQuery }) =>
            console.log('success', row, rowAffected, insertID, lastQuery)
        );
        insert([{ name: 'Jhon', email: '[e-mail]' }], 'user')
            .then(({ row, rowAffected, insertID, lastQuery }) => {
                console.log('success', row, rowAffected, insertID, lastQuery);
            })
            .catch((e) => console.log(e));
    }, []);
};

API

Initialize

import Database from "expo-sqlite-query-helper";

Database(databaseName:string, version:string);

databaseName (String) - Name of the database to create. Default is "esqh.db".
Reference: expo-sql's SQLite.openDatabase

Result object

All the queries will returns an object with following keys.

On Success

row - WewSQLRows -{ length:number, _array: object[] } Mostly useful in search (Select) type query, the returned data will be inside _array. leth.
rowAffected - Mostly useful in update/delete queries, Returns which row is affected by the query.
insertId - Mostly useful in insert query, returns the Auto incriment ID generated by sql. lastQuery - The string which contains last executed query

On Error

error - SQLite error.
lastQuery - The string which contains last executed query

Create Table

Async function to create new table.
under the hood it runs CREATE TABLE IF NOT EXIST.

import { createTable } from 'expo-sqlite-query-helper';

createTable(tableName: string, columns: { [key: string]: string });

tableName - Name of the table to create.
columns - Column object, key is name of column, value is type & other arguments for columns (as per sqlite).

Promise returns an object with row, rowsAffected, insertId, lastQuery

Example

await createTable('user', {
    name: 'varchar(100) NOT NULL',
    email: 'varchar(100) NULL'
});
// Creates a table with name 'user' with columns 'name' with varchar type & 'email' with varchar type

Insert

Async function to run insert data into the table, Takes array of objects to insert into specified table.
under the hood it runs INSERT INTO table (...columns{keys}) values ...(columns{values});

import { insert } from 'expo-sqlite-query-helper';

insert(table: string, data: InsertObject[]);

tableName - Name of the table to insert data.
data - array of objects to insert into table.
example: [{name:"test1",email:"[e-mail]"},{name:"test2",email:"[e-mail]"}].
Return promise resolving with rowsAffected, insertId, lastQuery

Example

await insert('user', { name: 'test', email: '[e-mail]' });
//Inserts a row into 'user' table with column 'name' with 'test' & 'email' with '[e-mail]'

Search (Select)

Async function to search specified parameter or select everything from the given table.
under the hood it runs SELECT * FROM tableName ?WHERE param{key}=param{value};

import { search } from 'expo-sqlite-query-helper';

search (
  tableName: string,
  param: InsertObject | null ,
  order_by: InsertObject | null,
  limit: number | null ,
  extra: string = ""
);

tableName - Name of the table to search.
param: {column:value} - objects to search.
example: {name:"test1"}
order_by : {column:"ASC"|"DESC"} - object to order the search result.
example: {id:"DESC"} limit - Number of records to return.
extra - Extra SQL query if any, It will be printed just after SELECT commmand.

Example

const result = await search('user', { name: 'test' });
// Returns rows from table 'user' where it matches column 'name' with value 'test'

Update

Async function to run update data in the table, Takes an objects to update into specified table & coulumn.

under the hood it runs UPDATE table SET (...data{keys}) values(...data{values}) WHERE where{key}=where{value};

import { update } from 'expo-sqlite-query-helper';

update(
    tableName: string,
    data: InsertObject,
    where: { [key: string]: string }
)

tableName - Name of the table to insert data.
data - An objects to Update into table.
example: [{name:"test1",email:"[e-mail]"},{name:"test2",email:"[e-mail]"}].
where - Object with key as column name & value as value to search in Where clause.

Example

await update(
    'user',
    { name: 'test1', email: '[e-mail]' },
    { name: 'test' }
);
// Updates a row matches with column 'name' have value 'test' with column 'name' with 'test1' & column 'email' with '[e-mail]'

Delete Data

Async function to run delete data from the table, Takes table name and object to delete perticular row.

Note: If you pass only table name, it will delete complete data from the mentioned table

under the hood it runs DELETE FROM table WHERE param{key}=param{value}

import { deleteData } from 'expo-sqlite-query-helper';

update(
    tableName: string,
    param: { [key: string]: string },
    extra: string
)

tableName - Name of the table to insert data.
param - Object with key as column name & value. Matching row will be deleted.

Example

await deleteData('user', { name: 'test' }); // Deletes rows matches with column 'name' have value 'test'
await deleteData('user'); // Deletes all rows from 'user' table

Drop Table

Async function to Drop a table from database. It takes a table name as arg.

under the hood it runs DROP TABLE IF EXISTS table.

import { dropTable } from 'expo-sqlite-query-helper';

dropTable(tableName: string);

tableName - Name of the table to drop.

Example

await dropTable('user'); // Drops table name 'user' from database.

Execute Sql

Async function to run any raw string query. it takes query string & arg as arg.

import { executeSql } from 'expo-sqlite-query-helper';

executeSql(query: string, arg:string[]);

query - SQL Query string.
arg - Optional arguiment to pass to value of query.

Example

await executeSql('SELECT * FROM user WHERE name=?', ['tester']);
// Selects all rows from 'user' where column 'name' have value 'tester'.

---

Todo

• More parameters & conditions for where clause
• to add Update if exist or Insert function