How Mendix can work with your Existing Database

Problem Statement

In some cases, the solution needs to work with organizations who want to keep the Database separately from Mendix or want to use the existing database.

1. Solution

First, it is possible to build a Mendix application and to bring your own database. But the question is how your Mendix application can work seamlessly with your existing database tables & data. The solution(s) we are discussing and elaborating here is not applicable in case the existing application is being developed with a Microservices or Service Oriented Architecture, with this architecture mostly the database operations are handled using APIs.

There are ways Mendix can work with existing database tables, but how? Each of our problem statements is different, but it can be broadly classified and sectioned into a few questions that we need to answer and identify the right approach for our problem statement(s)

1.1 Questions

1. How do we want to provide access to the database?

• Full Control to Mendix
• Limited access to user roles, e.g. Read only
• Whether the production needs database administrator intervention to approve the DML statements before the application installation?

2. Are we going to share this database with other applications?

3. Are we migrating the application, which uses your current database, to Mendix?

4. Do you want to migrate Mendix database but a different database like MSSQL to Postgres using Mendix?

1.2 Indium Matrix for using Existing Database in Mendix

Below are the tools and techniques to choose from

1. Database Replication

2. Database Connector

3. Mendix Modeller Configuration

4. Database as a Web Service – REST, OData, SOAP

5. Mendix Platform SDK (Programmatic solution)

We at Indium Software put together our knowledge and experience with the simple matrix to choose the right option at given point in time based on the problem the customer faces.

 

2. Tools

2.1  Database Replication

Database Supported	Type	Category	Company	Support	Link
RDMS	Module	Modules	Mendix	Platform	DB Replication

2.1.1 About

You can use the Database Replication module to import data from existing databases into your Mendix application. You have the ability to specify the mapping for each table, column, and relationship to your Mendix domain model. Even complex mappings involving multiple table joins can be achieved. The configuration can be done either in the client or using Java.

2.1.2 Typical Use Cases

• Convert an existing database to a Mendix domain model.
• Integrate your programme with a database used by another programme.
• Create mappings between database columns and object attributes
• Map database references to Mendix object references
• Map object attributes based on multiple joined tables

2.1.3 Features

• Support for custom queries for object attributes
• Compatibility with SQL Server 2005 or later, Oracle, AS400, DB2, PostgreSQL, DMS2, and Informix database systems.
• Assistance with non-persistent objects
• Automatic query generation for object attribute values
• Object events are executed when importing

https://marketplace.mendix.com/link/component/160

2.1.4 Advantages

• You can configure how you want to import the data using multiple options which gives the flexibility.
• Easy to use.
• Reduce the efforts of migration.

2.1.5 Limitation

• If  you are using the Excel Importer, then  you will need Excel Importer 3.0 or higher when using this module
• Consumes lots of memory, since all the values need to be remembered to keep the track of all the key changes
• It commits the object even though there are no changes to trigger the events, which can overburden the app.

2.1.6 Database Sync Process (Optional)

The data is updated in both systems thanks to scheduled synchronization between your application and a database used by another application.

Typical use case, during the phases of migration of the application. We do not recommend keeping two copies of the same data. This will cause the data to be inconsistent. So, choose wisely.

2.2  Database Connector

Database Supported	Type	Category	Sub Category	Company	Support	Link
Mendix Guide	Module	Addons	Connectors	Mendix	Platform	DB Connectors

 

 

GitHub Link: https://github.com/mendix/database-connector

2.2.1 About

The Database Connector allows for a quick connection to external DBs (databases), offering you the freedom to choose from a wide range of databases and SQL dialects. This enables you to integrate your external data directly into your Mendix application without any limitations.

The connector supports below functionality to execute queries at your databases: 

• Run (Execute) query – For executing SELECT queries and obtaining a list of objects as a result
• Run(Execute)  statement – For executing other DML commands and obtaining either an integer or long value indicating the number of rows affected.
• Run(Execute)  parameterized query – For executing SELECT queries with input parameters, resulting in a list of objects.
• Run(Execute)  parameterized statement – For executing other DML commands with input parameters and getting either an integer or long value representing the number of rows impacted.
• Execute callable statement – For executing a callable statement.

2.2.2 Prerequisites

These are the prerequisites for using this connector:

