SQLite Database: Native Cross-Platform Swift Apps

Introduction

Hello everyone 👋

My name is Vedant, and today I am going to show you how to integrate SQLite DB into the SCADE mobile app.

We often need to store data locally so that our app can use it. If we are building mobile apps for both Android and iOS we need to take care of the compatibility of the database. SCADE helps us to minimize this effort in integrating a local database so that we can focus only on writing our business logic instead of spending time on setting up the database compatible with both Android and iOS.

SCADE by its nature uses optimized minimal lines of code and is fully compatible with both mobile platforms. In this article, we will learn how to use the SQLite database, an offering by SCADE SDK, and build a demo SCADE app to demonstrate the SQLite DB.

Database Persistence

The database must be persistent for storing data in the form of objects. It is needed so that the data records are durable if the device or application software is changed. SCADE by default implements the persistent SQLite database wrapper so that the data remains static and is time-independent and therefore consistent. With SCADE we use the identical codebase from the iOS app to run it on the Android system. Currently, SCADE offers SQLite database to the users and RealmDB is in development and will soon be available in beta.

Prerequisite

If you haven’t installed the SCADE IDE, download the SCADE IDE and install it on your macOS system. Please make sure that the Xcode version is less than or equal to 13.0 and the Swift version is less than or equal to 5.4, otherwise, it will not be able to run on the Android platform. The support for Swift 5.5 will be released soon. The only prerequisite for SQLite is the Swift language (at least basics). Also, please make sure the Android emulator or physical device is running if you want to run SCADE apps on an android device.

Integrate SQLite

SCADE provides users with all the necessary APIs including Swift foundation and object C libs that are required to integrate the SQLite database. Let’s start by creating a new project, File -> New -> New Project. Then select the SCADE option, enter the name of the project, and click Create. We are ready to integrate SQLite DB into the SCADE app.

Now navigate to the Package.swift file and add the below SPM dependency for the SQLite database.

• GitHub URL: https://github.com/scadedoc/SQLite.swift
• Linked Library: SQLite

Now click on Tools -> Clean & Build the project so that the SQLite classes will be available to use. As the next step, we will include the necessary libraries to support SQLite for the Android platform.

We will download all the library files from this Github repository and will move them to our SCADE project. Create a folder lib at the project level of our SCADE app and move the files as shown in the below screenshot.

CRUD Operations

Let’s discuss the DB operations(Create, Read, Update, Delete) one by one for the SCADE SQLite library. We will first establish the SQLite DB connection and then create the database in the desired location.

DB Connection:

After the successful data.db creation, we will proceed to create a table by passing a table name and then a few column names to create columns.

To insert a record, we need to use the Table’s insert() inbuilt method and pass the key-value params for the columns that we declared above. And then use the database instance to run over the insert record. Please note that this is an async call and needs to be handled with Runtime exceptions.

Similarly for the Update and Delete operation, we need to first filter the record row bypassing the row ID. After that, we will call the inbuilt methods update() and pass the updated values to the column IDs. Further, for the delete operation, we will use the inbuilt delete() method for the filtered row.

Build a SCADE App

We will now develop a SCADE app to demonstrate the SQLite CRUD operations. Let’s create a simple UI design for the same.

Please choose the desired font family, size & color from the design palette.

1. Label: We will need a label to display the current records existing in the DB.
2. RowView: Drag a rowView and place it at the bottom of the page to contain 4 buttons.
3. 4 Buttons: We will need 4 buttons namely, Create, Read, Update, Delete to demonstrate the CRUD operations on the click events.
4. ToolBarItem Label: Drag the toolbar item and set the text to “SQLite SCADE” for the toolBar title.

Now save the project and navigate to the main.page.swift file to code. Create a few dummy data to play around with the DB operations.

  var tempData = [
    ["Vedant", "[e-mail]"], ["Sam", "[e-mail]"], ["Manisha", "[e-mail]"],
    ["Alice", "[e-mail]"],
  ]

Insert Operation:

We will set the click listener for the Create button and insert the data from the tempData list. We will use the keys “name” and “email” to save the values using the insert inbuilt method. If any exception occurs during the operation, will handle it in the catch method.

Read Operation:

To read the data we use the db.prepare() method to iterate through each row one by one and will append the value to the label.

Update/ Delete Operation:

To update the existing row data, we will use the filter method of the Table instance and pass the rowID to update/delete the particular row.

Run the App

Finally, we are now ready to run the project and test it on a Simulator. You will be able to see the app working as shown in the screenshot below.

iOS

Android

Voila🎊! We have successfully integrated the SQLite library into our SCADE app. Integrating the SQLite database is really easy as SCADE works smoothly on both Android and iOS without any pain. You should definitely try the SQLite database SCADE library.

Happy Coding 😊