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:
- 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 clickSignUp
to continue. - You will shortly receive an email from HostBuddy, so click on the activation link provided there.
- Sign in to the HostBuddy website and click
Try Now
to start using the free trial. No need to change anything, just clickNext
,Submit
, and wait a little, while your hosting is prepared. - Once it’s ready, you’ll see a HostBuddy Dashboard. Click
Database Manager
to the left and chooseMySQL Manager
. - Click
+Add Database
, type a database name and password. Your database ready to be filled up with data!
Creating a database table
- 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:
- Copy the following code and click
GO
to execute this query. This code will create a new table calledusers
in your database with oneid
field and onename
field:
1234CREATE TABLE users(id INT PRIMARY KEY NOT NULL,name VARCHAR(32)); - Select the newly-created table
users
to the left (under your database) and clickSQL
in the top toolbar. The following code will fill the table with the some text data. Copy it and click “GO”:
1INSERT 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
(UnderServer URL
) - Port – 3306
- Database name –
db_9cc333_mydb
(UnderDatabase Name
) - Username –
9cc333_mydb
(underLogin 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.
- Go to
API Express > Create new project
. - Open the project.
- Click
new model
in the root level to create a model based on theusers
table (to generate a new model, clicknew service
. Then selectGenerate REST API
). - Type
users
forName
and select the DB connection you created earlier forDatabase 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.
- Open the Appery.io app you worked on, or create a new one by clicking
Apps > Create new app > Bootstrap AngularJS App
orIonic AngularJS App
, enter the name and clickCreate
. - In the app, click
CREATE NEW > Extensions > API Express Generator
. - Now, select the project you created in API Express and confirm your choice.
- Then, click on the root folder icon to show the
users
model: - Now, click
generate
for users model, enterAPIExpressService_users
forService 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.
- Go the
Screen1
page. - In
DESIGN
, drag and drop the following components from thePALETTE
to the screen:- an
Input
for entering the ID for search. For theInput
, enteruserid
for theng-model
. - a
Button
for calling the function getting records. Change itsText
property toGet
and setng-click
toget()
. Text
for displaying the name field of the returned record. Set itsText
to{{username}}
.
- an
- The result (Bootstrap project):
- Now, switch to the
SCOPE
tab and add two variables –userid
andusername
. Both type ofString
.
Theuserid
variable binds a model to an input, and theusername
variable is used to display{{username}}
inText
. - Now, add and define the
get
function which will be called on theGet
button click. - Click
Edit
for theget
function and insert the following code:
12345678910111213141516171819202122var 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
.