React Native Local Database | 𝟐𝟎𝟐𝟎 | 𝐍𝐞𝐰𝐛𝐲𝐂𝐨𝐝𝐞𝐫.𝐜𝐨𝐦

Using SQLite Database in React Native

SQLite is an open-source SQL database that stores data to a text file on a device which supports CRUD SQL transactions

Example provided uses SQLite3 Native Plugin, based on Cordova SQLite plugin, which works in both Android and iOS

It consists of a HomeScreen with options to go to other screens like

RegisterUser: To Register the User (Create/Insert)
ViewAllUser: To View All Users (Read)
ViewUser: To View Single Users By Id (Read)
UpdateUser: To Update the User(Update)
DeleteUser: To Delete the User(Delete)

and custom components like IButton, UText, IUTextInput to be used over react-native Button, Text, and TextInput

Usage

Import openDatabase from react-native-sqlite-storage

import { openDatabase } from 'react-native-sqlite-storage';

Open database using openDatabase() function with name of db (which gets created if doesn't exist)

var db = openDatabase({ name: 'UserDatabase.db' });

Use db reference to execute a database query

db.transaction(function(txn) {
      txn.executeSql(
        query,                 //Query to execute as prepared statement
        argsToBePassed[],      //Argument to pass for the prepared statement
        function(tx, res) {}   //Callback function to handle the result
      );
    });

db provides a transaction() function which accepts a function as input

A transaction object is passed to this function which can be used to execute sql statements by accessing its executeSql() function

tx.executeSql(
    'SELECT * FROM user_table where user_id = ? OR user_name = ?',
    [input_user_id, input_user_id],
    (tx, results) => {
      console.log(results.rows)
    }
}

Creating new React Native App

Check React Native Installation for installation related info

Use react-native init command to create a new React Native project (here named NcLocalDbApp)

react-native init NcLocalDbApp

This creates a directory named NcLocalDbApp and initializes it as a react native application directory

This can be skipped in case an existing react native project is being integrated into

Dependency

Since this example uses NavigationDrawer component, related packages have to be added along with react-native-sqlite-storage

Go to project directory (here NcLocalDbApp)

cd NcLocalDbApp

Add react-native-sqlite-storage, react-navigation react-navigation-drawer && react-navigation-stack and associated dependencies to current project using yarn

yarn add react-native-sqlite-storage react-navigation react-navigation-drawer react-navigation-stack \
react-native-gesture-handler react-native-reanimated react-native-safe-area-context react-native-screens

or using npm

npm install react-navigation react-navigation-drawer react-navigation-stack \
react-native-gesture-handler react-native-reanimated react-native-safe-area-context react-native-screens --save

iOS

After yarn add command, cd into ios folder inside project directory

cd ios

Use pod command to install any required CocoaPods dependencies:

pod install

App Code

Following is the project structure

NcLocalDbApp/
 |-- android/
 |-- App.js
 |-- app.json
 |-- DeleteUser.js
 |-- HomeScreen.js
 |-- index.js
 |-- ios/
 |-- metro.config.js
 |-- node_modules/
 |-- package.json
 |-- RegisterUser.js
 |-- UiElements.js
 |-- UpdateUser.js
 |-- UserDao.js
 |-- ViewAllUsers.js
 |-- ViewUser.js
 +-- __tests__/

In App.js a stack navigator is declared to configure routes to the screens

The first screen is the initial route, in case no initial route is specified

App.js

import React from 'react';
import { createAppContainer } from 'react-navigation';
import { createStackNavigator} from 'react-navigation-stack';

import HomeScreen from './HomeScreen';
import RegisterUser from './RegisterUser';
import UpdateUser from './UpdateUser';
import ViewUser from './ViewUser';
import ViewAllUsers from './ViewAllUsers';
import DeleteUser from './DeleteUser';


const App = createStackNavigator({
  HomeScreen: {
    screen: HomeScreen,
    navigationOptions: {
      title: 'HomeScreen',
    },
  },
  View: {
    screen: ViewUser,
    navigationOptions: {
      title: 'View User',
    },
  },
  ViewAll: {
    screen: ViewAllUsers,
    navigationOptions: {
      title: 'View All User',
    },
  },
  Update: {
    screen: UpdateUser,
    navigationOptions: {
      title: 'Update User',
    },
  },
  Register: {
    screen: RegisterUser,
    navigationOptions: {
      title: 'Register User',
    },
  },
  Delete: {
    screen: DeleteUser,
    navigationOptions: {
      title: 'Delete User',
    },
  },
});

export default createAppContainer(App);

UserDao

Database Access Object to interact with sqlite

It contains functions which take callback (and other required parameters for transaction) as argument and passes the result of a database transaction to the callback

UserDao.js

import { openDatabase } from 'react-native-sqlite-storage';

export default class UserDao {

  constructor() {}

  static db = openDatabase({ name: 'UserDatabase.db' });

  static init() {
    this.db.transaction(function(txn) {
      txn.executeSql(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='user_table'",
        [],
        function(tx, res) {
          if (res.rows.length == 0) {
            txn.executeSql('DROP TABLE IF EXISTS user_table', []);
            txn.executeSql(
              'CREATE TABLE IF NOT EXISTS user_table(user_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR(20), user_contact INT(10), user_address VARCHAR(255))',
              []
            );
          }
        }
      );
    });
  }

  static getAllUsers(callback) {
    this.db.transaction(tx => {
      tx.executeSql('SELECT * FROM user_table', [], (tx, results) => {
        var temp = [];
        for (let i = 0; i < results.rows.length; ++i) {
          temp.push(results.rows.item(i));
        }
        callback(temp);
      });
    });
  }

  static getUser(callback, input_user_id) {
    this.db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM user_table where user_id = ? OR user_name = ?',
        [input_user_id, input_user_id],
        (tx, results) => {
          var temp = [];
          for (let i = 0; i < results.rows.length; ++i) {
            temp.push(results.rows.item(i));
          }
          callback(temp);
        }
      );
    });
  }

  static registerUser(callback, user_name, user_contact, user_address) {
    this.db.transaction((tx)=> {
      tx.executeSql(
        'INSERT INTO user_table (user_name, user_contact, user_address) VALUES (?,?,?)',
        [user_name, user_contact, user_address],
        (tx, results) => {
          callback(results.rowsAffected>0)
        }
      );
    });
  }

  static updateUser(callback, user_name, user_contact, user_address, input_user_id) {
    this.db.transaction((tx)=> {
      tx.executeSql(
        'UPDATE user_table set user_name=?, user_contact=? , user_address=? where user_id=?',
        [user_name, user_contact, user_address, input_user_id],
        (tx, results) => {
          callback(results.rowsAffected>0)
        }
      );
    });
  }

  static deleteUser(callback, input_user_id) {
    this.db.transaction((tx)=> {
      tx.executeSql(
        'DELETE FROM  user_table where user_id=?',
        [input_user_id],
        (tx, results) => {
          callback(results.rowsAffected>0)
        }
      );
    });
  }

}

