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 5 mobile app.
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.
To set up your database, follow these steps:
- 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, password, and click Sign Up 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 click Next, Submit, then wait while your hosting is prepared (you will be notified by a email that your hosting account is activated).
- Once it’s ready, click the corresponding button to log in to your hosting control panel:
- Now, click DATABASES to open the Database Manager and then click the +Add Database button:
- 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):
In this step, you are going to create a database table.
- For the database you have just created, click the Actions button on the right and select phpMyAdmin from the drop-down:
- Once the phpMyAdmin dashboard opens, input the server, username, and password. Click Go.
- 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) );
- Then click Go:
A database with one table will be created.
- Select the newly created table users on the left (under your database) and click SQL in the top toolbar.
- 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:
There are two general steps in working with this database and exposing it via REST APIs in API Express:
- Creating a database connection.
- Automatically generating REST APIs for a database table.
Let's start with the first step: creating a 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.
For HostBuddy, your DB credentials can be found under MySQL Manager.
Database connection name
xxxxxx_mysqldb. This value can be found under DATABASES >Database Manager > MySQL Manager, as Login ID of the needed database table.
The password you typed in when creating the corresponding MySQL database.
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.
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:
Now that the database connection is created, you need to create an API Express project that will hold the actual APIs.
- From the Connection tab, open the connection list and select Back to list:
- In the Projects section, click Create new project.
- Enter a project name: mysqlProject and optionally enter a description. Then click Create.
- From the API tab, click the project new service link:
- For Service group name enter: Users. Keep Generate REST API checked.
- Click the Create button:
- On the next page, for Database connection, select the connection you just created (mysqlDB).
- For Table, select the users table.
- For Actions, keep all the default values.
- 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!
To test any API, click the test link on the right:
On the Service test page, you can test any of the API generated:
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 5 Mobile app that uses the API you just created.
- Go to the Apps page.
- Click the Create new app button.
- Select the Ionic 5 tab and enter Ionic5DatabaseApp for the app name.
- Select the Ionic 5 Blank template and click Create:
In a moment, you will find yourself inside the Appery.io App Builder.
The app UI will be very simple and consist of just the List component.
- When the App Builder loads the new app, open Pages > Screen1.
- Go to the page Header and under the PROPERTIES view, set the Toolbar Title component's Text property as Ionic5DatabaseApp:
- You can now save the UI by clicking the SAVE button in the App Builder Toolbar.
To work with the services, we will need to import the API services.
- In the App Builder, select CREATE NEW > API Express Generator.
- Select the API Express project you have just created (here, mysqlProject).
- Click the generate link on the right, then the Generate button (keep the Service Name default).
- After generating the service, click the Refresh button.
All the generated services should now be listed under the Services > API Express > Models > users folder:
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.
- So, go back to the Screen1 page and open its DATA panel to add the needed datasource:
- Select users_find from the drop-down for a new datasource and click the Add button to add this service. You can rename to what is needed.
- Now, click the Success Mapping button:
- In the Mapping editor, click Expand all for both Service response and Page panels, recreate the below service mapping, and then click Save & Replace:
The final step is invoking the datasource we have just added.
- Switch to the page DESIGN panel and expand the EVENTS tab from the bottom.
- For the COMPONENT, select Page.
- For the EVENT, set After page showing.
- For the ACTION, set Invoke service, select your service (here, users_find), and click the Save button:
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.
Updated 19 days ago