Creating APIs for Relational Database

Exposing a relational database via REST APIs in a jQuery Mobile application

Introduction

In this tutorial, you are going to learn how to expose a relational database via REST APIs. Once a database is exposed via APIs you can easily use them in a mobile app. This tutorial uses HostBuddy hosting and MySQL database. You can use any other relational database with API Express.

The purpose of this tutorial is to show you how fast it is to expose a relational database via REST APIs.

📘

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 setup a database a Postgres Database On Heroku.

To set up your database, follow these steps:

  1. Go to the HostBuddy website and click Start Free Trial. A credit card is not required for the trial, so just enter your credentials, such as login name, email, 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, wait a little, while your hosting is prepared.
  4. Once it’s ready, you’ll see a HostBuddy Account Center page. Click Databasesto open the Database Manager.
  5. Click +MySQL Database, type in the database name and password, and confirm. Your database is ready to be filled up with data: DB name, MYSQL Server, and Username with Password (you will need it in the next step):

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)
);
  1. Click Go:

A database with one table will be created.

  1. Select the newly created table users on the left (under your database) and click SQL in the top toolbar.
  2. 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 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.

API Express

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

  1. Create a database connection.
  2. Automatically generate 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 API Express > Create new DB connection. Enter the required credentials. For HostBuddy, you can find them under MySQL Manager. For example:

Parameter name

Parameter value

Database connection name

mysqlhosting

Connection type

Relational database

Database type

MySQL

Host

mysqlxxxx.site4now.net. This value can be found under Database Manager > MySQL Database, as MYSQL of the needed database table.

Port

3306

Database name

db_xxxxx_mysqldb. This value can be found Database Manager > MySQL Database of the needed database table..

Username

xxxxxx_mysqldb. This value can be found under Database Manager > MySQL Database, as Username of the needed database table.

Password

The password you typed in when creating the corresponding MySQL database.

Click the Save button to save the connection.

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: MyDatabaseAPIs 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: people. Keep Generate REST API checked.
  6. Click the Create button.
  7. On the next page, for Database connection, select the connection you just created (mysqlhosting).
  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:

REST APIs.REST APIs.

REST APIs.

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:

Service test - GETService test - GET

Service test - GET

App (client)

Now that you have exposed a database via REST APIs (created the app backend), the next step is to create a mobile app that will use these APIs. In this tutorial, you have the option to create a jQuery Mobile app.

Building JQM App

In this section, you are going to build a jQuery 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. Select the JQuery Mobile tab and enter DatabaseApp for the app name.
  4. Select the Blank template and click Create:
Creating new appCreating new app

Creating new app

Designing App UI

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

  1. When the App Builder loads the new app open Pages > startScreen.
  2. Drag & drop the List component into the page.
  3. This component has three items. As you will be loading the data from an API you only need one item. Select item 3 and item 2 and remove them by hovering over the list item and clicking the red x icon.
  4. Drop two Label components to the list. For the first label, set userID for the Name field under the PROPERTIES panel, for the second one, set userName for the Name property:
  1. Click on the Save button on the top left side.

Importing API Services

Next, you are going to import the API services.

  1. In the App Builder, select CREATE NEW > API Express Generator.
  2. Keep Appery.io for the domain and click Next.
  3. Select the API Express project you have just created (MyDatabaseAPIs) and click Select.
  4. Click the generate link on the right, then the generate button (keep the Service Name default).
  5. After generating the service, click the Refresh button.

All the generated services should now be listed under the Services folder:

Defining Service

  1. Select startScreen and open its DATA tab.
  2. Select the Service type, then and select people_find from the drop-down for a new datasource. Click the Add button to add this service. You can rename to what is needed.
  3. Click Mapping for the Success event, click Expand all for both Service response and Page** panels.
  4. Map the items by dragging and connecting elements on either side:
    Map $[i] to mobilelistitem (the name of this item may be like mobilelistitem_X)
    id to userID > Text
    name to userName >Text
  5. Click Save and return:

Invoking Service

  1. Switch to the startScreen > DESIGN tab and unfold the EVENTS panel from the bottom.
  2. For the COMPONENT select startScreen.
  3. For the EVENT, set Page show.
  4. For the ACTION > Invoke service, select your service, and click on the Save button under the EVENTS tab:

Testing App

To test the app, simply click the Test button inside the App Builder. The app will launch in the browser and you should see the users from the database:

For more information, you can watch the video tutorial about How to Build an App with API Express and External REST API for JQM framework.