UiElements.js

Custom button, text and textinput components with some predefined style properties

UiElements.js

import React from 'react';
import { TouchableOpacity, Text, StyleSheet, TextInput, View } from 'react-native';

const IButton = props => {
  return (
    <TouchableOpacity style={styles.button} onPress={props.onPress}>
      <Text style={styles.buttonText}>{props.title}</Text>
    </TouchableOpacity>
  );
};

const UText = props => {
  return <Text style={styles.text}>{props.text}</Text>;
};

const IUTextInput = props => {
  return (
    <View style={{
        marginLeft: 35,
        marginRight: 35,
        marginTop: 10,
        borderColor: '#006EFF',
        borderWidth: 1,
      }}>
      <TextInput
        underlineColorAndroid="transparent"
        placeholder={props.placeholder}
        placeholderTextColor="#006EFF"
        keyboardType={props.keyboardType}
        onChangeText={props.onChangeText}
        returnKeyType={props.returnKeyType}
        numberOfLines={props.numberOfLines}
        multiline={props.multiline}
        onSubmitEditing={props.onSubmitEditing}
        style={props.style}
        blurOnSubmit={false}
        value={props.value}
      />
    </View>
  );
};

const styles = StyleSheet.create({
  button: {
    alignItems: 'center',
    backgroundColor: '#f04455',
    color: '#ffffff',
    padding: 10,
    marginTop: 16,
    marginLeft: 35,
    marginRight: 35,
  },
  buttonText: {
    color: '#ffffff',
  },
  text: {
    color: '#112030',
    fontSize: 18,
    marginTop: 16,
    marginLeft: 35,
    marginRight: 35,
  }
});

