Guide to Creating a REST API with Ubiquity PHP Connected to PostgreSQL

This guide will walk you through creating a REST API using Ubiquity PHP, connected to a PostgreSQL database. We will cover the following operations:

1. Querying all data in the users table (fields: username and email).
2. Updating the email based on username.
3. Deleting data based on username.

1. Install Ubiquity PHP

Start by installing Ubiquity using Composer:

composer create-project phpmv/ubiquity-project my-project
cd my-project

You can also install the development tools if you want to use Ubiquity Devtools:

composer require phpmv/ubiquity-devtools

2. Configure PostgreSQL Connection

Configure the database connection by editing the app/config/config.php file. Replace the default configuration with your PostgreSQL credentials:

return array(
    "siteUrl"=>"http://127.0.0.1/",
    "database"=>[
        "type"=>"pgsql",
        "dbName"=>"your_database_name",
        "serverName"=>"localhost",
        "port"=>5432,
        "user"=>"your_username",
        "password"=>"your_password",
        "options"=>[],
        "cache"=>false
    ],
    "sessionName"=>"myapp"
);

3. Generate the User Model

Use Ubiquity’s devtools to generate the model for the users table:

php vendor/bin/Ubiquity init-db
php vendor/bin/Ubiquity models:create users

This will generate a User model in the models folder.

4. Create the REST API Controller

Create a controller to handle the API endpoints. You can do this manually or by using Ubiquity Devtools:

php vendor/bin/Ubiquity controller rest UserController -r

This will generate a controller with basic REST actions. Modify the UserController.php to include specific actions for querying, updating, and deleting users.

Edit the UserController.php in the app/controllers directory:

<?php
namespace controllers;

use Ubiquity\controllers\rest\RestController;
use models\User;
use Ubiquity\orm\DAO;

class UserController extends RestController {

    // 1. Query all data in the users table
    /**
     * @route("users", "methods"=>["get"])
     */
    public function index() {
        $users = DAO::getAll(User::class, '1=1', false, ['username', 'email']);
        echo $this->_getResponseFormatter()->get($users);
    }

    // 2. Update email based on username
    /**
     * @route("users/update/{username}", "methods"=>["post"])
     */
    public function updateEmail($username) {
        $user = DAO::getOne(User::class, 'username = ?', false, [$username]);
        if($user){
            $email = $this->request->get('email');
            if ($email) {
                $user->setEmail($email);
                if(DAO::update($user)){
                    echo $this->_getResponseFormatter()->formatMessage('Email updated successfully');
                }
            } else {
                echo $this->_getResponseFormatter()->formatMessage('Email is required', false);
            }
        } else {
            echo $this->_getResponseFormatter()->formatMessage('User not found', false);
        }
    }

    // 3. Delete data based on username
    /**
     * @route("users/delete/{username}", "methods"=>["delete"])
     */
    public function deleteUser($username) {
        $user = DAO::getOne(User::class, 'username = ?', false, [$username]);
        if($user){
            if(DAO::remove($user)){
                echo $this->_getResponseFormatter()->formatMessage('User deleted successfully');
            }
        } else {
            echo $this->_getResponseFormatter()->formatMessage('User not found', false);
        }
    }
}

5. Define Routes

The routes are defined directly in the controller using annotations, as seen above with @route.

6. Run the Application

You can start the server using the built-in PHP server:

php -S 127.0.0.1:8090 -t public/

Or by using Ubiquity Devtools:

php vendor/bin/Ubiquity serve

7. Test the API

• Query all users: Send a GET request to http://localhost:8090/users.
• Update email: Send a POST request to http://localhost:8090/users/update/{username} with the following JSON body:

  {
    "email": "new_email@example.com"
  }

• Delete user: Send a DELETE request to http://localhost:8090/users/delete/{username}.

This setup provides a basic REST API using Ubiquity PHP connected to PostgreSQL. You can expand the functionality as needed for your project.