Подключение PostgreSQL

Внимание

Данная статья документации написана нейросетью. Возможны некоторые расхождения и неточность информации!

Руководство по подключению к PostgreSQL в IO7 фреймворке.

Конфигурация

Параметры подключения

Параметр	Значение по умолчанию	Обязателен	Описание
`driver`	`pgsql`	Да	Драйвер БД
`host`	—	Да	Адрес сервера
`port`	`5432`	Нет	Порт сервера
`database`	—	Да	Имя базы данных
`username`	—	Да	Пользователь
`password`	—	Да	Пароль
`charset`	`utf8`	Нет	Кодировка
`prefix`	`''`	Нет	Префикс таблиц
`schema`	`public`	Нет	Схема PostgreSQL
`sslmode`	`prefer`	Нет	Режим SSL

Подключение через SqlConnection

Простое подключение

use IO\Database\SqlConnection;

$db = new SqlConnection('pgsql');
$db->server = 'localhost';
$db->user = 'postgres';
$db->pass = 'password';
$db->db = 'mydb';

if ($db->tryconnect()) {
    echo "Подключено к PostgreSQL!";
}

Подключение через connect()

$db = new SqlConnection('pgsql');
$db->connect('192.168.1.10', 'app_user', 'secret', 'production');

Через create_conn()

$db = SqlConnection::create_conn(
    'localhost',
    'postgres',
    'password',
    'mydb',
    'pgsql'
);

Автоматические настройки после подключения

При подключении к PostgreSQL автоматически выполняются:

SET client_encoding TO 'utf8';
SET timezone TO '+05:00';

Подключение через DB фасад

Базовая инициализация

use IO\Database\DB;

DB::init([
    'driver'    => 'pgsql',
    'host'      => 'localhost',
    'port'      => 5432,
    'database'  => 'mydb',
    'username'  => 'postgres',
    'password'  => 'password',
    'charset'   => 'utf8',
    'prefix'    => '',
    'schema'    => 'public',
    'sslmode'   => 'prefer',
    'options'   => [
        \PDO::ATTR_PERSISTENT => false,
        \PDO::ATTR_TIMEOUT    => 5,
        \PDO::ATTR_ERRMODE    => \PDO::ERRMODE_EXCEPTION,
    ],
]);

Через существующий PDO

$pdo = new PDO(
    'pgsql:host=localhost;port=5432;dbname=mydb',
    'postgres',
    'password'
);

DB::initPdo($pdo, 'pgsql');

Подключение через ConnectionManager

Автоматическое определение

use IO\Database\ConnectionManager;
use IO\Database\DB;

// Установите DB_ENGINE=pgsql в .env
$connections = ConnectionManager::fromConstants();

foreach ($connections as $name => $config) {
    DB::init($config, $name);
}

Ручное создание конфигурации

$config = ConnectionManager::pgsqlConfig(
    '192.168.1.10',
    'mydb',
    'app_user',
    'secret',
    5432
);

DB::init($config, 'main');

Переменные окружения

Файл .env:

# Движок БД
DB_ENGINE=pgsql

# PostgreSQL
PGSQL_HOST=localhost
PGSQL_PORT=5432
PGSQL_USER=postgres
PGSQL_PASSWORD=secret
PGSQL_DB=mydb

Отличия от MySQL

Особенность	MySQL	PostgreSQL
Порт по умолчанию	3306	5432
Кодировка	`utf8mb4`	`utf8`
Экранирование имён	`table`	`"table"`
Auto-increment	`AUTO_INCREMENT`	`SERIAL` / `GENERATED`
UPSERT	`ON DUPLICATE KEY UPDATE`	`ON CONFLICT DO UPDATE`
INSERT IGNORE	`INSERT IGNORE`	`ON CONFLICT DO NOTHING`
Switch database	`USE db_name`	Не поддерживается
Функции	`FOUND_ROWS()`, `ROW_COUNT()`	Нет аналогов
Regex	`RLIKE`	`~`

Особенности в IO7

usedb() — в PostgreSQL не работает ( silently returns )
insert_or_update() — использует ON CONFLICT DO UPDATE
insert_or_ignore() — использует ON CONFLICT DO NOTHING
escape2() — экранирует имена через двойные кавычки вместо обратных

Примеры

CRUD операции

$db = new SqlConnection('pgsql');
$db->connect('localhost', 'postgres', 'password', 'mydb');

// CREATE
$db->insert('users', [
    'name'  => 'Иван',
    'email' => 'ivan@example.com',
]);

// READ
$r = $db->query("SELECT * FROM users WHERE email = $1", ['ivan@example.com']);
$user = $db->fetch($r);
$db->free_result($r);

PostgreSQL-специфичные запросы

// INSERT ... RETURNING
$r = $db->query(
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
    ['Мария', 'maria@example.com']
);
$row = $db->fetch($r);
$newId = $row['id'];

// CTE (Common Table Expression)
$r = $db->query("
    WITH active_users AS (
        SELECT * FROM users WHERE active = true
    )
    SELECT * FROM active_users WHERE role = 'admin'
");

// JSON операторы
$r = $db->query("SELECT data->>'name' as name FROM documents WHERE data @> ?", [
    json_encode(['type' => 'report'])
]);

// ARRAY
$r = $db->query("SELECT * FROM users WHERE $1 = ANY(tags)", ['vip']);

UPSERT (Insert or Update)

// PostgreSQL: ON CONFLICT DO UPDATE
$db->insert_or_update('user_stats', [
    'user_id'    => 1,
    'total_logins' => 1,
]);

// Генерируется:
// INSERT INTO "user_stats" ("user_id", "total_logins") VALUES ($1, $2)
// ON CONFLICT DO UPDATE SET "user_id" = EXCLUDED."user_id", "total_logins" = EXCLUDED."total_logins"

Query Builder

$users = SqlConnection::table('users')
    ->where('active', true)
    ->where('age', '>=', 18)
    ->orderBy('name')
    ->limit(25)
    ->get();

// PostgreSQL-специфичные операторы через raw
$users = SqlConnection::table('users')
    ->whereRaw("data->>'role' = ?", ['admin'])
    ->get();

Транзакции

$db->beginTransaction();
try {
    $db->insert('accounts', ['user_id' => 1, 'balance' => 1000]);
    $db->insert('transactions', ['account_id' => 1, 'amount' => 100]);
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

FAQ

Как подключиться к PostgreSQL с SSL?

$config = ConnectionManager::pgsqlConfig('localhost', 'mydb', 'postgres', 'secret');
$config['sslmode'] = 'require';
$config['options'][\PDO::PGSQL_ATTR_DISABLE_PREPARES] = false;
DB::init($config);

Как использовать prepared statements?

PostgreSQL нативно поддерживает подготовленные выражения:

$r = $db->query("SELECT * FROM users WHERE id = $1 AND status = $2", [1, 'active']);

Как переключить схему?

$db->query("SET search_path TO my_schema, public");

Как использовать JSONB?

// Запись JSONB
$db->insert('documents', [
    'data' => json_encode(['key' => 'value']),
]);

// Запрос по JSONB полю
$r = $db->query("SELECT * FROM documents WHERE data->>'key' = $1", ['value']);

Как узнать версию PostgreSQL?

$version = $db->get_var('version', "SELECT version() as version");
echo "Версия PostgreSQL: {$version}";