Как узнать источник соединение в БД?
#best_practice #database

TL;DR Всегда ставьте application_name в конфигурации подключения к БД.

Сегодня обсудим малодокументированную фишку баз данных. При создание соединения можно указать application_name. Это аналог http заголовка User-Agent в мире баз данных. Он виден в списке соединений и slow query log. Приведу примеры кода для PostgreSQL.

Без ORM:
import { Client } from 'pg';
const client = new Client({
connectionString: process.env.DB_URL,
application_name: 'my-app@0.1.0'
});

TypeORM
import { PostgresConnectionOptions } from 'typeorm/driver/postgres/PostgresConnectionOptions';
const dbConfig: PostgresConnectionOptions = {
type: 'postgres',
url: process.env.DB_URL!,
extra: { application_name: 'my-app@0.1.0' },
entities: ...
};

Sequelize
import { Sequelize } from 'sequelize';
const sequelize = new Sequelize(process.env.DB_URL!, {
dialect: 'postgres',
dialectOptions: { appName: 'my-app@0.1.0' }
});


Теперь вы сможете увидеть application_name в pg_stat_activity с помощью запроса
SELECT * FROM pg_stat_activity WHERE datname = '<db_name>';

Аналогичные параметр есть у всех клиентов баз данных.
Например, MongoDB использует appName, а Redis – CLIENT SETNAME.

В чем проектировать структуру базы данных?
#service #database

Как известно, исправлять ошибки на этапе проектирование проще всего. Поэтому делайте дизайн структуры базы данных.

Вот подборка онлайн сервисов с удобной визуализацией и кодогенераций. Все они платные, но бесплатного тарифа хватает для фича дизайна.
➡️ SqlDBM – лучший функционал, активно развивается. Легко переключается режим просмотра.
➡️ drawsql.app – Очень простой и приятный интерфейс. Бесплатный план отлично подойдет для проекта до 15 таблиц.
➡️ dbdiagram.io – есть свой DSL, так называемый Database Markup Language. К сожалению, проект не развивается уже год.

Альтернативы, про которые не стоит забывать:
➡️ Дизайнить в SQL с последующей визуализацией в DataGrip или его аналоге.
➡️ Бумага и карандаш

Как посчитать нарастающий итог?
#database

Частая ошибка начинающих #nodejs разработчиков – лишняя нагрузка CPU. Один из методов разгрузить CPU это использование базы данных для расчетов. Большинство разработчиков используют агрегатные функции, но не оконные. Возможно, их отпугивает название, которое на английском звучит как OS от Microsoft – windows.

На картинке к рецепту вы видите различие работы агрегатных и оконных функций. В качестве примера, решим SQL задачу.

Дана схема:
CREATE TABLE users (
 id       SERIAL PRIMARY KEY,
 email varchar(100) NOT NULL
);
CREATE TABLE operations (
 id       SERIAL PRIMARY KEY,
 user_id int4 NOT NULL,
 amount int4 NOT NULL,
 created_at timestamp DEFAULT now(),
 FOREIGN KEY(user_id) REFERENCES users(id)
);
Вывести историю операций пользователя с балансом на момент окончания операции.

Решение будет иметь вид:
SELECT *, SUM(amount) OVER (PARTITION BY user_id order by created_at) as balance FROM operations;

Ссылки:
🔗 пример в db-fiddle,
🔗 документация по windows функциям.

Что не стоит делать в PostgreSQL?
#best_practice #database

Сегодня у нас в рекомендациях ссылка Don't Do This из википедии PostgreSQL. Каждый из пунктов выдержан в стиле что не стоит делать, почему и в каких случаях все же стоит нарушить эту рекомендацию. Отлично подходит, чтобы отправить коллеге который таки решил сделать что-нибудь плохое.

Що таке Parameterized query у PostgreSQL?
#database

Використання template string під час створення SQL запитів може стати причиною SQL ін'єкції. Нехай у нас є такий код:
client.query(`INSERT INTO users(name, email) VALUES('${name}', '${email}') RETURNING *`);
Наприклад, порівняйте результати його виконання з
const name = 'Homer';
const email = 'homer@simpson.com';
та
const name = 'Hacker';
const email = "hacker@simpson.com'); DELETE FROM users --";
Звичайно, валідація вхідних даних може запобігти ін'єкції SQL. Однак правильно не використовувати у запитах JS змінні, а параметри.
Виправлений код виглядає так:
client.query('INSERT INTO users(name, email) VALUES($1, $2) RETURNING *', [name, email]);
Усі query builders генерують такі запити, а DB клієнти вміють із ними працювати.

Що таке Exclusion Constraint у Postgresql?
#database

Exclusion Constraint це тип обмеження, який дозволяє вказати, що значення в стовпці або наборі стовпців не повинні перекриватися зі значеннями в іншому стовпці або наборі стовпців. Це корисно для дотримання бізнес-правил або забезпечення цілісності даних.

Наприклад, скажімо, у вас є таблиця, яка представляє календар, і ви хочете переконатися, що дві події не можуть бути заплановані одночасно. Ви можете додати до таблиці обмеження виключення, яке перевіряє час початку та завершення подій на наявність збігів.

CREATE EXTENSION IF NOT EXISTS "btree_gist";
CREATE TABLE calendar (
  id serial primary key,
  user_id integer not null,
  start_time timestamp without time zone,
  end_time timestamp without time zone,
  EXCLUDE USING gist (user_id with =, tsrange(start_time, end_time, '[)') with &&)
);

У цьому прикладі ми використовуємо ключове слово EXCLUDE для створення Exclusion Constraint, яке перевіряє наявність перекриття між стовпцями start_time, end_time та user_id. Вираз USING gist повідомляє PostgreSQL використовувати метод індексу Generalized Search Tree (GiST) для забезпечення обмеження. Для цього нам допоможе btree-gist.

Після створення таблиці ми можемо вставляти в неї рядки, але будь-яка спроба вставити рядок, який порушує обмеження виключення, призведе до помилки. Приклад:

INSERT INTO calendar (user_id, start_time, end_time)
VALUES (1, '2023-01-01 10:00:00', '2023-01-01 11:00:00');
INSERT INTO calendar (user_id, start_time, end_time)
VALUES (1, '2023-01-01 10:30:00', '2023-01-01 11:30:00');

Варто зазначити, що Exclusion Constraint як і всякий сonstraint сповільнює вставку.