Skip to content
Skills Library
Programming · Building & coding

redaxo-sql-patterns

/redaxo-sql-patterns

Database access in REDAXO using rex_sql – queries, prepared statements, transactions, and proper escaping. Use when the user runs SQL in a REDAXO project, mentions rex_sql, queries the rex_* tables, or writes addon code that touches the database.

Licence
MIT
Last repository change
20.09.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 20.09.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Database access in REDAXO using rex_sql – queries, prepared statements, transactions, and proper escaping. Use when the user runs SQL in a REDAXO project, mentions rex_sql, queries the rex_* tables, or writes addon code that touches the database.

Original author description

Database access in REDAXO using rex_sql – queries, prepared statements, transactions, and proper escaping. Use when the user runs SQL in a REDAXO project, mentions rex_sql, queries the rex_* tables, or writes addon code that touches the database.

How it works

  1. Tables and the rex::getTable() helper
  2. Reading – setQuery with placeholders
  3. Counting / aggregates
  4. Inserting – fluent builder
  5. Updating
  6. Deleting
  7. Transactions
  8. Schema changes – rexsqltable
  9. Common pitfalls
Original SKILL.md headings · 135 lines, 666 words
SKILL.md preview
---
name: redaxo-sql-patterns
description: Database access in REDAXO using rex_sql – queries, prepared statements, transactions, and proper escaping. Use when the user runs SQL in a REDAXO project, mentions rex_sql, queries the rex_* tables, or writes addon code that touches the database.
---

# rex_sql Patterns

`rex_sql` is REDAXO's database wrapper. It enforces prepared statements, namespaced table prefixes (`rex_`), and ships with a fluent builder for inserts/updates.

Always prefer `rex_sql` over raw PDO so you get the configured connection, prefix handling, and the addon's logging.

## Tables and the `rex::getTable()` helper

REDAXO prefixes all tables (default `rex_`). Never hardcode the prefix – use `rex::getTable('article')` which returns `rex_article`. This keeps code portable across installations that use a different prefix (e.g. multi-instance setups).

```php
$table = rex::getTable('article'); // → "rex_article"
$tableMyAddon = rex::getTable('my_addon_items');
```

## Reading – `setQuery` with placeholders

Always use parameter binding. Never concatenate user input into SQL.

```php
$sql = rex_sql::factory();
$sql->setQuery(
    'SELECT id, name FROM ' . rex::getTable('article') . '
     WHERE clang_id = :clang AND status = :status
     ORDER BY priority',
    ['clang' => rex_clang::getCurrentId(), 'status' => 1]
);

foreach ($sql as $row) {
    echo (int) $row->getValue('id') . ': ' . rex_escape($row->getValue('name')) . "\n";
}
```

Iterating the `$sql` object yields one row at a time. Inside the loop, `$row` is the same object positioned on the current row.

To get all rows as an array (small result sets only):

```php
$rows = $sql->getArray();
```

## Counting / aggregates

```php
$sql = rex_sql::factory();
$sql->setQuery('SELECT COUNT(*) AS cnt FROM ' . rex::getTable('article') . ' WHERE status = ?', [1]);
$count = (int) $sql->getValue('cnt');
```

Positional placeholders (`?`) work too – pass values as a positional array.

## Inserting – fluent builder

```php
$sql = rex_sql::factory();
$sql->setTable(rex::getTable('my_addon_log'));
$sql->setValue('user_id', rex::getUser()->getId());
$sql->setValue('action', 'login');
$sql->setDateTimeValue('created_at', time()); // formats to MySQL datetime
$sql->insert();

$newId = (int) $sql->getLastId();
```

## Updating

```php
$sql = rex_sql::factory();
$sql->setTable(rex::getTable('my_addon_log'));
$sql->setWhere(['id' => $logId]);          // becomes "WHERE id = :id"
$sql->setValue('action', 'logout');
$sql->update();
```

For complex `WHERE` clauses, pass the SQL and parameters separately:

```php
$sql->setWhere('user_id = :uid AND created_at > :since', [
    'uid'   => $userId,
    'since' => '2026-01-01',
]);
```

## Deleting

