In this reading, you’ll delve into more detail about SQLite. You’ll explore the example of code interacting with a database containing a menu and how to use all CRUD operations when interacting with the SQLite database.

SQLite at a Glance

Recall that the expo-sqlite module offers an SQL database with a Web-SQL-based interface. That’s powerful since it supports most of the features of SQLite.

SQLite is also well suited for the type of use case that applications with offline requirements have.

It facilitates storing large chunks of structured data on a disk and reading only the parts needed for displaying a current screen of information.

However, the Expo-SQLite API is quite cumbersome to deal with, so the learning curve feels a bit steeper for some developers. This is an opportunity to showcase best practice principles.

These practices include placing queries in your codebase and building a user-friendly API on top of Expo-SQLite, which React Native components use.

Now let’s introduce you to a complete example of CRUD operations. This will give you a full picture of how to read, write, update and delete data when interacting with the underlying SQLite database.

In this example, code snippets interact with a database that contains a menu table, which stores different dishes. Each entry in the table contains only the dish name and its dish ID, which is guaranteed to be unique.

Reading and Writing Data with SQLite

Since reading and writing are core to every application that manages data, let’s start there.

To follow good practices with code organization, place all the queries in a single file called database.js.

This way, if you’re moving away from SQLite and replacing it or mocking out the database for testing purposes, you can just swap out a single file.

const getDishes = (successCallback) => {
  db.transaction((tx) => {
    tx.executeSql('select * from menu', [], (_, { rows: { _array } }) => {
      successCallback(_array)
    })
  })
}

Next, notice the getDishes function highlights a great design feature to keep your queries isolated from your React code. The reason is that they have no notion of React concepts, like props or state.

This is achieved by providing a callback function that will be invoked the moment the query is executed successfully.

This callback function could be a state setter function from a React component. So, in the getDishes function, the callback will be invoked with the data retrieved from the query.

const MenuList = () => {
  const [menuItems, setMenuItems] = useState([])

  useEffect(() => {
    getDishes(setMenuItems)
  }, [])

  return <ListView data={menuItems} />
}

Let’s move to insert new dishes in the database. For that, I have created a second function in the database.js file called insertDish, with the implementation shown below.

The function takes a single parameter, the dish name, and executes a SQL transaction to insert the new dish into the menu table.

const insertDish = (dishName) => {
  return new Promise((resolve, reject) => {
    db.transaction(
      (tx) => {
        tx.executeSql('insert into menu (name) values (?)', [dishName])
      },
      reject,
      resolve
    )
  })
}

In this particular case, the write operation is promisified in the disk so that the function returns a promise, enabling you to call those functions with the await keyboard.

In your React Native component, call the insertDish function by wrapping it in a try/catch clause to account for possible errors and inform the user accordingly:

const insertData = async (dishName) => {
  try {
    await insertDish(dishName)
  } catch (e) {
    Alert.alert(`Error inserting ${dishName}`, e.message)
  }
}

Updating and Deleting Data with SQLite

Finally, let’s complete the example with the two remaining CRUD operations, update and delete.

For changing a dish name, define a new function that takes the dishId as the first parameter.

This locates the entry to be updated within the database table. Ensure you include the new name as the second argument.

This function again returns a promise so that the caller can use the await keyword, with a try/catch statement to catch errors.

const updateDish = async (dishId, newName) => {
  return new Promise((resolve, reject) => {
    db.transaction(
      (tx) => {
        tx.executeSql(`update menu set name=? where uid=${dishId}`, [newName])
      },
      reject,
      resolve
    )
  })
}

The last operation is deleting a dish, as outlined in the code below. This follows a similar Promise-like design pattern, such as creating and updating dishes.

const deleteDish = async (dishId) => {
  return new Promise((resolve, reject) => {
    db.transaction(
      (tx) => {
        tx.executeSql('delete from menu where id = ?', [dishId])
      },
      reject,
      resolve
    )
  })
}

Conclusion

In this reading, you delved deeper into SQLite by exploring the example of code interacting with a database containing a menu. You learned to execute all CRUD operations to read, write, update and delete data when interacting with the SQLite database.