• A database connection URL address that points to your database
• The username for logging into the database, with respect to the database connection url address
• The password for logging into the database, with respect to the database connection url address
• Add necessary JDBC driver libraries (.jar files) in the userlib directory of Mendix application
  • For e.g., if Mendix app needs to establish the connection to the Cloud PostgreSQL database (jdbc:postgresql://<instance URL>:5432/postgres), we need to put the corresponding PostgreSQL JDBC driver .jar inside the userlib folder.
• Relevant to the Execute Query action: a domain model entity that can be utilized to hold the results of the executed query
  • For instance, if you have a query such as “select name, number from stock”, which has two columns (of string and integer data types respectively), to use the Execute Query action, you must add an entity in the domain model with attributes that match the columns in the query.

2.2.3 Advantages

• Database connector is maintained with single threaded, avoiding memory leakages
• Ability to connect to multiple databases in a single application (a composite microservices)

2.2.4 Limitation

• The parameterized actions are only available with Database Connector versions 3.0.0 and above. For these, it is necessary to use Mendix 8.6.0 and above.            
• You can face memory issues for large data sets.
• Doesn’t have any configurations on thread pool size, connection timeout, etc.

2.3  Mendix Modeller Configuration

These settings can be configured as follows:

• Studio Pro – To access the option to connect to a database in Studio Pro, go to the App Explorer, view the App, open Settings, edit a configuration, and check the Configuration tab. select either the Default Configuration or Active Configuration to display the option.

2.3.1 Prerequisites

1. Type: Currently supported RDMS databases
2. URL:  Database URL that points to your Database with the port. For example, if you want to connect to the Cloud PostgreSQL database, <postgresinstance_url>:<port>
3. Database Name: Your Initial Database
4. Use Integrated Security, Applicable only for MSSQL (Microsoft SQL Server) Database
5. The username and password for logging into the database, relative to the database URL address
6. If the database connection requires a self-signed certificate to establish the connection, then add a Certificate in Certificates Tab

2.3.2 Advantages

• Easy to use connection can be achieved easily by proving the details.       

2.3.3 Limitation

• You can connect to the databases which are available in the list, else you need to use connectors.
• For Production in Mendix Cloud, only PostgreSQL is available.
• To use Integrated Security with MSSQL, the Mendix application should be deployed in Windows Server(IIS)

2.4  Database as a Webservice

2.4.1 About

Web Services provide a solution to the interoperability issue by enabling different applications to connect their data. With Web Services, you can transfer data between diverse applications and platforms. To allow Mendix to use an existing database, you can expose the required functionality as a Service, making it easily accessible by Mendix.

Mendix supports the most widely used web service standards, including SOAP, REST, and OData. However, creating a wrapper for an existing database to connect with Mendix may require additional effort. The recent trend towards Service-Oriented Architecture or Microservices promotes API-based connectivity, which is effortless and efficient in Mendix.

On the other hand, if the database is not being utilized by any other applications, it is recommended to use the Data Connector or Data Replication to fully leverage the capabilities of Mendix.

2.4.2 Mendix Data Hub

There are few advantages when using Database as a web service when it is exposed as OData. Mendix provides a premium service called Data Hub.

The Mendix Data Hub Catalog is a comprehensive and open metadata repository that is based on industry standards, allowing developers and business experts to find and explore data resources within their interconnected ecosystem.

Data Hub Connectors enable organizations to integrate their data sources with Data Hub, thereby enhancing the catalog and making the data available to developers. Connecting to data from Mendix applications, Siemens Teamcenter, SAP, and numerous other commonly used enterprise data sources can be done with ease.

Refer: https://www.mendix.com/data-hub/

With Mendix Data Hub you find all data that is available across your Company’s software landscape and use it in your Mendix projects.

Share Data between Mendix Apps – Use and edit Data Assets from one Mendix app in another.

Connect to Non-Mendix Apps – Build an OData wrapper around your non-Mendix App to connect.

Integrated in Studio (Pro) – Use the Data Hub Panel in Studio Pro to search and use for Data Assets.

2.4.2.1  How to use Data Hub

Search – Finding Connectable Data Sources

Users can find shared datasets by searching the Data Hub Catalog

Register – Sharing Datasets

To make the data from your apps accessible to others, you can publish the datasets as an OData service and register it in Data Hub. In a Mendix application, the datasets correspond to the Entity sets for a specified Entity

Consume – Using Registered Datasets

Assets that have been registered in the Data Hub Catalog can be utilized in the Mendix Studio Pro for app development. These external data sources are displayed in the domain model as external entities, which can be combined with local entities.

Curate – Maintaining Registered Assets

To make sure the right people find your service, you can edit app owners, add tags and descriptions, and toggle discoverability.

2.4.2.2  Advantages

• DataHub has versioning, so you can stick to a specific version of the data and it is not required to change after structure is changed in the parent.
  • Latest Mendix  version supports CRUD operations in DataHub, which helps to maintain a single source of truth.

2.4.2.3  Considerations

• There will be some rework when DataHub data version is changed.