Navigate back to the homepage

react-native-sqlite-storage examples of query

Infinitbility
React Native
October 11th, 2020 · 2 min read
react-native-sqlite-storage examples of query

Hello Friends,

Welcome To Infinitbility!

React Native SQLite is a library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

Installation

SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed.

Create a New Application

1$ npx react-native init SQLite
1$ cd SQLite
1$ cd android
1$ gradlew clean

Run Your Project

1$ cd ..
1$ npx react-native run-android

Install React Native SQLite Storage

1$ npm install --save react-native-sqlite-storage
1$ react-native link react-native-sqlite-storage

Setup SQLite Database

For managing SQLite Download SQLite Browser

Using DB Browser for SQLite we are create autoloaded data on SQLite

https://sqlitebrowser.org/dl/

react-native-sqlite-storage database setup and location of database

Create a New Database file using SQLite Browser in your Project Folder/android/app/src/main/assets directory

DB Browser

Use Database of react-native-sqlite-storage

below code adds on your App.js file. on this code doing creating Global Variable Db for connection database and use in all my screen.

1// Add this code on your app.js
2
3import SQLite from 'react-native-sqlite-storage';
4
5global.db = SQLite.openDatabase(
6 {
7 name: 'SQLite',
8 location: 'default',
9 createFromLocation: '~SQLite.db',
10 },
11 () => { },
12 error => {
13 console.log("ERROR: " + error);
14 }
15);

Queries Of SQLite Index

Here, You will see some examples of queries and problems to execute the query.

Examples Of Query

  1. Create Table Query
  2. Insert Query
  3. Update Query
  4. Delete Query
  5. select Query
  6. Joins Query

Problems To Execute Query

Multiple Query Run At a Time Issue ( Use Promise on Execute Query)

if you are facing multiple select query issue then please my Execute Query function. on this function, we are using promise for all queries. Multiple Argument ( Param ) on Query For this, please check the query example.

Examples Of Query

Examples Of Query You will learn Basic CRUD operation on SQLite and Joins Query.

Create Table Query

Created ExecuteQuery function and it’s common for all queries to Execute. it’s handle promise also. using this function you can run multiple queries at a time.

1import React from 'react';
2import SQLite from 'react-native-sqlite-storage';
3
4export default class SQLiteScreen extends React.Component {
5 constructor() {
6 super();
7 SQLite.DEBUG = true;
8 }
9
10 /**
11 * Execute sql queries
12 *
13 * @param sql
14 * @param params
15 *
16 * @returns {resolve} results
17 */
18 ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
19 db.transaction((trans) => {
20 trans.executeSql(sql, params, (trans, results) => {
21 resolve(results);
22 },
23 (error) => {
24 reject(error);
25 });
26 });
27 });
28
29 // Create Table
30 async CreateTable() {
31 let Table = await this.executeQuery("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, first_name VARCHAR(16), last_name VARCHAR(16), is_deleted INTEGER)",[]);
32 console.log(Table);
33 }
34
35}

Insert Query

SQLite INSERT INTO Statement is used to add new rows of data into a table in the database.

1import React from 'react';
2import SQLite from 'react-native-sqlite-storage';
3
4export default class SQLiteScreen extends React.Component {
5 constructor() {
6 super();
7 SQLite.DEBUG = true;
8 }
9
10 /**
11 * Execute sql queries
12 *
13 * @param sql
14 * @param params
15 *
16 * @returns {resolve} results
17 */
18 ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
19 db.transaction((trans) => {
20 trans.executeSql(sql, params, (trans, results) => {
21 resolve(results);
22 },
23 (error) => {
24 reject(error);
25 });
26 });
27 });
28
29 /**
30 * Example Of Insert Rows on SQLite
31 */
32 async InsertQuery() {
33
34 // single insert query
35 let singleInsert = await this.ExecuteQuery("INSERT INTO users (id, first_name, last_name, is_deleted) VALUES ( ?, ?, ?, ?)", [1, 'Infinite', 'Ability', 0]);
36 console.log(singleInsert);
37
38 // multiple insert of users
39 let Data = [{ "id": 2, "first_name": "Shani", "last_name": "Tiwari", "is_deleted": "0" }, { "id": 3, "first_name": "John", "last_name": "Carter", "is_deleted": "0" }, { "id": 4, "first_name": "captain", "last_name": "marvel", "is_deleted": "0" }];
40 let query = "INSERT INTO users (id, first_name, last_name, is_deleted) VALUES";
41 for (let i = 0; i < Data.length; ++i) {
42 query = query + "('"
43 + Data[i].id //id
44 + "','"
45 + Data[i].first_name //first_name
46 + "','"
47 + Data[i].last_name //last_name
48 + "','"
49 + Data[i].is_deleted //is_deleted
50 + "')";
51 if (i != Data.length - 1) {
52 query = query + ",";
53 }
54 }
55 query = query + ";";
56 console.log(query);
57
58 let multipleInsert = await this.ExecuteQuery(query, []);
59 console.log(multipleInsert);
60
61 // multiple insert of state table
62 let countryData = [{ "id": 1, "user_id": "1", "country_name": "India", "is_deleted": "0" }, { "id": 2, "user_id": "2", "country_name": "USA", "is_deleted": "0" }, { "id": 3, "user_id": "3", "country_name": "USA", "is_deleted": "0" }, { "id": 4, "user_id": "4", "country_name": "USA", "is_deleted": "0" }];
63 let countryQuery = "INSERT INTO country (id, user_id, country_name, is_deleted) VALUES";
64 for (let i = 0; i < countryData.length; ++i) {
65 countryQuery = countryQuery + "('"
66 + countryData[i].id //id
67 + "','"
68 + countryData[i].user_id //user_id
69 + "','"
70 + countryData[i].country_name //country_name
71 + "','"
72 + countryData[i].is_deleted //is_deleted
73 + "')";
74 if (i != countryData.length - 1) {
75 countryQuery = countryQuery + ",";
76 }
77 }
78 countryQuery = countryQuery + ";";
79 console.log(countryQuery);
80
81 let countryMultipleInsert = await this.ExecuteQuery(countryQuery, []);
82 console.log(countryMultipleInsert);
83 }
84
85}

