Connecting OCI Autonomous Database to PHP / Laravel Apps

Hey there, fellow techies! ??

Ever tried connecting an Oracle Cloud Infrastructure (OCI) Autonomous Database or any Oracle DB to your PHP app? It can be a bit of a head-scratcher, right? Well, you're in luck! I've been tinkering with this setup on both Windows and Ubuntu, and I thought I'd share my experience with you.

In this guide, we'll walk through the process step-by-step. Whether you're working on your local Windows machine or deploying to an Ubuntu server on OCI, I've got you covered. We'll be focusing on Laravel projects, but the basic principles apply to other PHP frameworks too.

So, let's dive in!

1. Creating an Autonomous DB in Oracle Cloud

1. Login to your OCI account

2. Navigate to Autonomous Databases

3. Create the DB (in this case, select Transaction Processing and Serverless DB)

Now our OCI Serverless DB is up and running.

2. Connecting from Your Laravel App

I tested on both Linux and Windows as I have local dev in Windows and the test server as Ubuntu on ARM-based Ampere VM on OCI (both VM and Serverless DB are in my always free OCI account).

2.1 Connecting from Windows 11 Machine

This setup uses Laravel Herd (for Laravel dependencies - PHP and NGINX).

2.1.1 Set Up Oracle Instant Client

1. Download Oracle Instant Client:

- Visit https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

- Select the Basic package and download the file

2. Install Oracle Instant Client:

- Unzip it to any location (e.g., D:\instantclient-basic-windows\instantclient_23_4)

2.1.2 Configure DB Wallet

1. Download wallet:

- Go to your Autonomous DB in OCI

- Click on the Database connection tab

- In the "Download client credentials (Wallet)" section, select Instance Wallet

- Click download, set a password, and download it

2. Install wallet:

- Unzip Wallet_DBnameXXX.zip and copy all files inside

- Paste files to Instant Client network/admin folder (D:\instantclient-basic-windows\instantclient_23_4\network\admin)

2.1.3 Configure Environment

1. Add the Instant Client location to Windows system PATH (D:\instantclient-basic-windows\instantclient_23_4)

2. Ensure your PHP version is also in the PATH (C:\Users\YOUR_WIN_USER\.config\herd\bin\php83)

2.1.4 Activate OCI8 Extension

1. In php.ini (C:\Users\YOUR_WIN_USER\.config\herd\bin\php83), search for OCI8_19

2. Remove the commenting (;) before it:

   extension=oci8_19  ; Use with Oracle Database 19 Instant Client        

3. Restart PHP

2.1.5 Test OCI8 Installation

Add this route to Laravel routes/web file:

Route::get('phpmyinfo', function () {
phpinfo();
})->name('phpmyinfo');        

Open web browser to hostname/phpmyinfo and check if OCI8 is in the active extension list.

2.1.6 Set Up Laravel OCI8 Package

In your Laravel folder, run:

composer require yajra/laravel-oci8        

php artisan vendor:publish --tag=oracle        

2.1.7 Configure .env File

Add these lines to your .env file:

DB_CONNECTION=oracle
DB_PORT=1522
# check the tns name from tnsnames.ora file from the wallet or network/admin folder.
DB_TNS=XXXX_high 
DB_USERNAME= 
DB_PASSWORD=        

2.1.8 Set Up DB User Access

1. Go to your Autonomous DB page

2. Click on Database Actions

3. Navigate to Database Users

4. Give your user the required access to read, write, or create new user

2.1.9 Test Connection

Run Laravel migrations to test the connection.

2.2 Connecting from Ubuntu Instance

2.2.1 Set Up Oracle Instant Client

1. SSH into Ubuntu instance: ssh user@ip

2. Download Instant Client:

   wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basic-linux-arm64.zip        

3. Install Instant Client:

   sudo mkdir -p /opt/oracle        

   sudo unzip instantclient-basic-linux.x64.zip -d /opt/oracle        

2.2.2 Configure DB Wallet

1. Download wallet from OCI (as in Windows section)

2. Move files to /opt/oracle/instantclient_19_24/network/admin

3. Use scp or WinSCP (both accept only PuTTY key; WinSCP can convert normal private key)

2.2.3 Configure System

Run these commands:

echo /opt/oracle/instantclient_19_24 > /etc/ld.so.conf.d/oracle-instantclient.conf        

ldconfig        

2.2.4 Set Up PHP and OCI8

Run these commands:

sudo apt install php-dev php-pear build-essential libaio1        

sudo pecl channel-update pecl.php.net        

echo "instantclient,/opt/oracle/instantclient_19_24" | pecl install oci8        

2.2.5 Activate OCI8 Extension

1. In php.ini (e.g., /etc/php/8.3/fpm/php.ini), search for OCI8_19

2. Remove commenting:

   extension=oci8_19  ; Use with Oracle Database 19 Instant Client        

3. Restart PHP

2.2.6 Test OCI8 Installation

Run:

php -m | grep 'oci8'         

or use the phpinfo route as in Windows section.

2.2.7 Set Up Laravel OCI8 Package

If it's a fresh install, run:

composer require yajra/laravel-oci8        

php artisan vendor:publish --tag=oracle        

2.1.7 Configure .env File

Add these lines to your .env file:

DB_CONNECTION=oracle
DB_PORT=1522
# check the tns name from tnsnames.ora file from the wallet or network/admin folder.
DB_TNS=XXXX_high 
DB_USERNAME= 
DB_PASSWORD=        

2.1.8 Set Up DB User Access

1. Go to your Autonomous DB page

2. Click on Database Actions

3. Navigate to Database Users

4. Give your user the required access to read, write, and create new users

2.1.9 Test Connection

Run Laravel migrations to test the connection.

2.2.10 Test Connection

Run Laravel migrations to test.

Remember to prioritize security when handling database connections and credentials.

#OracleCloud #PHP #Laravel #DatabaseConnectivity #CloudComputing