matlab-use-database

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:

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

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

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:

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:

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

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

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

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

Update

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

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

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

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

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.

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.

---