export  { IButton , UText, IUTextInput }

HomeScreen.js

Landing page which buttons to navigate to pages performing database operations

HomeScreen.js

import React from 'react';
import { View } from 'react-native';
import {UText, IButton} from './UiElements';
import UserDao from './UserDao'


export default class HomeScreen extends React.Component {
  constructor(props) {
    super(props);
    UserDao.init();
  }

  render() {
    return (
      <View
        style={{
          flex: 1,
          backgroundColor: 'white',
          flexDirection: 'column',
        }}>
        <UText text="SQLite Example" />
        <IButton
          title="Register"
          onPress={() => this.props.navigation.navigate('Register')}
        />
        <IButton
          title="Update"
          onPress={() => this.props.navigation.navigate('Update')}
        />
        <IButton
          title="View"
          onPress={() => this.props.navigation.navigate('View')}
        />
        <IButton
          title="View All"
          onPress={() => this.props.navigation.navigate('ViewAll')}
        />
        <IButton
          title="Delete"
          onPress={() => this.props.navigation.navigate('Delete')}
        />
      </View>
    );
  }
}

RegisterUser.js

Page to take input and create database entry for it, if not empty

RegisterUser.js

import React from 'react';
import { View, ScrollView, KeyboardAvoidingView, Alert } from 'react-native';
import { IUTextInput, IButton } from './UiElements';
import UserDao from './UserDao'


export default class RegisterUser extends React.Component {

  constructor(props) {
    super(props);
    this.state = {
      user_name: '',
      user_contact: '',
      user_address: '',
    };
  }

  register_user = () => {
    const { user_name } = this.state;
    const { user_contact } = this.state;
    const { user_address } = this.state;
    if (user_name) {
      if (user_contact) {
        if (user_address) {
          UserDao.registerUser((userRegistered) => this.renderUserRegistered(userRegistered), user_name, user_contact, user_address)
        }
        else {
          alert('Enter Address');
        }
      }
      else {
        alert('Enter Contact Number');
      }
    }
    else {
      alert('Enter Name');
    }
  };

  renderUserRegistered(userRegistered) {
    if(userRegistered) {
      Alert.alert(
        'Register',
        'User Registered',
        [
          {
            text: 'Ok',
            onPress: () =>
              this.props.navigation.navigate('HomeScreen'),
          },
        ],
        { cancelable: false }
      );
    }
    else {
      alert('Registration Failed');
    }
  }

  render() {
    return (
      <View style={{ backgroundColor: 'white', flex: 1 }}>
        <ScrollView keyboardShouldPersistTaps="handled">
          <KeyboardAvoidingView
            behavior="padding"
            style={{ flex: 1, justifyContent: 'space-between' }}>
            <IUTextInput
              placeholder="Enter Name"
              onChangeText={user_name => this.setState({ user_name })}
              style={{ padding:10 }}
            />
            <IUTextInput
              placeholder="Enter Contact No"
              onChangeText={user_contact => this.setState({ user_contact })}
              maxLength={10}
              keyboardType="numeric"
              style={{ padding:10 }}
            />
            <IUTextInput
              placeholder="Enter Address"
              onChangeText={user_address => this.setState({ user_address })}
              maxLength={225}
              numberOfLines={5}
              multiline={true}
              style={{ textAlignVertical: 'top',padding:10 }}
            />
            <IButton
              title="Submit"
              onPress={this.register_user.bind(this)}
            />
          </KeyboardAvoidingView>
        </ScrollView>
      </View>
    );
  }
}

UpdateUser.js

Page to search and update entries

UpdateUser.js

