Recently, while revisiting an old project, I realized that the little excitement I felt during my first attempt to connect with PostgreSQL still remains 🙂 You know that moment when you first connect to a database server and see the “Connected” message? That moment came to my mind again. In this article, I will talk a bit about how to connect to PostgreSQL with C#, how to perform basic CRUD operations with Dapper, and share some personal experiences. But not just dry explanations—my style involves a mix of chat, code, and stories 🙂
PostgreSQL is an open-source and quite powerful database system. It performs remarkably well, especially when working with complex queries and large data sets. It’s also very useful for someone like me who develops REST APIs. Dapper, on the other hand, is a lightweight and fast micro ORM, perfect for those who enjoy writing SQL.
Establishing a Connection
First, you need to include the Npgsql NuGet package in your project. Npgsql is a library that enables .NET applications to communicate with PostgreSQL. After that, we add Dapper. Once these are set up, we create our connection string. An example connection string might look like this:
"Host=localhost;Port=5432;Username=postgres;Password=1234;Database=demo_db"Of course, it’s not advisable to store passwords in plain text, but I used it for the example 🙂 For real projects, it’s better to store such configurations in files. Anyway, after establishing the connection, we can move on to executing queries.
CRUD Operations
Working with Dapper is quite simple. For example, to list users, you might write:
using (var connection = new NpgsqlConnection(connString))
{
var users = connection.Query<User>("SELECT * FROM users").ToList();
}Here, the User class corresponds to the users table in the database. Column names must match exactly; otherwise, mappings won’t work. Also, the reason Dapper is so fast is due to its simplicity.
Inserting records is just as straightforward. Here’s an example:
var sql = "INSERT INTO users (name, email) VALUES (@Name, @Email)";
connection.Execute(sql, new { Name = "Ali", Email = "ali@example.com" });Update and delete operations follow the same logic: write your SQL, pass parameters, and execute. Fast, simple, and clear.
Last week, at a campsite, I wanted to jot down some notes after setting up my tent. I had my laptop but no internet. I decided to try offline coding. I had a sample project with Dapper, where I was running PostgreSQL in a local Docker container. Everything was going fine until I couldn’t connect. The connection string was correct, the port was open, Docker was running, but no connection was made.
It turned out I forgot to mount the volume when starting the PostgreSQL container in Docker. The database was resetting on every restart 🙂 It made me realize how sometimes we overlook simple things and spend hours troubleshooting elsewhere. Attempting to read log files with a flashlight in the dark of the campsite was quite an adventure 🙂
Advantages of Dapper
Here’s the thing: Although large ORMs like Entity Framework offer many features, they can be a bit much sometimes. Dapper, on the other hand, does exactly what you tell it to. If you love writing SQL, Dapper is just perfect. It’s performant, simple, and gives you full control. Just be careful with join-heavy complex queries because you handle the mapping yourself.
In my experience, Dapper is more than sufficient for small and medium projects. It’s especially useful for developing REST APIs. Sometimes, even just for read-only operations, using Dapper makes a lot of sense.
A Small Note
One thing to keep in mind when working with Dapper: if your queries involve dynamic fields (like table names, column names, etc.), you cannot pass those as parameters. Parameters only work for values. Be cautious of SQL injection. If you forget, you may encounter security issues.
Also, in some cases, you need to use async methods. For example:
var users = await connection.QueryAsync<User>("SELECT * FROM users");This can improve performance. Just make sure to handle async/await properly, or you’ll end up with spaghetti code 🙂
Oh, and I remember a friend asked me, “Does Dapper support transactions?” Yes, it does. Working with transactions is also quite simple. You just start with connection.BeginTransaction(), perform your operations, and commit. That’s all.
Final Words
Honestly, I love Dapper. It’s lightweight, fast, and works perfectly with PostgreSQL. But is it suitable for every project? Not necessarily. However, for most jobs, it’s more than enough. After all, the most complex tools are not always the best. Sometimes, the simple solution is more effective.
What do you think? Have you ever used Dapper? If not, I recommend starting with a small project. Once you get used to it, it’s hard to go back 🙂