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 to your app:
Connect to a DB
Using this option you can add an external database. You can import the database as Read-Only or as Editable. For more information, see Schema Import Modes.
With Connect to a DB option, you get the following options to work with:
1. MariaDB
Host a MariaDB database on WaveMaker Cloud.
2. 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
andbudget 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) andusername
andpassword
. - Vacation table giving the vacation details like
start date
,end date
,leave status
andtype
, and details related toEmployee table
viaempid
. - User table with
name
,password
and role information.
3. External Database
Connect to an external database. For more information, see Supported Databases and Version Matrix.
Upload MySQL files
This option lets you create a database by importing a DB Script. This option is available for hosting the database on WaveMaker Cloud.
Create your own DB
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.
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.
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). Also,the database service name can only be given at the time of importing the database. |
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) |
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.
After importing, you can always assign Virtual Primary key from the Database Designer Table properties. For more information, see Virtual Primary Keys and Relationships.
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, schema information will be persisted in the WaveMaker. 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 | 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 | Export Database will update the db with the changes from your workspace ie the draft database. |
Save 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 | 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 | 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. |