Skill: Create External Database Connections

Purpose

Create and manage external database connections in Mendix using the External Database Connector. This skill helps you set up JDBC connections to external databases (Oracle, PostgreSQL, MySQL, SQL Server, etc.) and define SQL queries that map results to non-persistent entities.

When to Use This Skill

• User asks to connect to an external database from a Mendix app (via JDBC)
• User needs to query data from Oracle, PostgreSQL, MySQL, SQL Server, or other JDBC databases
• User wants to create database connection configurations
• User needs to define SQL queries with parameter binding
• User wants to map query results to Mendix entities

Tip: Use generate connector to auto-create all constants, entities, and queries from a database schema:

SQL CONNECT postgres 'postgres://user:pass@host/db' AS source;
SQL source GENERATE CONNECTOR INTO MyModule;
-- Or generate for specific tables and execute immediately:
SQL source GENERATE CONNECTOR INTO MyModule TABLES (employees, departments) EXEC;

For manual exploration, use sql source show tables; and sql source describe tablename;.

Prerequisites

1. Required Mendix Version

• Mendix 9.22+ (Database Connector introduced)
• Mendix 10.10+ (stable version recommended)

2. Required Non-Persistent Entities

Database query results must be mapped to NON-PERSISTENT entities. Create these first:

-- Entity to hold query results
create non-persistent entity MyModule.EmployeeRecord (
  EmployeeId: integer,
  EmployeeName: string(100),
  Department: string(50),
  Salary: decimal
);

3. Required Constants

Connection credentials should be stored in constants:

-- Connection string (JDBC URL)
create constant MyModule.DbConnectionString type string
  default 'jdbc:oracle:thin:@//hostname:1521/SERVICENAME'
  comment 'JDBC connection string for external database';

-- Username
create constant MyModule.DbUsername type string
  default 'app_user'
  comment 'Database username';

-- Password (use PRIVATE for local development)
create constant MyModule.DbPassword type string
  default ''
  comment 'Database password - inject via environment variable in production'
  PRIVATE;

Database Connection Syntax

Basic Connection Structure

create database connection Module.ConnectionName
type '<database-type>'
connection string @Module.ConnectionStringConstant
username @Module.UsernameConstant
password @Module.PasswordConstant
begin
  -- Query definitions go here
end;

The @ is not optional. connection string, username and password are ConstantIdentifier properties — Mendix stores a reference to a Constant document, never a value. The grammar accepts a bare string there, but writing one produces a project that cannot be opened at all:

StorageLoadException: ... has an invalid value '' for property ConnectionString.
The text 'jdbc:postgresql://...' is not a valid ConstantIdentifier.

That is a load failure, not a build error: mx check dies before validating anything, so the whole project goes down rather than one document. mxcli refuses it as MDL058 at both check and exec.

-- WRONG — writes an unopenable .mpr
connection string 'jdbc:postgresql://localhost:5432/app'
username 'app'

-- RIGHT — declare the constant, then reference it
create constant Module.DbUrl  type String default 'jdbc:postgresql://localhost:5432/app';
create constant Module.DbUser type String default 'app';

connection string @Module.DbUrl
username @Module.DbUser

The indirection is the point: the constant's value is per-environment, so a password is configured at deploy time instead of living in the model.

Supported Database Types

These are the values Studio Pro's own connector editor offers — read out of the shipped bundle at modeler/ide-client/database-connector-editor/, identical on 11.10.0, 11.12.1 and 11.13.0.

Database	TYPE Value	Studio Pro label
SQL Server	'MSSQL'	Microsoft SQL
MySQL	'MySQL'	MySQL
Oracle	'Oracle'	Oracle
PostgreSQL	'PostgreSQL'	PostgreSQL
Snowflake	'Snowflake'	Snowflake
anything else	'BYOD'	Other

'BYOD' — bring your own driver. Selecting it forces connection-string configuration and skips the driver-presence check; its only validation is that the connection string is non-empty. That is the hook for any JDBC driver Mendix ships no picker entry for (DuckDB, SQLite, ClickHouse, …). Verified end to end on Mendix 11.13: a booted runtime opened jdbc:duckdb: through a BYOD connection and returned real rows — the runtime accepts it, not just the editor.

Getting the driver onto the classpath

The driver JAR has to be resolved, and declaring it is not resolving it:

ALTER MODULE MyModule ADD JAR DEPENDENCY (
  group = 'org.duckdb', artifact = 'duckdb_jdbc', version = '1.5.5.1', included = true
);

writes the coordinate to the model — list jar dependencies will report it — and downloads nothing. MxBuild does not resolve it either: a full mxbuild --target=deploy emits a build.gradle with no dependencies block. The first symptom is a runtime SQLException: No JDBC driver found in app for URL, from a connection that looks correctly configured.

Studio Pro runs the resolution when you edit Module Settings. Headless, ask for it:

