قرار بر این بود که درمورد تمام موضوعات BackEnd صحبت کنیم. ولی خب تا به امروز فقط درمورد Runtime های مربوط به js بیشتر NodeJS صحبت کردیم. امروز نگاهی به یکی از ویژگی های بسیار خوب #postgres اشاره میکنیم.
شما میتونید یک فیلد با تایپ JSON تعریف کنید و دیتا رو به صورت شبیه به document در mongodb ذخیر کنید. نکته جالب این هست که میتونید هم روی این JSON ها Index ایجاد کنید هم query های خوبی بزنید و کلا چون به صورت native روی postgres پیاده سازی شده تقریبا هرکاری میتونید کنید.

CREATE TABLE orders (
  id serial NOT NULL PRIMARY KEY,
  info json NOT NULL
);

INSERT INTO orders (info)
VALUES('{ "customer": "imanhpr", "items": {"product": "food","qty": 24}}'),
      ('{ "customer": "ali", "items": {"product": "bike","qty": 1}}'),
      ('{ "customer": "hasan", "items": {"product": "car","qty": 2}}');

شما به این طریق میتوانید یک table ایجاد کنید که در کنار ساختار نرمال خودش میتونه یک json document در خودش داشته باشه به عنوان فیلد که میشه اون رو به هرشکلی که میخواید query بزنید. حالا به مثال های زیر توجه کنید.
فرض کنید فقط میخواهیم نام های مشتریان رو از این json ها بکشیم بیرون.

SELECT info ->> 'customer' as customer FROM orders;

به ->> دقت کنید. این اپراتور شبیه به dot در js در عمل میکنه یا زبان ها دیگه. البته دو مدل داریم
-> اپراتور به صورت jsonb دیتا رو برمیگردونه.
->> اپراتور به صورت text دیتا رو به شما میرسونه.
حالا ما میخوایم که qty که خودش داخل یک داکیومنت دیگه هست رو برگردونیم

SELECT info ->> 'customer' AS customer ,
info -> 'items' ->> 'qty' AS qty
FROM orders;

نکته این هست که اپراتور ->> دیتا رو به صورت text برمیگردونه درصورتی که این فیلد عدد ذخیره میکنه و ممکنه بخوام عملیات شرطی یا aggregate هایی که روی اعداد میشه زد انجام بدم. برای این کار میتونیم از CAST استفاده کنیم. شبیه به Type Casting در زبان ها برنامه نویسی مثل java , go و حتی typescript هست.

SELECT info ->> 'customer' AS customer,
info -> 'items' ->> 'qty' AS qty
FROM orders 
WHERE CAST(info -> 'items' ->> 'qty' AS INTEGER) >= 2;

در ایجا شرط گذاشتیم اونهایی که بزرگتر یا مساوی 2 باشند که اگر اجرا کنید به زیبایی کار میکنه.

ذخیره کردن JSON در Postgres در یک حالت هایی به شدت قدرتمند هست و نیاز شما به mongodb رو حذف میکنه. البته این جمله رو در همه مواقع صدرصد فرض نکنید و باید با توجه به پیچدگی مسئله تصمیم بگیرید.
در پست بعد یک مسئله مطرح میکنم که به عنوان BackEnd Dev باید بدونید چون که میتونه سوال مصاحبه باشه و خیلی در دیزاین دیتابیس بهتون کمک میکنه و مزیت ذخیره کردن JSON در Postgres رو با یک مثال که در دنیای واقعی استفاده میشه خواهید دید.

#Tip #Postgres

درود و وقت بخیر.
مدتی فعالیت نداشتم و امروز دوباره برگشتم و امیدوارم هرجا هستید حالتون خوب باشه. در حال حاظر به صورت Full time دارم مطالعه میکنم قطعا پست های جالب تر و عمیق تری نسبت به قبل خواهیم داشت. امروز برای دست گرمی با معرفی یک ابزار شروع میکنیم.

اگر برای سرویستون تست مینویسید و تست های شما وابستگی به دیتابیس داره برای سرعت معمولا سعی میشه که تست ها با یک دیتابیس in-memory اجرا بشه که وابستگی بیرون نداشته باشه و دردسر کمتری هم این روش داره.
حالا مشکلی که اینجا وجود داشت این که اگر پروژه شما به #MongoDB وابستگی داشته باشه در این راه برای استفاده از پکیج مربوط به In memory در حالت هایی دردسر زیادی داره که استفاده از روش های پایین خیلی منطقی تره.

روش هایی که در این حالت میشد استفاده کرد به این صورت بودن:
- استفاده یک دیتابیس جدا و کاملا ایزوله
- استفاده از Service ها در CI/CD pipeline مثل github and gitlab services ( در صورت نبود دیتابیس in memory این روش خیلی بهتری هست )
https://docs.github.com/en/actions/use-cases-and-examples/using-containerized-services/about-service-containers
https://docs.gitlab.com/ee/ci/services/

