Guide to Creating a REST API with Slim and Workerman PHP Connected to PostgreSQL

This guide will help you create a REST API using Slim and Workerman PHP, connected to a PostgreSQL database. The operations we will cover include:

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

1. Install Slim and Workerman PHP

First, set up a new project with Slim and Workerman PHP. Use Composer to install the necessary packages:

composer require slim/slim:"^4.0"
composer require workerman/webman
composer require slim/psr7
composer require illuminate/database

2. Configure PostgreSQL Connection

Create a .env file in the root directory of your project and add the PostgreSQL connection details:

DB_CONNECTION=pgsql
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=your_database_name
DB_USERNAME=your_username
DB_PASSWORD=your_password

3. Set Up the Database Connection

In your project, create a new file config/database.php to handle the database connection:

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;

$capsule->addConnection([
    'driver'    => 'pgsql',
    'host'      => env('DB_HOST'),
    'database'  => env('DB_DATABASE'),
    'username'  => env('DB_USERNAME'),
    'password'  => env('DB_PASSWORD'),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
]);

$capsule->setAsGlobal();
$capsule->bootEloquent();

You also need to include this configuration in the Slim application. Update the index.php file to load the .env file and database configuration:

<?php

require 'vendor/autoload.php';

use Dotenv\Dotenv;
use Slim\Factory\AppFactory;

$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

require __DIR__ . '/config/database.php';

$app = AppFactory::create();

4. Create the REST API Endpoints

Create a controller to manage the API endpoints. For example, create src/Controller/UserController.php:

<?php

namespace App\Controller;

use Psr\Http\Message\ResponseInterface as Response;
use Psr\Http\Message\ServerRequestInterface as Request;
use Illuminate\Database\Capsule\Manager as DB;

class UserController
{
    // 1. Query all data in the users table
    public function index(Request $request, Response $response)
    {
        $users = DB::table('users')->select('username', 'email')->get();
        $response->getBody()->write($users->toJson());
        return $response->withHeader('Content-Type', 'application/json');
    }

    // 2. Update email based on username
    public function update(Request $request, Response $response, $args)
    {
        $username = $args['username'];
        $data = $request->getParsedBody();
        $new_email = $data['email'] ?? null;

        if (!$new_email) {
            return $response->withStatus(400)->withHeader('Content-Type', 'application/json')->write(json_encode(['error' => 'Email is required']));
        }

        DB::table('users')->where('username', $username)->update(['email' => $new_email]);
        return $response->withHeader('Content-Type', 'application/json')->write(json_encode(['message' => 'Email updated successfully']));
    }

    // 3. Delete data based on username
    public function delete(Request $request, Response $response, $args)
    {
        $username = $args['username'];

        DB::table('users')->where('username', $username)->delete();
        return $response->withHeader('Content-Type', 'application/json')->write(json_encode(['message' => 'User deleted successfully']));
    }
}

5. Define Routes

In your index.php file, define the routes for the API:

<?php

use Slim\Factory\AppFactory;
use App\Controller\UserController;

require __DIR__ . '/../vendor/autoload.php';
require __DIR__ . '/../config/database.php';

$app = AppFactory::create();

// Get all users
$app->get('/users', UserController::class . ':index');

// Update email by username
$app->post('/users/update/{username}', UserController::class . ':update');

// Delete user by username
$app->delete('/users/{username}', UserController::class . ':delete');

$app->run();

6. Run the Application with Workerman

To run the Slim application using Workerman, you can create a start.php script:

<?php

use Workerman\Worker;
use Workerman\Connection\TcpConnection;

require_once __DIR__ . '/vendor/autoload.php';

$worker = new Worker('http://0.0.0.0:8787');
$worker->count = 4;

$worker->onMessage = function(TcpConnection $connection, $data) {
    ob_start();
    require_once __DIR__ . '/index.php';
    $output = ob_get_clean();
    $connection->send($output);
};

Worker::runAll();

Start the Workerman server with the following command:

php start.php start

7. Test the API

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

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

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

This setup provides a basic REST API using Slim and Workerman PHP connected to PostgreSQL, which you can expand based on your project’s requirements.