Creating Secured API Express Project with External PostgreSQL Database

Sample Secured API Express Project built with Ionic framework

Introduction

From this tutorial, you’ll learn how to build a mobile app that displays a list of tasks from a cloud PostgreSQL database hosted on Heroku. The app communicates with the database via REST APIs, automatically generated with API Express.

📘

Want to know more?

You might also like to check our beginner Ionic TodoList Sample App tutorial that uses the Appery.io cloud Database.

Heroku Database

First, let's set up a PostgreSQL database on Heroku. Heroku provides free hosting for PostgreSQL databases.

Creating Heroku App

  1. Sign up to Heroku or log in if you already have an account.
  2. You will see your personal app dashboard. Click New > Create new app:

  1. Enter any app name and click Create app. You can also choose the region so that the database is hosted closer to where you are, but it's not necessary. In a moment, the app is created:

Creating PostgreSQL Database

Now, we need to attach a PostgreSQL database to the Heroku app you've just created.

  1. In the app dashboard, navigate to the Resources tab.
  2. Start typing Heroku Postgres in the Add-ons search field and select the Heroku Postgres add-on in the drop-down:

  1. In the popup that appears, select the Hobby Dev - Free pricing plan and confirm:
681

That's it, your PostgreSQL database is created. You will see it in the list of add-ons:

You can click it and check the database stats:

Filling Database with Data

At the moment, the database is empty. To add data to the created database, you can use various PostgreSQL clients: psql (CLI), pgAdmin, TablePlus, pgweb (GUIs) or others.

In this tutorial, let's use the TablePlus GUI client. You can download macOS or Windows installer from the website and install it on your machine.

  1. Database credentials can be found on the Settings tab. Click View Credentials to get credentials for manual connection to this database:

  1. Run TablePlus and connect to the database. When creating a new connection, you can choose to import directly from URI instead of filling in credentials manually. That’s a much faster and hassle-free option to create a connection using the provided database URI:

🚧

Note

If your machine does not support such import option (like in some versions of Windows OS, for example), you can connect using your credentials instead. Check here to see the example.

  1. Give it some name, test the connection to make sure everything works well and click Connect:

  1. The database has no tables right now, so let's create one. Click the small arrow next to the + button in the bottom and select New Table:

  1. This table will be used to store information about things to be done, so let's rename it to tasks. Also, create a new taskName column of text or varchar type and save the changes (press Ctrl + S / Cmd + S):

  1. Then we need to fill our database with some sample data. Select the created tasks table in the list of Tables on the left. Switch to the Data tab and add several items by clicking the + Row button and filling values in the taskName column. The id values should stay DEFAULT:

🚧

Important Note:

Please check that the table ID is set as a primary key by default. If not, go to the Structure tab and enter it manually:

Save the changes (press Ctrl + S / Cmd + S). Our database is ready, and we can move on to creating an API to work with the database.

Displaying Query Results with Heroku Dataclips

📘

Heroku Dataclips

If you want to quickly view some data from your Heroku Postgres database, you can use Heroku Dataclips. The results of a created SQL query can be shared, viewed in the browser and also downloaded in JSON and CSV formats.

  1. Go to data.heroku.com/dataclips and click New Dataclip or Create Dataclip:

  1. Enter getAllTasks as a dataclip title, select your PostgreSQL database as a datastore and type a simple query that selects all entries from the table: SELECT * FROM tasks. Click Save & Run:

In the table below you will see the query result — the data previously added to the tasks database table:

API Express

You can connect to an external database with the use of Appery.io API Express. There are two main steps to working with the PostgreSQL database and exposing it via REST APIs in API Express:

  1. First, create a database connection.
  2. Then, automatically generate REST APIs for the created database table.

Creating Database Connection

Let's start with the first step and create a database connection.

  1. From the Appery.io dashboard, navigate to the API Express tab.
  2. Click Create new DB connection and enter todoDB as the name for the new connection. Then click Create:

  1. Select Relational database as the Connection type, PostgreSQL as the Database type.
  2. Enter the database credentials that can be found on your Heroku dashboard:

After entering all the values, you can test the connection to make sure that it works correctly.
Scroll down and click Test. You should see a success message. After that, click Save":

Creating API Express Project

