• Каждое приложение использует БД
• Отсутствуют готовые решения
• Востребовано на рынке

База данных

Организованная структура,
которая умеет
хранить, обрабатывать
и изменять информацию
в больших объемах.

Зачем нужны БД?

Когда нужно использовать БД

• Хранение больших объемов данных
• Многопользовательский доступ
• Высокая скорость обработки

Модели данных

• Иерархическая
• Сетевая
• Реляционная
• ...

Реляционная модель

Все что нужно знать о реляционных БД — они хранят информацию в таблицах, и основная работа крутится вокруг отношений этих таблиц друг к другу

БД vs СУБД

PostgreSQL

PostgreSQL

Интерактив

SQL

Что такое SQL

• Язык структурированных запросов
• Стандартное средство общения с реляционной СУБД
• Посылаем запрос - получаем ответ.
  Всё просто. Казалось бы...

Основные операторы SQL

DDL - data defition language

-- создание/удаление базы данных
CREATE/DROP DATABASE database;

-- создание/изменение/удаление таблицы
CREATE/ALTER/DROP TABLE table;
        

Основные операторы SQL

DML - data manipulation language

-- выборка данных из таблицы
SELECT * FROM table ...;

-- вставка данных в таблицу
INSERT INTO table ... VALUES ...;

-- обновление данных в таблице
UPDATE table SET field=value ...;

-- удаление данных из таблицы
DELETE FROM table WHERE ...;
        

Основные операторы SQL

DCL - data control language

Управление доступом к данным
выходит за рамки лекции.

Проектируем Инстаграм

Какие объекты будем хранить?

• Публикации: фотографии и видео
• Профили пользователей
• Подписчики/подписки
• Комментарии и ответы
• Просмотры и лайки

Создаем новую базу данных

CREATE DATABASE instagram;
        

Профили пользователей

CREATE TABLE profiles
(
    id serial PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    username   VARCHAR(255) UNIQUE,
    fullname   VARCHAR(255) NOT NULL,
    bio        TEXT,
    avatar     vARCHAR(255),
    created    TIMESTAMP WITH TIME ZONE
        DEFAULT NOW() NOT NULL,
    updated    TIMESTAMP WITH TIME ZONE,
    is_blocked BOOLEAN DEFAULT false
);
                

Первичные ключи

PRIMARY KEY

• уникальность
• может быть составным
• создается индекс
• not null

Уникальные поля

UNIQUE

Публикации: посты

CREATE TABLE publications
(
    id serial PRIMARY KEY,
    author_id INTEGER NOT NULL
        REFERENCES profiles (id)
        ON DELETE CASCASE,
    published TIMESTAMP WITH TIME ZONE,
    location VARCHAR(255)
);
                

Внешние ключи

FOREIGN KEY

• связи между таблицами
• обеспечение целостности
• каскадное удаление

Публикации: медиа

CREATE TABLE media
(
    id SERIAL PRIMARY KEY,
    publication_id INTEGER NOT NULL
        REFERENCES publications(id)
        ON DELETE CASCADE,
    type VARCHAR(12) NOT NULL,
    file VARCHAR(255) NOT NULL,
    ordering SMALLINT DEFAULT 0 NOT NULL,
    created TIMESTAMP WITH TIME ZONE
        DEFAULT NOW() NOT NULL
);
                

Публикации: пост + медиа

Подписчики / подписки

CREATE TABLE followers
(
    id SERIAL PRIMARY KEY,
    profile_id INTEGER NOT NULL
        REFERENCES profiles(id)
        ON DELETE CASCADE,
    follower_id INTEGER NOT NULL
        REFERENCES profiles(id)
        ON DELETE CASCADE,
    created TIMESTAMP WITH TIME ZONE
        DEFAULT NOW() NOT NULL,

    UNIQUE(profile_id, follower_id)
);
                

Подписчики / подписки

Комментарии / ответы

CREATE TABLE comments
(
    id SERIAL PRIMARY KEY,
    publication_id INTEGER NOT NULL
        REFERENCES publications(id)
        ON DELETE CASCADE,
    reply_id INTEGER
        REFERENCES comments(id)
        ON DELETE CASCADE,
    author_id INTEGER
        REFERENCES profiles(id)
        ON DELETE SET NULL,
    message VARCHAR(255) NOT NULL,
    created TIMESTAMP WITH TIME ZONE
        DEFAULT NOW() NOT NULL
);
                

Комментарии / ответы

Лайки