mxcli sync-java-deps -p app.mpr          # download into vendorlib/
mxcli sync-java-deps -p app.mpr --check  # report what is missing, exit 1 (build gate)

mxcli run --local does this automatically for anything not already in vendorlib/, so the warm loop works from a fresh clone. Dropping the jar into userlib/ by hand works too — it is the same classpath — but then the model and the file system disagree about where the dependency comes from.

'Redshift' and 'SQLServer' are not real values. Both appeared in an earlier version of this table and neither is in the picker on any version checked. mxcli writes the type string through unchanged and mxbuild does not validate it — type 'Redshift' builds 0 errors and simply does not connect — so mxcli check warns about an unrecognised type (MDL-DB01) rather than letting a green build hide it.

Query Definition Syntax

Simple Query (No Parameters)

query QueryName
  sql 'SELECT column1, column2 FROM table_name'
  returns Module.EntityName;

Parameterized Query

query QueryName
  sql 'SELECT * FROM table_name WHERE column = {paramName}'
  parameter paramName: string
  returns Module.EntityName;

Query with Column Mapping

When database column names don't match entity attribute names:

query QueryName
  sql 'SELECT emp_id, emp_name, dept_no FROM employees'
  returns Module.EmployeeRecord
  map (
    emp_id as EmployeeId,
    emp_name as EmployeeName,
    dept_no as DepartmentNumber
  );

Supported Parameter Types

• string - Text values
• integer - Whole numbers
• decimal - Decimal numbers
• boolean - true/false
• datetime - Date and time values

Parameter Test Values

Parameters can include a test value for Studio Pro testing, or indicate they should be tested with NULL:

-- Test value (used in Studio Pro's Execute Query dialog)
parameter empName: string default 'Smith'

-- Test with NULL value
parameter optionalDate: datetime null

Complete Examples

Example 1: Oracle HR Database Connection

-- Step 1: Create module
create module OracleDemo;

-- Step 2: Create constants for connection
create constant OracleDemo.OracleConnectionString type string
  default 'jdbc:oracle:thin:@//10.211.55.2:1522/ORCLPDB1';

create constant OracleDemo.OracleUser type string default 'scott';

create constant OracleDemo.OraclePassword type string default 'tiger' PRIVATE;

-- Step 3: Create non-persistent entity for results
create non-persistent entity OracleDemo.EmpRecord (
  EMPNO: decimal,
  ENAME: string(10),
  JOB: string(9),
  SAL: decimal,
  DEPTNO: decimal
);

-- Step 4: Create database connection
create database connection OracleDemo.HRDatabase
type 'Oracle'
connection string @OracleDemo.OracleConnectionString
username @OracleDemo.OracleUser
password @OracleDemo.OraclePassword
begin
  query GetAllEmployees
    sql 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY EMPNO'
    returns OracleDemo.EmpRecord;

  query GetEmployeeByName
    sql 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE ENAME = {empName}'
    parameter empName: string
    returns OracleDemo.EmpRecord;

  query GetHighEarners
    sql 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE SAL >= {minSalary}'
    parameter minSalary: decimal
    returns OracleDemo.EmpRecord;
end;

Example 2: PostgreSQL Connection

create constant Inventory.PgConnectionString type string
  default 'jdbc:postgresql://localhost:5432/inventory_db';

create constant Inventory.PgUser type string default 'inventory_app';
create constant Inventory.PgPassword type string default '' PRIVATE;

create non-persistent entity Inventory.ProductRecord (
  ProductId: integer,
  ProductName: string(100),
  Quantity: integer,
  Price: decimal
);

create database connection Inventory.ProductDatabase
type 'PostgreSQL'
connection string @Inventory.PgConnectionString
username @Inventory.PgUser
password @Inventory.PgPassword
begin
  query GetAllProducts
    sql 'SELECT product_id, product_name, quantity, price FROM products'
    returns Inventory.ProductRecord
    map (
      product_id as ProductId,
      product_name as ProductName,
      quantity as Quantity,
      price as Price
    );

  query SearchProducts
    sql 'SELECT product_id, product_name, quantity, price FROM products WHERE product_name ILIKE {searchPattern}'
    parameter searchPattern: string
    returns Inventory.ProductRecord
    map (
      product_id as ProductId,
      product_name as ProductName,
      quantity as Quantity,
      price as Price
    );
end;

Viewing Connections

-- List all database connections
show database connections;

-- List connections in a specific module
show database connections in MyModule;

-- View connection source code
describe database connection MyModule.MyDatabase;

Best Practices

1. Connection String Management

• Store JDBC URLs in constants for environment-specific overrides
• Use MX_Module_ConstantName environment variables in production

2. Credential Security

• Use PRIVATE flag for password constants during development
• Never commit real passwords to version control
• Inject credentials via CI/CD pipelines in production

3. Entity Design

• Use NON-PERSISTENT entities for query results
• Match attribute types to database column types
• Use MAP clause when column names differ from attribute names

4. Query Design

• Use parameterized queries to prevent SQL injection
• Keep queries simple and focused
• Create separate queries for different use cases

Troubleshooting

Connection Issues

1. Verify JDBC URL format for your database type
2. Check network connectivity to database host
3. Verify credentials are correct
4. Ensure JDBC driver is available

Query Issues

1. Test queries directly in database client first
2. Check parameter types match expected database types
3. Verify entity attributes match query result columns
4. Use MAP clause for column name mismatches

Related Commands

-- Constants for configuration
create constant Module.Name type string default 'value';
show constants in module;

-- Non-persistent entities for results
create non-persistent entity Module.Name (...);
show entities in module;

Executing Queries from Microflows

Once a database connection and queries are defined, execute them from microflows using execute database query. The query is referenced by its 3-part qualified name: Module.Connection.Query.

Basic Syntax

-- Execute a query and store results
$ResultList = execute database query Module.Connection.QueryName;

-- Fire-and-forget (no output variable)
execute database query Module.Connection.QueryName;

Dynamic SQL Override

Override the query's SQL at runtime using dynamic:

$ResultList = execute database query Module.Connection.QueryName
  dynamic 'SELECT id, name FROM employees WHERE active = true LIMIT 10';

A dynamic override still requires a value for every declared parameter — including the ones the replacement SQL does not use. The parameter list belongs to the query definition, not to the SQL string, so Mendix asks for all of them whatever you substitute. Pass a placeholder for the unused ones:

-- The definition declares $driverId; this SQL ignores it, and the call still
-- has to supply it.
$Count = execute database query F1.DuckDB.CountAllDrivers
  dynamic 'SELECT count(*) AS n FROM read_csv(''/data/f1db-drivers.csv'')'
  ( driverId = 'unused' );

A {param} placeholder can be concatenated into a path, which is what keeps absolute paths out of the model — bind the data directory as one constant and build the file name around it:

--   read_csv({dataDir} || '/f1db-drivers.csv')

Verified against DuckDB through the connector on Mendix 11.13, and against a standalone JDBC harness before that.

Parameterized Queries

Pass values for query parameters defined with parameter in the query definition:

-- Query definition (in DATABASE CONNECTION block):
--   QUERY GetDriversByNationality
--     SQL 'SELECT * FROM drivers WHERE nationality = {nation}'
--     PARAMETER nation: String
--     RETURNS Module.DriverRecord;

-- Microflow execution:
$Drivers = execute database query Module.Connection.GetDriversByNationality
  (nation = $NationalityVar);

CRITICAL: Parameter names must exactly match those in the query definition (e.g., nation, not nationality). Mismatched names cause Studio Pro to regenerate mappings and clear values.

Runtime Connection Override

Override connection parameters at runtime using connection. Use case: multiple databases with the same schema but different data (e.g., region-specific databases).

$Results = execute database query Module.Connection.QueryName
  connection (DBSource = $url, DBUsername = $user, DBPassword = $Pass);

Caveat: ConnectionParameterMappings require the database connection to have been tested/validated in Studio Pro first. Creating them programmatically may trigger "parameters have been updated" on first open.

Error Handling

execute database query only supports on error rollback (the default). on error continue is not supported for this action type.

Complete Example

-- Set up non-persistent entity, constants, and connection
create non-persistent entity HR.EmployeeRecord (
  EmpId: integer,
  Name: string(100),
  Department: string(50)
);

create constant HR.DbUrl type string default 'jdbc:postgresql://localhost:5432/hrdb';
create constant HR.DbUser type string default 'app';
create constant HR.DbPass type string default '' PRIVATE;

create database connection HR.MainDB
type 'PostgreSQL'
connection string @HR.DbUrl
username @HR.DbUser
password @HR.DbPass
begin
  query GetAllEmployees
    sql 'SELECT emp_id, name, department FROM employees'
    returns HR.EmployeeRecord
    map (emp_id as EmpId, name as Name, department as Department);

  query GetByDepartment
    sql 'SELECT emp_id, name, department FROM employees WHERE department = {dept}'
    parameter dept: string
    returns HR.EmployeeRecord
    map (emp_id as EmpId, name as Name, department as Department);
end;

-- Microflow that executes the query
create microflow HR.ACT_LoadEmployees($Department: string)
returns list of HR.EmployeeRecord as $Employees
begin
  $Employees = execute database query HR.MainDB.GetByDepartment
    (dept = $Department);
  return $Employees;
end;

Importing Data from External Databases

To bulk-import data from an external database directly into the Mendix app's PostgreSQL database (bypassing the runtime), use import from instead of the Database Connector:

sql connect postgres 'postgres://user:pass@host:5432/legacydb' as source;
import from source query 'SELECT name, email FROM employees'
  into HRModule.Employee
  map (name as Name, email as Email);

See demo-data for details on the Mendix ID system and manual insertion.

References

• Mendix External Database Connector
• JDBC Connection Strings