.NET და PostgreSQL, MySQL ან SQL Server: provider-ები

.NET სამივესთან კარგად მუშაობს. SQL Server-ს ყველაზე ღრმა ინსტრუმენტები აქვს და provider-ს Microsoft თავად წერს, PostgreSQL-ს აქვს Npgsql, რომელიც შესანიშნავია და ეკოსისტემაში ყველაფერზე არანაკლებ სწრაფი, MySQL-ს კი აქვს MySqlConnector, რომელიც Entity Framework Core-ის საზოგადოებრივი Pomelo provider-ის ქვეშ დევს. არჩევანი ბაზას ეხება და არა დრაივერს: SQL Server Express უფასოა, მაგრამ შეზღუდულია 10 GB-ით თითო ბაზაზე და დაახლოებით 1.4 GB buffer მეხსიერებით; PostgreSQL-ს edition-ის შეზღუდვები არ აქვს და ყველაზე მდიდარი შესაძლებლობები აქვს; MySQL ყველგანაა და გასაშვებად ყველაზე მსუბუქია. როცა აირჩევ, პრობლემის შემქმნელი ნაწილები სამივესთვის ერთი და იგივეა - connection string, pool-ის ზომა, როგორ იქცევა თარიღები და სტრიქონები, და მიგრაციების provider-ზე მიბმულობა. ეს პოსტი ყველა მათგანს გვერდიგვერდ განიხილავს.

Provider-ები და დრაივერები#

ყველა .NET ბაზის სტეკს ორი ფენა აქვს. ქვემოთ არის ADO.NET დრაივერი, რომელიც ბაზის wire პროტოკოლზე ლაპარაკობს და DbConnection-სა და DbCommand-ს ახორციელებს. ზემოთ, სურვილისამებრ, დგას Entity Framework Core provider, რომელიც LINQ-ს ამ ბაზის SQL დიალექტზე თარგმნის. Dapper და ხელით დაწერილი SQL დრაივერს პირდაპირ იყენებს.

რამდენიმე შენიშვნა ამ ცხრილზე:

• `System.Data.SqlClient` ძველი SQL Server დრაივერია და მოძველებულად არის გამოცხადებული. ახალი კოდი და EF Core-ის ყველა მიმდინარე ვერსია Microsoft.Data.SqlClient-ს იყენებს. ძველი namespace-ის მქონე მაგალითის კოპირება მაინც დაკომპილირდება და მოგცემს დრაივერს ძველი ნაგულისხმევებით და ახალი შესწორებების გარეშე.
• MySQL-ისთვის MySqlConnector ამჯობინე. ის MIT ლიცენზიითაა, ნამდვილად ასინქრონულია (Oracle-ის MySql.Data ისტორიულად თავის async მეთოდებს სინქრონულად ახორციელებდა) და სწორედ მას იყენებს Pomelo. Oracle-ის პაკეტები GPL-ია FOSS გამონაკლისით, რაც ზოგიერთი კომერციული პროექტისთვის მნიშვნელოვანია.
• Pomelo-ს რელიზები EF Core-ის major ვერსიებს მისდევს, ზოგჯერ თვეების დაგვიანებით. სანამ EF Core-ს ახალ major ვერსიაზე განაახლებ, შეამოწმე, არსებობს თუ არა შესაბამისი Pomelo რელიზი. Npgsql ჩვეულებრივ თავის EF Core provider-ს Microsoft-ის რელიზთან ახლოს უშვებს.

თითოეულის მიერთება EF Core-ით#

რეგისტრაცია სამივეში თითქმის ერთნაირად გამოიყურება, და EF Core-ის აზრიც სწორედ ესაა:

