Develop a modern web app using EDB, an enterprise-class PostgreSQL database-as-a-service

In this code pattern, we walk you through a working example of a web application that tracks a company's internal support ticket system. The app will allow users to create, assign, manage, and close support tickets.

All support tickets, users, and support staff will be maintained in a Databases for EDB deployment provisioned on IBM Cloud. EDB is a PostgreSQL-based database that provides features such as: high availability, automated backup orchestration, and de-coupled scaling of storage, RAM, and vCPUs.

Other featured technologies in this code pattern include:

• Sequelize: A Node.js Object-Relational Mapper (ORM) for EDB, Postgres, MySQL, and other relational databases.
• Node.js: An open-source JavaScript run-time environment for executing server-side JavaScript code.
• Express: A popular and minimalistic web framework for creating an API and Web server.
• Vue: A JavaScript framework for building web app user interfaces.
• Vuetify: A Material Design component framework for Vue.js apps.
• psql: A command-line interface utility for managing PostgreSQL databases.

When you have completed this code pattern, you will understand how to:

• Provision an EDB for Databases instance on IBM Cloud.
• Use Sequelize to programmatically map objects to your relational database.
• Create a modern web app built on Node.js, with an Express server for REST APIs, and a Vue-based UI.
• Use psql scripts to seed the database tables with tickets, users, and support staff.

Flow

1. Administrator uses psql to seed EDB tables with user, assignee, and ticket data.
2. User interacts with the Node.js app to create and maintain ticket information. The Vue-based client UI accesses EDB data via the Express server REST APIs.
3. The server uses Sequelize to perform CRUD operations on the EDB instance.

Steps

1. Clone the repo
2. Provision the "Databases for EDB" service
3. Add service credentials to environment file
4. Load sample data
5. Run the application
6. Use the app
7. Review the application structure

1. Clone the repo

Clone the edb-tickets repo locally. In a terminal, run:

git clone https://github.com/IBM/edb-tickets
cd edb-tickets

Note: Example commands below will assume you ran the cd edb-tickets command to start from the base directory of your cloned repo.

2. Provision the "Databases for EDB" service

• If you do not have an IBM Cloud account, register for a free trial account here
• Create a Databases for EDB instance from the IBM Cloud catalog
  • Verify the default region, and modify the instance name if you like.
  • Keep all other default options and values.

3. Add service credentials to environment file

Copy the local env.sample file and name it .env:

cp env.sample .env

You will need to update the .env file with the credentials from your EDB service. Here is an example .env file showing the credentials you will need to collect:

# Copy this file to .env and replace the credentials with
# your own before starting the app.

DB_USERNAME=admin
DB_PASSWORD=*******
DB_DATABASE=edb-tickets
DB_HOST=Aaa12345-ed12-45d6-aa63-0d2a83dcc93b.bn2a2uid0de8vv7mv2ig.databases.appdomain.cloud
DB_PORT=32465
DB_CERTFILE=/users/username/edb-cert

To find your DB_HOST and DB_PORT values, navigate to your EDB service panel and click the Overview tab.

Copy and paste the Certificate into a local file, and provide the name as the DB_CERTFILE value.

For convenience, we're using the admin account as the DB_USERNAME. To set the DB_PASSWORD for the admin account, use the Update Password option located in the Settings tab.

NOTE: You can create additional credentials...

• Use Service credentials in the left menu.
• Click on New credential +.
• Once created, use the expand icon and copy the parts you need.

For the DB_DATABASE value, keep the default name edb-tickets or choose your own name. The service is provisioned with a database named ibmclouddb, but we can create a new one.

4. Load sample data

You can create, edit, assign, and close tickets using the app, but we recommend loading some fake data to get things started. We have provided shell scripts that use psql to help you create a database, create tables and indexes, and load the database with a staff to assign tickets to, some fake user IDs, and a few issues so you don't have to start with an empty user interface.

1. Install psql

   We're using the command-line tool psql. psql is a very common tool used for EDB and PostgreSQL databases. Even if you prefer using one of the many available GUIs, it is worth getting comfortable with psql so that you have a tool that you can use in scripts and whenever you need to work in a terminal without a browser. Another reason we are using psql is because it has a handy way of loading data from a CSV file.

   Instructions for downloading and installing psql are here.

2. Verify your .env file settings

   The scripts below will use the settings in your .env file. Please verify that you provided these settings as described in Step 3.

   TIP: psql will prompt you for your database password (instead of reading it from the .env). If you would like to avoid those prompts, setup a .pgpass file as described here.

