CRUD
от англ. create, read, update, delete — «создать, прочесть, обновить, удалить»
Create
INSERT INTO notes
(id, name, text)
VALUES
(1, 'Books', 'Books to read');
Read
SELECT id, name AS title, text FROM notes;
+----+-------+---------------+
| id | title | text |
+----+-------+---------------+
| 1 | Books | Books to read |
+----+-------+---------------+
Read. Массивы
CREATE TABLE example
(numbers integer[]);
INSERT INTO example (numbers)
VALUES ('{1,2,3}'),('{4,5,6}');
SELECT numbers FROM example;
+------------+
| numbers |
+------------+
| {1,2,3 } |
| {4,5,6 } |
+------------+
SELECT numbers[2] FROM example;
+-----------+
| numbers |
+-----------+
| 2 |
| 5 |
+-----------+
SELECT numbers[1:2] FROM example;
+------------+
| numbers |
+------------+
| { 1, 2 } |
| { 4, 5 } |
+------------+
Read
SELECT * FROM notes;
+----+-------+---------------+----------+
| id | name | text | owner_id |
+----+-------+---------------+----------+
| 1 | Books | Books to read | NULL |
+----+-------+---------------+----------+
Auto increment
INSERT INTO notes (name, text)
VALUES ('Films', 'Films to watch');
ERROR: duplicate key value violates
unique constraint "notes_pkey"
DETAIL: Key (id)=(1) already exists.
Auto increment
SELECT * FROM notes_id_seq;
+-----------------+------------+-----+-----------+
| sequence_name | last_value | ... | is_called |
+-----------------+------------+-----+-----------+
| notes_id_seq | 1 | ... | t |
+-----------------+------------+-----+-----------+
Auto increment
INSERT INTO notes (name, text)
VALUES ('Films', 'Films to watch');
SELECT * FROM notes;
+----+-------+----------------+----------+
| id | name | text | owner_id |
+----+-------+----------------+----------+
| 1 | Books | Books to read | NULL |
| 2 | Films | Films to watch | NULL |
+----+-------+----------------+----------+
Auto increment
SELECT * FROM notes_id_seq;
+-----------------+------------+-----+-----------+
| sequence_name | last_value | ... | is_called |
+-----------------+------------+-----+-----------+
| notes_id_seq | 2 | ... | t |
+-----------------+------------+-----+-----------+
Read. Where
SELECT id, name, text FROM notes
WHERE name = 'Films';
+----+-------+----------------+
| id | name | text |
+----+-------+----------------+
| 2 | Films | Films to watch |
+----+-------+----------------+
Read. Where + AS
SELECT id, name AS title, text FROM notes
WHERE title = 'Films';
ERROR: column "title" does not exist
Read. Sort, offset, limit
SELECT name FROM notes;
+----------+
| name |
+----------+
| Books |
| Films |
| Music |
| Rules |
| Markdown |
+----------+
SELECT name FROM notes
ORDER BY name DESC
OFFSET 2
LIMIT 2;
+----------+
| name |
+----------+
| Markdown |
| Films |
+----------+
Read. Count
SELECT count(*) FROM notes;
+-------+
| count |
+-------+
| 5 |
+-------+
Update
UPDATE notes
SET text = 'My favorite books to read', owner_id = 1
WHERE id = 1;
Delete
DELETE FROM notes
WHERE id = 1;
FOREIGN KEY
CREATE TABLE notes (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
text TEXT,
owner_id INTEGER,
CONSTRAINT fk_notes_users FOREIGN KEY (owner_id)
REFERENCES users (id)
);
FOREIGN KEY
CREATE TABLE notes (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
text TEXT,
owner_id INTEGER REFERENCES users (id)
);
FOREIGN KEY
ALTER TABLE notes
ADD CONSTRAINT fk_notes_users
FOREIGN KEY (owner_id)
REFERENCES users (id);
Виды JOIN
- INNER
- LEFT OUTER
- RIGHT OUTER
- FULL OUTER
- CROSS
Данные
notes | users
|
id name owner_id | id name
-- ----- -------- | -- ------
1 Books 1 | 1 Олег
2 Films 1 | 2 Сергей
3 Music 2 | 3 Михаил
4 Rules NULL |
5 Markdown NULL |
INNER JOIN
SELECT notes.id AS note_id,
notes.name AS note_name,
users.name AS user_name
FROM notes
INNER JOIN users
ON notes.owner_id = users.id;
notes | users
|
id name owner_id | id name
-- ----- -------- | -- ------
1 Books 1 | 1 Олег
2 Films 1 | 2 Сергей
3 Music 2 | 3 Михаил
4 Rules NULL |
5 Markdown NULL |
notes | users
|
id name owner_id | id name
-- ----- -------- | -- ------
1 Books 1 | 1 Олег
2 Films 1 | 2 Сергей
3 Music 2 | 3 Михаил
4 Rules NULL |
5 Markdown NULL |
INNER JOIN
note_id note_name user_name
------- --------- ---------
1 Books Олег
2 Films Олег
3 Music Сергей
OUTER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
LEFT JOIN
SELECT notes.id AS note_id,
notes.name AS note_name,
users.name AS user_name
FROM notes
LEFT JOIN users
ON notes.owner_id = users.id;
LEFT JOIN
note_id note_name user_name
------- --------- ---------
1 Books Олег
2 Films Олег
3 Music Сергей
4 Rules NULL
5 Markdown NULL
RIGHT JOIN
SELECT notes.id AS note_id,
notes.name AS note_name,
users.name AS user_name
FROM notes
RIGHT JOIN users
ON notes.owner_id = users.id;
RIGHT JOIN
note_id note_name user_name
------- --------- ---------
1 Books Олег
2 Films Олег
3 Music Сергей
NULL NULL Михаил
FULL JOIN
SELECT notes.id AS note_id,
notes.name AS note_name,
users.name AS user_name
FROM notes
FULL JOIN users
ON notes.owner_id = users.id;
FULL JOIN
note_id note_name user_name
------- --------- ---------
1 Books Олег
2 Films Олег
3 Music Сергей
4 Rules NULL
5 Markdown NULL
NULL NULL Михаил
CROSS JOIN
SELECT notes.id AS note_id,
notes.name AS note_name,
users.name AS user_name
FROM notes
CROSS JOIN users;
SELECT notes.id AS note_id,
notes.name AS note_name,
users.name AS user_name
FROM notes, users;
CROSS JOIN
note_id note_name user_name
------- --------- ---------
1 Books Олег
2 Films Олег
3 Music Олег
4 Rules Олег
5 Markdown Олег
1 Books Сергей
2 Films Сергей
... ... ...
ORM
Object-Relational Mapping
Установка
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);
Типы данных
PostgeSQL ⇒ Sequelize
Строковые
| PostgreSQL |
Sequelize |
| CHAR |
CHAR |
| VARCHAR(255) |
STRING |
| TEXT |
TEXT |
Числовые
| PostgreSQL |
Sequelize |
| INTEGER |
INTEGER |
| BIGINT |
BIGINT |
| REAL |
REAL |
| DOUBLE PRECISION |
DOUBLE |
| DECIMAL |
DECIMAL |
Даты
| PostgreSQL |
Sequelize |
| TIMESTAMP WITH TIME ZONE |
DATE / NOW |
| DATE |
DATEONLY |
| TIME |
TIME |
Массивы
| PostgreSQL |
Sequelize |
| ARRAY |
ARRAY |
Модели. Объявление
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) {});
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
}
});
Внешние связи. 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 })
);
});