Update Query

SQLite UPDATE Query is used to modifying the existing records in a table. You can use the WHERE clause with the UPDATE query to update selected rows, otherwise, all the rows would be updated.

1import React from 'react';
2import SQLite from 'react-native-sqlite-storage';
3
4export default class SQLiteScreen extends React.Component {
5 constructor() {
6 super();
7 SQLite.DEBUG = true;
8 }
9
10 /**
11 * Execute sql queries
12 *
13 * @param sql
14 * @param params
15 *
16 * @returns {resolve} results
17 */
18 ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
19 db.transaction((trans) => {
20 trans.executeSql(sql, params, (trans, results) => {
21 resolve(results);
22 },
23 (error) => {
24 reject(error);
25 });
26 });
27 });
28
29 /**
30 * Example Of update query
31 */
32 async UpdateQuery(){
33 let updateQuery = await this.ExecuteQuery('UPDATE users SET first_name = ? , last_name = ? WHERE id = ?', ["Doctor", "Strange", 3]);
34
35 console.log(updateQuery);
36 }
37
38}

Delete Query

SQLite DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with the DELETE query to delete the selected rows, otherwise, all the records would be deleted.

1import React from 'react';
2import SQLite from 'react-native-sqlite-storage';
3
4export default class SQLiteScreen extends React.Component {
5 constructor() {
6 super();
7 SQLite.DEBUG = true;
8 }
9
10 /**
11 * Execute sql queries
12 *
13 * @param sql
14 * @param params
15 *
16 * @returns {resolve} results
17 */
18 ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
19 db.transaction((trans) => {
20 trans.executeSql(sql, params, (trans, results) => {
21 resolve(results);
22 },
23 (error) => {
24 reject(error);
25 });
26 });
27 });
28
29 /**
30 * Delete Query Example
31 */
32 async DeleteQuery(){
33 let deleteQuery = await this.ExecuteQuery('DELETE FROM users WHERE id = ?', [4]);
34
35 console.log(deleteQuery);
36 }
37
38}

Select Query

The SQLite select statement is used to fetch the data from an SQLite database table which returns data in the form of a result table. These result tables are also called result sets.

1import React from 'react';
2import SQLite from 'react-native-sqlite-storage';
3
4export default class SQLiteScreen extends React.Component {
5 constructor() {
6 super();
7 SQLite.DEBUG = true;
8 }
9
10 /**
11 * Execute sql queries
12 *
13 * @param sql
14 * @param params
15 *
16 * @returns {resolve} results
17 */
18 ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
19 db.transaction((trans) => {
20 trans.executeSql(sql, params, (trans, results) => {
21 resolve(results);
22 },
23 (error) => {
24 reject(error);
25 });
26 });
27 });
28
29 /**
30 * Select Query Example
31 */
32 async SelectQuery(){
33 let selectQuery = await this.ExecuteQuery("SELECT * FROM users",[]);
34 var rows = selectQuery.rows;
35 for (let i = 0; i < rows.length; i++) {
36 var item = rows.item(i);
37 console.log(item);
38 }
39 }
40
41}

Joins Query

