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

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

Create Table Query
Insert Query
Update Query
Delete Query
select Query
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

INNER JOIN
LEFT JOIN
RIGHT JOIN
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

SQLite encryption

Below documentation, I have explained step by step to encrypt SQLite database using SQLcipher

https://infinitbility.com/how-to-encrypt-sqlite-database

Thanks for reading…

Basics

Advances

Error & Issue Solution

Follow me on Twitter

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.

react-native-sqlite-storage examples of query

Installation

Setup SQLite Database

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

Use Database of react-native-sqlite-storage

Queries Of SQLite Index

Examples Of Query

Problems To Execute Query

Examples Of Query

Create Table Query

Insert Query

Update Query

Delete Query

Select Query

Joins Query

Code Tips

SQLite Delete Database and db file location

SQLite encryption

More From React Native Tutorial

Basics

Advances

Error & Issue Solution

Follow me on Twitter

Join our email list and get notified about new content

Looking For React Native Tutorial?

Guide book

Tutorials

Categories

More articles from Infinitbility

How to align icon with text in react native

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