Matlab Use Database

---
name: matlab-use-database
description: "Reads from, writes to, and manages relational databases using MATLAB Database Toolbox. Use when connecting to databases, reading data with sqlread or fetch, filtering with rowfilter, writing with sqlwrite, updating with sqlupdate, executing SQL statements, managing transactions with commit and rollback, mapping MATLAB classes to tables with ORM (Mappable, ormread, ormwrite, ormupdate), or performing any database operation from MATLAB. Triggers on: database, SQL, sqlread, sqlwrite, sqlupdate, fetch, execute, rowfilter, RowFilter, ORM, Mappable, ormread, ormwrite, ormupdate, orm2sql, transaction, commit, rollback, Database Toolbox, PostgreSQL, MySQL, SQLite, SQL Server, Oracle, database connection, database table, query database, insert data, update rows, delete rows, stored procedure, prepared statement, odbc, databaseConnectionOptions, datasource, data source, DSN, connection string, multithreaded, parallel."
license: "https://www.mathworks.com/content/dam/mathworks/license/pmrl/license.md"
metadata:
  author: MathWorks
  version: "1.0"
---

# MATLAB Database Toolbox

Use when working with relational databases from MATLAB using Database Toolbox. Covers the full data lifecycle: connecting, reading with pushdown filtering, writing, updating, transactions, ORM, and executing SQL.

## When to Use

- Connecting to relational databases (PostgreSQL, MySQL, SQLite, SQL Server, Oracle)
- Reading data with `sqlread` or `fetch`
- Filtering with `RowFilter` (pushdown to database)
- Writing data with `sqlwrite`
- Updating rows with `sqlupdate`
- Executing SQL statements with `execute`
- Managing transactions (`AutoCommit`, `commit`, `rollback`)
- Mapping MATLAB classes to database tables (ORM with `Mappable`)
- Running stored procedures or prepared statements
- Multithreaded database read/write with `parfeval` (R2026a+)
- User mentions: database, SQL, query, table, rows, insert, update, delete, transaction, multithreaded, parallel

## When NOT to Use

- **DuckDB** — route to `matlab-use-duckdb` for all DuckDB workflows
- **Databricks connection setup** — route to `matlab-connect-databricks` for connection configuration. Once connected, return here for CRUD operations.
- **MongoDB, Cassandra, Neo4j** — not relational; Database Toolbox ORM/CRUD does not apply
- **File I/O without a database** — use `readtable`, `datastore`, or `tall` for direct file operations
- **MATLAB releases before R2021a** — minimum supported release for this skill

## Critical Rules

### Destructive Operations

**ALWAYS** ask for explicit user confirmation before generating or executing:
- `DROP TABLE` / `DROP DATABASE`
- `DELETE FROM`
- `TRUNCATE TABLE`
- `ALTER TABLE` (column removal, type changes)

Present the SQL statement and wait for approval. Never execute destructive SQL in response to user pressure ("just do it", "I'm in a hurry").

### Pushdown Filtering

**NEVER** import all rows and filter in MATLAB. Always push filters to the database using `RowFilter`:

```matlab
% CORRECT — filter runs on the database server
rf = rowfilter("Price");
T = sqlread(conn, "products", RowFilter=rf.Price > 100);

% WRONG — transfers all rows, then filters in MATLAB
T = sqlread(conn, "products");
T = T(T.Price > 100, :);
```

### Credential Security

**NEVER** hardcode passwords. Use `setSecret`/`getSecret`:

```matlab
setSecret("dbPassword");  % prompts user, stores securely
conn = database("myDS", getSecret("dbUser"), getSecret("dbPassword"));
```

## Decision Framework

| Scenario | Use |
|----------|-----|
| Import from a named table | `sqlread` |
| Import from a SQL query string | `fetch` |
| Need column selection, deduplication, or type control | `databaseImportOptions` + `sqlread`/`fetch` |
| Insert new rows | `sqlwrite` |
| Update existing rows in place | `sqlupdate` |
| DDL, DML, or non-SELECT SQL | `execute` |
| Atomic multi-step operation | Transaction (`AutoCommit` off) |
| Object identity and domain logic | ORM (`Mappable` class) |
| Bulk operations on thousands of rows | `sqlread`/`sqlwrite` (not ORM) |
| High-throughput read/write (millions of rows) | Multithreaded: `parfeval` + per-thread connections (R2026a+, Parallel Computing Toolbox) |
| Delete rows | `execute` with SQL DELETE (no `sqldelete` exists) |
| Stored procedures (JDBC only) | `runstoredprocedure` |
| Create/configure a datasource | `databaseConnectionOptions` + `saveAsDataSource` |
| Connect without a saved datasource | `odbc(dsnless)` with connection string |