برای دیتابیس های SQL هم معمولا از #Sqlite نسخه In-memory رو به عنوان engine در هنگام تست استفاده میکنن ولی به این نکته باید توجه داشت که #Sqlite مثل هر #SQL دیگ تفاوت هایی با بقیه دارد و اگر همون تست ها رو بخواید با دیتابیس دیگ مثل #Postgres و #MySQL اجرا کنید ممکنه به خطا بخورید که این هم بخاطر تفاوت ها هست. مخصوصا اگر در تست هاتون به Primary key وابستگی داشته باشید که قطعا خیلی زیاد پیش میاد. ولی درکل هندل کردنشون به طوری که روی همشون کار کنه کار سختی نیست و با ریفکتور کوچیک بیشتر مواقع این موضوع حل میشه.
نمونه کد برای اجرا کردن #Sqlite با #NodeJS پکیج استاندارد جدیدی که اخیرا اضافه شده.

import { DatabaseSync } from 'node:sqlite';
const database = new DatabaseSync(':memory:');

البته شما باید باتوجه به ORM که استفاده میکنید کانفیگ رو انجام بدید.

برای Redis هم که Package مربوط به redis in-memory هست میتونید نصب کنید و استفاده کنید.

و حالا میرسیم برای #MongoDB دردسر ساز که این پکیج کارتون رو راه میندازه. نکته ای که وجود داره این که شما اگر بخواید با استفاده از in memory mongo پروژه خودتون تست کنید اگر هر نسخه از #NodeJS کانتینر برای تست خودتون استفاده کنید کار نمیکنه که جزیاتش رو میتونید داخل داکیومنتش ببینید و البته در این حالت ترجیح میدم از سرویس services مربوط به gitlab یا github استفاده کنم.
https://github.com/typegoose/mongodb-memory-server

یکی از ابزار هایی که وقتی با #Postgres کار میکنید و کمتر استفاده میشه ولی خیلی کاربردی هست Window function ها هستن.
به صورت کلی Window function ها این قابلیت رو میدن که دسترسی داشته باشین به مجموعه row های قبل یا بعد از record که در حال حاظر در حال برسی هست.

به عنوان مثال سناریویی رو در نظر بگیرد که نیاز دارید یک سطر X رو با سطر های X+N یا X-N برسی کنید ( row های قبل یا بعد ). یکی از کاربرد هایی که ممکنه براتون داشته باشه داخل کار کردن سناریو هایی است که با Timeseries دیتا ها سرکار دارید.

سینتکس مربوط به استفاده از window function ها به صورت کلی به این صورت هستن.

window_function(arg1, arg2,..) OVER (
   [PARTITION BY partition_expression]
   [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }])

- منظور از window_function در اینجا اون فانکشنی هست که میخوایم استفاده کنیم به عنوان مثال LAG function
- قسمت PARTITION BY به صورت کلی شباهت هایی به Group by دارد ولی در حال حاظر کاری بهشون نداریم.
- قسمت Order by هم ترتیب مقایسه Row ها رو با سطر بعد یا قبلش مشخص میکنه. دقیقا مثل Order by در حالت معمولی کار میکنه.

به صورت کلی window function های مختلفی داریم ولی برای درک بهتر و این که یک مثال ساده داشته باشیم LAG function هم کاربردی هست هم ساده.
فرض کنید یک table دارید که تعداد فروش محصولات رو برای هر سال دارد.

"year","sales_amount"
"2018","100"
"2019","120"
"2020","150"
"2021","180"

حالا سناریویی رو تصور کنید که میخواید در هر سطر برسی کنید که نسبت به سال قبل چقدر محصول بیشتر فروش داشتین؟ اینجا LAG function به داد شما میرسه

SELECT 
  year, 
  sales_amount, 
  sales_amount - LAG(sales_amount , 1) OVER (ORDER BY year) AS sales_diff
FROM 
  sales;

در اینجا ما نیاز داریم که مقدار sales_amount سطر جاری رو از sales_amount سطر قبل که مربوط به سال قبل هست کم کنیم که تفاوت این دو رو بتونیم به دست بیاریم.
حالا Lag 3 تا arg میگیره که به این ترتیب هستن
- نام فیلدی که دیتا مربوط به سطر قبل یا بعدش رو لازم داریم.
- مقدار offset که میتوان عدد منفی به عنوان مثل -1 هم داشت. به صورت پیش فرض عدد 1 هست
- مقدار سوم که در اینجا نداریم default value هست که به صورت پیش فرض Null هست وقتی معمولا پیش میاد که در سطر اول یا اخر در هرکدوم از این سناریو ها ممکنه سطر بعدی یا قبلی وجود نداشته باشه پس در نتیجه یک مقداری باید قرار بگیره که پیش فرض Null هست

حالا وقتی Query رو اجرا کنید همچین نتیجه ای میبینید.

year,sales_amount,sales_diff
2018,100,
2019,120,20
2020,150,30
2021,180,30

