Коллекция SQL запросов для PostgreSQL
Привет, коллеги! Если работаете с SQL, то зацените коллекцию готовых запросов по часто возникающим задачам.
https://github.com/rin-nas/postgresql-patterns-library
#db #pg
Привет, коллеги! Если работаете с SQL, то зацените коллекцию готовых запросов по часто возникающим задачам.
https://github.com/rin-nas/postgresql-patterns-library
#db #pg
Совет дня от дядюшки Макса
При использовании транзакций и блокировок в работе с базой данных старайся получать записи в одинаковом порядке для избежания взаимной блокировки (Deadlock).
Например, мы разрабатываем кошелек, где у пользователей есть финансовые счета (accounts). Пользователь может совершать разные операции со счётом: пополнять (deposit), переводить другому пользователю (transfer) или выводить средства со счёта (payout).
Во всех этих операциях скорее всего нужно будет использовать транзакции и блокировки, чтобы другая параллельная транзакция не могла изменять эти записи в базе данных в тот же момент.
Так вот, в каждой такой транзакции нужно получать записи из базы в одинаковом порядке, чтобы не получился замкнутый круг из блокировок (deadlock).
Пример ошибки: в одной транзакции мы сначала получили и заблокировали счёт (account), а потом получили и заблокировали пополнение (deposit). В другой транзакции мы сделали тоже самое, но наоборот - сначала заблокировали deposit, а потом account. Если обе транзакции запустятся одновременно, то одна сначала заблокирует счёт, а вторая пополнение, затем первая попытается заблокировать пополнение, но оно уже заблокировано второй транзакцией, которая в этот момент пытается заблокировать счёт, который в свою очередь уже заблокирован первой транзакцией.
В такой ситуации успешно завершится только одна из транзакций, а вторая завершится с ошибкой.
Документация на примере PostgreSQL:
https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS
Ставь лайк, если полезно 👍 делись своим опытом в комментариях!
#db #deadlock #tip
При использовании транзакций и блокировок в работе с базой данных старайся получать записи в одинаковом порядке для избежания взаимной блокировки (Deadlock).
Например, мы разрабатываем кошелек, где у пользователей есть финансовые счета (accounts). Пользователь может совершать разные операции со счётом: пополнять (deposit), переводить другому пользователю (transfer) или выводить средства со счёта (payout).
Во всех этих операциях скорее всего нужно будет использовать транзакции и блокировки, чтобы другая параллельная транзакция не могла изменять эти записи в базе данных в тот же момент.
Так вот, в каждой такой транзакции нужно получать записи из базы в одинаковом порядке, чтобы не получился замкнутый круг из блокировок (deadlock).
Пример ошибки: в одной транзакции мы сначала получили и заблокировали счёт (account), а потом получили и заблокировали пополнение (deposit). В другой транзакции мы сделали тоже самое, но наоборот - сначала заблокировали deposit, а потом account. Если обе транзакции запустятся одновременно, то одна сначала заблокирует счёт, а вторая пополнение, затем первая попытается заблокировать пополнение, но оно уже заблокировано второй транзакцией, которая в этот момент пытается заблокировать счёт, который в свою очередь уже заблокирован первой транзакцией.
В такой ситуации успешно завершится только одна из транзакций, а вторая завершится с ошибкой.
Документация на примере PostgreSQL:
https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS
Ставь лайк, если полезно 👍 делись своим опытом в комментариях!
#db #deadlock #tip
👍14
DBngin
Бесплатная программа для MacOS, которая позволяет в пару кликов запустить сервер базы данных (PostgreSQL, MySQL, Redis), выбрав нужную версию.
https://dbngin.com
#tools #db
Бесплатная программа для MacOS, которая позволяет в пару кликов запустить сервер базы данных (PostgreSQL, MySQL, Redis), выбрав нужную версию.
https://dbngin.com
#tools #db
👍4
На днях понадобилось развернуть старый проект, написанный с использованием Laravel 7 и базы данных MySQL 5.7.
Первым делом я обновил Laravel до 9 версии, что кстати заняло менее часа с учетом того, что я делал каждое изменение, которое показывал GitHub.
Дальше решил перенести базу данных на PostgreSQL, потому что она по ощущениям работает быстрее и более популярна сегодня.
Смена движка базы данных оказалось новой задачей для меня.
Сначала попробовал перенести через обычный дамп, но ничего не вышло, потому что SQL слишком сильно отличается.
Дальше начал гуглить и нашел PgLoader, созданный как раз для моей задачи.
Особых сложностей здесь не возникло - установил, запустил и всё получилось. Так что рекомендую.
К слову мне НЕ нужно было переносить сами данные, а только лишь восстановить всю структуру базы - таблицы, индексы, связи и тд. Но я думаю с данными тоже НЕ возникло бы особых проблем.
https://pgloader.io/
#tools #db #postgres #pg
Первым делом я обновил Laravel до 9 версии, что кстати заняло менее часа с учетом того, что я делал каждое изменение, которое показывал GitHub.
Дальше решил перенести базу данных на PostgreSQL, потому что она по ощущениям работает быстрее и более популярна сегодня.
Смена движка базы данных оказалось новой задачей для меня.
Сначала попробовал перенести через обычный дамп, но ничего не вышло, потому что SQL слишком сильно отличается.
Дальше начал гуглить и нашел PgLoader, созданный как раз для моей задачи.
Особых сложностей здесь не возникло - установил, запустил и всё получилось. Так что рекомендую.
К слову мне НЕ нужно было переносить сами данные, а только лишь восстановить всю структуру базы - таблицы, индексы, связи и тд. Но я думаю с данными тоже НЕ возникло бы особых проблем.
https://pgloader.io/
#tools #db #postgres #pg
👍14🔥5
Изменение состояния сущности
Изображения (скрины) отправлю в следующем сообщении.
Наши модели и база данных содержат поля, отвечающие за состояние сущности.
Например, пользователь может быть заблокирован или РАЗблокирован.
Способ 1
В этом случае можно добавить в таблицу
Тогда админ сможет блокировать пользователя, переключая чекбокс в админке.
Этот пример я накидал на первом изображении 👇
Проблема
Рано или поздно админ спросит - а когда был заблокирован этот юзер?
Начинающий разработчик скорее всего пожмёт плечами и скажет, что таких данных нет.
А опытный разработчик заглянет в логи и ответит на поставленый вопрос.
Если, конечно, значения поля НЕ менялось напрямую в базе данных 😁
Решение
В любом случае ничего страшного НЕ произойдёт, но нормальный разработчик сделает выводы, что в следующий раз лучше сразу хранить дату и время блокировки юзера.
(Способ 2)
Для решения этой задачи, можно вместо
В таком случае мы получаем всё тоже самое (переключатель ```null/datetime```) + имеем дату блокировки пользователя.
Этот пример показан на втором изображении 👇
Признавайся, делал так?
Проблема
Однако этот способ тоже НЕ идеальный, потому что здесь есть дата блокировки пользователя, НО нет даты его РАЗблокировки 😁
Получается, что задача решена наполовину, а для нас (хацкеров) это НЕприемлемо.
Решение
Логичное решение - добавить отдельное поле для хранения времени изменения переключателя.
Способ 3
Этот вариант сочатает в себе оба предыдущих способа и, наверно, является наиболее универсальным.
Теперь главное НЕ забыть обновлять дату и время ```blocked_at``` в момент изменения поля
Внимание на третье изображение 👇
Проблема
Но помимо поля
И тут возникает логичный вопрос - добавлять дополнительное поле с временем изменения к каждому из полей юзера?
То есть по-хорошему, мы хотим знать какие поля менялись? когда менялись? и значения ДО и ПОСЛЕ?
Решение
Начинающий разработчик, возможно, действительно начнёт добавлять отдельные поля.
Хотя бы для некоторых свойств пользователя (самых важных).
Но мы же не такие? Мы будем думать над универсальным "оружием".
А кто-то пойдёт гуглить готовое решение. Лентяй 🫵
Способ 4
Все выходные мы думали над решением и есть же!
1. Можно создать отдельную модель, например
2. Привязываем эту модель к любой другой модели нашего приложения (через трейт HasChanges).
3. Указываем какие поля мы хотим отслеживать (через абстрактный метод в трейте).
4. На основе событий модели (created, updated) наш трейт автоматически записывает историю в базу данных (таблица changes).
5. Мы в любой момент видим лог всех изменения наших записей: дату, время, значения ДО и ПОСЛЕ.
Изображение НЕ прилагаю. Предлагаю тебе самому подумать на решением 😝
Итог
Подобное решение иногда раально упрощает жизнь.
Особенно, если пользователь заявляет, что его аккаунт взломали 😃
Мы можем увидеть: когда был вход, когда изменился email, пароль и тд.
Ставь лайк, если полезно! Может запишем видео на эту тему.
ПС: если руками лазить в базу, то тут ничего не поможет 😁
ППС: если ооочень хочется, то можно 😉
#php #laravel #db #bool #boolean
Изображения (скрины) отправлю в следующем сообщении.
Наши модели и база данных содержат поля, отвечающие за состояние сущности.
Например, пользователь может быть заблокирован или РАЗблокирован.
Способ 1
В этом случае можно добавить в таблицу
usersполе
blockedс типом
boolean.
Тогда админ сможет блокировать пользователя, переключая чекбокс в админке.
Этот пример я накидал на первом изображении 👇
Проблема
Рано или поздно админ спросит - а когда был заблокирован этот юзер?
Начинающий разработчик скорее всего пожмёт плечами и скажет, что таких данных нет.
А опытный разработчик заглянет в логи и ответит на поставленый вопрос.
Если, конечно, значения поля НЕ менялось напрямую в базе данных 😁
Решение
В любом случае ничего страшного НЕ произойдёт, но нормальный разработчик сделает выводы, что в следующий раз лучше сразу хранить дату и время блокировки юзера.
(Способ 2)
Для решения этой задачи, можно вместо
booleanдля поля
blockedиспользовать дату и время, например
timestampс возможностью НЕ указывать значение (nullable).
В таком случае мы получаем всё тоже самое (переключатель ```null/datetime```) + имеем дату блокировки пользователя.
Этот пример показан на втором изображении 👇
Признавайся, делал так?
Проблема
Однако этот способ тоже НЕ идеальный, потому что здесь есть дата блокировки пользователя, НО нет даты его РАЗблокировки 😁
Получается, что задача решена наполовину, а для нас (хацкеров) это НЕприемлемо.
Решение
Логичное решение - добавить отдельное поле для хранения времени изменения переключателя.
Способ 3
Этот вариант сочатает в себе оба предыдущих способа и, наверно, является наиболее универсальным.
Теперь главное НЕ забыть обновлять дату и время ```blocked_at``` в момент изменения поля
blocked.
Внимание на третье изображение 👇
Проблема
Но помимо поля
blockedу юзера есть много других полей.
И тут возникает логичный вопрос - добавлять дополнительное поле с временем изменения к каждому из полей юзера?
То есть по-хорошему, мы хотим знать какие поля менялись? когда менялись? и значения ДО и ПОСЛЕ?
Решение
Начинающий разработчик, возможно, действительно начнёт добавлять отдельные поля.
Хотя бы для некоторых свойств пользователя (самых важных).
Но мы же не такие? Мы будем думать над универсальным "оружием".
А кто-то пойдёт гуглить готовое решение. Лентяй 🫵
Способ 4
Все выходные мы думали над решением и есть же!
1. Можно создать отдельную модель, например
Change(изменение).
2. Привязываем эту модель к любой другой модели нашего приложения (через трейт HasChanges).
3. Указываем какие поля мы хотим отслеживать (через абстрактный метод в трейте).
4. На основе событий модели (created, updated) наш трейт автоматически записывает историю в базу данных (таблица changes).
5. Мы в любой момент видим лог всех изменения наших записей: дату, время, значения ДО и ПОСЛЕ.
Изображение НЕ прилагаю. Предлагаю тебе самому подумать на решением 😝
Итог
Подобное решение иногда раально упрощает жизнь.
Особенно, если пользователь заявляет, что его аккаунт взломали 😃
Мы можем увидеть: когда был вход, когда изменился email, пароль и тд.
Ставь лайк, если полезно! Может запишем видео на эту тему.
ПС: если руками лазить в базу, то тут ничего не поможет 😁
ППС: если ооочень хочется, то можно 😉
#php #laravel #db #bool #boolean
🔥4