// PostgreSQLbuilder.Services.AddDbContext<AppDbContext>(o =>    o.UseNpgsql(builder.Configuration.GetConnectionString("Default")));// MySQL via Pomelovar mysql = builder.Configuration.GetConnectionString("Default");builder.Services.AddDbContext<AppDbContext>(o =>    o.UseMySql(mysql, ServerVersion.AutoDetect(mysql)));// SQL Serverbuilder.Services.AddDbContext<AppDbContext>(o =>    o.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Pomelo-ს სერვერის ვერსია სჭირდება, რადგან MySQL და MariaDB იმ SQL-ში განსხვავდება, რომელსაც იღებენ. ServerVersion.AutoDetect გაშვებისას კავშირს ხსნის, რომ იკითხოს, რაც აპლიკაციის გაშვებას ჩააგდებს, თუ ბაზა იმ მომენტში მიუწვდომელია; new MySqlServerVersion(new Version(8, 4, 0)) ამ მიმოსვლას თავიდან აგარიდებს და production-ში უკეთესი არჩევანია.

Npgsql-ისთვის ვერსია 7 და შემდეგი გირჩევს, ერთხელ ააწყო NpgsqlDataSource და გადასცე EF Core-ს ან პირდაპირ გამოიყენო. სწორედ იქ კონფიგურირდება ტიპების მიბმა, enum-ები და logging:

var dataSource = new NpgsqlDataSourceBuilder(    builder.Configuration.GetConnectionString("Default")).Build();builder.Services.AddDbContext<AppDbContext>(o => o.UseNpgsql(dataSource));

GetConnectionString("Default") კონფიგურაციიდან კითხულობს ConnectionStrings:Default-ს, რომელსაც production-ში აყენებ გარემოს ცვლადით სახელად ConnectionStrings__Default. Connection string საიდუმლოა; მას რეპოზიტორიაში appsettings.json-ში ადგილი არ აქვს. ASP.NET Core-ის კონფიგურაცია და საიდუმლოებები ფენებს განიხილავს.

Connection string-ები გვერდიგვერდ#

PostgreSQL (Npgsql)Host=db.example.net;Port=5432;Database=app;Username=app;Password=...;SSL Mode=PreferMySQL (MySqlConnector)Server=db.example.net;Port=3306;Database=app;User ID=app;Password=...;SslMode=PreferredSQL Server (Microsoft.Data.SqlClient)Server=tcp:db.example.net,1433;Database=app;User ID=app;Password=...;Encrypt=True;TrustServerCertificate=True

განსხვავებები, რომლებზეც ხალხი ბრკოლდება:

• SQL Server-ში პორტი მძიმის შემდეგ იწერება, და არა ორწერტილის, და ცალკე Port საკვანძო სიტყვა არ არსებობს. db.example.net:1433 ჰოსტის სახელად იკითხება და ვერ უერთდება. იგივე მძიმე ჩნდება SQL Server Management Studio-ს სერვერის სახელის ველშიც.
• SQL Server ნაგულისხმევად შიფრავს. Microsoft.Data.SqlClient 4.0-დან (და შესაბამისად EF Core 7-დან) Encrypt-ის ნაგულისხმევი მნიშვნელობა True-ა და დრაივერი სერვერის სერტიფიკატს ამოწმებს. სერვერი self-signed სერტიფიკატით - სწორედ ასეთს აგენერირებს SQL Server თავისთვის, როცა სხვა კონფიგურირებული არ არის - ვარდება შეცდომით "The certificate chain was issued by an authority that is not trusted", სანამ არ დაამატებ TrustServerCertificate=True-ს ან სანდო სერტიფიკატს არ დააყენებ. SQL Server-ის connection string-ები ამას ყველა დრაივერის ვერსიისთვის გადის.
• MySQL 8.4 ავთენტიფიკაციას `caching_sha2_password`-ით ახდენს. დაუშიფრავ კავშირზე პირველ შესვლას სერვერის RSA საჯარო გასაღები სჭირდება და MySqlConnector უარს ამბობს შეტყობინებით "Retrieval of the RSA public key is not enabled for insecure connections", თუ ან TLS-ს არ გამოიყენებ (SslMode=Required), ან AllowPublicKeyRetrieval=True-ს არ დაამატებ. TLS ამჯობინე.
• Npgsql-ის ნაგულისხმევი `SSL Mode=Prefer`-ია, რომელიც TLS-ს იყენებს, როცა სერვერი მას სთავაზობს, სერტიფიკატის შემოწმების გარეშე. გამოიყენე Require, რომ plaintext უარყო, და VerifyFull შენთვის სანდო CA-თი, როცა სერტიფიკატის შემოწმება გჭირდება.