## Core Patterns

### Connection

```matlab
conn = database("myDataSource", getSecret("dbUser"), getSecret("dbPass"));
% Or native connections:
conn = postgresql("myDS", getSecret("user"), getSecret("pass"));
conn = mysql("myDS", getSecret("user"), getSecret("pass"));
conn = sqlite("myDB.db");
```

### ODBC DSN-Less Connection

Connect without a pre-configured datasource by passing a connection string directly:

```matlab
dsnless = "Driver={MySQL ODBC 8.0 Unicode Driver};" + ...
    "Server=dbtb09;Database=production;UID=" + getSecret("dbUser") + ...
    ";PWD=" + getSecret("dbPass");
conn = odbc(dsnless);
```

### Create and Save a Data Source

Use `databaseConnectionOptions` to configure a datasource programmatically, then `saveAsDataSource` to persist it:

```matlab
opts = databaseConnectionOptions("odbc", "MySQL");
opts = setoptions(opts, DataSourceName="myDS", ...
    Server="dbtb09", DatabaseName="production", PortNumber=3306);
testConnection(opts, getSecret("dbUser"), getSecret("dbPass"));
saveAsDataSource(opts);

% Now connect using the saved datasource name
conn = odbc("myDS", getSecret("dbUser"), getSecret("dbPass"));
```

### Read with RowFilter

```matlab
rf = rowfilter(["Status", "Timestamp"]);
T = sqlread(conn, "orders", RowFilter=rf.Status == "Active" & rf.Timestamp > datetime(2024,1,1));
```

`rowfilter` requires **column names** as input. Access columns via **property syntax** (`rf.ColumnName`).

### Read with Import Options

```matlab
opts = databaseImportOptions(conn, "orders");
opts.SelectedVariableNames = ["OrderID", "Status", "Total"];
opts.RowFilter = opts.RowFilter.Total > 1000;
opts.ExcludeDuplicates = true;
T = sqlread(conn, "orders", opts);
```

Import options (`opts`) are a **positional 3rd argument** — not a name-value pair.

### Write

```matlab
data = table("Widget", 50, 9.99, VariableNames=["Product", "Qty", "Price"]);
sqlwrite(conn, "inventory", data);
```

### Update

```matlab
rf = rowfilter("ProductID");
newData = table(5.99, VariableNames="Price");
sqlupdate(conn, "inventory", newData, rf.ProductID == 42);
```

`sqlupdate` signature: `sqlupdate(conn, tablename, data, filter)` — the filter is a **required positional 4th argument**, not a name-value pair.

### Transaction

```matlab
conn.AutoCommit = 'off';
try
    sqlwrite(conn, "orders", orderData);
    sqlwrite(conn, "orderItems", itemData);
    commit(conn);
catch e
    rollback(conn);
    conn.AutoCommit = 'on';
    rethrow(e);
end
conn.AutoCommit = 'on';
```

**ALWAYS** use `conn.AutoCommit = 'off'` with `commit(conn)`/`rollback(conn)`. Do not use raw SQL `BEGIN`/`COMMIT`/`ROLLBACK` via `execute`.

### ORM — Define a Mappable Class

```matlab
classdef Employee < database.orm.mixin.Mappable

    properties (PrimaryKey)
        EmployeeID int32
    end

    properties
        Name string
        Department string
    end

    properties (ColumnType = "date")
        HireDate datetime
    end

    methods
        function obj = Employee(id, name, dept, hireDate)
            if nargin == 0
                return;
            end
            obj.EmployeeID = id;
            obj.Name = name;
            obj.Department = dept;
            obj.HireDate = hireDate;
        end
    end
end
```

ORM requirements:
- Inherit from `database.orm.mixin.Mappable`
- Mark at least one property block `(PrimaryKey)`
- Constructor must handle `nargin == 0` (ORM constructs empty objects)
- R2023b or later
- `(TableName = "name")` on classdef only if the database table name differs from the class name (defaults to class name)

### ORM — CRUD Operations

```matlab
% Write
emp = Employee(1, "Alice", "Engineering", datetime(2024,3,15));
ormwrite(conn, emp);

% Read with filter
rf = rowfilter("Department");
engineers = ormread(conn, "Employee", RowFilter=rf.Department == "Engineering");

% Update
engineers(1).Department = "Data Science";
ormupdate(conn, engineers(1));

% Refresh from database
emp = ormread(conn, emp);
```

