Oct 20

 Working with Databases

Adding Database

Databases can be accessed from the Resource Menu. Clicking the + will allow you to add a database to your app.

There are various ways of adding a database into your app:

  1. Connect to a DB: Using this option you can add an external database (supported databases as per the list above). You can import the database as Read-Only or as Editable. Know more about Schema Import Modes.
    For user convenience, WaveMaker gives you the option of:

    • hosting a MariaDB database on WaveMaker Cloud,
    • using a Sample DB. You can import this database into your app and try various scenarios, it is a great place to get started on DB services. This is an HSQL database hrdb, with four tables:
      • Department table with deptid, name, code, location and budget figures;
      • Employee table with employee details like id, name, address, picture, date of birth, job title, department id (foreign key), manager id (self-referential key) and username password;
      • Vacation table giving the vacation details like start date, end date, leave status and type, and related to Employee table via empid;
      • User table with name, password and role information.
    • Connect to an external database meeting the WaveMaker requirements.
  2. Upload MySQL files option lets you create a database by importing a DB Script. This option is available for hosting the database on WaveMaker Cloud.
  3. You can Create your own database using the database designer. Using this option you can create MySQL, PostgreSQL, or SQL Server database and host them on WaveMaker Cloud (for MariaDB) or your external database.

Configuring Virtual Primary Key

10.0 release

When connecting to an external Database, if the tables do not have Primary Key, you will be given an option to assign a Virtual Primary Key. It is strongly recommended that you assign Virtual Primary Key to avoid any runtime issues.

While connecting or importing the Database, select the Configure option to assign Virtual Primary Key

You can select column or columns whose values are unique and assign them to be the Virtual Primary Key. This assignment is specific to the app and will not impact the Database.
Note: Post import you can always assign Virtual Primary key from the Database Designer Table properties. Click here for how.

Map Sequences

For databases that support sequences (Oracle, SQL Server, PostgreSQL, DB2, etc.) for the Primary Key generation, the mapping of Primary Key columns to the respective Sequences can be done at the time of connecting to Database.

You also have the option to change the assigned type to sequence and then assign a sequence to the same.

Database Actions

Once you have your app integrated with a database, a copy is created in your local project workspace. While you can access the data through Variables, any changes made to the database at the schema level to the local database or external database has to be synced with its counterpart. The various ways to achieve this are:

Action Description
  Re-Import Database will rewrite the db in your workspace with the actual db thus reverting any changes. It deletes the draft and generates the data model with given database details.
  Export Database will update the db with the changes from your workspace ie the draft database.
  Save Database will save the db in your app workspace, this is the draft copy of the database solely available to your app.
  Update Database will update the db with the changes from your workspace and incorporate changes made by any other collaborator (provided they have exported the changes). This option is available after you make changes to the database schema from the design tab of the database designer.
  Revert Database will revert the db and delete the changes from your workspace. This option is available after you make changes to the database schema from the design tab of the database designer.

Advanced Settings (only for External Databases)

Apart from the basic Database settings like host details, schema name etc., advanced database configuration options need to be provided, when using an External Database. Based upon the basic settings the Advanced Settings are auto-populated but can be modified. Each Database System provides different options so you will see a portion of the options listed below, tailored to the Database System you are using.

Note: For connecting to an external database, you need to Test Connection before proceeding.

Setting Description
Service Name Name used to reference the imported database. By default, this is same as the Database Name, defined in Database Info section (not available for HSQL and MariaDB).
Port The port number for the database. Typically this is set automatically when the database type is selected and should not be changed (not available for HSQL and MariaDB).
Connection URL When you import a database using the standard configuration options on the Import Database screen, WaveMaker creates a JDBC URL for you. This URL is shown here in the Connection URL field. These are usually specific to the database type. For example with MySQL some character set usage could require adding “?useUnicode=true&characterEncoding=UTF-8” to the URL to characters to display correctly. If you know what you are doing and want to change this JDBC URL, you can do it here.
Java Package WaveMaker generates Java classes for you when you import a database. If you want a different Java package name than the default name we generate, type it in here.
Table Filter By default WaveMaker imports all the tables in the database. If you want to import only a subset of the tables, type in a comma-delimited list of regular expressions here (not available for HSQL and MariaDB).
Schema Filter When you are importing a database that supports schemas, WaveMaker imports only the tables for the default schema. If you want to import tables from other schemas, add the schema names in this field.
Driver Class JDBC driver class name. WaveMaker creates a JAR file for each database that uses a specified driver. If you want to use a different JAR file for connecting, specify it here (the JAR file must be in the Application Server’s classpath).
Dialect Hibernate dialect. See Hibernate documentation for details, including the list of available dialects.
Naming Strategy Most users should leave this blank. Enter the name of a Java class you created for handling the naming strategy (not available for HSQL and MariaDB)

5. Creating Backend Services

We use cookies to provide you with a better experience. By using our website you agree to the use of cookies as described in our Privacy Policy.