Урок 05

Создание базы данных и подключение Laravel

Схема работы

Laravel → PDO → PostgreSQL

Laravel подключается к PostgreSQL через PDO-драйвер pgsql. Он должен быть установлен: php8.5-pgsql.

Проверка драйвера

php -m | grep pgsql
# pdo_pgsql
# pgsql

Если нет — установи:

sudo apt install php8.5-pgsql
sudo systemctl restart php8.5-fpm

Создание БД и пользователя

1. Зайди в psql:

sudo -u postgres psql

2. Создай пользователя:

CREATE USER skills_user WITH PASSWORD 'strong_password_here';

3. Создай БД:

CREATE DATABASE skills_db OWNER skills_user;

4. Дай права:

GRANT ALL PRIVILEGES ON DATABASE skills_db TO skills_user;
\c skills_db
GRANT ALL ON SCHEMA public TO skills_user;

5. Выход:

\q

Проверка подключения

psql -h 127.0.0.1 -U skills_user -d skills_db

Спросит пароль. Введи.

skills_db=>

Если пускает — подключение работает.

Проверь права:

CREATE TABLE test (id int);
DROP TABLE test;

Если оба прошли — у пользователя есть права на схему.

Настройка Laravel

1. Открой .env:

cd /var/www/skills/api
nano .env

2. Установи:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=skills_db
DB_USERNAME=skills_user
DB_PASSWORD=strong_password_here

3. Права на .env:

chmod 600 .env

Проверка подключения

1. Artisan:

php artisan db:show

Вывод:

PostgreSQL 14.10
Database: skills_db
Host: 127.0.0.1
Port: 5432

2. Или через tinker:

php artisan tinker

DB::connection()->getPdo();
// PDO instance

DB::select('SELECT version()');
// [['version' => 'PostgreSQL 14.10 ...']]

3. Или через Artisan migrate:

php artisan migrate

Если миграции прошли — БД работает.

Кодировка и collation

Проверь кодировку:

SHOW server_encoding;
SHOW client_encoding;

Должно быть UTF8.

Если нет — пересоздай БД:

DROP DATABASE skills_db;
CREATE DATABASE skills_db 
    OWNER skills_user
    ENCODING 'UTF8'
    LC_COLLATE 'ru_RU.UTF-8'
    LC_CTYPE 'ru_RU.UTF-8'
    TEMPLATE template0;

Locale зависит от системы. Проверь доступные:

locale -a | grep ru_RU

Схема public

PostgreSQL использует схемы для группировки таблиц. По умолчанию — public.

Проверка:

SHOW search_path;
# "$user", public

Дать права на схему:

\c skills_db
GRANT ALL ON SCHEMA public TO skills_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO skills_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO skills_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO skills_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO skills_user;

Что это даёт: пользователь может создавать таблицы, последовательности, читать/писать.

Миграции Laravel

Создать:

php artisan make:migration create_posts_table

Применить:

php artisan migrate

Проверить статус:

php artisan migrate:status

Откатить:

php artisan migrate:rollback
php artisan migrate:rollback --step=3

Пересоздать всё (⚠️ удалит данные):

php artisan migrate:fresh
php artisan migrate:fresh --seed

Сиды и фабрики

Сидер:

php artisan make:seeder PostSeeder

class PostSeeder extends Seeder
{
    public function run(): void
    {
        Post::factory()->count(50)->create();
    }
}

Запустить:

php artisan db:seed
php artisan db:seed --class=PostSeeder

Актуальные типы PostgreSQL в Laravel

В миграциях:

$table->id();                           // BIGSERIAL PRIMARY KEY
$table->string('title', 255);           // VARCHAR(255)
$table->text('content');                // TEXT
$table->integer('views')->default(0);   // INTEGER
$table->bigInteger('user_id');          // BIGINT
$table->boolean('published');           // BOOLEAN
$table->timestamp('published_at');      // TIMESTAMP
$table->jsonb('meta');                  // JSONB
$table->uuid('uuid');                   // UUID
$table->decimal('price', 10, 2);        // NUMERIC(10,2)
$table->enum('status', ['draft', 'pub']); // VARCHAR + CHECK
$table->foreignId('user_id')->constrained();

PostgreSQL-специфичные:

$table->ipAddress('ip');                // INET
$table->macAddress('mac');              // MACADDR
$table->jsonb('data');                  // JSONB (лучше JSON)
$table->uuid('id')->primary();

Работа с JSONB

Создать колонку:

$table->jsonb('settings')->nullable();

В модели:

protected $casts = [
    'settings' => 'array',
];

Использование:

$post->settings = ['color' => 'red', 'size' => 'L'];
$post->save();

$post->settings['color'];   // 'red'

Запросы:

Post::where('settings->color', 'red')->get();
Post::whereJsonContains('settings->tags', 'laravel')->get();

Индексы на JSONB:

DB::statement('CREATE INDEX idx_settings_color ON posts ((settings->>\'color\'));');

UUID вместо auto-increment

Зачем: не палит количество записей, безопаснее, удобно для распределённых систем.

Миграция:

$table->uuid('id')->primary();

Модель:

use Illuminate\Database\Eloquent\Concerns\HasUuids;

class Post extends Model
{
    use HasUuids;
}

Включить расширение:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Таймзоны

Проверить:

SHOW timezone;

Установить UTC:

SET timezone TO 'UTC';

В postgresql.conf:

timezone = 'UTC'

В Laravel:

APP_TIMEZONE=UTC

Правило: храни в БД UTC, показывай в локальной TZ.

Оптимизация: prepared statements

Laravel использует prepared statements для всех запросов — это защищает от SQL-инъекций.

PostgreSQL может кешировать план prepared statements:

# postgresql.conf
plan_cache_mode = auto

Пул соединений PgBouncer

Проблема: PHP-FPM создаёт новое соединение на каждый запрос. Для PostgreSQL это дорого.

Решение: PgBouncer — пул соединений. 25 PHP-процессов → 5 соединений к БД.

Установка:

sudo apt install pgbouncer

Конфиг /etc/pgbouncer/pgbouncer.ini:

[databases]
skills_db = host=127.0.0.1 port=5432 dbname=skills_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

userlist.txt:

"skills_user" "password_hash"

Хеш пароля:

SELECT 'md5' || md5('password' || 'skills_user');

В Laravel:

DB_HOST=127.0.0.1
DB_PORT=6432

Эффект: PostgreSQL видит 20 соединений вместо 200.

Проверка соединений

Сколько открыто:

SELECT count(*) FROM pg_stat_activity WHERE datname = 'skills_db';

Кто и что делает:

SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE datname = 'skills_db';

Убить зависший запрос:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE pid = 12345;

Частые ошибки

1. SQLSTATE[08006] Connection refused

PostgreSQL не запущен или неправильный хост/порт.

2. SQLSTATE[28P01] password authentication failed

Неверный пароль в .env.

3. SQLSTATE[3D000] database does not exist

БД не создана или неправильное имя.

4. SQLSTATE[42501] permission denied for schema public

Не даны права на схему (см. выше).

5. too many clients

Исчерпан max_connections. Решение — PgBouncer.

Итог

Порядок:

Создать пользователя и БД в psql
Дать права на схему public
Настроить .env в Laravel
Проверить через php artisan migrate

Для прода — добавь PgBouncer.

Дальше — бэкапы и восстановление.