პაროლები, რომლებიც ;-ს ან =-ს შეიცავს, ADO.NET connection string-ებში ბრჭყალებში უნდა ჩასვა - Password="a;b=c" - ან ააწყო დრაივერის ConnectionStringBuilder კლასით, რომელიც escape-ს შენ მაგივრად აკეთებს.

Connection pooling და timeout-ები#

სამივე დრაივერი კავშირებს ნაგულისხმევად pool-ში ინახავს, ერთი pool ყოველ განსხვავებულ connection string-ზე თითო პროცესში. კავშირის გახსნა ერთს pool-იდან იღებს; მისი dispose უკან აბრუნებს. სწორედ ამიტომაა სტანდარტული შაბლონი - ერთი DbContext თითო მოთხოვნაზე, ბოლოს dispose-ით - იაფი.

მნიშვნელობები წამებშია. ასი კავშირი თითო პროცესზე ბევრად მეტია, ვიდრე პატარა ბაზის სერვერს ეფექტურად შეუძლია გამოიყენოს. განსაკუთრებით PostgreSQL ყოველ კავშირზე რეალურ მეხსიერებას ხარჯავს, და 1 ან 2 GB გეგმაზე ბაზის max_connections ბევრად დაბალია იმაზე, რისი გახსნაც სამ აპლიკაციის ინსტანსს ნაგულისხმევი pool-ებით შეუძლია. Pool-ის ზომა ბაზას მოარგე და არა პირიქით: ოციდან ოცდაათამდე კავშირი პატარა სერვერზე აპლიკაციების უმეტესობისთვის საკმარისზე მეტია, და აპლიკაცია, რომელსაც მეტი სჭირდება, ჩვეულებრივ კავშირებს ზედმეტად დიდხანს იჭერს. Connection pool-ები და ლიმიტები განმარტავს, რატომაა პატარა pool ხშირად უფრო სწრაფი.

როცა pool ამოიწურება, მოთხოვნები დაკავშირების timeout-მდე ელოდება და მერე pool timeout შეცდომით ვარდება. ეს შეცდომა თითქმის არასოდეს ნიშნავს, რომ pool ძალიან პატარაა. ის ნიშნავს, რომ კავშირები იკარგება (dispose არ ხდება) ან ნელი სამუშაოს განმავლობაში იჭერ - მაგალითად, HTTP მოთხოვნა ტრანზაქციის შუაში.

EF Core-ს თავისი, ცალკე pool აქვს: AddDbContextPool<AppDbContext>() DbContext ინსტანსებს ხელახლა იყენებს (ნაგულისხმევად 1024-მდე), რომ ალოკაცია დაზოგოს. ის არ ცვლის, რამდენი ბაზის კავშირი არსებობს, და მოითხოვს, რომ შენს context-ს თითო მოთხოვნის მდგომარეობა არ ჰქონდეს.

Retry-ები და დროებითი შეფერხებები#

ქსელები პაკეტებს კარგავს და ბაზები გადაიტვირთება. EF Core-ს შეუძლია ჩავარდნილი ოპერაციები ავტომატურად გაიმეოროს:

o.UseSqlServer(conn, sql => sql.EnableRetryOnFailure(    maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null));o.UseNpgsql(conn, pg => pg.EnableRetryOnFailure());o.UseMySql(conn, version, my => my.EnableRetryOnFailure());

