Database

Flarum supports a variety of database systems, including MySQL, PostgreSQL, and SQLite. Most extensions will not have to worry about the specifics of the database system, as Laravel's query builder handles the differences between them. However, you can still run into instances where you need to write certain database operations differently depending on the database system. This section aims to document some of the common pitfalls and solutions.

danger

Any usage of raw queries will require you to write the queries in a way that is compatible with all supported database systems. This is especially important if you are writing a public extension, as you cannot guarantee which database system your users will be using.

Specifying supported database systems

You may choose to not support all database systems, but you should specify which ones you do support in your extension's composer.json file. This will alert users to the fact that your extension may not work with their database system.

{
    "extra": {
        "flarum-extension": {
            "database-support": [
                "mysql",
                "pgsql",
                "sqlite"
            ]
        }
    }
}

Conditional query methods

Flarum adds the following query builder methods to simplify writing queries specific to a database system:

// this is just an example, otherwise you would just use eloquent's whereYear method.
$query
  ->whenMySql(function ($query) {
      $query->whereRaw('YEAR(created_at) = 2022');
  })
  ->whenPgSql(function ($query) {
      $query->whereRaw('strftime("%Y", created_at) = 2022');
  })
  ->whenSqlite(function ($query) {
      $query->whereRaw('EXTRACT(YEAR FROM created_at) = 2022');
  });

Common pitfalls

Loose data grouping

In SQLite and non-strict MySQL, you can group by a column that is not in the SELECT clause. This fails in PostgreSQL, which requires all columns in the SELECT clause to be in the GROUP BY clause. In PostgreSQL, you can use the DISTINCT ON clause to achieve the same result.

$query
  ->whenPgSql(function ($query) {
      // PostgreSQL
      $query->select('id', 'name', 'created_at')
          ->distinct('name')
          ->orderBy('name');
  }, else: function ($query) {
      // MySQL, SQLite
      $query->select('id', 'name', 'created_at')
          ->groupBy('name');
  });

Seeding record with their IDs

In PostgreSQL, when inserting data with the Auto increment column value specified, the database will not increase the sequence value. So you have to do it manually. Here is an example of Flarum core inserting the default member groups:

    'up' => function (Builder $schema) {
        $db = $schema->getConnection();

        $groups = [
            [Group::ADMINISTRATOR_ID, 'Admin', 'Admins', '#B72A2A', 'fas fa-wrench'],
            [Group::GUEST_ID, 'Guest', 'Guests', null, null],
            [Group::MEMBER_ID, 'Member', 'Members', null, null],
            [Group::MODERATOR_ID, 'Mod', 'Mods', '#80349E', 'fas fa-bolt']
        ];

        foreach ($groups as $group) {
            if ($db->table('groups')->where('id', $group[0])->exists()) {
                continue;
            }

            $db->table('groups')->insert(array_combine(['id', 'name_singular', 'name_plural', 'color', 'icon'], $group));
        }

        // PgSQL doesn't auto-increment the sequence when inserting the IDs manually.
        if ($db->getDriverName() === 'pgsql') {
            $table = $db->getSchemaGrammar()->wrapTable('groups');
            $seq = $db->getSchemaGrammar()->wrapTable('groups_id_seq');
            $db->statement("SELECT setval('$seq', (SELECT MAX(id) FROM $table))");
        }
    },