js_utilblock_filename35.htm:44 import React from 'react';
import { View, YellowBox, ScrollView, KeyboardAvoidingView, Alert, } from 'react-native';
import { IUTextInput, IButton } from './UiElements';
import UserDao from './UserDao'


export default class UpdateUser extends React.Component {

  constructor(props) {
    super(props);
    this.state = {
      input_user_id: '',
      user_name: '',
      user_contact: '',
      user_address: '',
    };
  }

  searchUser = () => {
    const { input_user_id } = this.state;
    if(input_user_id)
      UserDao.getUser((userList) => this.setItems(userList), input_user_id);
    else
      alert('Enter User Id or Name ot search');
      this.setState({
        user_name:'',
        user_contact:'',
        user_address:'',
        user_id:''
      });
  };

  setItems(userList) {
    var len = userList.length;
    console.log('search res len',len);
    if (userList.length > 0) {
      this.setState({
       user_name:userList[0].user_name,
       user_contact:userList[0].user_contact,
       user_address:userList[0].user_address,
       user_id:userList[0].user_id,
      });
    }
    else {
      alert('No user found');
      this.setState({
        user_name:'',
        user_contact:'',
        user_address:'',
        user_id:''
      });
    }
  }

  updateUser = () => {
    var that=this;
    const { user_id } = this.state;
    const { user_name } = this.state;
    const { user_contact } = this.state;
    const { user_address } = this.state;
    if( user_id) {
      if (user_name) {
        if (user_contact) {
          if (user_address) {
            UserDao.updateUser((userUpdated) => this.renderUserUpdated(userUpdated), user_name, user_contact, user_address, user_id)
          }
          else {
            alert('Enter Address');
          }
        }
        else {
          alert('Enter Contact Number');
        }
      }
      else {
        alert('Enter Name');
      }
    }
    else {
      alert('Search user with user id or name');
    }
  };

  renderUserUpdated(userUpdated) {
    if(userUpdated) {
      alert('User updated')
    }
    else {
      alert('Updation Failed');
    }
    this.setState({
      user_name:'',
      user_contact:'',
      user_address:'',
    });
  }

  render() {
    return (
      <View style={{ backgroundColor: 'white', flex: 1 }}>
        <ScrollView keyboardShouldPersistTaps="handled">
          <KeyboardAvoidingView
            behavior="padding"
            style={{ flex: 1, justifyContent: 'space-between' }}>
            <IUTextInput
              placeholder="Enter User Id or Name"
              style={{ padding:10 }}
              onChangeText={input_user_id => this.setState({ input_user_id })}
            />
            <IButton
              title="Search User"
              onPress={this.searchUser.bind(this)}
            />
            <IUTextInput
              placeholder="Enter Name"
              value={this.state.user_name}
              style={{ padding:10 }}
              onChangeText={user_name => this.setState({ user_name })}
            />
            <IUTextInput
              placeholder="Enter Contact No"
              value={''+ this.state.user_contact}
              onChangeText={user_contact => this.setState({ user_contact })}
              maxLength={10}
              style={{ padding:10 }}
              keyboardType="numeric"
            />
            <IUTextInput
              value={this.state.user_address}
              placeholder="Enter Address"
              onChangeText={user_address => this.setState({ user_address })}
              maxLength={225}
              numberOfLines={5}
              multiline={true}
              style={{textAlignVertical : 'top', padding:10}}
            />
            <IButton
              title="Update User"
              onPress={this.updateUser.bind(this)}
            />
          </KeyboardAvoidingView>
        </ScrollView>
      </View>
    );
  }
}

ViewAllUsers.js

import React from 'react';
import { FlatList, Text, View, Button, Alert } from 'react-native';
import UserDao from './UserDao'


export default class ViewAllUsers extends React.Component {

  constructor(props) {
    super(props);
    this.state = {
      FlatListItems: [],
    };
    UserDao.getAllUsers((userList) => this.setItems(userList));
  }

  setItems(userList) {
    this.setState({
      FlatListItems: userList,
    });
    if(userList.length <= 0) {
      Alert.alert(
        'No users found',
        'No users found',
        [
          {
            text: 'Ok',
            onPress: () =>
              this.props.navigation.navigate('HomeScreen'),
          },
        ],
        { cancelable: false }
      );
    }
  }

