Introduction

The purpose of this tutorial is to show you how fast it is to expose a relational database via REST APIs and integrate it into an Ionic mobile app.

This tutorial uses HostBuddy hosting and a MySQL database. You can use any other relational database with API Express from Appery.io.

📘

Custom SQL Query

If you would like to write a custom SQL query and expose it via a REST API, use the SQL component to do that.

Creating Database

API Express can connect to any SQL database. In this tutorial, we will use an SQL database from HostBuddy.
Alternatively, you can set up a database as a Postgres Database On Heroku.

To set up your database, follow these steps:

1. Go to the HostBuddy website and start a free trial. A credit card is not required for the trial, so just enter your credentials, such as login name, email, and password, and click Sign Up to continue.
2. You will shortly receive an email from HostBuddy, so click on the activation link provided there.
3. Sign in to the HostBuddy website and click Try Now to start using the free trial. No need to change anything, just click Next, Submit, then wait while your hosting is prepared (you will be notified by an email that your hosting account is activated).
4. Once it’s ready, click the corresponding button to log in to your hosting control panel:

5. Now, click DATABASES to open the Database Manager and then click the +Add Database button:

6. Type in the database name and password, and confirm.
   Your database is created and ready to be filled up with data: DB name, MYSQL Server Name, and Login ID with Password (you will need those in the step described in the Creating Database Connection section):

Creating Database Table

In this step, you are going to create a database table.

1. For the database you have just created, click the Actions button on the right and select phpMyAdmin from the drop-down:

2. Once the phpMyAdmin dashboard opens, input the server, username, and password. Click Go.
3. To create a new table, go to the SQL tab in the top menu bar, select the database on the left, and paste the code below:

CREATE TABLE users(
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(32)
);

4. Then click Go:

A database with one table will be created.

5. Select the newly created table users on the left (under your database) and click SQL in the top toolbar.
6. Replace the default code in the SQL editor with the following SQL query to insert three users and click the Go button.

INSERT INTO users(id, name) VALUES (1, 'Alex'), (2, 'Bred'), (3, 'Colin');

You should now see that 3 rows have been inserted: the three created users records can be seen when you select the users collection:

Now, that the database is ready, we can proceed with creating a database connection for API Express.

📘

How to Add API Express into Mobile Application

You might also like this video for detailed instructions on how to:

• create a Database connection to a relational DB,
• expose the relational DB data as a REST service and
• use this functionality in an Appery.io Ionic mobile app:

Exposing Database via REST APIs

There are two general steps in working with this database and exposing it via REST APIs in API Express:

1. Creating a database connection.
2. Automatically generating REST APIs for a database table.

Let's start with the first step: creating a database connection.

Creating Database Connection

To create a new database connection, go to the API Express tab, click Create new DB connection, and provide a name for a new connection, for example, mysqlDB:

Now, you can proceed with providing your external relational database data.

📘

Note!

For HostBuddy, your DB credentials can be found under MySQL Manager.

For example:

After the required credentials are selected/entered, click the Save button to save the connection:

📘

Check this section to find the Host, Database name, and Username credentials.

Testing Connection

Once you are done entering all the connection values you can test the connection to ensure it works correctly. Simply click the Test button. You should see the Test successful message:

Creating API Express Project with APIs

Now that the database connection is created, you need to create an API Express project that will hold the actual APIs.

1. From the Connection tab, open the connection list and select Back to list:

2. In the Projects section, click Create new project.
3. Enter a project name: mysqlProject and optionally enter a description. Then click Create:

4. From the API tab, click the project new service link:

5. For Service group name, enter users. Keep Generate REST API checked.
6. Click the Create button:

7. On the next page, for Database connection, select the connection you just created (mysqlDB).
8. For Table, select the users table.
9. For Actions, keep all the default values.
10. Click the Generate service button to save and generate the APIs:

When the APIs are generated you should see the following page:

Congratulations, you have just exposed a relational database via REST APIs!

Testing APIs

To test any API, click the test link on the right:

On the Service test page, you can test any of the API generated:

Building Ionic App

Now that you have successfully exposed your database via REST APIs (created the app backend), the next step is to create a mobile app that will use these APIs.

In this section, we will demonstrate how to create a simple Ionic Mobile app that uses the API you just created.

Creating New App

1. Go to the Apps page.
2. Click the Create new app button.
3. Enter IonicDatabaseApp for the app name.
4. Select the Ionic Blank template and click Create.

In a moment, you will find yourself inside Appery.io App Builder.

Designing App UI

The app UI will be very simple and consist of just the List component.

1. When the App Builder loads the new app, open Pages > Screen1.
2. Go to the page Header and under the PROPERTIES view, set the Toolbar Title component's Text property as Ionic Database App. You can also do it by typing in right in the mobile frame:

3. Drag the List UI component and drop it to the Content area.
   A list with two default List item each having a child Item Label component will be created.
4. Delete the second List item since we will be loading the data from the API:

4. You can now save the UI by clicking the SAVE button in the App Builder Toolbar.

Importing API Services

To work with the services, we will need to import the API services.

1. In the App Builder, select CREATE NEW > API Express Generator.
2. Select the API Express project you have just created (here, mysqlProject).
3. Click the generate link on the right, then the Generate button (keep the Service Name default).
4. After generating the service, click the Refresh button.

All the generated services should now be listed under the Services > API Express > Models > users folder:

Defining Service

To get the list of database records, we will also need to create a datasource and it's time that the created service be added to the page.

1. So, go back to the Screen1 page and open its DATA panel to add the needed datasource:
2. Select users_find1 from the drop-down for a new datasource and click the Add button to add this service. You can rename it to what is needed.
3. Now, click the Success Mapping button:

4. In the Mapping editor, click Expand all for both Service response and Page panels, recreate the below service mapping, and then click Save & Replace:

Invoking Service

The final step is invoking the datasource we have just added.

1. Switch to the page DESIGN panel and expand the EVENTS tab from the bottom.
2. For the COMPONENT, select Page.
3. For the EVENT, set After page showing.
4. For the ACTION, set Invoke service, select your service (here, users_find1), and click the Save button:

Testing App

You can now save your work and click the TEST button in the App Builder Toolbar to run the app in the preview.
The app will launch in the browser and you should see the users from your relative database:

📘

Want to Know More?

To learn how to expose a relational database via REST APIs and use it a jQuery Mobile application, check this document or watch the video tutorial about How to Build an App with API Express and External REST API for JQM framework.