Here is an Example of SQLite Database in React Native. We are hoping you have read our previous post on Local Database in React Native App. SQLite database is one of the databases that we have discussed in our previous post. To define the SQLite we can say it is an open source SQL database that stores data to a text file on a device. We can perform all the CRUD SQL transactions in this database and it is the most common database that we have heard of for mobile development. Let’s get started.
To use SQLite database in our Example we will use SQLite3 Native Plugin for React Native for both Android and iOS. It is based on Cordova SQLite plugin.
In this Example, we will make a HomeScreen with the option to go to other screens like
- RegisterUser: To Register the User. (Create/Insert)
- ViewAllUser: To View All Users. (Read)
- ViewUser: To View Singel Users By Id. (Read)
- UpdateUser: To Update the User.(Update)
- DeleteUser: To Delete the User.
We will be having some custom components like Mybutton, Mytext, Mytextinput which will be used in place of react-native Button, Text, and TextInput.
How to Use
You just have to import the library like this:
1 | import { openDatabase } from 'react-native-sqlite-storage'; |
and open the database using
1 | var db = openDatabase({ name: 'UserDatabase.db' }); |
Now, whenever you need to make some database call you can use db variable to execute the database query.
1 2 3 4 5 6 7 | 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 response ); }); |
Please note down we are using callback function not real-time feedback because while running a query it does not provide the response instantly. Execution of query takes some time and give the response in the callback function.
To Make a React Native App
Getting started with React Native will help you to know more about the way you can make a React Native project. We are going to use react-native init to make our React Native App. Assuming that you have node installed, you can use npm to install thereact-native-cli command line utility. Open the terminal and go to the workspace and run Run the following commands to create a new React Native project
This will make a project structure with an index file named App.js in your project directory.
Installation of Dependency
To use openDatabase we need to install react-native-sqlite-storage package. To install this
Open the terminal and jump into your project
1 | cd ProjectName |
Run the following command.
1 | npm install react-native-sqlite-storage --save |
This command will copy all the dependencies into your node_module directory, You can find the directory in node_module the directory named react-native-sqlite-storage.
Linking of Library
To use SQLite library we have to make some changes in the following files:
Go to the SqliteExample> android > app > build.gradle.
Scroll down and add the following dependency.
1 | compile project(':react-native-sqlite-storage') |
Open SqliteExample> android > settings.gradle and add the following lines.
1 2 | include ':react-native-sqlite-storage' project(':react-native-sqlite-storage').projectDir = new File(rootProject.projectDir, '../node_modules/react-native-sqlite-storage/src/android') |
Open SqliteExample> android > app > src > main > java > com > sqliteexample> MainApplication.java and add the following lines in imports.
1 | import org.pgsqlite.SQLitePluginPackage; |
Scroll down and add the following line in getPackages after new MainReactPackage(). Please add a comma(,) and then add
1 | new SQLitePluginPackage() |
Here we have done with our installation of the library. If you are still facing the trouble please comment below so that we can help you out from the problem.
Database Setup
While working with the React Native it provides two option either you can use the blank DB file to start with and make your database tables at runtime or you can use the pre-populated database so that you can start working directly.
We are making a blank file with the extension .db and placing it in www folder and going to make the database tables at runtime which is a very good practice.
In this Example, we are going to use createStackNavigator component to navigate so we need to install the react-navigation library. If you are new to the React Native Navigation then this post will help you.
To install react-navigation run the following command on terminal
1 | npm install react-navigation --save |
Just to confirm the dependencies here is the screenshot of the dependencies that we are using ;
If you are on the same boat then let’s go further or please update your package.json and run npm install to install new dependencies.
For this Example, we are using the following structure. You have to make page directory in SqliteExample and components directory under that. Make the marked js files too.
You can download the code from Github and can run directly
OR
Open the project directory and replace the following code
App.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | /*Example of SQLite Database in React Native*/ import React from 'react'; import { createStackNavigator } from 'react-navigation'; import HomeScreen from './pages/HomeScreen'; import RegisterUser from './pages/RegisterUser'; import UpdateUser from './pages/UpdateUser'; import ViewUser from './pages/ViewUser'; import ViewAllUser from './pages/ViewAllUser'; import DeleteUser from './pages/DeleteUser'; const App = createStackNavigator({ HomeScreen: { screen: HomeScreen, navigationOptions: { title: 'HomeScreen', headerStyle: { backgroundColor: '#f05555' }, headerTintColor: '#ffffff', }, }, View: { screen: ViewUser, navigationOptions: { title: 'View User', headerStyle: { backgroundColor: '#f05555' }, headerTintColor: '#ffffff', }, }, ViewAll: { screen: ViewAllUser, navigationOptions: { title: 'View All User', headerStyle: { backgroundColor: '#f05555' }, headerTintColor: '#ffffff', }, }, Update: { screen: UpdateUser, navigationOptions: { title: 'Update User', headerStyle: { backgroundColor: '#f05555' }, headerTintColor: '#ffffff', }, }, Register: { screen: RegisterUser, navigationOptions: { title: 'Register User', headerStyle: { backgroundColor: '#f05555' }, headerTintColor: '#ffffff', }, }, Delete: { screen: DeleteUser, navigationOptions: { title: 'Delete User', headerStyle: { backgroundColor: '#f05555' }, headerTintColor: '#ffffff', }, }, }); export default App; |
Mybutton.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | /*Custom Button*/ import React from 'react'; import { TouchableOpacity, Text, StyleSheet } from 'react-native'; const Mybutton = props => { return ( <TouchableOpacity style={styles.button} onPress={props.customClick}> <Text style={styles.text}>{props.title}</Text> </TouchableOpacity> ); }; const styles = StyleSheet.create({ button: { alignItems: 'center', backgroundColor: '#f05555', color: '#ffffff', padding: 10, marginTop: 16, marginLeft: 35, marginRight: 35, }, text: { color: '#ffffff', }, }); export default Mybutton; |
Mytext.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /*Custom Text*/ import React from 'react'; import { TouchableHighlight, Text, StyleSheet } from 'react-native'; const Mytext = props => { return <Text style={styles.text}>{props.text}</Text>; }; const styles = StyleSheet.create({ text: { color: '#111825', fontSize: 18, marginTop: 16, marginLeft: 35, marginRight: 35, }, }); export default Mytext; |
Mytextinput.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | /*Custom TextInput*/ import React from 'react'; import { View, TextInput } from 'react-native'; const Mytextinput = props => { return ( <View style={{ marginLeft: 35, marginRight: 35, marginTop: 10, borderColor: '#007FFF', borderWidth: 1, }}> <TextInput underlineColorAndroid="transparent" placeholder={props.placeholder} placeholderTextColor="#007FFF" 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> ); }; export default Mytextinput; |
HomeScreen.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | /*Home Screen With buttons to navigate to diffrent options*/ import React from 'react'; import { View } from 'react-native'; import Mybutton from './components/Mybutton'; import Mytext from './components/Mytext'; import { openDatabase } from 'react-native-sqlite-storage'; var db = openDatabase({ name: 'UserDatabase.db' }); export default class HomeScreen extends React.Component { constructor(props) { super(props); db.transaction(function(txn) { txn.executeSql( "SELECT name FROM sqlite_master WHERE type='table' AND name='table_user'", [], function(tx, res) { console.log('item:', res.rows.length); if (res.rows.length == 0) { txn.executeSql('DROP TABLE IF EXISTS table_user', []); txn.executeSql( 'CREATE TABLE IF NOT EXISTS table_user(user_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR(20), user_contact INT(10), user_address VARCHAR(255))', [] ); } } ); }); } render() { return ( <View style={{ flex: 1, backgroundColor: 'white', flexDirection: 'column', }}> <Mytext text="SQLite Example" /> <Mybutton title="Register" customClick={() => this.props.navigation.navigate('Register')} /> <Mybutton title="Update" customClick={() => this.props.navigation.navigate('Update')} /> <Mybutton title="View" customClick={() => this.props.navigation.navigate('View')} /> <Mybutton title="View All" customClick={() => this.props.navigation.navigate('ViewAll')} /> <Mybutton title="Delete" customClick={() => this.props.navigation.navigate('Delete')} /> </View> ); } } |
RegisterUser.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | /*Screen to register the user*/ import React from 'react'; import { View, ScrollView, KeyboardAvoidingView, Alert } from 'react-native'; import Mytextinput from './components/Mytextinput'; import Mybutton from './components/Mybutton'; import { openDatabase } from 'react-native-sqlite-storage'; var db = openDatabase({ name: 'UserDatabase.db' }); export default class RegisterUser extends React.Component { constructor(props) { super(props); this.state = { user_name: '', user_contact: '', user_address: '', }; } register_user = () => { var that = this; const { user_name } = this.state; const { user_contact } = this.state; const { user_address } = this.state; alert(user_name, user_contact, user_address); if (user_name) { if (user_contact) { if (user_address) { db.transaction(function(tx) { tx.executeSql( 'INSERT INTO table_user (user_name, user_contact, user_address) VALUES (?,?,?)', [user_name, user_contact, user_address], (tx, results) => { console.log('Results', results.rowsAffected); if (results.rowsAffected > 0) { Alert.alert( 'Success', 'You are registered successfully', [ { text: 'Ok', onPress: () => that.props.navigation.navigate('HomeScreen'), }, ], { cancelable: false } ); } else { alert('Registration Failed'); } } ); }); } else { alert('Please fill Address'); } } else { alert('Please fill Contact Number'); } } else { alert('Please fill Name'); } }; render() { return ( <View style={{ backgroundColor: 'white', flex: 1 }}> <ScrollView keyboardShouldPersistTaps="handled"> <KeyboardAvoidingView behavior="padding" style={{ flex: 1, justifyContent: 'space-between' }}> <Mytextinput placeholder="Enter Name" onChangeText={user_name => this.setState({ user_name })} /> <Mytextinput placeholder="Enter Contact No" onChangeText={user_contact => this.setState({ user_contact })} maxLength={10} keyboardType="numeric" /> <Mytextinput placeholder="Enter Address" onChangeText={user_address => this.setState({ user_address })} maxLength={225} numberOfLines={5} multiline={true} style={{ textAlignVertical: 'top' }} /> <Mybutton title="Submit" customClick={this.register_user.bind(this)} /> </KeyboardAvoidingView> </ScrollView> </View> ); } } |
UpdateUser.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 | /*Screen to update the user*/ import React from 'react'; import { View, YellowBox, ScrollView, KeyboardAvoidingView, Alert, } from 'react-native'; import Mytextinput from './components/Mytextinput'; import Mybutton from './components/Mybutton'; import { openDatabase } from 'react-native-sqlite-storage'; var db = openDatabase({ name: 'UserDatabase.db' }); 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; console.log(this.state.input_user_id); db.transaction(tx => { tx.executeSql( 'SELECT * FROM table_user where user_id = ?', [input_user_id], (tx, results) => { var len = results.rows.length; console.log('len',len); if (len > 0) { console.log(results.rows.item(0).user_contact); this.setState({ user_name:results.rows.item(0).user_name, }); this.setState({ user_contact:results.rows.item(0).user_contact, }); this.setState({ user_address:results.rows.item(0).user_address, }); }else{ alert('No user found'); this.setState({ user_name:'', user_contact:'', user_address:'', }); } } ); }); }; updateUser = () => { var that=this; const { input_user_id } = this.state; const { user_name } = this.state; const { user_contact } = this.state; const { user_address } = this.state; if (user_name){ if (user_contact){ if (user_address){ db.transaction((tx)=> { tx.executeSql( 'UPDATE table_user set user_name=?, user_contact=? , user_address=? where user_id=?', [user_name, user_contact, user_address, input_user_id], (tx, results) => { console.log('Results',results.rowsAffected); if(results.rowsAffected>0){ Alert.alert( 'Success', 'User updated successfully', [ {text: 'Ok', onPress: () => that.props.navigation.navigate('HomeScreen')}, ], { cancelable: false } ); }else{ alert('Updation Failed'); } } ); }); }else{ alert('Please fill Address'); } }else{ alert('Please fill Contact Number'); } }else{ alert('Please fill Name'); } }; render() { return ( <View style={{ backgroundColor: 'white', flex: 1 }}> <ScrollView keyboardShouldPersistTaps="handled"> <KeyboardAvoidingView behavior="padding" style={{ flex: 1, justifyContent: 'space-between' }}> <Mytextinput placeholder="Enter User Id" onChangeText={input_user_id => this.setState({ input_user_id })} /> <Mybutton title="Search User" customClick={this.searchUser.bind(this)} /> <Mytextinput placeholder="Enter Name" value={this.state.user_name} onChangeText={user_name => this.setState({ user_name })} /> <Mytextinput placeholder="Enter Contact No" value={''+ this.state.user_contact} onChangeText={user_contact => this.setState({ user_contact })} maxLength={10} keyboardType="numeric" /> <Mytextinput value={this.state.user_address} placeholder="Enter Address" onChangeText={user_address => this.setState({ user_address })} maxLength={225} numberOfLines={5} multiline={true} style={{textAlignVertical : 'top'}} /> <Mybutton title="Update User" customClick={this.updateUser.bind(this)} /> </KeyboardAvoidingView> </ScrollView> </View> ); } } |
ViewAllUser.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | /*Screen to view all the user*/ import React from 'react'; import { ListView, Text, View } from 'react-native'; import { openDatabase } from 'react-native-sqlite-storage'; var db = openDatabase({ name: 'UserDatabase.db' }); export default class ViewAllUser extends React.Component { constructor(props) { super(props); const ds = new ListView.DataSource({ rowHasChanged: (r1, r2) => r1 !== r2, }); this.state = { dataSource: ds.cloneWithRows([]), }; db.transaction(tx => { tx.executeSql('SELECT * FROM table_user', [], (tx, results) => { var temp = []; for (let i = 0; i < results.rows.length; ++i) { temp.push(results.rows.item(i)); } this.setState({ dataSource: ds.cloneWithRows(temp), }); }); }); } ListViewItemSeparator = () => { return ( <View style={{ height: 0.5, width: '100%', backgroundColor: '#000' }} /> ); }; render() { return ( <View> <ListView dataSource={this.state.dataSource} renderSeparator={this.ListViewItemSeparator} renderRow={rowData => ( <View style={{ backgroundColor: 'white', padding: 20 }}> <Text>Id: {rowData.user_id}</Text> <Text>Name: {rowData.user_name}</Text> <Text>Contact: {rowData.user_contact}</Text> <Text>Address: {rowData.user_address}</Text> </View> )} /> </View> ); } } |
ViewUser.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | /*Screen to view single user*/ import React from 'react'; import { Text, View, Button } from 'react-native'; import Mytextinput from './components/Mytextinput'; import Mybutton from './components/Mybutton'; import { openDatabase } from 'react-native-sqlite-storage'; var db = openDatabase({ name: 'UserDatabase.db' }); export default class ViewUser extends React.Component { constructor(props) { super(props); this.state = { input_user_id: '', userData: '', }; } searchUser = () => { const { input_user_id } = this.state; console.log(this.state.input_user_id); db.transaction(tx => { tx.executeSql( 'SELECT * FROM table_user where user_id = ?', [input_user_id], (tx, results) => { var len = results.rows.length; console.log('len', len); if (len > 0) { this.setState({ userData: results.rows.item(0), }); } else { alert('No user found'); this.setState({ userData: '', }); } } ); }); }; render() { return ( <View> <Mytextinput placeholder="Enter User Id" onChangeText={input_user_id => this.setState({ input_user_id })} /> <Mybutton title="Search User" customClick={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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | /*Screen to delete the user*/ import React from 'react'; import { Button, Text, View, Alert } from 'react-native'; import Mytextinput from './components/Mytextinput'; import Mybutton from './components/Mybutton'; import { openDatabase } from 'react-native-sqlite-storage'; var db = openDatabase({ name: 'UserDatabase.db' }); export default class UpdateUser extends React.Component { constructor(props) { super(props); this.state = { input_user_id: '', }; } deleteUser = () => { var that = this; const { input_user_id } = this.state; db.transaction(tx => { tx.executeSql( 'DELETE FROM table_user where user_id=?', [input_user_id], (tx, results) => { console.log('Results', results.rowsAffected); if (results.rowsAffected > 0) { Alert.alert( 'Success', 'User deleted successfully', [ { text: 'Ok', onPress: () => that.props.navigation.navigate('HomeScreen'), }, ], { cancelable: false } ); } else { alert('Please insert a valid User Id'); } } ); }); }; render() { return ( <View style={{ backgroundColor: 'white', flex: 1 }}> <Mytextinput placeholder="Enter User Id" onChangeText={input_user_id => this.setState({ input_user_id })} /> <Mybutton title="Delete User" customClick={this.deleteUser.bind(this)} /> </View> ); } } |
To Run the React Native App
Open the terminal again and jump into your project using. To run the project on an Android Virtual Device or on real debugging device
or on the iOS Simulator by running
This is how you can use SQLite Database in React Native Application. If you have any doubt or you want to share something about the topic you can comment below or contact us here. There will be more posts coming soon. Stay tuned!
Hope you liked it. 🙂
How useful was this post?
Click on a star to rate us!
Average rating / 5. Vote count:
We are sorry that this post was not useful for you!
Let us improve this post!
Thanks for your feedback!
I am doing POC on Sqlite with React Native. when i try execute below line getting complie time error. import org.pgsqlite.SQLitePluginPackage, it is expecting from value
Can you please check the SQLite version you are using?
We have used the following version for the example. If you are using any other version then please let me know so that we can run the trial with that or share the screenshot of the log to help you out.
“react-native-sqlite-storage”: “^3.3.6”,
Hello Naresh,
Finally, I faced the same problem now and find a solution. Please open the following file
SqliteProject/node_modules/react-native-sqlite-storage/src/android/build.gradle
and replace
compile ‘com.facebook.react:react-native:0.14.+’ (Or other version)
with
compile ‘com.facebook.react:react-native:+’
This will solve your problem.
Hello,
why using setstate in constructor in VIEWAllUSERS.js ?
because in react lifecycle we should not use setstate in constructor
Setting up state in constructor means you are assigning some default value to the state variable. If you do not want to setState in constructor you can skip it but if you try to get the value without setting the value then it will give you a state undefined error so you have to remember that first set the state and then get the value from state.
I am using SQLite version 3.3.9. I am trying to run on the ios, i get the following error on the screen –
null is not an object (evaluating ‘NativeModules[“SQLite”][method]’)
exec
sqlite.core.js:88:26
open
sqlite.core.js:279:16
SQLitePlugin
sqlite.core.js:110:12
sqlite.core.js:778:26
sqlite.core.js:77:22
HomeScreen.js:30:11
loadModuleImplementation
require.js:321:6
App.js:13
loadModuleImplementation
require.js:321:6
index.js:7
loadModuleImplementation
require.js:321:6
guardedLoadModule
require.js:199:45
global code
:0
This example is completely for android. If you want to run it in IOS you need to do some configuration which are listed in the following link
http://www.npmjs.com/package/react-native-sqlite-storage
Hello Tara,
Finally, I faced the same problem now and find a solution. Please open following file
SqliteProject/node_modules/react-native-sqlite-storage/src/android/build.gradle
and replace
compile ‘com.facebook.react:react-native:0.14.+’
with
compile ‘com.facebook.react:react-native:+’
This will solve your problem.
Is there a web ( not react-native ) version of this tutorial?
Are you talking about React?
Web platform does not support SQL database. You can use the browser database to store the data in your browser. Can you please confirm which web technology you want to use?
hello,
how to use pre created sqlite database in react-native for both android and ios using window os
ou can follow the simple steps from the following official URL. This should work.
https://www.npmjs.com/package/react-native-sqlite-storage#setting-up-your-project-to-import-a-pre-populated-sqlite-database-from-application-for-ios
Its showing duplicate declaration ? please tell me solution
Can you please share the line number where you are facing the error?