  ListViewItemSeparator = () => {
    return (
      <View style={{ height: 0.2, width: '100%', backgroundColor: '#808080' }} />
    );
  };

  render() {
    return (
      <View style={{ height: "100%", width: '100%', backgroundColor: '#fff' }} >
        <FlatList style={{ position:"absolute", top:0, left:0, bottom:0, right:0, height: "100%", width: '100%', backgroundColor: '#4e3' }}
          data={this.state.FlatListItems}
          ItemSeparatorComponent={this.ListViewItemSeparator}
          keyExtractor={(item, index) => index.toString()}
          renderItem={({ item }) => (
            <View key={item.user_id} style={{ backgroundColor: 'white', padding: 20 }}>
              <Text>Id: {item.user_id}</Text>
              <Text>Name: {item.user_name}</Text>
              <Text>Contact: {item.user_contact}</Text>
              <Text>Address: {item.user_address}</Text>
            </View>
          )}
        />
      </View>
    );
  }
}

ViewUser.js

To search based on user_id

ViewUser.js

js_utilblock_filename35.htm:44 import React from 'react';
import { Text, View, Button } from 'react-native';
import { IUTextInput, IButton, UText } from './UiElements';
import UserDao from './UserDao'


export default class ViewUser extends React.Component {

  constructor(props) {
    super(props);
    this.state = {
      input_user_id: '',
      userData: '',
    };
  }

  searchUser = () => {
    const { input_user_id } = this.state;
    if(input_user_id)
      UserDao.getUser((userList) => this.setItems(userList), input_user_id);
    else {
      alert('Enter User Id or Name or search');
      this.setState({
        userData: '',
      });
    }
  };

  setItems(userList) {
    var len = userList.length;
    console.log('search res len', len);
    if (len > 0) {
      this.setState({
        userData: userList[0],
      });
    }
    else {
      alert('No user found');
      this.setState({
        userData: '',
      });
    }
  }

  render() {
    return (
      <View>
        <IUTextInput
          placeholder="Enter User Id or Name"
          onChangeText={input_user_id => this.setState({ input_user_id })}
          style={{ padding:10 }}
        />
        <IButton
          title="Search User"
          onPress={this.searchUser.bind(this)}
        />
        <View style={{ marginLeft: 35, marginRight: 35, marginTop: 10 }}>
          <Text>User Id: {this.state.userData.user_id}</Text>
          <Text>User Name: {this.state.userData.user_name}</Text>
          <Text>User Contact: {this.state.userData.user_contact}</Text>
          <Text>User Address: {this.state.userData.user_address}</Text>
        </View>
      </View>
    );
  }
}

DeleteUser.js

Attempt to delete for a user id

DeleteUser.js

import React from 'react';
import { Button, Text, View, Alert } from 'react-native';
import {IUTextInput, IButton, UText} from './UiElements';
import UserDao from './UserDao'

export default class UpdateUser extends React.Component {

  constructor(props) {
    super(props);
    this.state = {
      input_user_id: '',
    };
  }

  deleteUser = () => {
    const { input_user_id } = this.state;
    if(input_user_id)
      UserDao.deleteUser((userDeleted) => this.renderUserDeleted(userDeleted), input_user_id)
    else
      alert('Enter user id');
  };

  renderUserDeleted(userDeleted) {
    if(userDeleted) {
      Alert.alert(
        'User Deleted',
        'User Deleted',
        [
          {
            text: 'Ok',
            onPress: () =>
              this.props.navigation.navigate('HomeScreen'),
          },
        ],
        { cancelable: false }
      );
    }
    else {
      alert('Deletion Failed');
    }
  }

  render() {
    return (
      <View style={{ backgroundColor: 'white', flex: 1 }}>
        <IUTextInput
          placeholder="Enter User Id"
          onChangeText={input_user_id => this.setState({ input_user_id })}
          style={{ padding:10 }}
        />
        <IButton
          title="Delete User"
          onPress={this.deleteUser.bind(this)}
        />
      </View>
    );
  }
}