როცა retry-ები ჩართულია, ტრანზაქცია, რომელსაც თავად ხსნი BeginTransactionAsync-ით, exception-ს აგდებს, რადგან EF Core ტრანზაქციის ნახევარს ვერ გაიმეორებს. სამაგიეროდ მთელი ერთეული execution strategy-ში შეფუთე:

var strategy = db.Database.CreateExecutionStrategy();await strategy.ExecuteAsync(async () =>{    await using var tx = await db.Database.BeginTransactionAsync();    // ... რამდენიმე SaveChangesAsync გამოძახება    await tx.CommitAsync();});

Retry-ები ხანმოკლე შეფერხებებს მალავს. ბაზას, რომელიც მართლა გათიშულია, retry-ების განმავლობაში ძალიან ნელ აპლიკაციად აჩვენებს, ამიტომ რაოდენობები ზომიერი დატოვე და ყოველი retry დალოგე.

განსხვავებები, რომლებიც გადასვლისას გაწუხებს#

EF Core SQL დიალექტს მალავს. ბაზის ქცევას არ მალავს, და აი ისინი, რომლებსაც ხალხი ხვდება, როცა აპლიკაციას ერთი ძრავიდან მეორეზე გადააქვს ან ტესტებს SQLite-ზე წერს, production კი სხვა რამეზე მუშაობს:

• სტრიქონების შედარება და რეგისტრი. SQL Server-ის ნაგულისხმევი collation (SQL_Latin1_General_CP1_CI_AS) და MySQL 8-ის ნაგულისხმევი (utf8mb4_0900_ai_ci) რეგისტრის გაუთვალისწინებლად ადარებს, ამიტომ WHERE Email = 'Bob@x.com' პოულობს bob@x.com-ს. PostgreSQL რეგისტრის გათვალისწინებით ადარებს. შესვლის ძიება, რომელიც SQL Server-ზე მუშაობდა, PostgreSQL-ზე ჩუმად ვარდება. ელფოსტები ჩაწერისას ნორმალიზე, ან PostgreSQL-ზე გამოიყენე რეგისტრისადმი გულგრილი collation ან citext ტიპი.
• თარიღები და დროის სარტყლები. Npgsql 6 და შემდეგი DateTime-ს timestamp with time zone-ზე მიაბამს და უარს ამბობს ისეთი DateTime-ის ჩაწერაზე, რომლის Kind არ არის Utc, შეტყობინებით "Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone'". ყველგან UTC შეინახე, ან გამოიყენე DateTimeOffset. SQL Server-ის datetime2 და MySQL-ის datetime დროის სარტყელს საერთოდ არ ინახავს, ამიტომ იგივე კოდი ადგილობრივ დროს უპრობლემოდ წერს და შეცდომა მოგვიანებით ჩნდება.
• Unicode. SQL Server-ზე EF Core string-ს nvarchar-ზე მიაბამს, რომელიც Unicode-ია. ხელით შექმნილმა ცხრილებმა varchar სვეტებით შეიძლება დაკარგონ სიმბოლოები, რომლებიც collation-ის code page-ის გარეთაა. MySQL-ზე გამოიყენე utf8mb4 - ძველი utf8 ფსევდონიმი სამბაიტიანია და emoji-ს ვერ ინახავს. SQL Server-ის collation-ები და Unicode და MySQL utf8mb4 და collation-ები უფრო ღრმად შედის.
• სტრიქონის სიგრძის ნაგულისხმევები. შეუზღუდავი string თვისება SQL Server-ზე ხდება nvarchar(max), MySQL-ზე longtext და PostgreSQL-ზე text. SQL Server-ზე nvarchar(max) სვეტები ინდექსის გასაღები ვერ იქნება; თვისებებს, რომლებითაც გაფილტრავ, მიეცი [MaxLength].
• Identity მნიშვნელობები. სამივე გასაღებებს აგენერირებს, IDENTITY-ით, AUTO_INCREMENT-ით ან sequence-ებზე დაფუძნებული identity სვეტებით. Rollback-ებისა და გადატვირთვების შემდეგ ხარვეზები სამივეზე ნორმალურია; გასაღები რაოდენობად არასოდეს გამოიყენო.
• მიგრაციები provider-ზეა მიბმული. SQL Server-ის წინააღმდეგ გენერირებული მიგრაცია SQL Server-ის სვეტის ტიპებსა და ანოტაციებს შეიცავს. ძრავის შეცვლა ნიშნავს მიგრაციების ნულიდან ხელახლა გენერირებას ახალი provider-ისთვის და არა საქაღალდის ხელახლა გამოყენებას.

ბაზის არჩევა .NET აპლიკაციისთვის#

მხოლოდ შესაძლებლობებით სამიდან ნებისმიერი ტიპურ ვებ-აპლიკაციას გაუშვებს. არჩევანი ჩვეულებრივ ლიმიტებზე, ინსტრუმენტებსა და იმაზე დადის, რაც უკვე იცი.

SQL Server Express შეეფერება გუნდებს, რომლებიც უკვე SQL Server Management Studio-ში ცხოვრობენ, აპლიკაციებს, რომლებიც SQL Server-ის სპეციფიკურ შესაძლებლობებს იყენებენ (temporal ცხრილები, T-SQL stored procedure-ები, SQL Server-ის სრული ინსტრუმენტები), და ყველაფერს, რაც შეიძლება მოგვიანებით ფასიან SQL Server edition-ზე ან Azure SQL-ზე გადავიდეს. მისი ლიმიტები რეალურია: თითო ბაზა 10 GB მონაცემით შემოიფარგლება, buffer pool დაახლოებით 1,410 MB-ით, გამოთვლითი სიმძლავრე კი ერთ socket-სა და ოთხ ბირთვს შორის ნაკლებით, და დაგეგმილი დავალებებისთვის SQL Server Agent არ არის. SQL Server Express ჰოსტინგი განიხილავს, როდისაა ეს საკმარისი.

PostgreSQL ნაგულისხმევი რეკომენდაციაა ახალი პროექტისთვის, რომელსაც სხვა შეზღუდვა არ აქვს: edition-ის ლიმიტები არ არის, JSON-ის შესანიშნავი მხარდაჭერა, მდიდარი ინდექსირება, და Npgsql პირველი კლასისაა. PostgreSQL-თან დისტანციური კავშირები პირველ კავშირს განიხილავს.

MySQL სამიდან გასაშვებად ყველაზე მსუბუქია და თითქმის ყველასთვის ნაცნობი. კარგი არჩევანია, როცა აპლიკაცია უკვე მასზეა გათვლილი ან ბაზას PHP პროგრამასთან იზიარებს.

უფრო ვრცელი შედარება, MongoDB-სა და Valkey-ს ჩათვლით, არის სტატიაში რომელი ბაზა გამოვიყენო.

RE:NODE-ზე თითოეული მათგანი ცალკე ბაზის გეგმაა, სერვერზე გენერირებული credential-ებით და გეგმის ჰოსტითა და პორტით მისაწვდომი: PostgreSQL თვეში $6-დან, MySQL 8.4 LTS $4-დან გენერირებული root პაროლით, აპლიკაციის ბაზითა და აპლიკაციის მომხმარებლით, და SQL Server 2022 Express $6-დან sa პაროლით და შექმნილი ბაზით, რომელიც sa-ს ნაგულისხმევად არის დაყენებული. ბაზის გეგმებს proxy slot არ აქვს - აპლიკაცია პირდაპირ ჰოსტსა და პორტს უერთდება. C# / .NET აპლიკაციის გეგმებს ასევე აქვს საკუთარი ბაზის slot-ები, რომლებიც პანელში იქმნება გენერირებული ჰოსტით, მომხმარებლითა და პაროლით.

პრობლემების მოგვარება#

`A network-related or instance-specific error occurred` (SQL Server). არასწორი ჰოსტი ან პორტი, ორწერტილი მძიმის ნაცვლად პორტის წინ, ან firewall. ჯერ პორტი შეამოწმე.

`The certificate chain was issued by an authority that is not trusted` (SQL Server). შიფრაცია ნაგულისხმევად ჩართულია და სერვერის სერტიფიკატი self-signed-ია. დაამატე TrustServerCertificate=True, ან დააყენე სანდო CA-ს სერტიფიკატი.

`Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool`. კავშირები იკარგება ან ზედმეტად დიდხანს იჭერ. მოძებნე context-ები ან კავშირები, რომლებიც using-ის გარეთ იქმნება, და სამუშაო, რომელიც ღია ტრანზაქციებში სრულდება.

`53300: sorry, too many clients already` (PostgreSQL) ან `Too many connections` (MySQL). შენი ყველა აპლიკაციის ინსტანსის pool-ები ერთად სერვერის ლიმიტს აჭარბებს. შეამცირე Maximum Pool Size.

`Unable to connect to any of the specified MySQL hosts`. ჰოსტი, პორტი ან firewall. თუ ჰოსტი სწორია, შეამოწმე, აქვს თუ არა მომხმარებელს შენი მისამართიდან დაკავშირების უფლება.

FAQ#

Entity Framework Core უფრო ნელია, ვიდრე Dapper?

მარტივ query-ებზე თანამედროვე EF Core ახლოსაა, განსაკუთრებით AsNoTracking()-ითა და projection-ებით. Dapper მაინც იგებს mapping-ის სუფთა ხარჯზე და SQL-ზე ზუსტ კონტროლს გაძლევს. ბევრი აპლიკაცია EF Core-ს ჩაწერისა და მიგრაციებისთვის იყენებს, Dapper-ს კი რამდენიმე მძიმე წაკითხვის query-სთვის, ერთსა და იმავე connection string-ზე.

შემიძლია ტესტებში SQLite გამოვიყენო, production-ში კი SQL Server?

შეგიძლია, და ეს შეცდომებს დამალავს: რეგისტრისადმი მგრძნობელობა, თარიღების დამუშავება, ტრანზაქციების ქცევა და SQL-ის თარგმნა - ყველაფერი განსხვავდება. ინტეგრაციული ტესტები იმავე ძრავზე გაუშვი, რაზეც production მუშაობს, საჭიროების შემთხვევაში კონტეინერში.

რომელი MySQL provider გამოვიყენო EF Core-თან?

Pomelo, MySqlConnector-ის თავზე. ის ყველაზე ფართოდ გამოყენებადი და ყველაზე სრულია. EF Core-ის განახლებამდე შეამოწმე, არსებობს თუ არა მისი რელიზი შენი EF Core major ვერსიისთვის.

მჭირდება TLS ჩემს აპლიკაციასა და ბაზას შორის?

თუ კავშირი გადის ნებისმიერ ქსელს, რომელსაც არ აკონტროლებ - რაც მოიცავს ინტერნეტს აპლიკაციის სერვერსა და ბაზის სერვერს შორის - დიახ. სამივე დრაივერი მას მხარს უჭერს; SQL Server-ის დრაივერი მას ნაგულისხმევად რთავს.

რამდენი კავშირი უნდა დაუშვას ჩემმა pool-მა?

იმაზე ნაკლები, ვიდრე გგონია. დაიწყე ოციდან ოცდაათამდე თითო აპლიკაციის ინსტანსზე პატარა ბაზისთვის, დარწმუნდი, რომ ინსტანსების ჯამი ბაზის ლიმიტზე დაბლა რჩება, და მხოლოდ მაშინ გაზარდე, თუ შეგიძლია აჩვენო, რომ მოთხოვნები pool-ს ელოდება, ხოლო თავად ბაზა უქმადაა.