SQL¶

Для работы Node.js с базами данных SQL используется модуль sequelize.

1

npm install sequelize --save

Node.js sequelize поддерживает следующие СУБД: MySQL, PostgreSQL, MSSQL и MariaDB.

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

Рассмотрим подключение к базе данных PostgreSQL.

connection.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

const Sequelize = require('sequelize');

const sequelize = new Sequelize(
    'db_name',
    'user',
    'password',
    {
        dialect: 'postgres',
    }
);

sequelize
    .authenticate()
    .then(() => console.log('Connected.'))
    .catch((err) =>
        console.error('Connection error: ', err)
    );

Во всех последующих примерах будет подразумеваться, что переменная sequelize хранит соединение с БД.

За подключение отвечает класс Sequelize, при создании экземпляра которого задаются следующие параметры:

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

Через объект конфигурации можно задать множество параметров, вот лишь некоторые из них:

• host - хост сервера БД (по умолчанию localhost);
• port - порт сервера БД (по умолчанию порт по умолчанию выбранной СУБД);
• dialect - тип используемой СУБД (mariadb, mysql, mssql, postgres);
• pool - настройка пула соединений;
• scheme - используемая схема (по умолчанию null).

Пример настройки пула соединений.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

const sequelize = new Sequelize(
    'db_name',
    'user',
    'password',
    {
        dialect: 'postgres',
        pool: {
            max: 10, //максимальное кол-во соединений в пуле (Default: 5)
            min: 0, //минимальное кол-во соединений в пуле (Default: 0)
            acquire: 30000, //время в миллисекундах, в течение которого будет осуществляться попытка установить соединение, прежде чем будет сгенерировано исключение (Default: 60000)
            idle: 10000, //время простоя в миллисекундах, по истечении которого соединение покинет пул (Default: 1000)
        },
    }
);

С полным перечнем задаваемых опций можно ознакомиться в документации Node.js sequelize.

Для проверки установки соединения в примере вызывается метод authenticate(), который возвращает объект Promise.

1
2
3
4
5
6

sequelize
    .close()
    .then(() => console.log('Closed.'))
    .catch((err) =>
        console.error('Close connection error: ', err)
    );

Модели¶

Модели используются для описания структуры таблицы. Одна модель описывает одну таблицу.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

const Sequelize = require('sequelize');

class Book extends Sequelize.Model {}

Book.init(
    {
        id: {
            type: Sequelize.NUMBER,
            primaryKey: true,
        },
        title: {
            type: Sequelize.STRING,
            allowNull: false,
            comment: "Book 's title",
        },
        author: {
            type: Sequelize.STRING,
            field: '_author',
        },
        description: {
            type: Sequelize.TEXT,
        },
        publishDate: {
            type: Sequelize.DATE,
            defaultValue: Sequelize.NOW,
        },
    },
    { sequelize, modelName: 'book' }
);

Модель создается с помощью метода init() класса, который является дочерним по отношению к Sequelize.Model. Метод init() принимает два объекта:

• объект с описанием полей таблицы;
• конфигурация создаваемой модели и соответствующей ей таблицы.

Описание поля задается объектом со следующими свойствами:

• type - тип поля;
• defaultValue - значение поля по умолчанию;
• primaryKey - булевое значение, если true, то поле является первичным ключом (по умолчанию false);
• autoIncrement - булевое значение, если true, то при добавлении новой записи значение поля будет значение предыдущей записи этого поля плюс единица (по умолчанию false);
• allowNull - булевое значение, если false, запрещает создавать новую запись с этим пустым полем;
• unique - булевое значение, если true, то значение указанное для этого поля в записи должно быть уникальным в пределах таблицы (по умолчанию false);
• comment - комментарий к полю;
• field - если указано, то в качестве названия поле будет использоваться именно это значение, а не ключ;
• validate - объект с заданием для поля валидаторов, с полным списком можно ознакомиться в документации;
• get() - функция, которая модифицирует значение поля при чтении записи;
• set() - функция, преобразующая передаваемое значение при сохранении записи.

get.js

1
2
3
4
5
6
7

title: {
    type: Sequelize.STRING,
    allowNull: false,
    get(){
        return `${this.getDataValue('title')}, ${this.getDataValue('author')}`
    }
}

Реализация аналогичного функционала с использованием set().

set.js

1
2
3
4
5
6
7

title: {
    type: Sequelize.STRING,
    allowNull: false,
    set(value){
        this.setDataValue('title', `${this.getDataValue('author')}, ${this.getDataValue('author')}`);
    }
}

Конфигурация модели (второй параметр, передаваемый init()) описывается следующим объектом:

