Hello guys, As we have learn about SQLite database in our previous tutorials. We had perform data insert, data view functionality but still 2 more unique features is remaining. Which is Editing or Updating single data using ID and deletion of selected record in SQLite. Today we are making a fully advanced tutorial of SQLite database in react native performing all CRUD operations Create, Read, Update and Delete. We would also use React Navigation in our current tutorial in order to use the multiple screens. There are three screens present in our example one for inserting data, one for showing all entered data in FlatList component and finally one for updating data and also deleting current record. So in this tutorial we would learn about Crud Operations on SQLite Database in React Native in Android iOS Example.
React Native Libraries we’re using in our example:
- React Navigation : To use multiple screens in our tutorial.
- react-native-sqlite-storage : To use SQLite database in react native app.
Contents in this project Crud Operations on SQLite Database in React Native Example:
1. The first step is to install the react navigation and its supporting libraries. I had already make tutorial on Installing React Navigation 5.x Guide. So visit my tutorial and follow step 1, 2 and 3 .
2. Visit our react-native-sqlite-storage installation guide tutorial and follow step 1 and 2.
3. Now all the installation is finish, Next step is to start coding for app. So open your project’s main App.js file and import SafeAreaView, Text, View, StyleSheet, Alert, TouchableOpacity, TextInput, FlatList, useState, and useEffect component.
|
1
2
3
|
import React, { useState, useEffect } from ‘react’;
import { SafeAreaView, Text, View, StyleSheet, Alert, TouchableOpacity, TextInput, FlatList } from ‘react-native’;
|
4. Import openDatabase from react-native-sqlite-storage, NavigationContainer, useIsFocused from @react-navigation/native and createStackNavigator from @react-navigation/stack library.
|
1
2
3
4
5
|
import { openDatabase } from ‘react-native-sqlite-storage’;
import { NavigationContainer, useIsFocused } from ‘@react-navigation/native’;
import { createStackNavigator } from ‘@react-navigation/stack’;
|
5. Creating a global variable named as db and here we would make our SQLite database named as SchoolDatabase.db.
|
1
|
var db = openDatabase({ name: ‘SchoolDatabase.db’ });
|
6. Creating our first home screen functional component named as HomeScreen. This is the home activity of our application. I had already explained current screen on our previous tutorial. So read my previous tutorial for brief explanation and follow step 5.
|
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
|
function HomeScreen({ navigation }) {
const [S_Name, setName] = useState(”);
const [S_Phone, setPhone] = useState();
const [S_Address, setAddress] = useState(”);
useEffect(() => {
db.transaction(function (txn) {
txn.executeSql(
“SELECT name FROM sqlite_master WHERE type=’table’ AND name=’Student_Table'”,
[],
function (tx, res) {
console.log(‘item:’, res.rows.length);
if (res.rows.length == 0) {
txn.executeSql(‘DROP TABLE IF EXISTS Student_Table’, []);
txn.executeSql(
‘CREATE TABLE IF NOT EXISTS Student_Table(student_id INTEGER PRIMARY KEY AUTOINCREMENT, student_name VARCHAR(30), student_phone INT(15), student_address VARCHAR(255))’,
[]
);
}
}
);
})
}, []);
const insertData = () => {
if (S_Name == ” || S_Phone == ” || S_Address == ”) {
Alert.alert(‘Please Enter All the Values’);
} else {
db.transaction(function (tx) {
tx.executeSql(
‘INSERT INTO Student_Table (student_name, student_phone, student_address) VALUES (?,?,?)’,
[S_Name, S_Phone, S_Address],
(tx, results) => {
console.log(‘Results’, results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(‘Data Inserted Successfully….’);
} else Alert.alert(‘Failed….’);
}
);
});
}
}
navigateToViewScreen = () => {
navigation.navigate(‘ViewAllStudentScreen’);
}
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={styles.mainContainer}>
<Text style={{ fontSize: 24, textAlign: ‘center’, color: ‘#000’ }}>
Insert Data Into SQLite Database
</Text>
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setName(text)
}
placeholder=“Enter Student Name”
value={S_Name} />
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setPhone(text)
}
placeholder=“Enter Student Phone Number”
keyboardType={‘numeric’}
value={S_Phone} />
<TextInput
style={[styles.textInputStyle, { marginBottom: 20 }]}
onChangeText={
(text) => setAddress(text)
}
placeholder=“Enter Student Address”
value={S_Address} />
<TouchableOpacity
style={styles.touchableOpacity}
onPress={insertData}>
<Text style={styles.touchableOpacityText}> Click Here To Insert Data Into SQLite Database </Text>
</TouchableOpacity>
<TouchableOpacity
style={[styles.touchableOpacity, { marginTop: 20, backgroundColor: ‘#33691E’ }]}
onPress={navigateToViewScreen}>
<Text style={styles.touchableOpacityText}> Click Here View All Students List </Text>
</TouchableOpacity>
</View>
</SafeAreaView>
);
};
|
7. Creating next screen functional component named as ViewAllStudentScreen. In this screen we would display all the entered records in SQLite database. When user clicks on any record then it will automatically navigate to next screen to Edit the current selected record.
|
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
|
function ViewAllStudentScreen({ navigation }) {
const [items, setItems] = useState([]);
const [empty, setEmpty] = useState([]);
const isFocused = useIsFocused();
useEffect(() => {
db.transaction((tx) => {
tx.executeSql(
‘SELECT * FROM Student_Table’,
[],
(tx, results) => {
var temp = [];
for (let i = 0; i < results.rows.length; ++i)
temp.push(results.rows.item(i));
setItems(temp);
if (results.rows.length >= 1) {
setEmpty(false);
} else {
setEmpty(true)
}
}
);
});
}, [isFocused]);
const listViewItemSeparator = () => {
return (
<View
style={{
height: 1,
width: ‘100%’,
backgroundColor: ‘#000’
}}
/>
);
};
const emptyMSG = (status) => {
return (
<View style={{ justifyContent: ‘center’, alignItems: ‘center’, flex: 1 }}>
<Text style={{ fontSize: 25, textAlign: ‘center’ }}>
No Record Inserted Database is Empty...
</Text>
</View>
);
}
const navigateToEditScreen = (id, name, phoneNumber, address) => {
navigation.navigate(‘EditRecordScreen’, {
student_id: id,
student_name: name,
student_phone: phoneNumber,
student_address: address
});
}
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1 }}>
{empty ? emptyMSG(empty) :
<FlatList
data={items}
ItemSeparatorComponent={listViewItemSeparator}
keyExtractor={(item, index) => index.toString()}
renderItem={({ item }) =>
<View key={item.student_id} style={{ padding: 20 }}>
<TouchableOpacity onPress={() => navigateToEditScreen(item.student_id, item.student_name, item.student_phone, item.student_address)} >
<Text style={styles.itemsStyle}> Id: {item.student_id} </Text>
<Text style={styles.itemsStyle}> Name: {item.student_name} </Text>
<Text style={styles.itemsStyle}> Phone Number: {item.student_phone} </Text>
<Text style={styles.itemsStyle}> Address: {item.student_address} </Text>
</TouchableOpacity>
</View>
}
/>
}
</View>
</SafeAreaView>
);
}
|
8. Creating our final screen component named as EditRecordScreen. In this activity first we would fill the student records sent by previous screen into Text Input component and update as per user requires. We would also give the Delete current record option for user to delete the opened record and navigate to previous screen.
|
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
|
function EditRecordScreen({ route, navigation }) {
const [S_Id, setID] = useState(”);
const [S_Name, setName] = useState(”);
const [S_Phone, setPhone] = useState();
const [S_Address, setAddress] = useState(”);
useEffect(() => {
setID(route.params.student_id);
setName(route.params.student_name);
setPhone(route.params.student_phone.toString());
setAddress(route.params.student_address);
}, []);
const editData = () => {
db.transaction((tx) => {
tx.executeSql(
‘UPDATE Student_Table set student_name=?, student_phone=? , student_address=? where student_id=?’,
[S_Name, S_Phone, S_Address, S_Id],
(tx, results) => {
console.log(‘Results’, results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(‘Record Updated Successfully…’)
} else Alert.alert(‘Error’);
}
);
});
}
const deleteRecord = () => {
db.transaction((tx) => {
tx.executeSql(
‘DELETE FROM Student_Table where student_id=?’,
[S_Id],
(tx, results) => {
console.log(‘Results’, results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(
‘Done’,
‘Record Deleted Successfully’,
[
{
text: ‘Ok’,
onPress: () => navigation.navigate(‘ViewAllStudentScreen’),
},
],
{ cancelable: false }
);
}
}
);
});
}
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={styles.mainContainer}>
<Text style={{ fontSize: 24, textAlign: ‘center’, color: ‘#000’ }}>
Edit Record In SQLite Database
</Text>
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setName(text)
}
placeholder=“Enter Student Name”
value={S_Name} />
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setPhone(text)
}
placeholder=“Enter Student Phone Number”
keyboardType={‘numeric’}
value={S_Phone} />
<TextInput
style={[styles.textInputStyle, { marginBottom: 20 }]}
onChangeText={
(text) => setAddress(text)
}
placeholder=“Enter Student Address”
value={S_Address} />
<TouchableOpacity
style={styles.touchableOpacity}
onPress={editData}>
<Text style={styles.touchableOpacityText}> Click Here To Edit Record </Text>
</TouchableOpacity>
<TouchableOpacity
style={[styles.touchableOpacity, { marginTop: 20, backgroundColor: ‘#33691E’ }]}
onPress={deleteRecord}>
<Text style={styles.touchableOpacityText}> Click Here To Delete Current Record </Text>
</TouchableOpacity>
</View>
</SafeAreaView>
);
};
|
9. Creating createStackNavigator() component object named as Stack. Now we would make the export default function App and call all 3 above screens in NavigationContainer.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
export default function App() {
return (
<NavigationContainer>
<Stack.Navigator>
<Stack.Screen name=“HomeScreen” component={HomeScreen} />
<Stack.Screen name=“ViewAllStudentScreen” component={ViewAllStudentScreen} />
<Stack.Screen name=“EditRecordScreen” component={EditRecordScreen} />
</Stack.Navigator>
</NavigationContainer>
);
}
|
10. Creating Style sheet.
|
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
|
const styles = StyleSheet.create({
mainContainer: {
flex: 1,
alignItems: ‘center’,
padding: 10,
},
touchableOpacity: {
backgroundColor: ‘#0091EA’,
alignItems: ‘center’,
borderRadius: 8,
justifyContent: ‘center’,
alignItems: ‘center’,
width: ‘100%’
},
touchableOpacityText: {
color: ‘#FFFFFF’,
fontSize: 23,
textAlign: ‘center’,
padding: 8
},
textInputStyle: {
height: 45,
width: ‘90%’,
textAlign: ‘center’,
borderWidth: 1,
borderColor: ‘#00B8D4’,
borderRadius: 7,
marginTop: 15,
},
itemsStyle: {
fontSize: 22,
color: ‘#000’
}
});
|
11. Complete Source Code for App.js file:
|
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
|
import React, { useState, useEffect } from ‘react’;
import { SafeAreaView, Text, View, StyleSheet, Alert, TouchableOpacity, TextInput, FlatList } from ‘react-native’;
import { openDatabase } from ‘react-native-sqlite-storage’;
import { NavigationContainer, useIsFocused } from ‘@react-navigation/native’;
import { createStackNavigator } from ‘@react-navigation/stack’;
var db = openDatabase({ name: ‘SchoolDatabase.db’ });
function HomeScreen({ navigation }) {
const [S_Name, setName] = useState(”);
const [S_Phone, setPhone] = useState();
const [S_Address, setAddress] = useState(”);
useEffect(() => {
db.transaction(function (txn) {
txn.executeSql(
“SELECT name FROM sqlite_master WHERE type=’table’ AND name=’Student_Table'”,
[],
function (tx, res) {
console.log(‘item:’, res.rows.length);
if (res.rows.length == 0) {
txn.executeSql(‘DROP TABLE IF EXISTS Student_Table’, []);
txn.executeSql(
‘CREATE TABLE IF NOT EXISTS Student_Table(student_id INTEGER PRIMARY KEY AUTOINCREMENT, student_name VARCHAR(30), student_phone INT(15), student_address VARCHAR(255))’,
[]
);
}
}
);
})
}, []);
const insertData = () => {
if (S_Name == ” || S_Phone == ” || S_Address == ”) {
Alert.alert(‘Please Enter All the Values’);
} else {
db.transaction(function (tx) {
tx.executeSql(
‘INSERT INTO Student_Table (student_name, student_phone, student_address) VALUES (?,?,?)’,
[S_Name, S_Phone, S_Address],
(tx, results) => {
console.log(‘Results’, results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(‘Data Inserted Successfully….’);
} else Alert.alert(‘Failed….’);
}
);
});
}
}
navigateToViewScreen = () => {
navigation.navigate(‘ViewAllStudentScreen’);
}
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={styles.mainContainer}>
<Text style={{ fontSize: 24, textAlign: ‘center’, color: ‘#000’ }}>
Insert Data Into SQLite Database
</Text>
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setName(text)
}
placeholder=“Enter Student Name”
value={S_Name} />
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setPhone(text)
}
placeholder=“Enter Student Phone Number”
keyboardType={‘numeric’}
value={S_Phone} />
<TextInput
style={[styles.textInputStyle, { marginBottom: 20 }]}
onChangeText={
(text) => setAddress(text)
}
placeholder=“Enter Student Address”
value={S_Address} />
<TouchableOpacity
style={styles.touchableOpacity}
onPress={insertData}>
<Text style={styles.touchableOpacityText}> Click Here To Insert Data Into SQLite Database </Text>
</TouchableOpacity>
<TouchableOpacity
style={[styles.touchableOpacity, { marginTop: 20, backgroundColor: ‘#33691E’ }]}
onPress={navigateToViewScreen}>
<Text style={styles.touchableOpacityText}> Click Here View All Students List </Text>
</TouchableOpacity>
</View>
</SafeAreaView>
);
};
function ViewAllStudentScreen({ navigation }) {
const [items, setItems] = useState([]);
const [empty, setEmpty] = useState([]);
const isFocused = useIsFocused();
useEffect(() => {
db.transaction((tx) => {
tx.executeSql(
‘SELECT * FROM Student_Table’,
[],
(tx, results) => {
var temp = [];
for (let i = 0; i < results.rows.length; ++i)
temp.push(results.rows.item(i));
setItems(temp);
if (results.rows.length >= 1) {
setEmpty(false);
} else {
setEmpty(true)
}
}
);
});
}, [isFocused]);
const listViewItemSeparator = () => {
return (
<View
style={{
height: 1,
width: ‘100%’,
backgroundColor: ‘#000’
}}
/>
);
};
const emptyMSG = (status) => {
return (
<View style={{ justifyContent: ‘center’, alignItems: ‘center’, flex: 1 }}>
<Text style={{ fontSize: 25, textAlign: ‘center’ }}>
No Record Inserted Database is Empty...
</Text>
</View>
);
}
const navigateToEditScreen = (id, name, phoneNumber, address) => {
navigation.navigate(‘EditRecordScreen’, {
student_id: id,
student_name: name,
student_phone: phoneNumber,
student_address: address
});
}
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={{ flex: 1 }}>
{empty ? emptyMSG(empty) :
<FlatList
data={items}
ItemSeparatorComponent={listViewItemSeparator}
keyExtractor={(item, index) => index.toString()}
renderItem={({ item }) =>
<View key={item.student_id} style={{ padding: 20 }}>
<TouchableOpacity onPress={() => navigateToEditScreen(item.student_id, item.student_name, item.student_phone, item.student_address)} >
<Text style={styles.itemsStyle}> Id: {item.student_id} </Text>
<Text style={styles.itemsStyle}> Name: {item.student_name} </Text>
<Text style={styles.itemsStyle}> Phone Number: {item.student_phone} </Text>
<Text style={styles.itemsStyle}> Address: {item.student_address} </Text>
</TouchableOpacity>
</View>
}
/>
}
</View>
</SafeAreaView>
);
}
function EditRecordScreen({ route, navigation }) {
const [S_Id, setID] = useState(”);
const [S_Name, setName] = useState(”);
const [S_Phone, setPhone] = useState();
const [S_Address, setAddress] = useState(”);
useEffect(() => {
setID(route.params.student_id);
setName(route.params.student_name);
setPhone(route.params.student_phone.toString());
setAddress(route.params.student_address);
}, []);
const editData = () => {
db.transaction((tx) => {
tx.executeSql(
‘UPDATE Student_Table set student_name=?, student_phone=? , student_address=? where student_id=?’,
[S_Name, S_Phone, S_Address, S_Id],
(tx, results) => {
console.log(‘Results’, results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(‘Record Updated Successfully…’)
} else Alert.alert(‘Error’);
}
);
});
}
const deleteRecord = () => {
db.transaction((tx) => {
tx.executeSql(
‘DELETE FROM Student_Table where student_id=?’,
[S_Id],
(tx, results) => {
console.log(‘Results’, results.rowsAffected);
if (results.rowsAffected > 0) {
Alert.alert(
‘Done’,
‘Record Deleted Successfully’,
[
{
text: ‘Ok’,
onPress: () => navigation.navigate(‘ViewAllStudentScreen’),
},
],
{ cancelable: false }
);
}
}
);
});
}
return (
<SafeAreaView style={{ flex: 1 }}>
<View style={styles.mainContainer}>
<Text style={{ fontSize: 24, textAlign: ‘center’, color: ‘#000’ }}>
Edit Record In SQLite Database
</Text>
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setName(text)
}
placeholder=“Enter Student Name”
value={S_Name} />
<TextInput
style={styles.textInputStyle}
onChangeText={
(text) => setPhone(text)
}
placeholder=“Enter Student Phone Number”
keyboardType={‘numeric’}
value={S_Phone} />
<TextInput
style={[styles.textInputStyle, { marginBottom: 20 }]}
onChangeText={
(text) => setAddress(text)
}
placeholder=“Enter Student Address”
value={S_Address} />
<TouchableOpacity
style={styles.touchableOpacity}
onPress={editData}>
<Text style={styles.touchableOpacityText}> Click Here To Edit Record </Text>
</TouchableOpacity>
<TouchableOpacity
style={[styles.touchableOpacity, { marginTop: 20, backgroundColor: ‘#33691E’ }]}
onPress={deleteRecord}>
<Text style={styles.touchableOpacityText}> Click Here To Delete Current Record </Text>
</TouchableOpacity>
</View>
</SafeAreaView>
);
};
const Stack = createStackNavigator();
export default function App() {
return (
<NavigationContainer>
<Stack.Navigator>
<Stack.Screen name=“HomeScreen” component={HomeScreen} />
<Stack.Screen name=“ViewAllStudentScreen” component={ViewAllStudentScreen} />
<Stack.Screen name=“EditRecordScreen” component={EditRecordScreen} />
</Stack.Navigator>
</NavigationContainer>
);
}
const styles = StyleSheet.create({
mainContainer: {
flex: 1,
alignItems: ‘center’,
padding: 10,
},
touchableOpacity: {
backgroundColor: ‘#0091EA’,
alignItems: ‘center’,
borderRadius: 8,
justifyContent: ‘center’,
alignItems: ‘center’,
width: ‘100%’
},
touchableOpacityText: {
color: ‘#FFFFFF’,
fontSize: 23,
textAlign: ‘center’,
padding: 8
},
textInputStyle: {
height: 45,
width: ‘90%’,
textAlign: ‘center’,
borderWidth: 1,
borderColor: ‘#00B8D4’,
borderRadius: 7,
marginTop: 15,
},
itemsStyle: {
fontSize: 22,
color: ‘#000’
}
});
|
Screenshots:
Also Read :
React Native Set Default PlaceHolder Text in TextInput Component
React Native Show Realm Database Data into ListView Android iOS Example Tutorial
React Native Create Price Card View Beautiful UI Design iOS Android
React Native Count TextInput Inside Text Length Automatically
React Native AutoComplete Text Input Android iOS Example Tutorial
