Cannot insert data into SQLite database in react native

Question

I stucked with SQLite database in React Native and I don't know what is a problem actually.

I am opening database, create table and try to insert some random data but when I am trying to display inserted data there is empty array. I am not sure its an error with insert or displaying or maybe my whole database is opened wrong.

Here I am opening database:

import SQLite from 'react-native-sqlite-storage';
SQLite.DEBUG(false);
SQLite.enablePromise(false);

export const db = SQLite.openDatabase(
    {
        name: 'baza.db'
    }
);

and here I am creating table and trying to insert data and display it:

fun = () => {
      db.transaction(function (txn) {
        txn.executeSql(
          "SELECT name FROM sqlite_master WHERE type='table' AND name='Test_Table'",
          [],
          function (tx, res) {
            console.log('item:', res.rows.length);
            if (res.rows.length == 0) {
              txn.executeSql('DROP TABLE IF EXISTS Test_Table', []);
              txn.executeSql(
                'CREATE TABLE IF NOT EXISTS Test_Table(test_id INTEGER PRIMARY KEY AUTOINCREMENT, test_value FLOAT, test_date VARCHAR(15))',
                []
              );
            }
          }
        );
      })
    }

    
    insertData = () => {
      db.transaction(function (tx) {
        tx.executeSql(
          'INSERT INTO Test_Table (test_value, test_date) VALUES (2, test)',
          (tx, results) => {
            console.log('Results', results.rowsAffected);
            if (results.rowsAffected > 0) {
              console.log('Data Inserted Successfully....');
            } else console.log('Failed....');
          }
        );
      });
      this.viewTable();
    }

    viewTable = () => {
      db.transaction((tx) => {
        tx.executeSql(
          'SELECT * FROM Test_Table',
          [],
          (tx, results) => {
            let temp = [];
            for (let i = 0; i < results.rows.length; ++i)
              temp.push(results.rows.item(i));
            console.log(temp);
          }
        );
      });
    }

My logs after creating table and insert data:

[Info] 06-10 10:47:50.486  5866  5924 I ReactNativeJS: 'item:', 0


[Info] 06-10 10:47:55.412  5866  5924 I ReactNativeJS: []

Any idea what is wrong or maybe how I should do this? I will be very grateful

Answer 1

You can check column type, because change type int to varchar in SQLite, because react native is sending string type.

Answer 2

I'm also facing this issue before but finally I can get it working. Here is what I do:

1. I download the db browser in here to create and manage my sqlite db.
2. Create a new folder if not exist on this path root\android\app\src\main\assets\www and place the created db inside www folder.
3. On project, I create a separate class/file for sqlite purposes.

Sample name sqlite-db.js:

import { openDatabase, deleteDatabase } from 'react-native-sqlite-storage';
var db = openDatabase(
    { name: 'sqlite.db', createFromLocation: 1 },
    () => {
        console.log('Connection success!');
    },
    error => {
        console.log(error);
    },
);
/// Below function is use in-case the db object is null
async function OpenDatabase() {
    db = openDatabase(
        { name: 'sqlite.db', createFromLocation: 1 },
        () => {
            console.log('Re-open connection success!');
        },
        error => {
            console.log(error);
        },
    );
}
function SQLSelect(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        db.transaction(function (txn) {
            console.log('SQLITE SELECT QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    var temp = [];
                    if (res.rows.length == 0) {
                        resolve(temp);
                    }
                    for (let i = 0; i < res.rows.length; ++i) temp.push(res.rows.item(i));
                    console.log('SQLITE SELECT RESULT:', temp)
                    resolve(temp);
                },
                function (error) {
                    console.log('Failed to select:', error);
                    reject();
                },
            );
        });
    });
}
function SQLInsert(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        db.transaction(function (txn) {
            console.log('SQLITE INSERT QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    console.log('SQLITE INSERT RESULT:', res)
                    resolve(res);
                },
                function (error) {
                    console.log('Failed to insert:', error);
                    reject();
                },
            );
        });
    });
}
function SQLUpdate(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        await db.transaction(function (txn) {
            console.log('SQLITE UPDATE QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    console.log('SQLITE UPDATE RESULT:', res);
                    if (res.rowsAffected > 0) {
                        // Data updated
                        resolve(res.rowsAffected);
                    } else {
                        // Data not updated
                        resolve(0);
                    }
                },
                function (error) {
                    console.log('Failed to update:', error);
                    reject();
                },
            );
        });
    });
}
function SQLDelete(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        await db.transaction(function (txn) {
            console.log('SQLITE DELETE QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    console.log('SQLITE DELETE RESULT:', res)
                    if (res.rowsAffected > 0) {
                        // Data deleted
                        resolve(res.rowsAffected);
                    } else {
                        // Data not delete
                        reject();
                    }
                },
                function (error) {
                    console.log('Failed to delete:', error);
                    reject();
                },
            );
        });
    });
}

async function GetMeSomethingOnDB(data) {
    return new Promise(async (resolve, reject) => {
        try {
            var SQData = {
                "TABLERESULT": []
            }
            let params = [];
            let queries = '';
            params = [data.TableID];
            queries = `SELECT * FROM [TABLE] WHERE TableID=?`;
            await SQLSelect(queries, params).then(results => SQData.TABLERESULT = results).catch((error) => console.log('Error retrieve TABLERESULT. ', error));
            resolve(SQData);
        } catch (error) {
            console.log('ERROR GET SOMETHING:', error);
            reject();
        }
    });
}

