Welcome to Abdul Malik Ikhsan's Blog

Connecting to non-public PostgreSQL schema with CodeIgniter 4

Posted in CodeIgniter 4, Tutorial PHP by samsonasik on April 21, 2020

If you are building application with CodeIgniter 4 using PostgreSQL database, you will have to use ‘public’ default schema. How about if you want to use a different schema for specific needs? For example, you have the the product table that placed in “inventory” schema like the following:

To be able to make operation against “inventory” schema, we need to update schema property of Database Connection class. If we use Model class, eg: ProductModel class, we can override __construct() method and update the schema value, like the following:

<?php

namespace App\Models;

use CodeIgniter\Database\ConnectionInterface;
use CodeIgniter\Model;
use CodeIgniter\Validation\ValidationInterface;

class ProductModel extends Model
{
    protected $table      = 'product';
    protected $returnType = 'array';

    public function __construct(ConnectionInterface &$db = null, ValidationInterface $validation = null)
    {
        parent::__construct($db, $validation);

        $this->db->schema = 'inventory';
    }
}

So, whenever we call:

use App\Models\ProductModel;

// ...
$model = model(ProductModel::class);
$model->findAll();

We will find all product table records in ‘inventory’ schema on first priority, if table not found in the ‘inventory’ schema, it will fallback to ‘public’.

That’s it.

Tagged with: ,