consume API in react native and save data in sqlite

user_18319843 0
Viewed 599

I'm new in sqlite and react native. (my app is with expo sqlite and expo react native) I'm trying to save data from an API to a table in SQLite database. The table name is "tb_user"

// MY DB
tx.executeSql(
    "CREATE TABLE IF NOT EXISTS tb_user (ID_user INTEGER PRIMARY KEY, username TEXT, senha TEXT, ID_empresa INT, nome_empresa TEXT, status INT);"
  );

// INSERT TO DB
const create = (obj) => {
    return new Promise((resolve, reject) => {
      db.transaction((tx) => {
        //comando SQL modificável
        tx.executeSql(
          "INSERT INTO tb_user (ID_user, username, senha, ID_empresa, nome_empresa, status) values (?, ?, ?, ?, ?, ?);",
          [obj.ID_user, obj.username, obj.senha, obj.ID_empresa, obj.nome_empresa, obj.status],
          //-----------------------
          (_, { rowsAffected, insertId }) => {
            if (rowsAffected > 0) resolve(insertId);    // resultado da operação
            else reject("Error inserting obj: " + JSON.stringify(obj)); // insert falhou
          },
          (_, error) => reject(error) // erro interno em tx.executeSql
        );
      });
    });
  };

//MY CODE IN SCREEN
const importTbUser = () => {
    fetch('url_API')
    .then( (res) => res.json())
        .then( ( res ) => {
            this.setState ({
                data: res.data || []
            })
            .then(
                data.map((user) => {
                    Tb_user.create( {ID_user:user.ID_user, username:user.username, senha:user.senha, ID_empresa:user.ID_empresa, nome_empresa:user.nome_empresa, status:user.status_user} )
                })
            )
        })
    .catch( ( error ) => console.error(error) )

Obviously this code doesn't work, how can i make it work?

My application has a button with "onPress={import bUser}", so, when clicking on the button, it inserts the data that is in the API into the database. I saw some videos, but they only show how to consume an API, without inserting it into a database.

react-native api sqlite
1 Answers

So ive worked on something like this few weeks back:

you need to install react-native-sqlite-storage npm i react-native-sqlite-storage or yarn add react-native-sqlite-storage

  1. Create a file containing db details, let say db.js
import SQLite from 'react-native-sqlite-storage';

function errorCB(err) {
    console.log("SQL Error: " + err);
}

function openCB() {
    console.log("Database OPENED");
}

//SQLite.deleteDatabase({name: 'user.db'});

const db = SQLite.openDatabase({
    name: "user.db",
    createFromLocation: 1
}, openCB, errorCB);

//db.executeSql('delete from tb_user');

export default db;

Then create a file for inserting values to DB:

    import db from './db';
    
    const TABLE_NAME = 'tb_user';
    


   export const createTable = (values) => {

const columns = Object.keys(values);

const query = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME}(
       ${columns.join('TEXT NOT NULL, ')
    );`;

  await db.executeSql(query);
};




    export const insertIfNotExists = (values) => {
    
        return new Promise((resolve, reject) => {
    
            const FIND_SQL = `SELECT COUNT(*) AS matches FROM ${TABLE_NAME} WHERE user_id
= ${values.id}`;
    
            db.transaction((tx) => {
                tx.executeSql(FIND_SQL, [], (tx, results) => {
    
                    if (results.rows.item(0).matches === 0) {
    
                        const columns = Object.keys(values);
                        let dbValues = Object.values(values);
    
     const SQL = `INSERT INTO ${TABLE_NAME} (  ${columns.join(',')}, user_id ) VALUES ( ${new Array(columns.length).fill('?').join(',')}, '${values.id}' )`;
    
                        db.transaction((tx) => {
                            tx.executeSql(SQL, dbValues, (tx, results) => {
                                resolve(true);
                                deleteDuplicateUser(values.id)
                            });
                        });
                    } else {
                        resolve(false);
                    }
                });
            });
        });
    };

Basically, whats happening here is, im fetching column names from api values const columns = Object.keys(values); and also their corresponding values let dbValues = Object.values(values);

then inserting them with comma separated using join follow this: ${columns.join(',')}

and call these methods in your service file(where you are calling the api) and pass the values.

 const result = await createTable(values);
 const result = await insertIfNotExists(values);

In my case, i had all text values, you can tweak and modify data types in SQlite as per your api response.

If you find it difficulty for creating data types dynamically in your sqlite db, you can find the file in path: android/app/src/main/assets/www/user.db. Modify the data types using sqlite browser. Here's the link to install: https://sqlitebrowser.org/

Here is also a detailed Tutorial for reference: https://aboutreact.com/example-of-sqlite-database-in-react-native/

user_84608370
Related