CREATE TABLE likes
(
    id SERIAL PRIMARY KEY,
    object_type VARCHAR(12) NOT NULL,
    object_id INTEGER NOT NULL,
    author_id INTEGER NOT NULL
        REFERENCES profiles(id)
        ON DELETE CASCADE,
    created TIMESTAMP WITH TIME ZONE
        DEFAULT NOW() NOT NULL,

    UNIQUE (object_type, object_id, author_id)
);
                

Универсальность vs Целостность

Общая схема данных

Типы данных

PostgreSQL

Строки

Числа. Целые

Числа. С произвольной точностью

1 ⇐ p ⇐ 1000, 0 ⇐ s ⇐ p

Числа. С плавающей точкой

Последовательности

Последовательности

Auto-increment

CREATE TABLE users (
  id SERIAL
);
        

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
  id integer NOT NULL DEFAULT nextval('users_id_seq')
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;
            

Логический тип. BOOLEAN

Даты

Даты. Примеры

Другие типы

Массивы

• integer[]
• integer ARRAY
• integer[][]
• text[][]

Массивы

'{ значение1 разделитель значение2 разделитель ... }'
        

integer[][] -> '{{1,2,3},{4,5,6},{7,8,9}}'
        

Тип JSON

{"tags": [
    "consequat",
    "amet",
    "fugiat",
    "nulla"
],
"friends": [
    {
        "id": 0,
        "name": "Letha Gill"
    },
    {
        "id": 1,
        "name": "Yvette Hudson"
    }
]}
        

одна строка

INSERT INTO table
    (id, name, created)
VALUES
    (1, 'John', '2020-01-01 12:00:00');
        

несколько строк сразу

INSERT INTO table
    (id, name, created)
VALUES
    (1, 'John', '2020-02-01 08:00:00'),
    (2, 'Mark', '2020-02-01 10:00:00');
        

Auto-increment или Default

 CREATE TABLE ... (
     id SERIAL,
     ...,
     created TIMESTAMP WITH TIME ZONE
        DEFAULT NOW() NOT NULL
 )
        

INSERT INTO table
    (id, name, created)
VALUES
    (1, 'John', '2020-02-01 08:00:00');
        

UPDATE table
SET name = 'Thing', value = 1
WHERE id = 1;
        

текущее значение поля

UPDATE table
SET value = value + 5
WHERE id = 2;
        

Общая форма запроса:

SELECT поля FROM таблица
WHERE условия
GROUP BY поля для группировки
ORDER BY поля для сортировки
LIMIT лимит строк;
        

Поля в запросе:

SELECT * FROM table;
        

SELECT id, name, created FROM table;
        

SELECT id, name AS fullname, created
FROM table AS catalog;
        

Вычисляемые поля:

SELECT value/2 AS half_value FROM table;
        

Вычисляемые функции:

            -- подсчет строк в таблице
            SELECT COUNT(*) FROM table;
        

            -- среднее арифметическое
            SELECT AVG(value) AS avg FROM table GROUP BY value;
        

            -- округление минимального значения
            SELECT ROUND(MIN(value) - 1) FROM table GROUP BY value;
        

Фильтрация WHERE:

SELECT id, name, type FROM table
WHERE name = 'my' AND (type = 'one' OR type = 'two')
        

Сравнение: <>,>,<,>=,<=,=

Операторы: NOT, AND, OR

Поиск LIKE / NOT LIKE:

SELECT ... WHERE description LIKE 'Once%';
        

SELECT ... WHERE description LIKE '%at the end';
-- работает медленно, индексы не работают!
        

BETWEEN

SELECT title, created FROM table
WHERE created BETWEEN '2020-01-01' AND '2020-01-20';
        

Группировка GROUP BY

SELECT type FROM table GROUP BY type;

SELECT type, MIN(created) FROM table GROUP BY type;
SELECT ... GROUP BY name, type;
        

Фильтрация просле группировки - HAVING

SELECT type, MIN(value) AS min_value FROM table
GROUP BY type HAVING min_value > 100;
        

Сортировка ORDER BY

SELECT id, name, created FROM table ORDER by name;

SELECT … ORDER BY name DESC;
SELECT … ORDER BY name, created;
        

Органичение строк LIMIT/OFFSET

SELECT * FROM table LIMIT 1;
SELECT id, name FROM table LIMIT 1 OFFSET 2;
        

Вложенные запросы

Вложенные запросы

SELECT … FROM (
    SELECT * FROM table
) AS table1
        

SELECT … FROM table WHERE id = (
   SELECT * FROM table ...
)
        

Все публикации одного автора

        SELECT * FROM publications WHERE author_id = 1;
        

Кол-во картинок в публикациях

        SELECT id, publication_id, COUNT(*)
        FROM media
        WHERE type = 'photo'
        GROUP BY publication_id;
        