جمع بندی:
کلا Window function ها ابزار های قدرتمندی هستن که بهمون خیلی کمک میکنن Query های پیچیده ای بزنیم که ممکنه در بیزینس لاجیک های خاص مخصوصا وقتی با timeseries دیتا کار داریم انجام بدیم.
موضوع بعدی این که ترکیبشون با Subquery یک ترکیب قدرتمند هست که رسما میشه Query هایی رو نوشت که در حالت عادی باتوجه به Data model که داریم ممکنه بدست آوردن اون دیتا غیرممکن باشه و قبل از هرچیزی برای رسیدن به نتیجه وقتی نیاز داریم که دیتا رو یک تغییرات بدیم و بعد از روی اون به اطلاعات برسیم; سناریو خوبی هست برای استفاده از این دو مفهوم.

قبلا در مورد استفاده از jsonb در #postgres صحبت کردیم و به صورت خلاصه اشاره کردیم چرا میتونه خوب باشه. امروز دوباره قراره یک نکته دیگ که خیلی خیلی میتونه مفید باشه یاد بگیرم. این که چطور اطلاعات یک جدول رو تبدیل کنیم به json ؟
حالا سوالی که برامون پیش میاد اصلا چرا باید همچین کاری کنیم؟ به صورت کلی گاهی اوقات نیاز دارید در هنگام Query زدن اطلاعات مربوط به polymorphic table ها رو همه و همه با جزیات در یک Query ببینید. در #MySQL ظاهرا راه حل های Native برای این گونه مسائل وجود داره که در آینده باهم یاد خواهیم گرفت ولی در Postgres با کمک Json ها که آزادی عمل زیادی بهتون میده میتونید هر کاری کنید از جمله حل کردن این گونه مسائل.

فرض کنید یک users table داریم با column های id, name , email. حالا میخواهیم تمام اطلاعات مربوط به هر row رو داخل یک column به اسم user_data داشته باشیم

SELECT to_json(users) AS user_data FROM users;

خب در اینجا ما از فانکشن کمکی to_json کمک گرفتیم که هر row در جدول users رو به json تبدیل کنیم که سطون های جدول users به عنوان key و مقادیر هر row به عنوان value در نظر گرفته میشود. این فانکشن کمکی به صورت کلی یکم محدود هست به این که کل سطون ها رو تبدیل به json میکنه. حالا اگر بخوایم قبل از json شدن روی دیتا مربوط به اون column های خاص یک کاری انجام بدیم مثلا type cast انجام بدیم چکار باید کنیم؟
اینجا json_build_object به نجات ما میاد.

SELECT
  json_build_object(
    'id',
    u.id :: TEXT,
    'base_mail_name',
    split_part(u.email, '@', 1),
    'fqdn',
    split_part(u.email, '@', 2)
  ) AS users_data
FROM
  users AS u;

اینجا رسما دیتا مربوط هر row رو میگیرم و دستی به شکلی که میخوایم در Result set نهایی داشته باشیم شکل میدیم. فانکشن json_build_object تعداد زوج از param ها رو میگیره که param های فرد به عنوان Key و param های زوج به عنوان Value نهایی ما در json هستن. فانکشن split_part هم مثل اینجا صرفا برای split کردن یک string هست مثل متد split در #JavaScript عمل میکنه.

به صورت کلی فانکشن های کمکی خیلی زیادی برای کار کردن با json ها در #Postgres هستن ولی این دوتا بین اون ها خیلی کاربردی تر هستن. بقیه خیلی خیلی خاص تر هستن. کلا استفاده از همین ها هم خیلی نمیبینیم چون به صورت روزمره کاربردی نیستن ولی داخل سناریو های خاصی که بالا به یکیش اشاره کردم این ها کمک خیلی بزرگی میکنند. یا حتی در استفاده از Union برای ایجاد یک interface یکسان برای Table های مربوط در Query.

نکته بعدی این که هردو فانکشن بالا ورژن jsonb هم دارند.

to_jsonb
jsonb_build_object

درمورد Performance اطلاعات دقیق ندارم و بیشتر نظر شخصی خودم هست.
- قطعا تاثیر منفی در Performance دارن ولی اونقدر کمک بزرگی میکنند که به هیچ عنوان این تاثیر منفی به چشم نمیاد برای مشکلاتی که از این روش ها استفاده میکنید.
- معمولا مشکلاتی که بالا اشاره کردم شخصا در کدبیس ها دیدم که به هردلیلی سناریو های مختلف که از این طریق میشه رفت جلو رو، در سطح application هندل کردن که خب اونجا قطعا هم دردسر خیلی بیشتری داره برای توسعه هم نگهداری و هم البته وب سرور رو درگیر کاری میکنه که لزوما براش ساخته و بهینه نشده. در نتیجه اینجا اگر تمام این موضوعات در نظر بگیری فدا کردن یک کوچولو Performance دیتابیس خیلی منطقی به نظر میاد.