3. Run the shell scripts

   Our simple shell scripts will read your .env file and then run psql along with one of our SQL files. Before you run them all, consider the following:

   • If the DB_DATABASE that you configured already exists, then you can skip create_database.sh.
   • If you skipped ahead and already ran the application (i.e. npm start), then you can also skip create_tables.sh.
   • load_data.sql is the one that loads sample data into those tables in that database.

   The scripts can be found in the data directory and need to be executed from that directory. Please take a look at the *.sh, *.sql, and *.csv files in the data directory to see what these scripts and SQL are really doing.

4. Run the following commands:

   cd data
   ./create_database.sh
   ./create_tables.sh
   ./load_data.sh

5. Run the application

1. Install the Node.js runtime.

2. Build and start the app by running the following commands:

   npm install
   npm run build
   npm start

3. The application will be available in your broswer at http://localhost:8080.

When the app starts it will connect and sync to the database. If needed, you can modify the code to drop the database tables and start fresh. The code is located in app.js file.

(async () => {
  // Clobber and recreate the tables for testing
  //await models.sequelize.sync({ force: true });

  // Don't clobber the tables, but alter and migrate if needed
  await models.sequelize.sync({ alter: true });
})();

For UI Developers

1. Run the command shown above to start the server with the REST API and a build of the UI.
2. Run the command below to start a development server so you can work on the UI and see your changes.
3. The development server will take the next available port so it should be accessible at http://localhost:8081.
4. Keep in mind that when using this development app, it will still rely on the REST services provided by the server running on port 8080.

npm run serve

6. Use the app

The app is a typically help desk application that tracks tickets for service requests. Users create the ticket, the ticket is assigned to a member of the support staff, and the ticket can be updated with current status as it progresses to being completed and closed.

Anyone who logs into the app is considered a valid user, and will be added to the database as such. Any valid user can add or modify tickets, but only support staff personnel can be assigned to work on a ticket.

To start, you will need to login to the app by clicking the avatar located in the top right corner of the home page. Since this data will be stored in the EDB cloud service, we suggest using an alias for username and email.

Once logged in, you will now have access to all of the menu options.

NOTE: You will not be able to create a ticket until you log in.

The All Tickets menu option displays all tickets in the database. Tabs are used to separate out Open and Closed tickets.

The Support Staff menu option displays all users who are designated as support staff. It also provides an option to add new support staff users.

NOTE: Only support staff users can be assigned to a ticket.

The New Ticket menu option displays a form you can use to create a ticket. Here you are required to enter a Subject and Description, and select a Category and Priority. The new ticket will automatically assign you as the created by user.

To assign a support staff member to work on the ticket, Edit the ticket by clicking the pencil icon shown in the Actions column for the ticket in the All Tickets panel.

From the Edit Ticket panel you can also change the current status, and other ticket details.

To delete a ticket, click the trashcan icon shown in the Actions column for the ticket in the All Tickets panel.

7. Review the application structure

Database support

Each of the database tables in EDB will be descibed by their respective files found in the /models directory. The files specify the field names and any relationships that exist between tables.

Files located in the /controllers directory define what access the server has to the database tables, and how to perform that access.

Here is an entity-relationship diagram of the tables in our EDB database. A ticket is linked to a user (who created the ticket) by user_id, and to an assignee by assignee_id. An assignee is linked to a user by user_id.

NOTE: in /controllers/ticket.js, you will see the Sequelize command Upsert used when a ticket is added. This insures that the created by user exists in the user table. If they do not, the user is added at the same time as the ticket. This ensures data integrity.

Express routing

The Express framework is used to define route paths in the server.

Files found in the /routes directory set up the REST endpoints for accessing the data in each of the EDB tables.

Vue components

Vue components are used to build the app UI, and can be found in the /src directory.

The main Vue component is App.vue, which defines the page banner, the login panel, and navigation links to the sub-pages.

Each sub-panel in the app has its own Vue file, which is located in the /src/pages directory.

Re-usable sub-components can be found in the /src/components directory.

Learn more

To learn more, visit the IBM and EDB product page and check out the IBM Hybrid Data Management Community.

License

This code pattern is licensed under the Apache License, Version 2. Separate third-party code objects invoked within this code pattern are licensed by their respective providers pursuant to their own separate licenses. Contributions are subject to the Developer Certificate of Origin, Version 1.1 and the Apache License, Version 2.

Apache License FAQ