• modelName - имя модели;
• timestamps - булевое значение, если true, то к таблице автоматически будут добавлены поля createdAt (дата и время создания записи) и updatedAt (дата и время обновления записи);
• paranoid - булевое значение, если true, то вместо фактического удаления записи добавит поле deletedAt с датой и временем выполнения запроса на удаление (работает совместно с {timestamps: true});
• underscored - булевое значение, если true, то названия полей будут переименованы с использованием символа нижнего подчеркивания, например, если поле названо authorName, то в таблице оно фактически будет называться author_name (параметр не распространяется на значение поля field, указанное при описании поля таблицы);
• freezeTableName - булевое значение, если true, то название таблицы будет таким, как указано в tableName (по умолчанию названия всех таблиц преобразуются в множественное число);
• tableName - название таблицы;
• hooks - определение триггеров (рассмотрены отдельно далее);
• indexes - определение индексов (рассмотрены отдельно далее);
• sequelize - экземпляр объекта активного соединения с БД.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

const Sequelize = require('sequelize');

const sequelize = new Sequelize(
    'db_name',
    'user',
    'password',
    {
        dialect: 'postgres',
    }
);

sequelize.sync();

Создание связей¶

Создание связей между таблицами осуществляется с использованием моделей. Рассмотрим установление следующих типов связей:

• один к одному;
• один ко многим;
• многие ко многим.

Пример установки связи один к одному.

one-to-one.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

class Car extends Sequelize.Model {}
class Driver extends Sequelize.Model {}

Car.init(
    {
        id: {
            type: Sequelize.NUMBER,
            primaryKey: true,
        },
        model: {
            type: Sequelize.STRING,
            allowNull: false,
        },
        year: {
            type: Sequelize.NUMBER,
            allowNull: false,
            validate: {
                isNumeric: true,
            },
        },
    },
    { sequelize, modelName: 'car' }
);

Driver.init(
    {
        id: {
            type: Sequelize.NUMBER,
            primaryKey: true,
        },
        name: {
            type: Sequelize.STRING,
            allowNull: false,
        },
    },
    { sequelize, modelName: 'driver' }
);

//Создание связи "один к одному"
Car.belongsTo(Driver);
//или так
Car.hasOne(Driver);

Определение связи один к одному подразумевает, что у одного водителя имеется одна машина.

По умолчанию формат добавляемого foreignKey следующий: modelName + "Id". Так, belongsTo() добавит в Car поле driverId, а hasOne() - в Driver поле carId.

Если вы хотите задать собственное наименование foreignKey или связать таблицы не по полю id, используйте следующий формат определения связи.

1
2
3
4

Car.belongsTo(Driver, {
    foreignKey: 'driver_fk',
    sourceKey: 'uuid',
});

Значение поля sourceKey должно быть уникальным.

Теперь рассмотрим пример установки связи один ко многим. В качестве исходных моделей используем модели из примера связи один к одному.

1
2

//у модели Car добавится поле driverId
Driver.hasMany(Car);

Здесь в примере определение связи один ко многим указывает, что у одного водителя может быть несколько машин.

Если необходимо указать пользовательские поля связывания, то придется указать полную связь.

1
2
3
4
5
6
7
8

Driver.hasMany(Car, {
    foreignKey: 'driver_fk',
    sourceKey: 'uuid',
});
Car.belongsTo(Driver, {
    foreignKey: 'driver_fk',
    sourceKey: 'uuid',
});

Теперь посмотрим, как определить связь многие ко многим на примере все тех же моделей Car и Driver.

1
2

Car.belongstoMany(Driver, { through: 'CarDriver' });
Driver.belongstoMany(Car, { through: 'CarDriver' });

Для хранения соответствия ключей водителей и автомобилей будет создана отдельная таблица, название которой указывается в поле through объекта, передаваемого методу belongsToMany() вторым параметром.

Задание полей в создаваемой таблице можно указать через свойство foreignKey.

1
2
3
4
5
6
7
8

Car.belongstoMany(Driver, {
    through: 'CarDriver',
    foreignKey: 'car_id',
});
Driver.belongstoMany(Car, {
    through: 'CarDriver',
    foreignKey: 'driver_id',
});

Пример извлечения данных из таблицы вместе с данными связанной таблице приведен далее в разделе "Получение/создание/обновление/удаление записи".

Если необходимо, чтобы модель CarDriver содержала дополнительные поля, просто заранее определите модель с этими самыми полями. Поля car_id и driver_id будут добавлены автоматически в момент создания связи.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

class CarDriver extends Sequelize.Model {}

CarDriver.init(
    {
        expired: {
            type: Sequelize.BOOLEAN,
            defaultValue: false,
        },
    },
    { sequelize, modelName: 'CarDriver' }
);

Получение/создание/обновление/удаление записи¶

Для получения данных таблицы, применительно к соответствующей модели используйте методы findAll() и findOne().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

//вернет все записи со всеми полями из таблицы cars
sequelize.car.findAll();

//вернет первую запись из таблицы cars
sequelize.car.findOne();

