Data Querying & Management
Which Query Languages Does Mendix Support?
Mendix offers a number of ways to specify the data you want to retrieve:
- Mendix Studio Pro offers visual ways to specify your query needs via an expression editor
- To retrieve specific objects or a set of related objects, you can use XPath expressions (see XPath below)
- For reporting needs where the aggregation and joining of multiple entities into a single result set is important, Mendix offers OQL queries (see OQL below)
- A Java API is available if you want to use SQL queries on the application database (see SQL below)
- For data sharing with other applications or data analytics tools, Mendix offers out-of-the-box support for OData on entities in your domain model (see OData below)
-
XPath
XPath is an easy-to-use query language that enables retrieving specific objects. You can use it to get objects that you want to display or edit in pages as well as to modify these objects through microflows.
With XPath, you can define expressions to filter the objects you are interested in, and you can use associations to retrieve and filter related objects.
XPath automatically ensures that all the access rules you define on your entities are applied.
For more details, consult XPath in the Mendix Studio Pro Guide.
-
OQL
The Mendix Object Query Language (OQL) is a relational query language based on SQL. The major advantage of OQL over SQL is that OQL uses the entity and association names used in your model instead of the actual database table names.
OQL can use the predefined relations (associations) to easily join objects without having to calculate which columns should be coupled. Despite these differences, many SQL keywords also work in OQL.
OQL is mostly used for reporting where you need aggregation functions while grouping over certain attributes. Another reason to use OQL is to define custom security expressions, which you need to define manually. In some cases, this may result in faster queries if you handle security yourself using OQL than by using the out-of-the-box security of XPath.
For more details, see OQL in the Mendix Studio Pro Guide.
-
SQL
All application data is stored in a relation database. In the Mendix Cloud, this is PostgreSQL, but there are other options as well, depending on your deployment environment.
Mendix provides Java APIs to directly interact with your underlying relation database. Through the Mendix Java API, you can use Java JDBC to run SQL queries and execute stored procedures. This provides you with a very powerful way of using your data, but it requires an understanding of relational database systems and SQL. You can use database vendor-specific extensions, but this will limit the portability of your application to other databases.
For more information on access to the JDBC API from Mendix, see DataStorage.executeWithConnection.
-
OData
OData provides a generic data access and query interface on top of the REST protocol. It enables a large range of third-party tools to access your data in a controlled way. These tools include basic query tools like LINQPad, spreadsheets like MS-Excel, and business intelligence and analytics tools like PowerBI and R. By providing access through OData, all the validation and security constraints defined on your data and handled by the Mendix Runtime are guaranteed to be applied. This means that OData is a safe and secure way to provide access to data to external parties.
While OData is a broad standard that provides both query and manipulation operations on data, Mendix currently only provides query facilities. Currently, OData in Mendix is targeted at read-only analytical use of Mendix data by third-party applications.
For more information on the use of OData with Mendix, see the Turn Data into Insight with Mendix OData Support and Using R to Gain Insight from the Data in Your Mendix Applications blog posts.
How Can I Optimize My Queries?
The Mendix Platform provides a lot of optimization out of the box. For example, when retrieving data for your pages through XPath, Mendix will automatically retrieve all the related information used on the page in one go. A concept called schemas is used for this. A schema tells the data retrieval component to not only retrieve a specific set of objects from the database, but to include specific associated entities in the result as well.
Besides optimized queries, Mendix also optimizes to avoid the need to query. This is done, for example, in the web client where objects are cached and reused through multiple pages. Another optimization made in the Mendix Runtime uses knowledge of the page structure to determine if objects need to be sent from the Runtime to the client at all.
In addition to these out-of-the-box optimizations, there are a number of steps you can take to optimize your queries:
- Retrieve only what you need, and make sure you do not include attributes or objects in your query that you do not need
- Ensure that you have indexes on the following:
- Columns that are regularly searched or filtered on
- Columns that are used for sorting
In some cases—especially when you have a large number of objects—it can be helpful to use database-specific indexes. You can create these through the JDBC API, as described above in SQL.
-
Database Replication Module
The Database Replication module available from the Mendix Marketplace enables connecting to a large number of different types of databases. It will show you what data structures exist in the existing database. You can then define (through a web page) what data you want migrated to your Mendix database. You can also define how you want to map the data from existing columns to your entity attributes.
-
Database Connector
The Database Connector module provides you with microflow activities to run queries on any database with JDBC support. This means you can create microflows to define what data you want from your existing database as well as how you want to convert that data into your new Mendix application.
-
Initializing Your App Database from an Existing Database
When deploying a Mendix application for the first time, you have the option to initialize it with data from an existing Mendix app. The main purpose of this functionality is to enable migrating from one environment to another (for example, from the Mendix Cloud to a private cloud). Once you have migrated your non-Mendix data into Mendix data, you can use this data to initialize a new Mendix app. This will work between databases from the same vendor, and also when moving from one database vendor to another. This means that you can initialize an Azure SQL database using data in a PostgreSQL database running on your workstation.
For more information, see How to Migrate Your Mendix Database and Backups in the Mendix Documentation.
How Can I Use My Existing SQL Database in Mendix?
You can use the Database Connector to run SQL actions on any database you are using. To learn more about using an existing SQL database in Mendix, see the section How Does Mendix Support Direct Access to an External SQL Database? in External Data.
What APIs Does Mendix Offer to Extend Data Storage Behavior?
The Mendix Runtime provides Java APIs that enable extending the data storage behavior. Mendix ensures that anybody on your team can use these extensions by enabling you to provide them through normal microflow activities. For more info on this extensibility feature of Mendix, see the section What Kind of APIs Does Mendix Expose? in Openness (API & SDK).
Regarding data storage extensibility, the main Java APIs provide the following functionality:
- XPath – APIs to retrieve from the database using Xpath queries
- OQL – APIs to retrieve data from the database using OQL queries
- SQL – APIs to directly use the underlying Java JDBC connection to run any SQL required on your application database
- Entity event handlers – APIs to register entity event listeners for all your entities, which can be used to extract auditing information or do a real-time export of all data changes to an external system