Reading Time: 9 minutes

19,174 Views

Working with several databases in CodeIgniter 4 enables you to organise and separate your data more efficiently, which is very important for complicated applications. This article will walk you through the process of working with various databases in CodeIgniter 4.

If you need to handle several types of data or perform splits for scale, CodeIgniter 4 makes it simple to connect to and interact with many databases. This lesson will walk you through the full process, from setting up database connections to running queries and transactions.

Using multiple databases can improve the performance, security, and scalability of web applications, making them more reliable and efficient.

Let’s get started.

CodeIgniter 4 Installation

To create a CodeIgniter 4 setup run this given command into your shell or terminal. Please make sure composer should be installed.

composer create-project codeigniter4/appstarter codeigniter-4

Assuming you have successfully installed application into your local system.

Environment (.env) Setup

When we install CodeIgniter 4, we will have env file at root. To use the environment variables means using variables at global scope we need to do env to .env

Either we can do via renaming file as simple as that. Also we can do by terminal command.

Open project in terminal

cp env .env

Above command will create a copy of env file to .env file. Now we are ready to use environment variables.

Enable Development Mode

CodeIgniter starts up in production mode by default. You need to make it in development mode to see any error if you are working with application.

Open .env file from root.

# CI_ENVIRONMENT = production

 // Do it to 
 
CI_ENVIRONMENT = development

Now application is in development mode.

Why we need Multiple Databases in Application?

Sometimes, when we create an application which needs to be connected with two or more than two databases then we use the concept of multiple databases. But this will be only for those application whose scope of work is large.

Not all application needs multiple databases, few are very simple. They new only few tables to work.

In CodeIgniter, we have a core feature to connect application with multiple databases. Inside this article we will see connecting multiple databases only in MySQL. But apart from MySQL it also support Postgres, SQLlite etc.

Connect Application to Database

Open .env file, we will see all connection objects are commented (using # symbol).

So to connect with any default object we need to first remove # symbol to make it working.

 
#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

database.default.hostname = localhost
database.default.database = codeigniter_db1
database.default.username = myuser
database.default.password = Myuser@123
database.default.DBDriver = MySQLi
database.default.DBPrefix =
database.default.port = 3306

# database.tests.hostname = localhost
# database.tests.database = ci4_test
# database.tests.username = root
# database.tests.password = root
# database.tests.DBDriver = MySQLi
# database.tests.DBPrefix =
# database.tests.port = 3306

Here, we have provided database connection details at .env file. Now we are connected with database object default and ready to work with single database.

By default CodeIgniter 4 configuration provides two object/groups for database connection i.e default and tests.

How to load Database Instance in Application?

To load database in application we need to use database service. We have two different syntax to load database and use it in application.

$db_instance = \Config\Database::connect($group = null, bool $getShared = true);

$this->db = db_connect($db = null, bool $getShared = true)

By default these connection loads ‘default‘ group database connect variable.It’s because when we see Database.php file we can find public $defaultGroup = ‘default’; It is a connection group name.

Execute any Query

$query = $this->db->query("Query here");

$this->db points to default database group. More information about this query execution click here.

Setup Multiple Database Connection Groups

You need to follow few steps by help of which you can connect with multiple databases.

Steps #1: Add Connection Group in .env

Add database connection groups in .env file. Open .env file add your database connections.

#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

# DEFAULT connection group
database.default.hostname = localhost
database.default.database = codeigniter_db1
database.default.username = myuser
database.default.password = Myuser@123
database.default.DBDriver = MySQLi
database.default.DBPrefix =
database.default.port = 3306

# CUSTOM connection group - otherDb  
database.otherDb.hostname = localhost
database.otherDb.database = codeigniter_db2
database.otherDb.username = myuser
database.otherDb.password = Myuser@123
database.otherDb.DBDriver = MySQLi
database.otherDb.DBPrefix =
database.otherDb.port = 3306

# database.tests.hostname = localhost
# database.tests.database = ci4_test
# database.tests.username = root
# database.tests.password = root
# database.tests.DBDriver = MySQLi
# database.tests.DBPrefix =
# database.tests.port = 3306

You can see here, we have added other set of connection group with the name of otherDb.

Inside that connection group, provided details are hostname, database name, username, password.

Step #2: Add Connection Array in Database.php

Open Database.php file from /app/Config folder.

We only copied the $default array and pasted. Rename it to the custom connection group what we added in .env file.

//...

public array $default = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => '',
    'password' => '',
    'database' => '',
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => true,
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 3306,
];

public array $otherDb = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => '',
    'password' => '',
    'database' => '',
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => true,
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 3306,
];

//...

How To Load Database Connection Groups

Here, you will see how to load database connection groups into controller file as well as inside model file.

Load Database Groups in Controller

<?php

namespace App\Controllers;

use App\Controllers\BaseController;

class Dashboard extends BaseController
{
    private $db1;
  
    private $db2;

    public function __construct()
    {
        $this->db1 = db_connect(); // default database group
        
        $this->db2 = db_connect("otherDb"); // other database group
    }
}

$this->db = \Config\Database::connect(); // Loads default group

$this->db1 = \Config\Database::connect("otherDb"); // Loads OtherDb group

$this->db = db_connect(); // Loads default group

$this->db1 = db_connect("otherDb"); // Loads OtherDb group

otherDb is a connection group where we have connected with the database library_system.

Execute Database Query

$query = $this->db1->query("Query here");

This time $this->db1 indicates otherDb database.

Load Database Group in Model

<?php

namespace App\Models;

use CodeIgniter\Model;

class TestModel extends Model
{
	protected $DBGroup              = 'default'; // default database group
  
    // Change it for other database group
    // protected $DBGroup  = 'otherDb';
  
	//...
}

protected $DBGroup = ‘default’; // default database group

When we change database group, model will work with that connection.

Change Default Connection Group

If we open Database.php file then we can see that connection group is defined inside key as

public $defaultGroup = 'default';

This is $default connection group. So we load database by using this syntax \Config\Database::connect() OR db_connect(), It indicates default group.

\Config\Database::connect() OR db_connect() -> default group as per above settings

In case if we want that it should be point out to different say otherDb connction, simply define connection group in database.php and change above value to public $defaultGroup = ‘otherDb’;

\Config\Database::connect() OR db_connect() -> points to OtherDb as per above settings.

That’s it.

We hope this article helped you to learn about CodeIgniter 4 Working with Multiple Databases Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.

How To Connect CodeIgniter 4 with Multiple Databases?