React Native SQLite Database
Here is an example of the 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.
For the SQLite database, we will use the SQLite3 Native Plugin which will work in both Android and iOS. It is based on the 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 react-native-sqlite-storage?
You just have to import the library like this:
import { openDatabase } from 'react-native-sqlite-storage';and open the database using
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.
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
);
});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 command line interface to make our React Native App.
If you have previously installed a global react-native-cli package, please remove it as it may cause unexpected issues:
npm uninstall -g react-native-cli @react-native-community/cliRun the following commands to create a new React Native project
npx react-native init ProjectNameIf you want to start a new project with a specific React Native version, you can use the --version argument:
npx react-native init ProjectName --version X.XX.XNote If the above command is failing, you may have old version of react-native or react-native-cli installed globally on your pc. Try uninstalling the cli and run the cli using npx.
This will make a project structure with an index file named App.js in your project directory.
Installation of Dependencies
To install the dependencies open the terminal and jump into your project
cd ProjectName1. Install react-native-sqlite-storage dependency to use SQLite
npm install react-native-sqlite-storage --save2. Install following dependencies for react-navigation
npm install @react-navigation/native --savenpm install @react-navigation/native-stack --savenpm install react-native-screens react-native-safe-area-context --savereact-native-screens package requires one additional configuration step to properly work on Android devices. Edit MainActivity.java file which is located in android/app/src/main/java/<your package name>/MainActivity.java.
Add the following code to the body of MainActivity class:
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(null);
}and make sure to add the following import statement at the top of this file below your package statement:
import android.os.Bundle;This change is required to avoid crashes related to View state being not persisted consistently across Activity restarts. These commands will copy all the dependencies into your node_module directory.
CocoaPods Installation
Please use the following command to install CocoaPods
npx pod-installDatabase Setup
While working with React Native it provides two option:
- You can make the database in runtime.
- You can use the pre-populated database so that you can start working directly.
In this example, we will make the database on runtime and for the pre-populated database, You can visit Example of Pre Populated SQLite Database in React Native.
Project Structure
We will perform complete CRUD operation in this example. Please create the following project structure and copy the code given below
Code for SQLite Database
Open the project directory and replace the following code
App.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
import * as React from 'react';
import { NavigationContainer } from '@react-navigation/native';
import { createNativeStackNavigator } from '@react-navigation/native-stack';
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 Stack = createNativeStackNavigator();
const App = () => {
return (
<NavigationContainer>
<Stack.Navigator initialRouteName="HomeScreen">
<Stack.Screen
name="HomeScreen"
component={HomeScreen}
options={{
title: 'Home', //Set Header Title
headerStyle: {
backgroundColor: '#f4511e', //Set Header color
},
headerTintColor: '#fff', //Set Header text color
headerTitleStyle: {
fontWeight: 'bold', //Set Header text style
},
}}
/>
<Stack.Screen
name="View"
component={ViewUser}
options={{
title: 'View User', //Set Header Title
headerStyle: {
backgroundColor: '#f4511e', //Set Header color
},
headerTintColor: '#fff', //Set Header text color
headerTitleStyle: {
fontWeight: 'bold', //Set Header text style
},
}}
/>
<Stack.Screen
name="ViewAll"
component={ViewAllUser}
options={{
title: 'View Users', //Set Header Title
headerStyle: {
backgroundColor: '#f4511e', //Set Header color
},
headerTintColor: '#fff', //Set Header text color
headerTitleStyle: {
fontWeight: 'bold', //Set Header text style
},
}}
/>
<Stack.Screen
name="Update"
component={UpdateUser}
options={{
title: 'Update User', //Set Header Title
headerStyle: {
backgroundColor: '#f4511e', //Set Header color
},
headerTintColor: '#fff', //Set Header text color
headerTitleStyle: {
fontWeight: 'bold', //Set Header text style
},
}}
/>
<Stack.Screen
name="Register"
component={RegisterUser}
options={{
title: 'Register User', //Set Header Title
headerStyle: {
backgroundColor: '#f4511e', //Set Header color
},
headerTintColor: '#fff', //Set Header text color
headerTitleStyle: {
fontWeight: 'bold', //Set Header text style
},
}}
/>
<Stack.Screen
name="Delete"
component={DeleteUser}
options={{
title: 'Delete User', //Set Header Title
headerStyle: {
backgroundColor: '#f4511e', //Set Header color
},
headerTintColor: '#fff', //Set Header text color
headerTitleStyle: {
fontWeight: 'bold', //Set Header text style
},
}}
/>
</Stack.Navigator>
</NavigationContainer>
);
};
export default App;Mybutton.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// 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
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// Custom Text
import React from 'react';
import { 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
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// 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
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
import React, { useEffect } from 'react';
import { View, Text, SafeAreaView } 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' });
const HomeScreen = ({ navigation }) => {
useEffect(() => {
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))',
[]
);
}
}
);
});
}, []);
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1, backgroundColor: 'white' }}>
<View style={{ flex: 1 }}>
<Mytext text="SQLite Example" />
<Mybutton
title="Register"
customClick={() => navigation.navigate('Register')}
/>
<Mybutton
title="Update"
customClick={() => navigation.navigate('Update')}
/>
<Mybutton
title="View"
customClick={() => navigation.navigate('View')}
/>
<Mybutton
title="View All"
customClick={() => navigation.navigate('ViewAll')}
/>
<Mybutton
title="Delete"
customClick={() => navigation.navigate('Delete')}
/>
</View>
<Text
style={{
fontSize: 18,
textAlign: 'center',
color: 'grey'
}}>
Example of SQLite Database in React Native
</Text>
<Text
style={{
fontSize: 16,
textAlign: 'center',
color: 'grey'
}}>
www.aboutreact.com
</Text>
</View>
</SafeAreaView>
);
};
export default HomeScreen;RegisterUser.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// Screen to register the user
import React, { useState } from 'react';
import {
View,
ScrollView,
KeyboardAvoidingView,
Alert,
SafeAreaView,
Text,
} 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' });
const RegisterUser = ({ navigation }) => {
let [userName, setUserName] = useState('');
let [userContact, setUserContact] = useState('');
let [userAddress, setUserAddress] = useState('');
let register_user = () => {
console.log(userName, userContact, userAddress);
if (!userName) {
alert('Please fill name');
return;
}
if (!userContact) {
alert('Please fill Contact Number');
return;
}
if (!userAddress) {
alert('Please fill Address');
return;
}
db.transaction(function (tx) {
tx.executeSql(
'INSERT INTO table_user (user_name, user_contact, user_address) VALUES (?,?,?)',
[userName, userContact, userAddress],
(tx, results) => {
console.log('Results', results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(
'Success',
'You are Registered Successfully',
[
{
text: 'Ok',
onPress: () => navigation.navigate('HomeScreen'),
},
],
{ cancelable: false }
);
} else alert('Registration Failed');
}
);
});
};
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1, backgroundColor: 'white' }}>
<View style={{ flex: 1 }}>
<ScrollView keyboardShouldPersistTaps="handled">
<KeyboardAvoidingView
behavior="padding"
style={{ flex: 1, justifyContent: 'space-between' }}>
<Mytextinput
placeholder="Enter Name"
onChangeText={
(userName) => setUserName(userName)
}
style={{ padding: 10 }}
/>
<Mytextinput
placeholder="Enter Contact No"
onChangeText={
(userContact) => setUserContact(userContact)
}
maxLength={10}
keyboardType="numeric"
style={{ padding: 10 }}
/>
<Mytextinput
placeholder="Enter Address"
onChangeText={
(userAddress) => setUserAddress(userAddress)
}
maxLength={225}
numberOfLines={5}
multiline={true}
style={{ textAlignVertical: 'top', padding: 10 }}
/>
<Mybutton title="Submit" customClick={register_user} />
</KeyboardAvoidingView>
</ScrollView>
</View>
<Text
style={{
fontSize: 18,
textAlign: 'center',
color: 'grey'
}}>
Example of SQLite Database in React Native
</Text>
<Text
style={{
fontSize: 16,
textAlign: 'center',
color: 'grey'
}}>
www.aboutreact.com
</Text>
</View>
</SafeAreaView>
);
};
export default RegisterUser;UpdateUser.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// Screen to update the user
import React, { useState } from 'react';
import {
View,
ScrollView,
KeyboardAvoidingView,
Alert,
SafeAreaView,
Text,
} 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' });
const UpdateUser = ({ navigation }) => {
let [inputUserId, setInputUserId] = useState('');
let [userName, setUserName] = useState('');
let [userContact, setUserContact] = useState('');
let [userAddress, setUserAddress] = useState('');
let updateAllStates = (name, contact, address) => {
setUserName(name);
setUserContact(contact);
setUserAddress(address);
};
let searchUser = () => {
console.log(inputUserId);
db.transaction((tx) => {
tx.executeSql(
'SELECT * FROM table_user where user_id = ?',
[inputUserId],
(tx, results) => {
var len = results.rows.length;
if (len > 0) {
let res = results.rows.item(0);
updateAllStates(
res.user_name,
res.user_contact,
res.user_address
);
} else {
alert('No user found');
updateAllStates('', '', '');
}
}
);
});
};
let updateUser = () => {
console.log(inputUserId, userName, userContact, userAddress);
if (!inputUserId) {
alert('Please fill User id');
return;
}
if (!userName) {
alert('Please fill name');
return;
}
if (!userContact) {
alert('Please fill Contact Number');
return;
}
if (!userAddress) {
alert('Please fill Address');
return;
}
db.transaction((tx) => {
tx.executeSql(
'UPDATE table_user set user_name=?, user_contact=? , user_address=? where user_id=?',
[userName, userContact, userAddress, inputUserId],
(tx, results) => {
console.log('Results', results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(
'Success',
'User updated successfully',
[
{
text: 'Ok',
onPress: () => navigation.navigate('HomeScreen'),
},
],
{ cancelable: false }
);
} else alert('Updation Failed');
}
);
});
};
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1, backgroundColor: 'white' }}>
<View style={{ flex: 1 }}>
<ScrollView keyboardShouldPersistTaps="handled">
<KeyboardAvoidingView
behavior="padding"
style={{ flex: 1, justifyContent: 'space-between' }}>
<Mytextinput
placeholder="Enter User Id"
style={{ padding: 10 }}
onChangeText={
(inputUserId) => setInputUserId(inputUserId)
}
/>
<Mybutton
title="Search User"
customClick={searchUser}
/>
<Mytextinput
placeholder="Enter Name"
value={userName}
style={{ padding: 10 }}
onChangeText={
(userName) => setUserName(userName)
}
/>
<Mytextinput
placeholder="Enter Contact No"
value={'' + userContact}
onChangeText={
(userContact) => setUserContact(userContact)
}
maxLength={10}
style={{ padding: 10 }}
keyboardType="numeric"
/>
<Mytextinput
value={userAddress}
placeholder="Enter Address"
onChangeText={
(userAddress) => setUserAddress(userAddress)
}
maxLength={225}
numberOfLines={5}
multiline={true}
style={{ textAlignVertical: 'top', padding: 10 }}
/>
<Mybutton
title="Update User"
customClick={updateUser}
/>
</KeyboardAvoidingView>
</ScrollView>
</View>
<Text
style={{
fontSize: 18,
textAlign: 'center',
color: 'grey'
}}>
Example of SQLite Database in React Native
</Text>
<Text
style={{
fontSize: 16,
textAlign: 'center',
color: 'grey'
}}>
www.aboutreact.com
</Text>
</View>
</SafeAreaView>
);
};
export default UpdateUser;ViewAllUser.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// Screen to view all the user*/
import React, { useState, useEffect } from 'react';
import { FlatList, Text, View, SafeAreaView } from 'react-native';
import { openDatabase } from 'react-native-sqlite-storage';
var db = openDatabase({ name: 'UserDatabase.db' });
const ViewAllUser = () => {
let [flatListItems, setFlatListItems] = useState([]);
useEffect(() => {
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));
setFlatListItems(temp);
}
);
});
}, []);
let listViewItemSeparator = () => {
return (
<View
style={{
height: 0.2,
width: '100%',
backgroundColor: '#808080'
}}
/>
);
};
let listItemView = (item) => {
return (
<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>
);
};
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1, backgroundColor: 'white' }}>
<View style={{ flex: 1 }}>
<FlatList
data={flatListItems}
ItemSeparatorComponent={listViewItemSeparator}
keyExtractor={(item, index) => index.toString()}
renderItem={({ item }) => listItemView(item)}
/>
</View>
<Text
style={{
fontSize: 18,
textAlign: 'center',
color: 'grey'
}}>
Example of SQLite Database in React Native
</Text>
<Text
style={{
fontSize: 16,
textAlign: 'center',
color: 'grey'
}}>
www.aboutreact.com
</Text>
</View>
</SafeAreaView>
);
};
export default ViewAllUser;ViewUser.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// Screen to view single user
import React, { useState } from 'react';
import { Text, View, SafeAreaView } 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' });
const ViewUser = () => {
let [inputUserId, setInputUserId] = useState('');
let [userData, setUserData] = useState({});
let searchUser = () => {
console.log(inputUserId);
setUserData({});
db.transaction((tx) => {
tx.executeSql(
'SELECT * FROM table_user where user_id = ?',
[inputUserId],
(tx, results) => {
var len = results.rows.length;
console.log('len', len);
if (len > 0) {
setUserData(results.rows.item(0));
} else {
alert('No user found');
}
}
);
});
};
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1, backgroundColor: 'white' }}>
<View style={{ flex: 1 }}>
<Mytextinput
placeholder="Enter User Id"
onChangeText={
(inputUserId) => setInputUserId(inputUserId)
}
style={{ padding: 10 }}
/>
<Mybutton title="Search User" customClick={searchUser} />
<View
style={{
marginLeft: 35,
marginRight: 35,
marginTop: 10
}}>
<Text>User Id: {userData.user_id}</Text>
<Text>User Name: {userData.user_name}</Text>
<Text>User Contact: {userData.user_contact}</Text>
<Text>User Address: {userData.user_address}</Text>
</View>
</View>
<Text
style={{
fontSize: 18,
textAlign: 'center',
color: 'grey'
}}>
Example of SQLite Database in React Native
</Text>
<Text
style={{
fontSize: 16,
textAlign: 'center',
color: 'grey'
}}>
www.aboutreact.com
</Text>
</View>
</SafeAreaView>
);
};
export default ViewUser;DeleteUser.js
// Example: Example of SQLite Database in React Native
// https://aboutreact.com/example-of-sqlite-database-in-react-native
// Screen to delete the user
import React, { useState } from 'react';
import { Text, View, Alert, SafeAreaView } 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' });
const DeleteUser = ({ navigation }) => {
let [inputUserId, setInputUserId] = useState('');
let deleteUser = () => {
db.transaction((tx) => {
tx.executeSql(
'DELETE FROM table_user where user_id=?',
[inputUserId],
(tx, results) => {
console.log('Results', results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(
'Success',
'User deleted successfully',
[
{
text: 'Ok',
onPress: () => navigation.navigate('HomeScreen'),
},
],
{ cancelable: false }
);
} else {
alert('Please insert a valid User Id');
}
}
);
});
};
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1, backgroundColor: 'white' }}>
<View style={{ flex: 1 }}>
<Mytextinput
placeholder="Enter User Id"
onChangeText={
(inputUserId) => setInputUserId(inputUserId)
}
style={{ padding: 10 }}
/>
<Mybutton title="Delete User" customClick={deleteUser} />
</View>
<Text
style={{
fontSize: 18,
textAlign: 'center',
color: 'grey'
}}>
Example of SQLite Database in React Native
</Text>
<Text
style={{
fontSize: 16,
textAlign: 'center',
color: 'grey'
}}>
www.aboutreact.com
</Text>
</View>
</SafeAreaView>
);
};
export default DeleteUser;To Run the React Native App
Open the terminal again and jump into your project using.
cd ProjectName1. Start Metro Bundler
First, you will need to start Metro, the JavaScript bundler that ships with React Native. To start Metro bundler run following command:
npx react-native startOnce you start Metro Bundler it will run forever on your terminal until you close it. Let Metro Bundler run in its own terminal. Open a new terminal and run the application.
2. Start React Native Application
To run the project on an Android Virtual Device or on real debugging device:
npx react-native run-androidor on the iOS Simulator by running (macOS only)
npx react-native run-iosOutput Screenshots
This is how you can use SQLite Database in React Native Application. If you want to see how to use a pre-populated SQLite database then please visit Example of Pre Populated SQLite Database in React Native.
If you have any doubts 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. 🙂
Very helpful post thank you great post
Hi, Where and How to handle onUpgrade() version increase of DB. Thanks for good article. It really helped alot to learn React Native with SQLITE.
Hello Nikhil,
I think you are from the Android Development Background. Let me tell you in case of React Native you don’t have the way like android to upgrade the database, if you want to upgrade the database then you need to drop the table by the query. For this, you can manage the database version on your own.
If you need some suggestion then you can use the following example
Example: If you are releasing your application put the application version somewhere on the server and check the version from the application. if the application version and the server application version is same then go ahead else drop the old database and make a new one.
Hi, in that case it is fine. But my question was, I have released the app with some Database version and if in next release I need to alter the table without deleting existing data, As in android and in iOS we do have version management, onUpgrade function will call, How to manage such scenario as its very important in offline apps. Thanks!!
Hello Nikhil,
In that case, you have to manage it using the database version. For example, I have a customer table in an offline database with [id, name, address] now you need to add pin code in the table. So in the next release create a global variable with name DB_VERSION =1.1 and make sync varriableDB_VERSION.
Now on the starting of the app check whether async DB_VERSION is equal to DB_VERSION in the global variable. If they are equal just move else
1. Copy the data from the old customer table into an array.
2. Drop the old table.
3. Create a new one.
4. Insert the copied array from array to new table.
5. insert the DB_VESRION of the global variable into Async Storage.
Thus you can alter the table in the live application. I have done it one of my application. Can you please see and suggest if any problem in this concept? as it is working fine in my case.
I have a question I created a base like in the point: You can make the database in runtime. How can I view the base physically in SQLite? e.g. View records in the database.
1. For Android connect the device which has the application now open the project in Android Studio and You can see the option called “Device File Explore” in the rightmost upper/bottom corner.
2. Now click the option and data > data and your application package name which can be found in the AndroidManifest.xml file (It is something link xyz.abd.kjh).
3. you can right-click to save the database and it can be opened via SQLite Browser.
Can you please check your inbox for further information.
Hello,
I read your post on local storage in React Native and It was very helpful compared to other post I have been reading.
Thank You, for that first.
Now my question is,
I need offline storage,
And when I mean that, I need the database to be persisted even after the app is un installed.
How to go about that ?
Hello Madara,
Thank you for your words. 🙂
If we talk about your query then it is not possible. Device OS removes the database and everything related to the application when we uninstall the app. The only thing that remains is the external files like images and videos.
If you want you can export the database and can take the backup of it and when user installed the app you can import that again but the files are visible to the user so he/she can remove it also.
Hello Snehal,
I am able to see data over android mobile. But while opening the database locally on laptop which is connected to mobile , it is showing empty db in sqlite browser. I followed the steps that you mentioned while replying to @ola’s question.
How to proceed further?
One more doubt, are we able to have .db copy in phone memory also? If yes, what is the location.
The DB in the laptop is for the reference. It is like an APK builder picks the database from the laptop and makes the APK with that and manages the database further in the device only(Which is a copy of the database). The DB in the laptop is for the reference. Do you want to see the database of the app? in the SQLite Browser?
Here is the URL for the tutorial on How to See SQLite Database Data Saved in Device using Android Studio
Hi Snehal Agrawal,
It is very helpful to me. I would like to display ten items from sqlite DB by swipeing left and right in react native. can you please give an example solution..? By which I can learn more for my project.. Kindly help me out in this situation.. Thank you.
You can use This SQLite example to store the data and get the data anytime. Now for the left to right swipe you can use
React Native Swipeable Card View UI like Tinder example.
Bonjour Snehal, bon travail.
Au fait est ce que vous pouvez créer une base de données sqlite avec 2 o 3 tables avec des clés étrangères juste comme un exemple. Je galère vraiment sur ça
This is just simple queries like we do in our SQL. I am unable to do it this week may be in next week. Are you ok with that?
Thanks for this tutorial.
Hi Agarawal,
Great tutorial……..
[From Brazil]
Olá, eu gosto muito do seu site.
Os exemplos são sensacionais!!
Me ajudam muito a entender e aprender React-Native.
Parabéns e obrigado.
Thank you a million time Sir !!!
you just saved me from many sleepless nights sir !!!
I’m so grateful for your help sir
Hi
Thanks for great tutorial..I just want to save array into table . So Please guide me the syntax for table creation
Eg: [‘cat’,’dog’,’rat’] this array should save into table
You have to convert it to the JSON and then have to store it as a string and at the time of fetch again convert it to the JSON.
Thanks for the response.
If you don’t mind please provide syntax for where clause
Eg:
const save_name = this.state.photos[i]
‘SELECT id FROM picture WHERE pic_name= ?;’, [save_name]
In the above “picture” is my table name, “pic_name” is column name in “picture” table.
I want to check “save_name(eg:hill.jpg)” is present in “picture table” or not.
Please help me.
Can you please try on the tool I have posted about https://aboutreact.com/sqlite-query-tester/
I want to pass dynamic array values into where clause IN condition.
Please suggest me a valid syntax.
var query=`SELECT * FROM table_name nm WHERE nm.meta_data IN(?)`
var params=[‘bird’,’elephant’] //dynamic array
tx.executeSql(query,params,(tx, results) => {})}
hey hi, I understand the situation. I have faced the same situation and found a simple way. I am not sure if it the right way or not but it worked for me. the param in your case needs a string only so you can remove the square bracket and can use it as –>
It will give you array as string and you can use it.
Thank you snehal. I appreciate you for responding to everyone’s doubts in this comment section.
I followed the above suggestion. But it is asking for ‘?’ which is equal to the number of elements in an array
For eg:
var query=SELECT * FROM table_name nm WHERE nm.meta_data IN(?,?)// as params have two elements.It is asking two question mark
var params=[‘bird’,’elephant’]
I figure out another solution.
Instead of using IN condition, I use the “OR” clause by appending it to the main query.
var search_name=[‘bird’,’evening’] //dynamic array
var params = [];
var query=`SELECT DISTINCT np.pic_uri AS uri FROM new_metadata nm
INNER JOIN new_picture np ON nm.picture_id=np.id `;
for(let i=0;i<search_name.length;i++)
{
if(i == 0){
query = query + `nm.meta_data = ? `;
}else{
query = query + `OR nm.meta_data = ? `;
}
params.push(search_name[i]);
}
final query= SELECT DISTINCT np.pic_uri AS uri FROM new_metadata nm INNER JOIN new_picture np ON nm.picture_id=np.id WHERE nm.meta_data = ? OR nm.meta_data = ?
This "OR nm.meta_data = ? " is being appended to the main query based on number of elements in a dynamic array.
Thanks for sharing. I have also sent you some other ways. Please check your mailbox too.
Thanks for this tutorial.
Hi, Snehal!
I’m Vitor, from Brazil. I apreciated your tutorial very much!
Please keep working on these powerful tutorials about React Native, it helped me a lot and I’m sure it’ll help everybody interested in mobile dev.
Thank you!
Very nice and clean. Worked on first launch (which surprised me given the age of the code). I plan to update it and post it to my public github when I get the next opportunity.
People need more sample code that just works. Given my recent experience with time-wasting broken demos, this is a welcome change.
Thank You Brother, it is very usefull tutorial for beginner to advance.Thanks for again and again
Hi can you please guide me how to truncate table or reset primary key from 1 in SQLite using React Native?
Either change the database name or run truncate table query given below
Truncate is a two step process:
1. Delete all data from that table using
2. Then
I want to appreciate for the skills I have learnt as result of this tutorial. Thank you so much.
how to run the same with expo setup ??
This package will not work with Expo, My suggestion is to use Expo SQLite for Expo (It is just a suggestion I do not recommend to use Expo for the React Native development)
Please refer to https://expo.fyi/no-registered-application for more information
i get this when i run it on expo.
Unable to start your application. Please refer to https://expo.fyi/no-registered-application for more information.
Any idea why?
It will not run on Expo, You can see expo-sqlite
I want to create two tables. And search by id from first table of id found from table 1 then store id and current date in second table.
Please use
Hello bro, thank you for the post, i wanna ask a question, for every table , should i create a database file ? i need join between tables, how can i do that in best way ? Thank you for your awesome posts, it is really great.
Yeah, you can create one database file separately which can be used to initialise the database but I suggest not to use join in mobile.
You have the database in local mobile app so you can model the database in a way that you can access the data in easy manner without join (not to use Database normalisation) as you have limited data related to single user only.
Hello!
Could you please, give information about versions of your packages? I mean react, react-native etc. Your example doesn’t work on newest version of react and react-native.
Best 🙂
Let me try once and update the example.
Hello how to make the username unique?
Hi Snehal, thank you for your guide. It is very helpful. Quick question, I’m a total newbie to SQLite. Can I input multiple line of query in a single .executeSql() function? For example, I want to INSERT multiple data.
I do not have time for now to do that but I’ll surely update the example to include the same but you can use this to get the idea how we can do it.
Hi ,This is not working on ios?
I have tried on iOS but not with the latest one, can you please share the device you have tested on?
Hi Snehal,
could you please elaborate the queries from HomeScreen.js? like three of them if possible?
thanks in advance.
1. First is to find the table in the database
2. Second to Delete table from the database if exists
3. Third to create new table
Hi There ,
i am trying to use promise with the following code
import SQLite from ‘react-native-sqlite-storage’;
SQLite.DEBUG(true);
SQLite.enablePromise(true);
const db = SQLite.openDatabase({name: ‘mydb.db’});
export const initdb = () => {
const promise = new Promise((resolve, reject) => {
db.transaction(tx => {
tx.executeSql(‘DROP TABLE transactions’, []);
tx.executeSql(
‘CREATE TABLE IF NOT EXISTS transactions (id INTEGER PRIMARY KEY NOT NULL, date TEXT NOT NULL, start TEXT NOT NULL, end TEXT NOT NULL);’,
[],
() => {
resolve();
},
(_, err) => {
reject(err);
},
);
});
});
return promise;
};
ITIS EXECUTED AS
initdb()
.then(() => {
console.log(‘Sucessfully Initialised Databse ‘);
})
.catch(error => {
console.log(‘Failed Database’);
console.log(error);
});
However it is spitting out the following error
Promise based runtime ready
LOG OPEN database: mydb.db
LOG SQLite.open({“name”:”mydb.db”,”dblocation”:”nosync”})
LOG Failed Database
LOG [TypeError: undefined is not a function (near ‘…db.transaction…’)]
LOG Running “timekeep” with {“rootTag”:1,”initialProps”:{}}
Hii..your code is working fine but when i am inserting some new values into this same table it is not working ,
example:-
tx.executeSql(
‘INSERT INTO table_user (user_name, user_contact, user_address,user_gender) VALUES (?,?,?,?,)’,
[userName, userContact, userAddress,userGender],
i have added this userGender in all the files as you did for the user_name etc
can you pls tell me why this is happening and i am not gettinng any type of error also
Have you updated the create table query as well?
Hii..your code is working fine but when i am inserting some new values into this same table it is not working ,
i faced similar problem.
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), user_gender VARCHAR(255));
Dam the table creation query. but registration is not done
Hii..your code is working fine but when i am inserting some new values into this same table it is not working ,
i faced similar problem.
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), user_gender VARCHAR(255));
Dam the table creation query. but registration is not done
Have dropped your old table? If you have ran this program once then your DB has this table already just delete this table and create new one.
great post I just have 1 query, my app is currently an online app where all data comes from API including some assets like pdf and images and I want my app to run offline too so I make a master API that consists of all data of my app, So how can I put this API data into my database and use in my project, I am new in the database so I m little scared, can anyone help me?
Hi, Can you provide the query of multiple searches at a time, with OR and LIKE query ?
I need to search both name and address on a single search filed at a time , How can i do it ?