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

$ npx react-native init SQLite

$ cd SQLite

$ cd android

$ gradlew clean

Run Your Project

$ cd ..

$ npx react-native run-android

Install React Native SQLite Storage

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

$ 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.


// Add this code on your app.js

import SQLite from 'react-native-sqlite-storage';

global.db = SQLite.openDatabase(
  {
    name: 'SQLite',
    location: 'default',
    createFromLocation: '~SQLite.db',
  },
  () => { },
  error => {
    console.log("ERROR: " + error);
  }
);

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.


import React from 'react';
import SQLite from 'react-native-sqlite-storage';

export default class SQLiteScreen extends React.Component {
  constructor() {
    super();
    SQLite.DEBUG = true;
  }

  /**
  * Execute sql queries
  * 
  * @param sql
  * @param params
  * 
  * @returns {resolve} results
  */
  ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
    db.transaction((trans) => {
      trans.executeSql(sql, params, (trans, results) => {
        resolve(results);
      },
        (error) => {
          reject(error);
        });
    });
  });

  // Create Table
  async CreateTable() {
    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)",[]);
    console.log(Table);
  }

}

Insert Query

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

import React from 'react';
import SQLite from 'react-native-sqlite-storage';

export default class SQLiteScreen extends React.Component {
  constructor() {
    super();
    SQLite.DEBUG = true;
  }

  /**
  * Execute sql queries
  * 
  * @param sql
  * @param params
  * 
  * @returns {resolve} results
  */
  ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
    db.transaction((trans) => {
      trans.executeSql(sql, params, (trans, results) => {
        resolve(results);
      },
        (error) => {
          reject(error);
        });
    });
  });

  /**
   *  Example Of Insert Rows on SQLite
   */
  async InsertQuery() {

    // single insert query 
    let singleInsert = await this.ExecuteQuery("INSERT INTO users (id, first_name, last_name, is_deleted) VALUES ( ?, ?, ?, ?)", [1, 'Infinite', 'Ability', 0]);
    console.log(singleInsert);

    // multiple insert of users
    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" }];
    let query = "INSERT INTO users (id, first_name, last_name, is_deleted) VALUES";
    for (let i = 0; i < Data.length; ++i) {
      query = query + "('"
        + Data[i].id //id
        + "','"
        + Data[i].first_name //first_name
        + "','"
        + Data[i].last_name //last_name
        + "','"
        + Data[i].is_deleted //is_deleted
        + "')";
      if (i != Data.length - 1) {
        query = query + ",";
      }
    }
    query = query + ";";
    console.log(query);

    let multipleInsert = await this.ExecuteQuery(query, []);
    console.log(multipleInsert);

    // multiple insert of state table
    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" }];
    let countryQuery = "INSERT INTO country (id, user_id, country_name, is_deleted) VALUES";
    for (let i = 0; i < countryData.length; ++i) {
      countryQuery = countryQuery + "('"
        + countryData[i].id //id
        + "','"
        + countryData[i].user_id //user_id
        + "','"
        + countryData[i].country_name //country_name
        + "','"
        + countryData[i].is_deleted //is_deleted
        + "')";
      if (i != countryData.length - 1) {
        countryQuery = countryQuery + ",";
      }
    }
    countryQuery = countryQuery + ";";
    console.log(countryQuery);

    let countryMultipleInsert = await this.ExecuteQuery(countryQuery, []);
    console.log(countryMultipleInsert);
  }

}

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.

import React from 'react';
import SQLite from 'react-native-sqlite-storage';

export default class SQLiteScreen extends React.Component {
  constructor() {
    super();
    SQLite.DEBUG = true;
  }

  /**
  * Execute sql queries
  * 
  * @param sql
  * @param params
  * 
  * @returns {resolve} results
  */
  ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
    db.transaction((trans) => {
      trans.executeSql(sql, params, (trans, results) => {
        resolve(results);
      },
        (error) => {
          reject(error);
        });
    });
  });

  /**
   * Example Of update query
   */
  async UpdateQuery(){
    let updateQuery = await this.ExecuteQuery('UPDATE users SET first_name = ? , last_name = ? WHERE id = ?', ["Doctor", "Strange", 3]);

    console.log(updateQuery);
  }

}

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.

import React from 'react';
import SQLite from 'react-native-sqlite-storage';

export default class SQLiteScreen extends React.Component {
  constructor() {
    super();
    SQLite.DEBUG = true;
  }

  /**
  * Execute sql queries
  * 
  * @param sql
  * @param params
  * 
  * @returns {resolve} results
  */
  ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
    db.transaction((trans) => {
      trans.executeSql(sql, params, (trans, results) => {
        resolve(results);
      },
        (error) => {
          reject(error);
        });
    });
  });

  /**
   * Delete Query Example
   */
  async DeleteQuery(){
    let deleteQuery = await this.ExecuteQuery('DELETE FROM users WHERE id = ?', [4]);

    console.log(deleteQuery);
  }

}

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.

import React from 'react';
import SQLite from 'react-native-sqlite-storage';

export default class SQLiteScreen extends React.Component {
  constructor() {
    super();
    SQLite.DEBUG = true;
  }

  /**
  * Execute sql queries
  * 
  * @param sql
  * @param params
  * 
  * @returns {resolve} results
  */
  ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
    db.transaction((trans) => {
      trans.executeSql(sql, params, (trans, results) => {
        resolve(results);
      },
        (error) => {
          reject(error);
        });
    });
  });

 /**
   * Select Query Example
   */
  async SelectQuery(){
    let selectQuery = await this.ExecuteQuery("SELECT * FROM users",[]);
    var rows = selectQuery.rows;
    for (let i = 0; i < rows.length; i++) {
        var item = rows.item(i);
        console.log(item);
    }
  }

}

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

import React from 'react';
import SQLite from 'react-native-sqlite-storage';

export default class SQLiteScreen extends React.Component {
  constructor() {
    super();
    SQLite.DEBUG = true;
  }

  /**
  * Execute sql queries
  * 
  * @param sql
  * @param params
  * 
  * @returns {resolve} results
  */
  ExecuteQuery = (sql, params = []) => new Promise((resolve, reject) => {
    db.transaction((trans) => {
      trans.executeSql(sql, params, (trans, results) => {
        resolve(results);
      },
        (error) => {
          reject(error);
        });
    });
  });

  //**
   * Joins Example
   */
  async JoinsQuery() {
    // INNER JOIN
    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", []);
    var rows = innerJoin.rows;
    for (let i = 0; i < rows.length; i++) {
      var item = rows.item(i);
      console.log(item);
    }
    console.log(innerJoin);

    // LEFT JOIN
    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", []);
    var rows = leftJoin.rows;
    for (let i = 0; i < rows.length; i++) {
      var item = rows.item(i);
      console.log(item);
    }
    console.log(leftJoin);

    // RIGHT JOIN
    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", []);
    var rows = rightJoin.rows;
    for (let i = 0; i < rows.length; i++) {
      var item = rows.item(i);
      console.log(item);
    }
    console.log(rightJoin);

    // FULL OUTER JOIN
    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", []);
    var rows = fullOutterJoin.rows;
    for (let i = 0; i < rows.length; i++) {
      var item = rows.item(i);
      console.log(item);
    }
    console.log(fullOutterJoin);

  }

}

Code Tips

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


 /**
   * Select Query Example
   */
  async SelectQuery(){
    let selectQuery = await this.ExecuteQuery("SELECT * FROM users",[]);
    var rows = selectQuery.rows;

    this.setState({users: rows.raw()});
  }

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…

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

13. Network request failed in react native fetch

14. React Native upload video example