Вывести все сообщения
для одной публикации без ответов

        SELECT * FROM comments
        WHERE publication_id = 1 AND reply_id IS NULL;
        

JOIN

когда информации из одной таблицы недостаточно

Виды JOIN

INNER JOIN

            SELECT t1.id, t1.author_id, t2.name AS author_name
            FROM table1 AS t1
            INNER JOIN table2 AS t2 ON t2.id=t1.author_id;
        

LEFT/RIGHT JOIN

            SELECT t1.id, t1.author_id, t2.name AS author_name
            FROM table1 AS t1
            LEFT JOIN table2 AS t2 ON t2.id=t1.author_id;
        

Список публикаций авторов за период времени

            SELECT p.id, p.published, u.username
            FROM publications AS p
            INNER JOIN profiles AS u ON u.id = p.author_id
            WHERE published BETWEEN '2020-01-01' AND '2020-01-31'
            ORDER BY published;
        

Список авторов с кол-вом публикаций

SELECT u.id, u.username, COUNT(p.id) as count
FROM profiles AS u
LEFT JOIN publications p on u.id = p.author_id
GROUP BY u.id
ORDER BY count;
        

Авторы с максимальным кол-во лайков

SELECT p.author_id, u.username, SUM(ll.like_cnt) AS max_likes
FROM publications AS p
INNER JOIN (
    SELECT l.object_id as publication_id, COUNT(l.id) as like_cnt
    FROM likes AS l
    WHERE l.object_type = 'publication'
    GROUP BY l.object_id
) AS ll ON ll.publication_id = p.id
INNER JOIN profiles u on p.author_id = u.id
GROUP BY p.author_id, u.username
ORDER BY max_likes DESC;
        

Индексы

повышение производительности выполнения запросов

Что такое индекс?

Объект БД, который устанавливает соответствие между ключом и строками таблицы, в которых этот ключ встречается.

Поиск данных

SELECT COUNT(*) FROM table; -- 100500
        

SELECT COUNT(*) FROM table WHERE name = 'слон'; -- 7
        

Создание индекса

CREATE INDEX table_name_idx ON table (name);
        

Уникальный индекс

CREATE UNIQUE INDEX name_idx ON table (name);
        

Составной индекс

SELECT * FROM table
WHERE name = 'Books' AND owner_id = 1;
        

CREATE INDEX name_owner_id_idx
ON notes (name, owner_id);
        

Плюсы индексов

• высокая скорость поиска
• консистентность данных засчет уникальных индексов

Минусы индексов

• расходуют память
• замедление операций вставки и обновления

Транзакции

• Совокупность операций, которые образуют логически целостную процедуру
• Будут выполнены все вместе, либо не будет выполнена ни одна из них
• Переводит базу данных из одного согласованного состояния в другое согласованное состояние

Пример транзакции

BEGIN;
    

UPDATE users SET account = account - 10000
WHERE id = 3;
    

UPDATE users SET account = account + 10000
WHERE id = 4;
    

COMMIT;
    

NoSQL vs SQL

NoSQL

• Большие объемы данных
• Гибкая модель данных
• Простота

SQL. PostgreSQL

• Логическая структура
• Целостность данных
• Транзакции

ORM

Установка

npm install sequelize
        

npm install pg
        

const Sequelize = require('sequelize');
        

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

const sequelize = new Sequelize('db','user','pass', {
  host: 'localhost',
  dialect: 'postgres' // 'mariadb','sqlite',
                      // 'mysql','mssql'
});
        

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

const uri = 'postgresql://user:pass@localhost:5432/dbname';
        

const sequelize = new Sequelize(uri, options);
        

Типы данных

Строковые

Числовые

Даты

Массивы

Другие

Модели

Модели. Объявление

sequelize.define('name', {attributes}, {options});
    

Модели. Атрибуты

const Note = sequelize.define('note', {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    name: {...},
    text: {...},
    ownerId: {...}
}, {...});
        

Именование

const Note = sequelize.define('note', {
    id: {...},
    name: {...},
    text: {...},
    ownerId: {
        type: Sequelize.INTEGER,
        field: 'owner_id'
    }
}, {...});
        

Модели. Getters & setters

const Note = sequelize.define('note', {
  text: {
    set(value) {
      var text = value.replace(/\*(\w+)\*/g, '$1');
      this.setDataValue('text', text);
    }
  }
}, {...});
        

'Films to *watch*' -> 'Films to watch'
        

Модели. Валидаторы

const Note = sequelize.define('note', {
  id: {...},
  name: {
    type: Sequelize.STRING,
    validate: {
      is: /^[a-z]+$/i
    }
  }
}, {...});
        

Модели. Конфигурация

