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.
iv) Google’s cloud SQL – Offers fully managed relational database service for MySQL, PostgreSQL, and SQL Server.
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 into 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 platforms 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 start from free to as high as $2798. This means that it can support students who want to learn, startups, who are starting off, too 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.
- 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?
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
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 the Postgres database. Create a server, which connects to your remote database. Hostname/address is the same as the 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,
usersin the database. We will demonstrate using a simple application, which allows a user to register and log in.
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;-
db.php file connects to the database using PDO
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
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.
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 services 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.