Introduction

In this tutorial, you’ll learn how to integrate an external relational database to an Appery.io app via API Express. You can use your existing database or create a new one. For a quick test, you can create the database using any 3-rd party service, but don’t use these services when going live. You can also use database providers such as Amazon (see this tutorial for more), or quickly signup with trial hosting that offers SQL databases. The following tutorial uses HostBuddy hosting and MySQL database (with API Express, you can use any database) as an example.

Before you begin

Tutorial level: intermediate.
Prerequisites: an Appery.io account.

Creating the database

API Express can connect to any SQL database, but for this tutorial we will use an SQL Database from HostBuddy. 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 SignUp 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, and wait a little, while your hosting is prepared.
4. Once it’s ready, you’ll see a HostBuddy Dashboard. Click Database Manager to the left and choose MySQL Manager.
5. Click +Add Database, type a database name and password. Your database ready to be filled up with data!

Creating a database table

1. Go to PHPMyAdmin by clicking the link provided in MySQL Manager on the HostBuddy website. To the left, select your database and click SQL in the top toolbar:
   
2. Copy the following code and click GO to execute this query. This code will create a new table called users in your database with one id field and one name field:
   
   1 2 3 4

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

3. Select the newly-created table users to the left (under your database) and click SQL in the top toolbar. The following code will fill the table with the some text data. Copy it and click “GO”:
   
   1	INSERT INTO users(id, name) VALUES (1, 'Alex'), (2, 'Bred'), (3, 'Colin');

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

Creating a DB Connection

To create a new DB Connection, go to API Express > Create new DB connection. Enter the required credentials. For HostBuddy,  you can find them under MySQL Manager. For example:

• Database connection name – mysqlhosting
• Connection type – Relational database
• Database type – MySQL
• Host – MYSQL5009.HostBuddy.com (Under Server URL)
• Port – 3306
• Database name – db_9cc333_mydb (Under Database Name)
• Username – 9cc333_mydb (under Login Name)
• Password – password that you typed when creating the MySQL database.

Click Test to check if everything is working.

Creating a Project

Now, after the DB connection is created, you need to create a project on the API Express tab.

1. Go to  API Express > Create new project.
2. Open the project.
3. Click new model in the root level to create a model based on the users table (to generate a new model, click new service. Then select Generate REST API).
4. Type users for Name and select the DB connection you created earlier for Database connection.

Once the model is created, it can be used in the app to create REST services.

Creating REST services via API Express Generator

The API Express Generator extension helps to instantly create multiple REST services based on the created API Express models. In other words, you can create REST services that will refer to the database you connected earlier in just a few clicks.

1. Open the Appery.io app you worked on, or create a new one by clicking Apps > Create new app > Bootstrap AngularJS App or Ionic AngularJS App, enter the name and click Create.
2. In the app, click CREATE NEW > Extensions > API Express Generator.
3. Now, select the project you created in API Express and confirm your choice.
4. Then, click on the root folder icon to show the users model:
5. Now, click generate for users model, enter APIExpressService_users for Service Name, confirm, and then refresh your browser window.

New services and JavaScript assets will appear in the project tree under Services and JavaScript folders after refreshing the browser.

Invoking REST services

Now lets test your newly-created REST services by invoking them via JavaScript and displaying the results in the app UI.

1. Go the Screen1 page.
2. In DESIGN, drag and drop the following components from the PALETTE to the screen:
   • an Input for entering the ID for search. For the Input, enter userid for the ng-model.
   • a Button for calling the function getting records. Change its Text property to Get and set ng-click to get().
   • Text for displaying the name field of the returned record. Set its Text to {{username}}.
3. The result (Bootstrap project):
   
4. Now, switch to the SCOPE tab and add two variables – userid and username. Both type of String.
   The userid variable binds a model to an input, and the username variable is used to display {{username}} in Text.
5. Now, add and define the get function which will be called on the Get button click.
6. Click Edit for the get function and insert the following code:
   
   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   var requestData = {}; requestData = Apperyio.EntityAPI('APIExpressService_users_get.request.data', undefined, true);   var userid_scope = $scope["userid"]; requestData.id = userid_scope;       Apperyio.get("APIExpressService_users_get")(requestData).then(       function(success) {         var username_scope = $scope["username"];         username_scope = success.name;         $scope["username"] = username_scope;         $scope.$apply();           },     function(error) {         alert('Error on get object');     });

So, the userid is mapped to the id in the request and, in the case of success, the name is mapped to the username in response.

Also, if there’s a successful response,UI changes will be applied by using $scope.$apply(); .

Click TEST to test the app, type a numerical id (for example, 1) and click Get. You’ll see the name associated with that id.