Use Dapper, .Net Core and Postgresql on Vagrant
In this post you’re going to learn how to setup a simple console application in
.Net core that talks to Postgresql.
This application will run on Ubuntu 14.04 using Vagrant.
I’m using iTerminal on macOS
- Setting up Vagrant box.
- Setting up Postgresql.
- Creating .Net core console application.
- Adding Dapper and Npgsql
- Connecting to Database and persisting data.
Create a new directory for our project:
> mkdir dapper-dotnet-postgresql && cd $_
Use the following Vagrantfile. This Vagrant file will provision the box with
dotnet-dev-1.0.0-preview2-003131. I got the installation steps for DotNet Core from
Microsoft Net Core Website
Now in your terminal window run:
> vagrant up && vagrant ssh
You can verify it worked by typing
Postgresql is a little different from Sql Server, here is a really basic breakdown
on how it works:
- “Roles” are used in Postgres for Authentication and Authorization
- ident identification is the default auth method. Similar to Active Directory.
- For Postgresql there are 2 user types: Linux ones and Postgres ones.
- You will need to create a user in both places in order to connect and query.
- By default there is going to be a “postgres” user, both in linux and postgres.
The following steps will help you on:
- Creating a user
postgresuser and adding
- Creating a sample database
- Adding a default password for your new user.
- Testing the connection.
vagrant@dotnetcore:~$ sudo adduser dotnetcore_db_user
If you see that message, it means you’re ready to create your first table inside
otherwise if things go wrong you can check: not becomming
postgres user before typing the commnads,
not adding users in both places, not setting the password in postgresql for the given user.
Let’s create a product table and insert one row into it:
dotnetcore_test=# CREATE TABLE products (
Now we can create our .Net Core console application inside our Vagrant box.
Make sure that you’re back into
vagrant home directory (
vagrant@dotnetcore:~$) and from
there switch to
src folder and create a new dotnet core application:
vagrant@dotnetcore:~$ cd ~ && cd src
After that last command you should see something like this:
Project src (.NETCoreApp,Version=v1.0) will be compiled because expected outputs are missing
Before connecting to our database, we need two nugget packages:
- Npgsql: Npgsql is the .NET data provider for PostgreSQL.
It allows any program developed for .NET framework to access a PostgreSQL database server. more
- Dapper: Dapper - a simple object mapper for .Net. more
To do this, modify your
The added lines are below “Microsoft.NETCore.App” inside
Go back to your command line and run:
vagrant@dotnetcore:~/src$ dotnet restore
You haven’t modified
Program.cs until this point, but that’s our next step, you
will connect to postgresql, insert a new row and then query that table.
This will be a quick and dirty application, nothing production ready but it will
give a kick-start to something more substantial.
Start by modifying
Here’s the breakdown of
- Created a
Productclass to map it to our table.
Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;allows Dapper to map
- Declared required connection string and two sql commands.
- Then it opens a connection, insert a new row and queries for all rows.
- Print all rows to Console.
Once you have this, go back to your command line and type:
vagrant@dotnetcore:~/src$ dotnet run
If everything worked, you should see two entries in your table.
Creating .Net Core applications that connect to Postgresql seems pretty straightforward. As usual,
setting up your environment with a new database is time consuming but hopefully you will only have
to make it one time.
Remember, never har-dcode connection strings or passwords into your source code, I did it here
for the sake of this example.
Let me know if you have any problems while setting this up, and please let me know which topics of
.Net Core are important to you.