Database Management

Improvements?

Let us know by posting here.

Database Management

Concrete CMS utilizes a database to store persistent data like Pages, Files, Users, etc. It offers developers an API for database access, based on the Doctrine Database Access Layer and PHP's PDO.

MySQL in Concrete CMS

MySQL is required and connected during Concrete CMS installation. However, most database interactions are database-agnostic, thanks to the use of the DBAL library.

Accessing the Database for Queries

Custom database queries in Concrete CMS are straightforward. Retrieve the database object using:

\Database::connection();

Example Query

public static function getFoo($id)
{
    $db = \Database::connection();
    $foo = $db->fetchColumn('SELECT foo FROM bar WHERE id = ?', [$id]);
    return $foo;
}

This example uses Prepared Statements for safety against SQL injection.

Doctrine API

Concrete's database class builds on Doctrine DBAL Connection. For Doctrine functions, see Doctrine DBAL Documentation.

Avoid Deprecated Methods

Avoid using Loader::db() or Database::get(). Preferably use \Database::connection();.

Using Dependency Injection

For custom classes, inject the database connection in the constructor:

namespace My\Custom\Name;
use Concrete\Core\Database\Connection\Connection;

class Bar
{
    protected $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    public function getFoo($id)
    {
        $foo = $this->connection->fetchColumn('SELECT foo FROM bar WHERE id = ?', [$id]);
        return $foo;
    }
}

Instantiate custom objects with dependencies using Core::make():

$bar = Core::make('My\Custom\Name\Bar');
$foo = $bar->getFoo(12);

Core::make() automatically handles dependency injection.

Connecting to Multiple Databases

In Concrete CMS, you can connect to multiple databases. While the default connection is accessed with:

\Database::connection();

For additional databases, modify your application/config/database.php file. Add a new entry under the connections array. For example, for a "pricing" database:

return array(
    'default-connection' => 'concrete',
    'connections' => array(
        'concrete' => array(
            'driver' => 'c5_pdo_mysql',
            'server' => 'localhost',
            'database' => 'database',
            'username' => 'username',
            'password' => 'password',
            'charset' => 'utf8',
        ),
        'pricing' => array(
            'driver' => 'c5_pdo_mysql',
            'server' => 'secure-pricing.wherever.com',
            'database' => 'pricing_db',
            'username' => 'username',
            'password' => 'password',
            'charset' => 'utf8',
        ),
    ),
);

To connect to this additional database, use:

$db = \Database::connection('pricing');

This method allows custom queries on the specified database.