//вернет поле model для каждой записи таблицы cars
sequelize.car.findAll({
    attributes: ['model'],
});

//вернет поле model для первой записи таблицы cars
sequelize.car.findOne({
    attributes: ['model'],
});

//вернет все записи таблицы cars со всеми полями, кроме поля model
sequelize.car.findAll({
    attributes: { exclude: ['model'] },
});

//вернет все машины с маркой BMW таблицы cars
sequelize.car.findAll({
    where: { model: 'BMW' },
});

//пример использования с оператором and
const Operators = Sequelize.Op;

sequelize.car.findAll({
    where: {
        model: 'BMW',
        year: {
            [Operators.and]: 2019,
        },
    },
});

//пример с сортировкой и ограничением выборки
sequelize.car.findAll({
    order: ['year', 'DESC'],
    offset: 0,
    limit: 10,
});

//подсчет количества всех записей в таблице
sequelize.car.count().then((count) => console.log(count));

//определение самой новой машины
sequelize.car.max('year').then((max) => console.log(max));

//получение данных связанной таблицы
sequelize.driver.findAll(
    (include: [
        {
            model: Car, //здесь передается модель
            through: {
                attributes: ['model', 'year'],
            },
        }
    ])
);

Создание новых записей осуществляется с помощью методов create() и bulkCreate().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

//создание новой записи, если передать неполную модель, то недостающие поля будут null
sequelize.car
    .create({
        model: 'Audi',
        year: 2019,
    })
    .then((record) => console.log(record));

//создание сразу множества записей
sequelize.car
    .bulkCreate(
        [
            {
                model: 'Audi A5',
                year: 2019,
            },
            {
                model: 'Audi A7',
                year: 2018,
            },
        ],
        { returning: true }
    )
    .then((records) => console.log(records));

За обновление записей отвечает метод update(), который первым параметром принимает новые значения для записей, попадающих под задаваемую вторым параметром выборку.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

sequelize.car
    .update(
        {
            model: 'Audi',
        },
        {
            where: { model: 'BMW' },
        }
    )
    .then((record) => console.log(record));

Для удаления записей имеется метод destroy().

1
2
3
4
5

sequelize.car
    .destroy({
        where: { model: 'BMW' },
    })
    .then((result) => console.log(result));

Для выполнения самописных запросов без использования модели таблицы имеется метод sequelize.query(), который первым параметром принимает сам запрос в строковом виде, а вторым параметром - конфигурационный объект.

1
2
3
4
5
6

sequelize
    .query(`SELECT * FROM car`, {
        raw: true, //если для таблицы, к которой происходит обращение, не определена модель
        type: Sequelize.QueryTypes.SELECT, //тип запроса: SELECT | INSERT | UPDATE | DELETE ...
    })
    .then((result) => console.log(result));

Триггеры¶

Триггеры представляют собой функции, которые выполняются (если они определены) до/после/во время действий с данными. Список самых популярных триггеров в порядке их выполнения:

• beforeValidate(данные, опции) - выполняется перед валидацией;
• afterValidate(данные, опции) или validationFailed(данные, опции, ошибка) - выполняется после успешной или неуспешной проверки валидации соответственно;
• beforeCreate(данные, опции) - вызывается перед созданием записи;
• beforeDestroy(данные, опции) - выполняется перед удалением записи;
• beforeUpdate(данные, опции) - вызывается перед обновлением записи;
• beforeSave(данные, опции) - вызывается перед сохранением записи;
• afterCreate(данные, опции) - вызывается после создания записи;
• afterDestroy(данные, опции) - выполняется после удаления записи;
• afterUpdate(данные, опции) - вызывается после обновления записи;
• afterSave(данные, опции) - вызывается после сохранения записи.

Определение триггеров осуществляется в модели таблицы в объекте конфигурации в свойстве hooks.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

Driver.init({
    id: {
        type: Sequelize.NUMBER,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false
    }
},
{
    sequelize,
    modelName: 'driver',
    hooks: {
        beforeCreate(record, opts) => {
            console.log(record);
            console.log(opts);
        }
    }
});

Также имеется пара триггеров beforeConnect() и afterConnect() для подключения к БД.

1
2
3
4
5
6
7
8

sequelize.beforeConnect((config) =>
    console.log('Config: ', config)
);

sequelize.afterConnect((conn, config) => {
    console.log('Connection: ', conn);
    console.log('Config: ', config);
});

С полным перечнем триггеров можно ознакомиться в официальной документации.

Индексы¶

Создание индексов осуществляется при создании модели таблицы.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

Driver.init(
    {},
    {
        sequelize,
        modelName: 'driver',
        indexes: [
            { unique: true, fields: ['name'] }, //уникальный индекс
            //составной индекс
            {
                name: 'index_name',
                fields: ['name', 'active'],
                where: {
                    active: true,
                },
            },
        ],
    }
);