redaxo-sql-patterns
/redaxo-sql-patterns
Pomaga korzystać z bazy danych w REDAXO przez rex_sql: zapytania, przygotowane instrukcje, transakcje i poprawne escapowanie przy pracy z tabelami rex_*.
- Autor
- friendsofredaxo ↗
- Repozytorium
- claude-marketplace ↗
- Licencja
- MIT
- Ostatnia zmiana repozytorium
- 20.09.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 1
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 0
Brak sygnałów. Repozytorium zmienione 20.09.2026, bez odwołań do wycofanych modeli i API.
MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.
Co robi i kiedy
Pomaga korzystać z bazy danych w REDAXO przez rex_sql: zapytania, przygotowane instrukcje, transakcje i poprawne escapowanie przy pracy z tabelami rex_*.
Oryginalny opis (EN)
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.
Jak działa
- Tables and the rex::getTable() helper
- Reading – setQuery with placeholders
- Counting / aggregates
- Inserting – fluent builder
- Updating
- Deleting
- Transactions
- Schema changes – rexsqltable
- Common pitfalls
Podgląd SKILL.md
---
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.
Wersje
W bazie nie ma innych skilli o nazwie redaxo-sql-patterns.
Migawka bazy z 13.11.2026 · wersja paczki 1a9332416d29 (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 100redaxo-architecture
Wyjaśnia strukturę projektu REDAXO, cykl życia żądania i klasy rdzenia, takie jak rex_addon, rex_config, rex_path, rex_url, rex_clang i rex_request. - 100redaxo-extension-points
Wyjaśnia punkty rozszerzeń REDAXO, czyli system hooków, którym zmienia się działanie rdzenia i dodatków bez edycji plików, na przykład przez rex_extension::register. - 80Backend Queries
Pisze bezpieczne i wydajne zapytania do bazy danych z parametryzowanymi instrukcjami, indeksami i optymalizacją, w tym SQL i metody ORM oraz eliminowanie N+1 przez eager loading lub joiny. - 100bun-sqlite
Obsługuje bun:sqlite: operacje SQLite, przygotowane zapytania, transakcje i zapytania. - 100go-database
Pomaga pisać i recenzować zapytania SQL, transakcje, repozytoria, pule połączeń, migracje i mapowanie wierszy w Go z database/sql, pgx lub ORM, także przy wolnych zapytaniach i zapytaniach w pętlach. - 100sql-optimization-patterns
Pomaga optymalizować zapytania SQL, dobierać strategię indeksów i analizować plany EXPLAIN przy wolnych zapytaniach.