CodeIgniter 4 Working with Multiple Databases & Connection Groups

Share this Article

CodeIgniter 4 is a open source PHP Framework and is too much flexible to work. It’s working structure is totally different from previous versions of CodeIgniter. If you are also looking for complete beginners article for CodeIgniter 4, click here to go.

In a article, we discussed about how can we work with a single database. Also we have discussed about all available methods to work with it. But inside this article we are work over CodeIgniter 4 Working with Multiple Databases. Application with multiple MySQL databases and it’s connection groups.

Note*: For this article, CodeIgniter v4.1 setup has been installed. May be when you are seeing, version will be updated. CodeIgniter 4.x still is in development mode.

Let’s get started.


Download & Install CodeIgniter 4 Setup

We need to download & install CodeIgniter 4 application setup to system. To set application we have multiple options to proceed. Here are the following ways to download and install CodeIgniter 4 –

  • Manual Download
  • Composer Installation
  • Clone Github repository of CodeIgniter 4

Complete introduction of CodeIgniter 4 basics – Click here to go. After going through this article you can easily download & install setup.

Here is the command to install via composer –

$ composer create-project codeigniter4/appstarter codeigniter-4

Assuming you have successfully installed application into your local system.


Settings Environment Variables

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

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.

CodeIgniter starts up in production mode by default. Let’s do it in development mode. So that while working if we get any error then error will show up.

# 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. Now, we are going to to see CodeIgniter 4 Working with Multiple Databases. Also we will see how can we create Multiple Database connection groups in CodeIgniter 4.

Let’s see database configuration first.


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 = ci4_application
database.default.username = admin
database.default.password = Admin@123
database.default.DBDriver = MySQLi

# database.tests.hostname = localhost
# database.tests.database = ci4
# database.tests.username = root
# database.tests.password = root
# database.tests.DBDriver = MySQLi
   

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);

OR

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

By default these connection loads ‘defaultgroup 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”); More information about this query execution click here.

$this->db points to default database group.


Multiple Database Connection Groups

As we have discussed that in CodeIgniter 4 application we can create multiple database connection groups. So let’s create some more connection groups.

We have two options to create connection groups –

  • We can use .env file to add custom groups
  • Using Database.php file

Connection Groups Using .env File

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

# DEFAULT connection group
database.default.hostname = localhost
database.default.database = ci4_application
database.default.username = admin
database.default.password = Admin@123
database.default.DBDriver = MySQLi

# CUSTOM connection group - otherDb  
database.otherDb.hostname = localhost
database.otherDb.database = library_system
database.otherDb.username = admin
database.otherDb.password = Admin@123
database.otherDb.DBDriver = MySQLi

# database.tests.hostname = localhost
# database.tests.database = ci4
# database.tests.username = root
# database.tests.password = root
# database.tests.DBDriver = MySQLi
  

We 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.

How can we load Custom Connection Group ?

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

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

OR

$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.

Connection Groups Using Database.php file

Database.php file location at application /app/Config/Database.php

public $default = [
		'DSN'      => '',
		'hostname' => 'localhost',
		'username' => '',
		'password' => '',
		'database' => '',
		'DBDriver' => 'MySQLi',
		'DBPrefix' => '',
		'pConnect' => false,
		'DBDebug'  => (ENVIRONMENT !== 'production'),
		'cacheOn'  => false,
		'cacheDir' => '',
		'charset'  => 'utf8',
		'DBCollat' => 'utf8_general_ci',
		'swapPre'  => '',
		'encrypt'  => false,
		'compress' => false,
		'strictOn' => false,
		'failover' => [],
		'port'     => 3306,
];

public $otherDb = [
		'DSN'      => '',
		'hostname' => 'localhost',
		'username' => 'root',
		'password' => 'root',
		'database' => 'library_system',
		'DBDriver' => 'MySQLi',
		'DBPrefix' => '',
		'pConnect' => false,
		'DBDebug'  => (ENVIRONMENT !== 'production'),
		'cacheOn'  => false,
		'cacheDir' => '',
		'charset'  => 'utf8',
		'DBCollat' => 'utf8_general_ci',
		'swapPre'  => '',
		'encrypt'  => false,
		'compress' => false,
		'strictOn' => false,
		'failover' => [],
		'port'     => 3306,
	];

How can we load & use ?

$this->db1 = \Config\Database::connect(“otherDb”);

OR

$this->db1 = db_connect(“otherDb”);


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.

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

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.

Find More on CodeIgniter 4 here

1 thought on “CodeIgniter 4 Working with Multiple Databases & Connection Groups”

Leave a Comment

What are CSRF Functions in CodeIgniter 4 Tutorial
MySQL Stored Procedure in CodeIgniter 4 Tutorial