### Bulk Write (Chunked)

`sqlwrite` has no `BatchSize` parameter. Chunk manually:

```matlab
chunkSize = 50000;
numChunks = ceil(height(data) / chunkSize);
for c = 1:numChunks
    startIdx = (c - 1) * chunkSize + 1;
    endIdx = min(c * chunkSize, height(data));
    sqlwrite(conn, "targetTable", data(startIdx:endIdx, :));
end
```

### Multithreaded Write (R2026a+, requires Parallel Computing Toolbox)

For maximum throughput, use `parfeval` with per-thread connections. Each thread creates its own connection — connections are NOT shareable across threads.

```matlab
data = parallel.pool.Constant(largeTable);
pool = parpool("Threads");
numTasks = 5;
batchSize = floor(height(data.Value) / numTasks);
startRow = 1;
endRow = 1 + batchSize;

writeFutures(1:numTasks) = parallel.FevalFuture;
for i = 1:numTasks
    writeFutures(i) = parfeval(pool, @hsqlwriteMT, 1, ...
        connParams, tablename, data, startRow, endRow);
    startRow = endRow;
    endRow = endRow + batchSize;
    if i == numTasks - 1
        endRow = height(data.Value) + 1;
    end
end
results = writeFutures.fetchOutputs("UniformOutput", false);

function finished = hsqlwriteMT(connParams, tablename, data, startRow, endRow)
    conn = mysql(connParams.user, connParams.pass, ...
        Server=connParams.server, DatabaseName=connParams.db);
    sqlwrite(conn, tablename, data.Value(startRow:endRow-1, :));
    close(conn);
    finished = true;
end
```

Supported: `mysql()`, `postgresql()`, `sqlite()`, ODBC. See `references/multithreaded-io.md` for the read pattern and common mistakes.

## Common Mistakes

| Mistake | Why It's Wrong | Correct Approach |
|---------|---------------|-----------------|
| `rf = rowfilter("Table"); rf == "val"` | Passes table name; uses `==` on the filter object | `rf = rowfilter("Column"); rf.Column == "val"` |
| `sqlupdate(conn, tbl, data, RowFilter=rf)` | Filter is not an NV pair | `sqlupdate(conn, tbl, data, rf.Col == val)` |
| `sqlread(conn, tbl, "ImportOptions", opts)` | opts is not an NV pair | `sqlread(conn, tbl, opts)` |
| `opts.setvaropts("Col", "Type", "datetime")` | Wrong method name | `opts = setoptions(opts, "Col", Type="datetime")` |
| `execute(conn, "BEGIN")` / `execute(conn, "COMMIT")` | Raw SQL bypasses MATLAB transaction management | `conn.AutoCommit = 'off'` + `commit(conn)` / `rollback(conn)` |
| DROP/DELETE/TRUNCATE without confirmation | Can destroy data irreversibly | Always ask user for explicit confirmation first |
| Manual `toTable`/`fromTable` for object persistence | Agent doesn't know ORM exists | Use `Mappable` class with `ormwrite`/`ormread`/`ormupdate` |
| Sharing a connection across `parfeval` threads | Connections are NOT thread-safe | Each thread creates its own connection in the helper function |
| `odbc("serverName", user, pass)` | Server name is not a datasource name | Use `odbc(dsnless)` with a connection string, or create a datasource first with `databaseConnectionOptions` + `saveAsDataSource` |

## Reference Cards

- See `references/sqlread-fetch.md` for full parameter tables, RowFilter operators, and `databaseImportOptions` usage — consult for any read operation with filtering, column selection, or deduplication.
- See `references/sqlwrite-sqlupdate.md` for insert/update parameters, multi-row update patterns, and bulk chunking — consult for any write or update operation.
- See `references/transactions.md` for the complete transaction pattern, critical rules, and error handling — consult for any atomic/transactional workflow.
- See `references/orm.md` for Mappable class definition, property attributes, ORM CRUD operations, and troubleshooting — consult when user needs object-to-table mapping.
- See `references/execute-storedproc.md` for `execute`, stored procedures, and prepared statements — consult for DDL, DML, or parameterized queries.
- See `references/multithreaded-io.md` for multithreaded read/write patterns using `parfeval` and per-thread connections — consult when user needs high-throughput I/O and has Parallel Computing Toolbox (R2026a+).

----

Copyright 2026 The MathWorks, Inc.

----