How can you call the function in sqlite-db.js? Sample are on below: import * as ApxSQLite from "../../../sqlite-db";

React.useEffect(() => {
    //Get data directly to function
    let params = [];
    let queries = '';
    // You can add the parameter in the query or use ? and 
    // add the parameter ordinance in params array.
    queries = 'SELECT * FROM [OTHERTABLE] WHERE OtherID=1';
    ApxSQLite.SQLSelect(queries, params)
        .then((data) => {
            console.log('Mounted:', data);
        })
        .catch((error) => console.log('OTHERTABLE ERROR:', error));

    // Call the function in sqlite-db
    var data = {TableID : 13}
    ApxSQLite.GetMeSomethingOnDB(data)
        .then((data) => {
            console.log('Mounted 2:', data);
        })
        .catch((error) => console.log('GETSOMETHING ERROR:', error));
}, []);

By the way, removing SQLite.DEBUG(false); and SQLite.enablePromise(false); did help

Answer 3

I'm also facing this issue before but finally I can get it working. Here is what I do as a reference for other who need a complete way:

1. I download the db browser in here to create and manage my sqlite db design.
2. Create a new folder if not exist on this path root\android\app\src\main\assets\www and place the created db inside www folder.
3. On project, I create a separate class/file for sqlite purposes.

Sample name sqlite-db.js:

import { openDatabase, deleteDatabase } from 'react-native-sqlite-storage';
var db = openDatabase(
    { name: 'sqlite.db', createFromLocation: 1 },
    () => {
        console.log('Connection success!');
    },
    error => {
        console.log(error);
    },
);
/// Below function is use in-case the db object is null
async function OpenDatabase() {
    db = openDatabase(
        { name: 'sqlite.db', createFromLocation: 1 },
        () => {
            console.log('Re-open connection success!');
        },
        error => {
            console.log(error);
        },
    );
}
function SQLSelect(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        db.transaction(function (txn) {
            console.log('SQLITE SELECT QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    var temp = [];
                    if (res.rows.length == 0) {
                        resolve(temp);
                    }
                    for (let i = 0; i < res.rows.length; ++i) temp.push(res.rows.item(i));
                    console.log('SQLITE SELECT RESULT:', temp)
                    resolve(temp);
                },
                function (error) {
                    console.log('Failed to select:', error);
                    reject();
                },
            );
        });
    });
}
function SQLInsert(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        db.transaction(function (txn) {
            console.log('SQLITE INSERT QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    console.log('SQLITE INSERT RESULT:', res)
                    resolve(res);
                },
                function (error) {
                    console.log('Failed to insert:', error);
                    reject();
                },
            );
        });
    });
}
function SQLUpdate(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        await db.transaction(function (txn) {
            console.log('SQLITE UPDATE QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    console.log('SQLITE UPDATE RESULT:', res);
                    if (res.rowsAffected > 0) {
                        // Data updated
                        resolve(res.rowsAffected);
                    } else {
                        // Data not updated
                        resolve(0);
                    }
                },
                function (error) {
                    console.log('Failed to update:', error);
                    reject();
                },
            );
        });
    });
}
function SQLDelete(query, params = []) {
    return new Promise(async (resolve, reject) => {
        if (db === null) {
            await OpenDatabase();
        }
        await db.transaction(function (txn) {
            console.log('SQLITE DELETE QUERY:', query, ',Value:', params);
            txn.executeSql(query, params,
                function (tx, res) {
                    console.log('SQLITE DELETE RESULT:', res)
                    if (res.rowsAffected > 0) {
                        // Data deleted
                        resolve(res.rowsAffected);
                    } else {
                        // Data not delete
                        reject();
                    }
                },
                function (error) {
                    console.log('Failed to delete:', error);
                    reject();
                },
            );
        });
    });
}

async function GetMeSomethingOnDB(data) {
    return new Promise(async (resolve, reject) => {
        try {
            var SQData = {
                "TABLERESULT": []
            }
            let params = [];
            let queries = '';
            params = [data.TableID];
            queries = `SELECT * FROM [TABLE] WHERE TableID=?`;
            await SQLSelect(queries, params).then(results => SQData.TABLERESULT = results).catch((error) => console.log('Error retrieve TABLERESULT. ', error));
            resolve(SQData);
        } catch (error) {
            console.log('ERROR GET SOMETHING:', error);
            reject();
        }
    });
}

How can you call the function in sqlite-db.js? Sample are on below: import * as ApxSQLite from "./sqlite-db";

React.useEffect(() => {
    //Get data directly to function
    let params = [];
    let queries = '';
    // You can add the parameter in the query or use ? and 
    // add the parameter ordinance in params array.
    queries = 'SELECT * FROM [OTHERTABLE] WHERE OtherID=1';
    ApxSQLite.SQLSelect(queries, params)
        .then((data) => {
            console.log('Mounted:', data);
        })
        .catch((error) => console.log('OTHERTABLE ERROR:', error));

    // Call the function in sqlite-db if you do not want to
    // flood your screen script with all the things like ApxSQLite.SQLSelect code.
    var data = {TableID : 13}
    ApxSQLite.GetMeSomethingOnDB(data)
        .then((data) => {
            console.log('Mounted 2:', data);
        })
        .catch((error) => console.log('GETSOMETHING ERROR:', error));
}, []);

And by the way, I'm not adding SQLite.DEBUG(false); and SQLite.enablePromise(false);