Amazon RDS – PostgreSQL for .NET Developers

PostgreSQL is a free, open-source database that emphasizes extensibility and SQL compliance and was first released in 1996. A true competitor to commercial databases such as SQL Server and Oracle, PostgreSQL supports both online transaction processing (OLTP) and online analytical processing (OLAP) and has one of the most advanced performance features available, multi-version concurrency control (MVCC). MVCC supports the simultaneous processing of multiple transactions with almost no deadlock, so transaction-heavy applications and systems will most likely benefit from using PostgreSQL over SQL Server, and there are companies that use PostgreSQL to manage petabytes of data.

Another feature that makes PostgreSQL attractive is that not only does it support your traditional relational database approach, but it also fully supports a JSON/JSONB key/value storage approach that makes it a valid alternative to your more traditional NoSQL databases; so, you can now use a single product to support the two most common data access approaches. Because of its enterprise-level of features and the amount of work it takes to manage and maintain those, even though it is also open source and free software like MySQL and MariaDB, it is slightly more expensive to run PostgreSQL on Amazon RDS than those other open-source products.

PostgreSQL and .NET

As with any database products that you will access from your .NET application, its level of support for .NET is important. Fortunately for us, there is a large community involved in helping ensure that PostgreSQL is relevant to .NET users.

Let’s look at what you need to do to get .NET and PostgreSQL working together. The first thing you need to do is to include the necessary NuGet packages, Npgsql and Npgsql.EntityFrameworkCore.PostgreSQL as shown in Figure 1.

Figure 1. NuGet packages required to connect to PostgreSQL

Once you have the packages, the next thing that you need to do is to configure your application to use PostgreSQL. You do this by using the UseNpgsql method when overriding the OnConfiguring method in the context class as shown below:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("connection string here");
}

A connection string for PostgreSQL has six required fields:

  • server – server with which to connect
  • port – port number on which PostgreSQL is listening
  • user id – user name
  • password – password
  • database – database with which to connect
  • pooling – whether to use connection pooling (true or false)

When working in an ASP.NET Core application the connection string is added to the appsettings.json file as shown in Figure 2.

Figure 2. Adding a connection string to an ASP.NET Core application

Let’s now go create a PostgreSQL database.

Setting up a PostgreSQL Database on Amazon RDS

Now that we know how to set up our .NET application to access PostgreSQL, let’s go look at setting up a PostgreSQL instance. First, log into the console, go to RDS, select Create database. On the Create Database screen, select Standard create and then PostgreSQL. You then have a lot of different versions that you can select from, however, the NuGet packages that we used in our earlier example require a reasonably modern version of PostgreSQL, so unless you have any specific reason to use an older version you should always use the default, most updated version.

Once you have defined the version of PostgreSQL that you will use, your next option is to select the Template that you would like to use. Note that you only have two different templates to choose from:

·         Production – defaults are set to support high availability and fast, consistent performance.

·         Dev/Test – defaults are set in the middle of the range.

Note: Both MySQL and MariaDB had a third template, Free tier, that is not available when creating a PostgreSQL database. That does not mean that you must automatically pay, however, as the AWS Free Tier for Amazon RDS offer provides free use of Single-AZ Micro DB instances running PostgreSQL. It is important to consider that the free usage tier is capped at 750 instance hours per month across all your RDS databases.

Selecting the template sets defaults across the rest of the setup screen and we will call those values out as we go through those items.

Once you select a template, your next setup area is Availability and durability. There are three options to choose from:

·         Multi-AZ DB cluster – As of the time of writing, this option is in preview. Selecting this option creates a DB cluster with a primary DB instance and two readable standby instances, with each instance in a different Availability Zone (AZ). Provides high availability, data redundancy and increases capacity to serve read workloads.

·         Multi-AZ DB instance – This option creates a primary DB instance and a standby DB instance in a different AZ. Provides high availability and data redundancy, but the standby instance doesn’t support connections for read workloads. This is the default value if you chose the Production template.

·         Single DB instance– This option creates a single DB instance with no standby instances. This is the default value if you chose the Dev/Test template.

The next section, Settings, is where you provide the DB instance identifier, or database name, and your Master username and Master password. Your database identifier value must be unique across all the database instances you have in the current region, regardless of engine option. You also have the option of having AWS auto-generate a password for you.

The next section allows you to select the DB instance class. You have the same three filters that you had before of Standard classes, Memory optimized classes, and Burstable classes. Selecting one of the filters changes the values in the instance drop-down box, You need to select Burstable classes and then one of the instances with micro in the title, such as a db.t3.micro as shown in Figure 3.

Figure 3. Selecting a free-tier compatible DB instance

The next section in the setup is the Storage section, with the same options that you had available when going through the MySQL and MariaDB setups, though the default values may be different based upon the instance class that you selected. After the storage section are the Connectivity and Database authentication sections that we walked through earlier, so we will not go through them again now – they are standard across all RDS engine options. Selecting the Create database button will take you back to the RDS Databases screen where you will get a notification that the database is being created as well as a button that you can click to access the connection details. Make sure you get the password if you selected for AWS to create your administrative password. You will only be able to access the password this one time.

The pricing for PostgreSQL is slightly higher than MariaDB or MySQL when looking at compatible configurations, about 6% higher.

Selecting between PostgreSQL and MySQL/MariaDB