```php
$sql = rex_sql::factory();
$sql->setTable(rex::getTable('my_addon_log'));
$sql->setWhere(['id' => $logId]);
$sql->delete();
```

## Transactions

```php
$sql = rex_sql::factory();
$sql->beginTransaction();
try {
    // multiple statements...
    $sql->commit();
} catch (Throwable $e) {
    $sql->rollBack();
    throw $e;
}
```

`rex_sql::factory()` returns a **new** `rex_sql` object on every call (own query, result and values), but all instances for the same DB share one PDO connection. A transaction therefore spans every `rex_sql` instance – no need to share an instance manually, and any second `beginTransaction()` call while the shared connection is already in a transaction (same instance or another) throws `rex_sql_exception`.

## Schema changes – `rex_sql_table`

For `install.php` / `update.php` in addons, use `rex_sql_table` instead of raw `CREATE TABLE`:

```php
rex_sql_table::get(rex::getTable('my_addon_items'))
    ->ensurePrimaryIdColumn()
    ->ensureColumn(new rex_sql_column('title', 'varchar(255)', false, ''))
    ->ensureColumn(new rex_sql_column('body', 'text', true))
    ->ensureColumn(new rex_sql_column('created_at', 'datetime', false))
    ->ensureIndex(new rex_sql_index('idx_created', ['created_at']))
    ->ensure();
```

`ensure()` is idempotent – safe to run on every install/update. It compares the desired schema with the actual one and only applies the diff.

## Common pitfalls

- Concatenating values into the SQL string – every placeholder must use parameter binding.
- Hardcoding `rex_` as the table prefix – use `rex::getTable()`.
- Using `mysqli_*` or raw `PDO` directly – breaks with non-default DB configs.
- Calling `getValue()` on a row object that hasn't been fetched (after exhausted iteration). Re-run the query or seek with `$sql->next()`.
- Forgetting that `getArray()` consumes the result set – call it once, then operate on the array.
- Re-creating a table in the same request after a raw `DROP TABLE` – `rex_sql_table::get()` caches the table definition per name, so `exists()` still returns `true` and `ensure()` tries to `ALTER` a table that is gone. Drop via `rex_sql_table::get(rex::getTable('my_addon_items'))->drop()`, which marks the cached instance as non-existing so `ensure()` creates the table again, or call `rex_sql_table::clearInstance(rex::getTable('my_addon_items'))` after an external `DROP TABLE`.
- Reading whole rows with `getRow()` – REDAXO opens PDO with `ATTR_FETCH_TABLE_NAMES` (`core/lib/sql/sql.php`), so the keys are `rex_article.id`, not `id`, and every `$row['id']` lookup silently returns `null`. `getValue('id')` resolves the prefix itself and is unaffected; for plain keys use `getArray()`, which switches the attribute off for its own query. An object built from a `getRow()` array looks complete and has every property empty.

Versions

The database has no other skills named redaxo-sql-patterns.

Database snapshot: 13.11.2026 · package version 1a9332416d29 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100redaxo-architecture
    REDAXO project structure, request lifecycle, and core classes. Use when the user works on a REDAXO codebase, mentions REDAXO directory layout, asks about rex_addon/rex_config/rex_path/rex_url/rex_clang/rex_request, or when files under redaxo/src/, redaxo/data/, or redaxo/cache/…
  • 100redaxo-extension-points
    REDAXO Extension Points – the hook system for modifying core/addon behavior without patching files. Use when the user mentions rex_extension::register, hooks into events like ART_CONTENT / OUTPUT_FILTER / PACKAGES_INCLUDED, or wants to extend backend pages and content.
  • 80Backend Queries
    Write secure, performant database queries using parameterized statements, proper indexing, and query optimization techniques. Use this skill when writing SQL queries, ORM query methods, or database access code. When implementing data fetching logic that retrieves or updates…
  • 100bun-sqlite
    Use for bun:sqlite, SQLite operations, prepared statements, transactions, and queries.
  • 100go-database
    Use when writing or reviewing Go SQL queries, transactions, repositories, pools, migrations, or row mapping with database/sql, pgx, or an ORM; also for slow queries and queries in loops.
  • 100sql-optimization-patterns
    Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries. Use when debugging slow queries, designing database...

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.