---
name: database-connections
description: "Connect a Mendix app to an external database over JDBC with the External Database Connector, and define the queries microflows run against it. Use when the app must read or write Oracle, PostgreSQL, MySQL or SQL Server directly from a microflow."
---

# 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:
> ```
> mdl 1;
> 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:

```sql
mdl 1;
-- 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:

```sql
mdl 1;
/** JDBC connection string for external database */
create constant MyModule.DbConnectionString ( Type: string, DefaultValue: 'jdbc:oracle:thin:@//hostname:1521/SERVICENAME' );

/** Database username */
create constant MyModule.DbUsername ( Type: string, DefaultValue: 'app_user' );

/** Database password - inject via environment variable in production */
create constant MyModule.DbPassword ( Type: string, DefaultValue: '' );
```

## Database Connection Syntax

### Basic Connection Structure

```sql
mdl 1;
create database connection Module.ConnectionName (
  Type: '<database-type>',
  ConnectionString: @Module.ConnectionStringConstant,
  Username: @Module.UsernameConstant,
  Password: @Module.PasswordConstant
) {
  -- query definitions go here: query Name ( Sql: …, Returns: … )
};
```

The connection's properties are in `( )` and its queries are its children, in
`{ }` — the shape of every declarative document (R2). The old clause form
(`type '…' connection string @… begin query … ; end`) still parses and warns
(MDL-DEPR127); `mxcli fmt --upgrade` rewrites it.

**The `@` is not optional.** `ConnectionString`, `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`.

```sql
-- WRONG — writes an unopenable .mpr
ConnectionString: 'jdbc:postgresql://localhost:5432/app',
Username: 'app'

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

ConnectionString: @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:

```sql
mdl 1;
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:

```bash
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

A query is a child of the connection, with its properties in `( )`:
`Sql`, `Parameters`, `Returns` and `Map`. The SQL may be a string or `$$…$$`,
which needs no quote doubling.

### Simple Query (No Parameters)

```sql
query QueryName (
  Sql: 'SELECT column1, column2 FROM table_name',
  Returns: Module.EntityName
)
```

### Parameterized Query

```sql
query QueryName (
  Sql: 'SELECT * FROM table_name WHERE column = {paramName}',
  Parameters: ( paramName: string ),
  Returns: Module.EntityName
)
```

### Query with Column Mapping

When database column names don't match entity attribute names, `Map` binds each
attribute to its column — `Attribute = column`, the way a mapping side is written:

```sql
query QueryName (
  Sql: 'SELECT emp_id, emp_name, dept_no FROM employees',
  Returns: Module.EmployeeRecord,
  Map: (
    EmployeeId = emp_id,
    EmployeeName = emp_name,
    DepartmentNumber = dept_no
  )
)
```

### 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:

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

-- Test with NULL value
Parameters: ( optionalDate: datetime null )
```

## Complete Examples

### Example 1: Oracle HR Database Connection

```sql
mdl 1;
-- Step 1: Create module
create module OracleDemo;

-- Step 2: Create constants for connection
create constant OracleDemo.OracleConnectionString ( Type: string, DefaultValue: 'jdbc:oracle:thin:@//10.211.55.2:1522/ORCLPDB1' );

create constant OracleDemo.OracleUser ( Type: string, DefaultValue: 'scott' );

create constant OracleDemo.OraclePassword ( Type: string, DefaultValue: 'tiger' );

-- 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',
  ConnectionString: @OracleDemo.OracleConnectionString,
  Username: @OracleDemo.OracleUser,
  Password: @OracleDemo.OraclePassword
) {
  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}',
    Parameters: ( empName: string ),
    Returns: OracleDemo.EmpRecord
  )

  query GetHighEarners (
    Sql: 'SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE SAL >= {minSalary}',
    Parameters: ( minSalary: decimal ),
    Returns: OracleDemo.EmpRecord
  )
};
```

### Example 2: PostgreSQL Connection

```sql
mdl 1;
create constant Inventory.PgConnectionString ( Type: string, DefaultValue: 'jdbc:postgresql://localhost:5432/inventory_db' );

create constant Inventory.PgUser ( Type: string, DefaultValue: 'inventory_app' );
create constant Inventory.PgPassword ( Type: string, DefaultValue: '' );

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

create database connection Inventory.ProductDatabase (
  Type: 'PostgreSQL',
  ConnectionString: @Inventory.PgConnectionString,
  Username: @Inventory.PgUser,
  Password: @Inventory.PgPassword
) {
  query GetAllProducts (
    Sql: 'SELECT product_id, product_name, quantity, price FROM products',
    Returns: Inventory.ProductRecord,
    Map: (
      ProductId = product_id,
      ProductName = product_name,
      Quantity = quantity,
      Price = price
    )
  )

  query SearchProducts (
    Sql: 'SELECT product_id, product_name, quantity, price FROM products WHERE product_name ILIKE {searchPattern}',
    Parameters: ( searchPattern: string ),
    Returns: Inventory.ProductRecord,
    Map: (
      ProductId = product_id,
      ProductName = product_name,
      Quantity = quantity,
      Price = price
    )
  )
};
```

## Viewing Connections

```sql
-- List all database connections
list database connections;

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

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

-- Remove one
drop database connection [if exists] 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
- Leave a password constant's default empty (`DefaultValue: ''`) and set the value per environment; MDL has no private flag, and a trailing `PRIVATE` was never read
- 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

```sql
-- Constants for configuration
create constant Module.Name ( Type: string, DefaultValue: 'value' );
list constants in module;

-- Non-persistent entities for results
create non-persistent entity Module.Name (...);
list 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

```sql
-- 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`:

```sql
$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:

```sql
-- 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:

```sql
--   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 the query parameters declared in the query's `Parameters:` list:

```sql
-- Query definition (in the database connection's { } block):
--   query GetDriversByNationality (
--     Sql: 'SELECT * FROM drivers WHERE nationality = {nation}',
--     Parameters: ( 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).

```sql
$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

```sql
mdl 1;
-- 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, DefaultValue: 'jdbc:postgresql://localhost:5432/hrdb' );
create constant HR.DbUser ( Type: string, DefaultValue: 'app' );
create constant HR.DbPass ( Type: string, DefaultValue: '' );

create database connection HR.MainDB (
  Type: 'PostgreSQL',
  ConnectionString: @HR.DbUrl,
  Username: @HR.DbUser,
  Password: @HR.DbPass
) {
  query GetAllEmployees (
    Sql: 'SELECT emp_id, name, department FROM employees',
    Returns: HR.EmployeeRecord,
    Map: (EmpId = emp_id, Name = name, Department = department)
  )

  query GetByDepartment (
    Sql: 'SELECT emp_id, name, department FROM employees WHERE department = {dept}',
    Parameters: ( dept: string ),
    Returns: HR.EmployeeRecord,
    Map: (EmpId = emp_id, Name = name, Department = department)
  )
};

-- 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
mdl 1;
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](../demo-data/SKILL.md) for details on the Mendix ID system and manual insertion.

## References

- [Mendix External Database Connector](https://docs.mendix.com/appstore/modules/external-database-connector/)
- [JDBC Connection Strings](https://docs.mendix.com/appstore/modules/external-database-connector/#connection-details)