const Note = sequelize.define('note', {...}, {
    timestamps: true, // add 'created_at', 'updated_at',
                      //     'deleted_at'
    underscored: true,
    paranoid: true,
    tableName: 'notes'
});
        

Создание таблицы

Note.sync({ force: true });
    

Note.sync()
  .then(function () {
    // :)
  })
  .catch(function (err) {
    // :(
  });
        

Удаление таблицы

Note.drop()
  .then(function () {})
  .catch(function (err) {});
        

CRUD

Create

Note.create({
  name: 'Books',
  text: 'Books to read'
});
        

Read

Note.findOne({
  where: {
    name: 'Films'
  }
}).then(function (noteModel) {
  return noteModel.get('text'); // 'Films to watch'
});
        

Read. Projection

Note.findOne({
  where: { name: 'Films' },
  attributes: ['id', 'text']
});
        

Read. Операторы

Note.findOne({
  where: {
    name: {
      $like: '%s'
    },
    created_at: {
      $gt: '2017-04-09 13:25:13'
    }
  }
});
        

Read. Поиск по id

Note.findOne({
  where: { id: 23 }
});
        

Note.findById(23);
        

Read. Все записи

Note.findAll({
  where: {
    ownerId: 1
  }
});
        

Read. Sort, skip, limit

Note.findAll({
  order: [
    ['name', 'DESC']
  ],
  offset: 2,
  limit: 2
});
        

Update

Note.update(
  {
      text: 'My favorite book'
  },
  {
      where: { name: 'Books' }
  }
);
        

Delete

Note.destroy({
    where: { id: 23 }
});
        

Комбинации. findOrCreate

Note.findOrCreate({
  where: { name: 'Books' },
  defaults: {
    name: 'Books',
    text: 'Books to read',
    ownerId: 1
  }
});
        

JOIN

Внешние связи. belongsTo

Note.belongsTo(User);
        

Внешние связи. belongsTo

Note.belongsTo(User, { as: 'owner' });
        

Внешние связи. foreignKey

Note.belongsTo(User, {
  foreignKey: 'owner_id',
  targetKey: 'id'
});
        

Внешние связи. hasMany

User.hasMany(Note, {
    foreignKey: 'user_id',
    targetKey: 'id'
});
        

Внешние связи. hasOne

Note.hasOne(Category, { as: 'best_note' });
        

Внешние связи

Include

User.findAll({
  attributes: ['name'],
  include: [
     {
       model: Note,
       attributes: ['name']
     }
  ]
});
            

[
  {
    name: 'Олег',
    notes: [
      { name: 'Films' },
      { name: 'Books' }
    ]
  },
  {
    name: 'Сергей',
    notes: [
      { name: 'Music' }
    ]
  },
  {
    name: 'Михаил',
    notes: []
  }
]
        

Include. Where

User.findAll({
  attributes: ['name'],
  include: [
     {
       model: Note,
       where: {
         name: {
           $not: null
         }
       },
       attributes: ['name']
     }
  ]
});
            

[
  {
    name: 'Олег',
    notes: [
      { name: 'Films' },
      { name: 'Books' }
    ]
  },
  {
    name: 'Сергей',
    notes: [
      { name: 'Music' }
    ]
  }
]
        

Include. Where + required

User.findAll({
  attributes: ['name'],
  include: [
     {
       model: Note,
       where: {
         name: {
           $not: null
         }
       },
       attributes: ['name'],
       required: false
     }
  ]
});
            

[
  {
    name: 'Олег',
    notes: [
      { name: 'Films' },
      { name: 'Books' }
    ]
  },
  {
    name: 'Сергей',
    notes: [
      { name: 'Music' }
    ]
  },
  {
    name: 'Михаил',
    notes: []
  }
]
        

Include. Multiple join

User.findAll({
  include: [
    {
      model: Note,
      include: [
        {
          model: Category
        }
      ]
    }
  ]
});
        

Индексы

Создание

sequelize.define('note', {...}, {
  indexes: [
    {
      name: 'name_idx'
      fields: ['name']
    }
  ]
});
        

Уникальный индекс

sequelize.define('note', {...}, {
  indexes: [
    {
      name: 'unique_name_idx'
      fields: ['name']
      unique: true
    }
  ]
});
        

Составной индекс

sequelize.define('note', {...}, {
  indexes: [
    {
      fields: ['name', 'ownerId']
    }
  ]
});
        

Транзакции

sequelize.transaction(function (t) {
    return User.increment(
        'account',
        { by: -10000, where: { id: 1 }, transaction: t }
    )
    .then(() => User.increment(
        'account',
        { by: 10000, where: { id: 2 }, transaction: t })
    );
});