PHP

This guide will walk you through the process of connecting to a PostgreSQL database using PHP within your Sealos Devbox project.

Prerequisites

A Sealos Devbox project with PHP environment
A PostgreSQL database created using the Database app in Sealos

Install Required Extensions

In your Cursor terminal, ensure that the PostgreSQL extension for PHP is installed:

sudo apt-get update
sudo apt-get install php-pgsql

Connection Setup

Create a Configuration File

First, let's create a configuration file to store our database connection parameters. Create a file named config.php in your project directory with the following content:

config.php

<?php
return [
    'host' => 'your_database_host',
    'port' => '5432',
    'dbname' => 'your_database_name',
    'user' => 'your_username',
    'password' => 'your_password'
];

Replace the placeholders with your actual PostgreSQL credentials from the Database app in Sealos.

Create a Database Connection Function

Next, let's create a PHP file that will handle the database connection. Create a file named db_connect.php with the following content:

db_connect.php

<?php
function getDbConnection() {
    $config = include 'config.php';
    
    $dsn = "pgsql:host={$config['host']};port={$config['port']};dbname={$config['dbname']};";
    
    try {
        $pdo = new PDO($dsn, $config['user'], $config['password'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
        echo "Connected successfully to the database.\n";
        return $pdo;
    } catch (PDOException $e) {
        die("Connection failed: " . $e->getMessage());
    }
}

This function reads the configuration from config.php and establishes a connection to the PostgreSQL database using PDO (PHP Data Objects).

Create a Test Script

Now, let's create a test script to verify our connection and perform some basic database operations. Create a file named test_db.php with the following content:

test_db.php

<?php
require_once 'db_connect.php';
 
$pdo = getDbConnection();
 
// Create a table
$pdo->exec("CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100) NOT NULL
)");
echo "Table created successfully.\n";
 
// Insert a record
$stmt = $pdo->prepare("INSERT INTO employees (name, position) VALUES (?, ?)");
$stmt->execute(['John Doe', 'Developer']);
echo "Record inserted successfully.\n";
 
// Query the table
$stmt = $pdo->query("SELECT * FROM employees");
echo "Employees:\n";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: {$row['id']}, Name: {$row['name']}, Position: {$row['position']}\n";
}
 
// Close the connection
$pdo = null;

This script demonstrates creating a table, inserting a record, and querying the table.

Usage

To run the test script, use the following command in your Cursor terminal:

php test_db.php

This will execute the script, demonstrating the connection to the database, table creation, data insertion, and querying.

Best Practices

Use environment variables or a separate configuration file for database credentials.
Always use prepared statements to prevent SQL injection.
Handle potential errors using try-catch blocks.
Close the database connection after operations are complete.
Use connection pooling for better performance in production environments.

Troubleshooting

If you encounter connection issues:

Verify your database credentials in the config.php file.
Ensure your PostgreSQL database is running and accessible.
Check for any network restrictions in your Devbox environment.
Confirm that the php-pgsql extension is correctly installed.

For more detailed information on using PostgreSQL with PHP, refer to the official PHP PostgreSQL documentation.

On this page