Introduction

When building a dynamic web application, one needs a database. You can host your application and database in the same server or you can use a database hosting managed by a third party.

When you use a managed database hosting service, all the administrative tasks such as DBMS installation, upgrading, scalability, high availability and backup handling is taken care of by the hosting service so that you can dedicate time to build the application - it is known as Database-as-a-Service (DBaaS).

Some hosting service can offer both web application hosting and database hosting but you can choose either of the services.

The following are some of the services, which offers database hosting services for the commonly used open source databases - MySQL/MariaDB and Postgres;-

i) Digital Ocean's database service - Offers MySQL, Postgres and Redis services.  

ii) Amazon's Aurora service - Offers MySQL and Postgres-compatible database service in multiple locations.  

iii) Microsoft Azure provides 2 separate services - Azure database for MySQL and Azure database for Postgres.

iv) Google's cloud SQL - Offers fully managed relational database service for MySQL, PostgreSQL, and SQL Server.

v) Heroku Data service - Offers clearDB, which is a high speed database for MySQL powered applications and Heroku Postgres, which is a managed SQL Database as a Service.

vi) IBM Cloud Database for PostgreSQL

vii) Alibaba cloud's ApsaraDB RDS - offers both hosted databases for both MySQL and PostgreSQL.

viii) Oracle MySQL service - MySQL offering for Oracle.

ElephantSQL - This is a PostgreSQL database hosting service.

What is ElephantSQL?

ElephantSQL is a PostgreSQL database hosting service, which is integrated to many cloud application platforms. With ElephantSQL, you just create an account and you're ready to use a database immediately.

The integration of ElephantSQL with other cloud platform such as Google cloud, Amazon web services, Microsoft azure and IBM cloud makes it possible to spin a database instance in a location of choice across the world.

ElephantSQL pricing plans starts from free to as high as $2798. This means that it can support students who want to learn, startups, who are starting off, to big established multinational co-operations.

Objectives of this walk-through

i) Create ElephantSQL account and Postgres database instance on ElephantSQL.

ii) Demonstrate how to connect a PHP application to a Postgres database running in ElephantSQL.

Prerequisite

  • A basic understanding of PHP programming language.
  • A basic understanding of PDO (PHP Data Objects)
  • A basic understanding of SQL based DBMS.
  • Have PHP and Apache web server installed in your machine. In this tutorial, we will use PHP and Apache Web server bundled in XAMPP.
  • Have PgAdmin4 installed in your machine.

Procedure

Let's follow the following steps to achieve our objectives.

1. Create ElephantSQL account and Postgres database instance

Create an account in ElephantSQL and then a Postgres database instance - you can choose tiny turtle option, which is free of charge. When the database instance is created, you will get access to server name, database name (which you can change), database user (which you can change) and database user password - see screenshot below.

If you have trouble creating the account and database instance, you can check out the video below.  

2. Use pgAdmin 4 to connect to your remote database

pgAdmin is the open source tool for managing Postgres database. Create a server, which connects to your remote database. Host name/address is same as server.

Once a connection is made, you will see the remote database, which was created in ElephantSQL.

Next, run the following query to create a table, users, in the database. We will demonstrate using a simple application, which allows a user to register and login.

CREATE TABLE users(
    id serial PRIMARY KEY,
    full_name varchar (32) NOT NULL,
    username varchar (32) NOT NULL,
    password varchar (255),---Notice the password field size
    UNIQUE(username)
);

3. Clone app from GitHub and set it up in your Apache server.

Clone our simple app from Github. If you use XAMPP, then the app has the following structure;-

htdocs
    article3
    	-util.php
        -db.php
        -user.php
        -index.php

util.php has database connection variables and is as shown below;-

<?php
	class Util{
            static $DB_NAME = "your elephantsql  DB name here";
            static $DB_USER = "your elephantsql user username here";
            static $DB_USER_PASS = "db user here";
            static $SERVER_NAME = "your elephantsql server name here";
	}
?>

db.php file connects to the database using PDO

<?php
    include_once './util.php';	
    class DBConnector {
        var $pdo;
        function __construct(){
                $dsn= "pgsql:host=". Util::$SERVER_NAME . ";dbname=" . Util::$DB_NAME ."";
                $options = [ 
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_EMULATE_PREPARES => false,
                        //Notice here that we read data from tables and is presented as an associative array
                        //indexed by names of column
                        PDO::ATTR_DEFAULT_FETCH_MODE =>PDO::FETCH_ASSOC
                        ];
                try{
                        $this->pdo = new PDO($dsn, Util::$DB_USER, Util::$DB_USER_PASS, $options);				
                }catch (PDOException $e){
                        echo $e->getMessage();
                }			
        }
        public function connectToDB(){
                return $this->pdo;
        } 			
        public function closeDB(){
                $this->pdo = null;
        }
    }
?>

Notice that in the $dsn variable, we have used pgsql as the dialect and thus you have to enable PDO_PGSQL driver for PostgreSQL database in php.ini file (see here).

$dsn= "pgsql:host=". Util::$SERVER_NAME . ";dbname=" . Util::$DB_NAME ."";

Finally, index.php is the entry point to the application and user.php is a class, which implements register and login operations.

4. Testing the application

We will use Postman to execute our operations. If you have never used postman to execute endpoints, this article on How to test API endpoints using Postman can help you.

Let's test register operation:

We create a postman request as shown below and click Send;-

If you check in the users table in your remote database, you should have a record inserted.

Let's test the login operation:

We change the request as shown below and click Send

When we login, we just need a username and password and this is the reason why we have unchecked fullName in the body of the request.  If you provide the correct username and password, then you will see the text Correct blah blah....  as your response.

Conclusion

In this walk-through, we have demonstrated how to connect  a web application to a database hosting service, ElephantSQL. ElephantSQL is one of the hosted database service but you can use other services available in the market such as the ones we outlined in the introductory part of this article.

I hope you enjoyed implementing this walk-through. Feel free to leave your questions in the comments section.

See you in the next article.

You've successfully subscribed to Decoded For Devs
Welcome back! You've successfully signed in.
Great! You've successfully signed up.
Your link has expired
Success! Your account is fully activated, you now have access to all content.