Now, when the database connection is created, you need to create an API Express project with actual APIs.

  1. Go back to the API Express tab and click Create new project. Enter a project name, for example todoDatabaseAPI, and click Create:

  1. On the API tab of the project, click the new service link:

  1. In the popup, type tasks as the Service group name. Keep Generate REST API box checked and click Create:
646
  1. On the next page, for Database connection, select the connection you've just created (todoDB). For Table, select the tasks table. For Actions, leave all defaults. Click Generate service to save and generate the APIs:
1236

Create REST APIs for a database table

After the APIs are generated, you should see the following page. You can click the test link to test any of the APIs:

1246

Generated APIs

Mobile App

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

Creating New App

  1. From the Apps tab, click Create new app.
  2. Select Ionic Blank for the application type, enter Ionic todoList for the app name, and click Create.

Importing API Services

Next, we're going to import the API services.

  1. In the Appery.io App Builder, click CREATE NEW > API Express Generator. Leave the Appery.io domain selected and click Next.
  2. If you already have some projects listed, select the todoDatabaseAPI, the API Express project you've just created:

  1. Click the generate link and leave the default service name, tasks, and click Generate:

  1. After generating the services, click Refresh. All the generated services are now listed under the Services folder:

Creating Client Data Model

Next, let's create a model to hold the tasks in the app.

  1. Open Project > Model and Storage > Model. Here, enter Task for a new model name and click Add.
  2. Then in the Task object row, click the Add button to add a new parameter. Rename the parameter to taskName and set its type to String.
  3. Add one more parameter with id for its name and set its type to String:

  1. Create one more model item called TaskList. Set its type to Array and then change the type of TaskList[i] to Task:

Save the changes. Now we can move on to creating the app UI.

Building UI

  1. Every new Ionic app comes with a default Screen1 page. Let's open it: unfold the Pages folder and select Screen1. We won't need the bottom app toolbar, so let's set the Footer property of the Page component to False.
  2. Click the Toolbar title in the app screen and change its default Text property value to My Todo App.
  3. Now, add a Button component to the Toolbar buttons area, and set its properties to:
  • Text = empty
  • Button Color = primary
  • Icon.Style = add icon
  • Icon.Slot = Icon Only:

In our app, we would need a List component for showing the records that we have in the database. So, from the components PALETTE, drag one List component to the screen.

  1. Click the second List item to activate the Context menu and click the red cross to delete it. We won't need the second list item since we are going to use a dynamic service for displaying lists in our app.
  2. Select the ListItem1 component. In the PROPERTIES panel, set its Styles > Class property to todoListItem. Also, add the *ngFor attribute with the let task of taskList value:

  1. Let's now add some flavor to our list element and customize its color. Open the SCSS panel panel and add the following code:
.todoListItem:nth-child(odd) {
    --background: #f2f2f2;
}

  1. The SCSS styles (the item color changes to grey) should be applied on the app DESIGN overview:

Adding Tasks List Button

Okay, it looks good! Now let's add some elements to manage our tasks list.

  1. Under the app DESIGN panel, drag the Button component into the ListItem1 element, placing it to the right.
  2. Now, select the created button and set the following properties:
  • Text = empty
  • Icon.Style = remove icon
  • Icon.Slot = Icon Only
  • (click) = deleteTask(task.id):

  1. Also, select the Styles tab and set the button Button Color property to Danger:

  1. Inside the list item, select ItemLabel1 and set its Text property to {{task.taskName}} (you can use the OUTLINE view menu or the Breadcrumbs for navigating between the app UI components):

Creating Task Page

We will need a separate page for adding a new task to the list, so let's create it.

  1. Click CREATE NEW > Page and name the created page AddTaskPage:

  1. On the new page, disable the Page footer by setting its Footer property to False. Also, change the Toolbar title Text property value to Add new task.
  2. Drag the Button element into the Toolbar buttons area. We will use this button for back navigation. To do this, change the Component Type value to Ionic Back Button in the PROPERTIES panel.
  3. Next, unfold the Back Button group and set the Text property to Back:

  1. To enter the names of new tasks, we will need an Input component. Let's drag it to the page from the PALETTE and set the following properties:
  • Placeholder = Enter task
  • Label.Text = empty
  • [(ngModel)] = newTaskName:

  1. Then, under the input, add another Button that will be used to save the entered tasks. Set its Text property to Add Task:

That's it, the app UI is ready and we can move on to implementing the app logic.

Application Logic

Getting Tasks List

Now let's use the generated services in our app. The first thing we need to do is to get the full list of tasks from the database.

  1. Go to Screen1 CODE panel. In the Variables section, enter taskList as a new variable name, click Add and change the type of the created variable to TaskList:

  1. Now, navigate to the Screen1 DATA panel. Add a new datasource here — the previously generated service tasks_find. Rename it to getTasksList:

  1. Then, we need to display the data that is returned from the service in the list. Click the Mapping button next to the service's Success event.
  2. Map the service response to the taskList variable. Don't forget to click Save & Replace after that:

  1. As we need to load the list of tasks on the page load, let's add the required event. Go back to the page DESIGN panel. In the EVENTS tab, add the Before page showing event for the Page component. Select Invoke service as it action and getTasksList service as the Datasource. Click Save:

The service will be invoked each time you enter the Screen1 page, and the list of tasks will be loaded from our database.

  1. Also, let's set up some routing. We need to navigate to the AddTaskPage when the user clicks the + button in the toolbar. To do that, select the Button2 component inside the Toolbar buttons area and create a new Click event in the EVENTS tab. Select [Navigate to page](https://docs.appery.io/docs/5-event-handling#navigate-to-page) as the action and AddTaskPage as the Route name. Click Save:

At this point, we can test our app in the browser. Click the TEST button in the App Builder Toolbar to launch your app in the browser.
Check that tasks will appear on the app screen, and you can navigate to the AddTasksPage by clicking on the + button in the toolbar. Also, make sure that the back navigation works too:

528

Deleting Tasks

Let's proceed with the app. The next thing we need to do is to implement the functionality to remove tasks from the list.

  1. Go to the Screen1 CODE panel. Here, create a Method named deleteTask with taskId as its argument.
  2. In the list of Services on the left, unfold the API Express > Models > tasks folder. Drag the tasks_delete service into the function code window. This will automatically add the code of the service invocation:

  1. Add some edits to the code. First, insert the Execute DataService snippet instead of console.log(res); (on line 13). There, replace "service_name" with "getTasksList" — the name of the previously imported service. This will reload the list of tasks from the database right after one of them is removed.
  2. Then, add id: taskId to the data block. Save the changes. As a result, the function code will look like this:
this.Apperyio.getService("tasks_delete").then(
    service => {
        if (!service) {
            console.log("Error. Service was not found.");
            return;
        }
        service.execute({
            data: {
                id: taskId
            },
            params: {},
            headers: {}
        }).subscribe(
            (res: any) => {
                this.Apperyio.execDataService(this, "getTasksList"); // Change service_name to created DataSource name
            },
            (err: any) => {
                console.log(err)
            }
        )
    }
)

Adding Tasks

Now we can remove tasks from the list, but we also need the ability to create new ones.

  1. On the CODE panel of the AddTaskPage add a new variable to store the name of a new task. Enter newTaskName as its name and select String type:

  1. Go to the DATA panel and add the tasks_create service as the new Datasource. Change its local name to createTask:

  1. Then, click the Mapping button next to the Before send event and map the newTaskName variable to the taskName service request parameter. The id is generated automatically, so you don't have to provide it. After editing, click Save & Replace:

  1. Also, we need to clear the input field if the task was successfully added. Click Add next to the Success event and select Run TypeScript from the list. In the code editor, type in this.newTaskName = ""; and click Save:

  1. And the last thing we need to do when the task is successfully added is to return to the main page. Сlick the Add button next to the Success event again. Select Navigate to page as the action and Screen1 as the Route name. Don't forget to save the changes:

  1. Finally, we need to invoke the createTask service when the user clicks the ADD TASK button. So, go to the DESIGN panel, unfold its EVENTS tab from the bottom and for the Button2 component, create a new Click event. Select Invoke service as the action and createTask as the Datasource:

Save all the app changes, and we are ready to test the app!

Testing App

Ready! You can click the TEST button in the App Builder Toolbar to launch your app in the browser.
Try removing tasks from the list and creating new ones.