There are some significant differences between PostgreSQL and MySQL\MariaDB that can become meaningful when building your .NET application. Some of the more important differences are listed below. There are quite a few management and configuration differences, but those are not mentioned since RDS manages all of those for you!

·         Multi-Version Concurrency Control – PostgreSQL was the first DBMS to rollout multi-version concurrency control (MVCC), which means reading data never blocks writing data, and vice versa. If your database is heavily used for both reading and writing than this may be a significant influencer.

·         More types supported – PostgreSQL natively supports NoSQL as well as a rich set of data types including Numeric Types, Boolean, Network Address, Bit String Types, and Arrays. It also supports JSON, hstore (a list of comma-separated key/value pairs), and XML, and users can even add new types.

·         Sequence support – PostgreSQL supports multiple tables taking their ids from the same sequence while MySQL/MariaDB do not.

·         Index flexibility – PostgreSQL can use functions and conditional indexes, which makes PostgreSQL database tuning very flexible, such as not having a problem if primary key values aren’t inserted sequentially.

·         Spatial capability – PostgreSQL has much richer support for spatial data management, quantity measurement, and geometric topology analysis.

While PostgreSQL is considered one of the most advanced databases around, that doesn’t mean that it should automatically be your choice. Many of the advantages listed above can be considered advanced functionality that you may not need. If you simply need a place to store rarely changing data, then MySQL\MariaDB may still be a better choice. Why? Because it is less expensive and performs better than PostgreSQL when performing simple reads with simple join. As always, keep your use cases in mind when selecting your database.

Note: AWS contributes to an open-source project called Babelfish for PostgreSQL, which is designed to provide the capability for PostgreSQL to understand queries from applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL. This understanding means that you can use SQL Server drivers for .NET to talk to PostgreSQL databases. As of this writing, this functionality is not yet available in the PostgreSQL version of RDS. It is, however, available for Aurora PostgreSQL. We will go over this in more detail later in the chapter. The project can be seen at https://www.babelfishpg.org.

MariaDB, MySQL, and PostgreSQL are all open-source databases that have existed for years and that you can use anywhere, including that old server under your desk. The next database we will talk about is only available in the cloud and within RDS, Amazon Aurora.

Configuring the AWS Toolkit in JetBrains’ Rider

JetBrains just announced their release of their Amazon Web Services Toolkit for Rider.  For those that aren’t familiar with it, Rider is JetBrains’ cross-platform .NET IDE. Rider is based on JetBrains’ IntelliJ platform and ReSharper, and can be considered a direct competitor to Visual Studio.  It is also considerably less expensive then is Visual Studio.  If you don’t already have Rider, you can download a 30-day trial version at their site.

NOTE:  There is actually more work that needs to be done to fully configure your system to support the AWS Toolkit in Rider.  These steps include:

  • An AWS account configured with the correct permissions. Create a free AWS account here.
  • The AWS CLI (Command Line Interface) and AWS SAM (Serverless Application Model) CLI tools, which the plugin uses to build, deploy and invoke your code. SAM Instructions are several steps long and not trivial.  The regular CLI install steps, shown here, are also not trivial.
  • .NET Core 2.0 or 2.1. It’s not enough to just have .NET Core 3.0 or above installed. Make sure you’re running tests on the same version you’re targeting. Instructions
  • The SAM CLI will download and create a container to invoke your Lambda locally. Instructions (Should be covered in the CLIs, but just in case)

Install the Toolkit

Before we can walk through the new toolkit, we have to get it installed.  Installing the toolkit is simple once all the prerequisites are completed.  The directions are available from the AWS site here.  I have created my own version of these directions below because the sample on the AWS site is based upon the IntelliJ IDE rather than the Rider IDE so some of the steps are slightly unclear or look different.

  1. Open Rider. You do the installation of tool kits through the IDE rather than installing them outside of the context of the IDE (like a lot of the Visual Studio tool kits)
  2. Select Configure on the splash screen as shown below.
    Rider-Configure
  3. Select Plugins on the drop-down menu. This will bring up the Plugins
  4. Search for “AWS”. You should get a list of plugins, including the AWS Toolkit.
    Rider-AWSToolkit
  5. Install it.
  6. After installing, you will be asked to restart the IDE. You should probably go ahead and follow those directions.  I didn’t try NOT doing it, but I am a firm believer in the usefulness of “turning it off and on”; so I am all over it when the manufacturer tells me to take that step.
  7. Once the application is restarted, selecting New Solution will show you that there is a new solution template available, the AWS Serverless Application, as shown below.
    Rider-CREATEAWSSolution
  8. Go ahead and Create the solution named HelloWorld. It may be a little predictable, but HelloWorld is still the gold standard of initial applications…  Rider will go ahead and load the solution once it is completed and then open the README.md file.

Configure your connection to AWS

If you have already connected to AWS through the CLI or another toolkit or IDE, you should see the following in the bottom left corner of the IDE:

Rider-CredentialsReloaded

If not, follow the instructions for Configuring the AWS CLI here.  This will setup your default profile.  You will probably have to restart Rider after setting up your credentials.

Once Rider is restarted, you will see a notification on the bottom-right of the window that says “No credentials selected.”  Before you do what I did and roll your eyes, muttering “OMG” under your breath, click on that message (which is really a button)

Rider-NoCredentialsSelected

And find out that it is actually a menu.  Select the credential from the drop-down (it is probably called Default)

Rider-SelectProfile

And you’ll see that the it no longer says “No credentials selected.”  Congratulations, you are connected!