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
;; const App = { ; ;};
API
Initialize
; DatabasedatabaseName: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
.
;
createTabletableName: string, columns: ;
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 ;// 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});
;
inserttable: string, data: InsertObject;
tableName
- Name of the table to insert data.
data
- array of objects to insert into table.
example: [{name:"test1",email:"test1@emmail.com"},{name:"test2",email:"test2@exmail.com"}]
.
Return promise resolving with
rowsAffected, insertId, lastQuery
Example
await ;//Inserts a row into 'user' table with column 'name' with 'test' & 'email' with 'test@tester.com'
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};
;
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 ;// 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};
;
update tableName: string, data: InsertObject, where:
tableName
- Name of the table to insert data.
data
- An objects to Update into table.
example: [{name:"test1",email:"test1@emmail.com"},{name:"test2",email:"test2@exmail.com"}]
.
where
- Object with key as column name & value as value to search in Where clause.
Example
await ;// Updates a row matches with column 'name' have value 'test' with column 'name' with 'test1' & column 'email' with 'test1@tester.com'
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}
;
update tableName: string, param: , 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 ; // Deletes rows matches with column 'name' have value 'test'await ; // 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
.
;
dropTabletableName: string;
tableName
- Name of the table to drop.
Example
await ; // Drops table name 'user' from database.
Execute Sql
Async function to run any raw string query. it takes query string & arg as arg.
;
executeSqlquery: string, arg:string;
query
- SQL Query string.
arg
- Optional arguiment to pass to value of query.
Example
await ;// 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