SQLite Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Provided Example of

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL OUTER JOIN
1import React from 'react';
2import SQLite from 'react-native-sqlite-storage';
3
4export default class SQLiteScreen extends React.Component {
5 constructor() {
6 super();
7 SQLite.DEBUG = true;
8 }
9
10 /**
11 * Execute sql queries
12 *
13 * @param sql
14 * @param params
15 *
16 * @returns {resolve} results
17 */
18 ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
19 db.transaction((trans) => {
20 trans.executeSql(sql, params, (trans, results) => {
21 resolve(results);
22 },
23 (error) => {
24 reject(error);
25 });
26 });
27 });
28
29 //**
30 * Joins Example
31 */
32 async JoinsQuery() {
33 // INNER JOIN
34 let innerJoin = await this.ExecuteQuery("SELECT users.id, users.first_name, users.last_name, c.country_name FROM users INNER JOIN country c on c.user_id = users.id", []);
35 var rows = innerJoin.rows;
36 for (let i = 0; i < rows.length; i++) {
37 var item = rows.item(i);
38 console.log(item);
39 }
40 console.log(innerJoin);
41
42 // LEFT JOIN
43 let leftJoin = await this.ExecuteQuery("SELECT users.id, users.first_name, users.last_name, c.country_name FROM users LEFT JOIN country c on c.user_id = users.id", []);
44 var rows = leftJoin.rows;
45 for (let i = 0; i < rows.length; i++) {
46 var item = rows.item(i);
47 console.log(item);
48 }
49 console.log(leftJoin);
50
51 // RIGHT JOIN
52 let rightJoin = await this.ExecuteQuery("SELECT users.id, users.first_name, users.last_name, c.country_name FROM users RIGHT JOIN country c on c.user_id = users.id", []);
53 var rows = rightJoin.rows;
54 for (let i = 0; i < rows.length; i++) {
55 var item = rows.item(i);
56 console.log(item);
57 }
58 console.log(rightJoin);
59
60 // FULL OUTER JOIN
61 let fullOutterJoin = await this.ExecuteQuery("SELECT users.id, users.first_name, users.last_name, c.country_name FROM users FULL OUTER JOIN country c on c.user_id = users.id", []);
62 var rows = fullOutterJoin.rows;
63 for (let i = 0; i < rows.length; i++) {
64 var item = rows.item(i);
65 console.log(item);
66 }
67 console.log(fullOutterJoin);
68
69 }
70
71}

Code Tips

directly store all data on the state if you didn’t want to loop all your rows use below example

1/**
2 * Select Query Example
3 */
4 async SelectQuery(){
5 let selectQuery = await this.ExecuteQuery("SELECT * FROM users",[]);
6 var rows = selectQuery.rows;
7
8 this.setState({users: rows.raw()});
9 }

SQLite Delete Database and db file location

created separate posts for delete database and DB file location.

https://infinitbility.com/how-to-delete-sqlite-database-in-android-react-native

if you get help, please share post on your social network

More From React Native Tutorial

Basics

  1. Introduction To React Native
  2. React Native Environment Setup using expo
  3. React Native Environment Setup for windows
  4. React Native Environment setup on Mac OS
  5. React Native Environment setup on linux
  6. React Native Project Structure
  7. React Native State
  8. React Native Props
  9. React Native Styling
  10. React Native Flexbox
  11. React Native Text
  12. React Native Textinput
  13. React Native Commands
  14. React Native ScrollView

Advances

  1. React Native Dark Mode
  2. React Native Fonts
  3. React Native SQLite
  4. React Native DatepickerAndroid
  5. React native ScrollView scroll to position
  6. How to align icon with text in react native
  7. React Native Image
  8. React Native Firebase Crashlytics
  9. React Native Async Storage
  10. React Native Share

Error & Issue Solution

  1. Task :app:transformDexArchiveWithDexMergerForDebug FAILED In React Native
  2. Expiring Daemon because JVM heap space is exhausted In React Native
  3. Task :app:transformNativeLibsWithMergeJniLibsForDebug FAILED In React Native
  4. Unable to determine the current character, it is not a string, number, array, or object in react native
  5. App crashed immediately after install react native video or track player
  6. how to delete SQLite database in android react native
  7. React native material dropdown twice click issue
  8. How to get the current route in react-navigation?
  9. how to disable drawer on the drawer navigation screen?
  10. Image not showing in ios 14 react native
  11. React Native image picker launchimagelibrary on second time issue
  12. how to open any link from react native render Html

Looking For React Native Tutorial?

we are trying to create the best Tutorial for react native developers.

When you want a daily updates about React Native Tutorial or infinitbility update subscribe to our newsletter.

Read React Native Tutorial

Request New Tutorial or Article on mail [email protected]

Join our email list and get notified about new content

No worries, I respect your privacy and I will never abuse your email.

Every week, on Tuesday, you will receive a list of free tutorials I made during the week (I write one every day) and news on other training products I create.

More articles from Infinitbility

How to align icon with text in react native

How to align icon with text in react native

align text with icon in react native in button, card, etc.

October 10th, 2020 · 1 min read
How to solve page expired error in laravel for webhooks, ajax, and form

How to solve page expired error in laravel for webhooks, ajax, and form

Laravel 419 Page Expired Error solutions for webhooks, ajax, and form

October 2nd, 2020 · 1 min read
© 2020–2021 Infinitbility
Disclaimer
Link to $https://medium.com/infinitbilityLink to $https://www.facebook.com/InfinitbilityLink to $https://github.com/infinitbilityLink to $https://twitter.com/infinitbilityLink to $https://www.buymeacoffee.com/infinitbilityLink to $mailto:[email protected]