Working with the Managed Databases service from Digital Ocean in .NET Core
While other cloud platforms have long had their own database solutions, Digital Ocean didn’t offer anything in this segment until today. But on February 14, on Valentine's Day, the company decided to make a gift to its customers and, in Limited Availability mode, launched the Managed Databases service.
Since Digital Ocean is now quite a popular platform for hosting small projects on .NET Core, I could not ignore this event.
How the service works and what are the nuances when connecting to the database when using .NET Core I will discuss in this publication.
To date, the creation of PostrgeSQL 10 and 11 databases is available. MySQL and Redis will be on the lineup.
According to Digital Ocean, the managed database service solves common problems that many companies and developers face when creating a cluster from scratch:
- Determining the optimal infrastructure
- Scaling infrastructure as business and data requirements grow
- Designing and managing high availability infrastructure and failover processes
- Implementing a backup and restore strategy
- Prediction and maintenance of operating costs for infrastructure
To date, the creation of the most compact base will cost you $ 15. The most expensive configuration can cost more than two thousand dollars a month .
Connection Pools
When a client connects directly to a PostgreSQL database, the server creates a process to handle this connection. Each individual connection requires approximately 10 MB of RAM and uses this memory until it is closed. In addition, the total number of connections is fixed, and when all connections are used, new customers will no longer be able to connect.
Digital Ocean allows you to create connection pools that run on the PgBouncer base. Connection pooling reduces performance problems by directing client connections to the pool application, thereby reducing the number of processes that the database must handle. The pool application transfers part of the connections to the database, and puts the rest in the queue where they are until the base is released for new connections.
When using a connection pool, applications do not need to manage the actual connection. They will only need to connect to the pool and disconnect after the task is completed.
Pool creation
Creating a connection pool is very simple - you need to open the cluster and go to the Connection Pools tab. DigitalOcean supports three types of pooling:
Transaction
This mode allows each client to use the pool simultaneously for one transaction. If more transactions are sent than are available in the pool, additional transactions are queued and processed after connections are available. Transaction mode is suitable when you have a large number of customers who use idle connections. These clients will maintain their connection to the pool without establishing a connection with PostgreSQL.
Session
This mode allows the client to process requests until it disconnects from the database, maintaining a connection with this client all the time. If more clients are connected than another for processing in a pool, then these clients are queued and connected after disconnecting an existing client.
Statement
This mode is the most restrictive and allows only one statement at a time before moving on to the next client in the queue. This means that requests with multiple operators are not allowed and will not be executed. This mode is useful mainly in situations where each transaction is limited to one operator. Transactions with multiple operators will be rejected by the pool.
After creating the pool, to connect to the database you need to use the parameters of the pool. They can be viewed by clicking on the Connection details link:
Connection to pool
I use Npgsql in my projects , so further examples will imply the use of this driver.
The connection string will look like this:
User ID=xxx;Password=xxxx;Host=xxx.db.ondigitalocean.com;Port=xxx;Database=pool_name;Pooling=false;SslMode=Require;Server Compatibility Mode=Redshift;What you should pay attention to:
- The Database parameter will contain not the name of the database, but the name of the pool through which the connection to the database will take place.
- The Pooling parameter will be set to false. The connection pool will be managed by the PgBouncer, not the driver.
- The SslMode parameter must be Require, as a certificate will be required to open a connection.
- The Server Compatibility Mode parameter needs to be initialized to the Redshift value . This will avoid a number of problems when using the npgsql driver via PgBouncer.
The method of preparing the connection will be as follows:
protectedasync Task<IDbConnection> CreateConnection()
{
var connection = new NpgsqlConnection(_connectionString)
{
ProvideClientCertificatesCallback = certificates =>
{
certificates.Add(new X509Certificate2(_cert));
},
UserCertificateValidationCallback = CertificateValidation()
};
await connection.OpenAsync();
return connection;
}
private RemoteCertificateValidationCallback CertificateValidation() =>
(sender, certificate, chain, errors) =>
{
if (errors == SslPolicyErrors.None)
returntrue;
if ((errors & SslPolicyErrors.RemoteCertificateChainErrors) != 0)
{
var allErrorsIsUntrustedRoot = chain?
.ChainStatus
.All(o => o.Status == X509ChainStatusFlags.UntrustedRoot);
return allErrorsIsUntrustedRoot ?? true;
}
returntrue;
};Here it is worth paying attention to two points.
- The first . When creating a connection, you must use a certificate. It can be downloaded in the dialog for viewing connection settings to the pool. Certificate connection is configured via ProvideClientCertificatesCallback .
- The second . The procedure for verifying the pool certificate, which is configured via RemoteCertificateValidationCallback. In my example, a small hack is used, which allows you to ignore an error that occurs because the verification of the root certificate does not work.
Do not forget that the service is currently provided in the LIMITED AVAILABILITY mode, which means that some of the functionality can still be expanded and problems may arise during the work with the services that the DO team has not yet resolved.
Links
Only registered users can participate in the survey. Sign in , please.