Using Marten with Postgres as a Document Database
In the last two weeks I have been working through AWS DynamoDB learning material. AWS DynamoDB is great service but appears to be very tricky to use effectively. In this post I am going to explore a C# library called Marten. It uses Postgres as a NoSQL store. Why? Postgres has fantastic support for JSON types. It provides a great alternative to NoSQL options like DynamoDB. Tying my solution to a service like DynamoDB feels like I’m tying myself to a specific implementation specific to the vendor’s service. Imagine I need to deploy my solution somewhere else other than a cloud service. Postgres is a relational database management service independent of a cloud service. The somewhere else might not use DynamoDB. The website for Marten describes it as:
Polyglot Persistence for .NET Systems using the Rock Solid PostgreSQL Database
What does it all mean? Polyglot is a term used by those developers proficient in multiple languages and frameworks. They can code solutions on a similar standard regardless of the technology. Give them NodeJS, .NET, Java — it does not matter. How does it translate to Marten? According to Wikipedia polyglot persistence means using a mixture of storage technologies. Postgres remains a relational database — nothing prevents you from using it as a relational store. Nothing prevents you from using it as a NoSQL store either.
SQL is a personal weakness I have to overcome. I prefer writing code such as C#. SQL is extremely powerful. In the majority of the environments I have worked in SQL Server is the mainstay. Exploring something different is a great reason to learn SQL using Postgres.
Installing Postgres on Windows 10
First lets download and install Postgres. I’m on Windows 10 — it only makes sense to download the appropriate package. For my purposes I’ll be using 12.1. I’m no Postgres expert. In fact I’m a total n00b. I do however enjoy solving problems in different ways. After downloading Postgres I step through the installation steps.
I have used pgAdmin briefly in the past. I remember it not being quite as refined. I found the navigation to be a bit terse. For my purposes though I’m going to try and use the CLI to interact with the database engine. I’ll install pgAdmin just in case though. I tried using HeidiSQL to connect to Postgres but found it a bit buggy. HeidiSQL works great for MySQL or MariaDb.
The Postgres installation has two parts to it — the installation of the postgres database engine is the first part. The second part is the StackBuilder configuration — I skipped it. I will only be connecting to the database through SDKs for C#. From previous experience the StackBuilder adds Npgsql — something I add through Nuget. Defining a dependency at a package level seems like a better idea.
Setup Postgres in your environment variables
I want the Postgres commands to be available from the CLI. Be sure to add it to your path variable under environment variables.
Create sample database using CLI
Setting up Postgres in your path variable allows you to connect to the Postgres instance through a CLI client like Windows Terminal. We need a sample database to write documents too. Launch Windows Terminal. Connect to the postgres instance through a CLI client such as Powershell Core using psql -U postgres.
The -U flag is the username for the database, in my case its postgres. It is the same username supplied during the setup process. You will be prompted to provide a password — the same password used during the setup.
Get Fredrik Erasmus’s stories in your inbox
Join Medium for free to get updates from this writer.
Lets create a database and call it marten_demo. Type CREATE DATABASE marten_demo;. The semicolon is quite important — it terminates the line.
You can verify the database’s existence with the \l command.
Marten Demo C# Project
With the database created lets write some code to store and read a document. For the demo I use Visual Studio 2019 Community Edition. Start up Visual Studio and select Create a new project:
Select Console App (.NET Core) from the templates.
Give the project a name Marten.Demo:
Add the Marten nuget package by right-clicking on the project. Select Manage NuGet Packages…
In the Nuget browser select the Browse tab — Installed is selected by default. In the search textbox type Marten:
Click the install button:
Create the following code:
Press ctrl + f5 to test it. You should see han appear in the console.
Okay so what happened here? We basically stored an instance of the User class as JSON in the postgres database. The important part to note though is that the JSON is not stored as a string.
Navigate back to the CLI window. Type \c marten_demo to connect to the database we created. Type \dt to get a list of the tables in the database:
The user object is stored in the mt_doc_user table. You can perform a select to see the data:
If you type the command \d+ you get definition of the table:
The data column has a jsonb type. Not a string. I have seen so many applications using SQL Server storing JSON or XML as a string. What is the significance? The significance lies in the fact that your data is stored as JSON and not a string.
References
- Marten: https://martendb.io/
- Postgres create database: https://www.guru99.com/postgresql-create-database.html
