Get the FREE Ultimate OpenClaw Setup Guide →

working-with-database

npx machina-cli add skill MacareuxDigital/concretecms-skills/working-with-database --openclaw
Files (1)
SKILL.md
3.0 KB

Working with Database

How to connect

Use $db = $this->app->make(\Concrete\Core\Database\Connection\Connection::class); Do not use the Database facade, it is deprecated.

XML file format for database schemas

Concrete CMS uses "Doctrine XML" file format to define database schema. Ref: https://concretecms.github.io/doctrine-xml/doctrine-xml-0.5.xsd

Here's an example with all the features offered by Doctrine XML:

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns="http://www.concrete5.org/doctrine-xml/0.5"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.concrete5.org/doctrine-xml/0.5
    https://concretecms.github.io/doctrine-xml/doctrine-xml-0.5.xsd"
>

    <table name="Companies" comment="List of companies">
        <field name="Id" type="integer" comment="Record identifier">
            <unsigned/>
            <autoincrement/>
            <key/>
        </field>
        <field name="Name" type="string" size="50" comment="Company name">
            <notnull/>
            <opt for="mysql" collation="utf8_bin"/>
        </field>
        <opt for="mysql" engine="InnoDB" charset="utf8" collate="utf8_unicode_ci" row_format="compact"/>
    </table>

    <table name="Employees">
        <field name="Id" type="integer">
            <unsigned/>
            <autoincrement/>
            <key/>
        </field>
        <field name="IdentificationCode" type="string" size="20">
            <fixed/>
        </field>
        <field name="Company" type="integer">
            <unsigned/>
            <notnull/>
        </field>
        <field name="FirstName" type="string" size="50">
            <default value=""/>
            <notnull/>
        </field>
        <field name="LastName" type="string" size="50">
            <notnull/>
        </field>
        <field name="Income" type="decimal" size="10.2">
            <default value="1000"/>
        </field>
        <field name="HiredOn" type="datetime">
            <deftimestamp/>
        </field>
        <index>
            <fulltext/>
            <col>FirstName</col>
        </index>
        <index name="IX_EmployeesIdentificationCode">
            <unique/>
            <col>IdentificationCode</col>
        </index>
        <references table="Companies" onupdate="cascade" ondelete="restrict">
            <column local="Company" foreign="Id"/>
        </references>
    </table>

</schema>

AXMLS (Adodb-xmlschema, using <schema version="0.3"></schema>) is a legacy format, so do not use it for new code. No need to refactor XML files already exists.

Best Practices

  • Avoid using SQL reserved words (especially in MySQL 8+) for column names. For example, lead is reserved in MySQL 8 — do not use it as a column name. Prefer alternatives like summary.

Source

git clone https://github.com/MacareuxDigital/concretecms-skills/blob/main/working-with-database/SKILL.mdView on GitHub

Overview

This skill enables connecting to Concrete CMS databases, executing queries, and manipulating data. It also covers defining database schemas with Doctrine XML and avoiding deprecated practices.

How This Skill Works

Connect to the database using $db = $this->app->make(\Concrete\Core\Database\Connection\Connection::class); and do not use the Database facade. Define schemas with Doctrine XML (not AXMLS) to describe tables, fields, indexes, and references, as shown in the example, including a Companies table and an Employees table with constraints.

When to Use It

  • Building a custom block type that requires persisting data to a new database table
  • Connecting to an external or separate database from a Concrete project
  • Defining or updating a module's schema using Doctrine XML
  • Adding indexes or foreign key references to enforce data integrity
  • Starting new code—avoid legacy AXMLS formats for new implementations

Quick Start

  1. Step 1: Obtain a DB connection with $db = $this->app->make(\Concrete\Core\Database\Connection\Connection::class);
  2. Step 2: Create a Doctrine XML schema file (e.g., schema.xml) describing your tables, fields, and references as shown in the example
  3. Step 3: Validate the XML schema and implement queries against $db; ensure you’re not using the deprecated Database facade

Best Practices

  • Avoid using SQL reserved words for column names; prefer alternatives like summary instead of lead
  • Use Doctrine XML for schema definitions in Concrete CMS; avoid the legacy AXMLS format for new code
  • Obtain a DB connection via the Concrete CMS service container: $db = $this->app->make(\Concrete\Core\Database\Connection\Connection::class)
  • Do not use the deprecated Database facade when interacting with the database
  • Carefully define constraints, defaults, and indexes (e.g., notnull, default values, fulltext/index definitions) as shown in the XML example

Example Use Cases

  • Define a Companies table with Id (auto-increment, key) and Name (notnull, string) in Doctrine XML
  • Create an Employees table with various fields (Id, IdentificationCode, Company, FirstName, LastName, Income, HiredOn) and appropriate defaults and constraints
  • Establish a foreign key relationship from Employees.Company to Companies.Id with onupdate and ondelete actions
  • Add reverse indexes (e.g., fulltext on FirstName) to optimize search queries
  • Migrate an existing business schema by describing new tables and relationships in Doctrine XML rather than AXMLS

Frequently Asked Questions

Add this skill to your agents
Sponsor this space

Reach thousands of developers ↗