Sometimes, when I’m sitting down coding, it hits me — those cool evenings in Bursa, at home in front of the computer, while the wind from Uludağ blows outside. Spending time with my wife and child, I hardly find time for coding, but I always keep an eye out for ideas. I remember last Sunday morning, after breakfast, while my child was sleeping, I quickly tested a REST API connection to PostgreSQL using Dapper. We were planning a mountain hike, but coding kept lingering in my mind. Anyway, today I want to tell you how I connected PostgreSQL with Dapper in C#, hoping it will be useful for fellow developers.
When I first discovered Dapper, I was a bit tired of Entity Framework; it felt heavy for my projects. I love PostgreSQL — it’s open source, powerful, and I used it as a local database for a project in Bursa. Dapper is a micro ORM, perfect for fast queries. I find managing connection strings with Dapper very practical for REST API development. Of course, I also use MySQL sometimes, but PostgreSQL’s JSON support and flexibility make it more attractive.
Why Do I Prefer Dapper?
Dapper was developed by Sam Saffron on Stack Overflow, I believe. It offers quick mapping and is secure against SQL injection. In my experience, Dapper uses less memory than EF for large datasets. For example, retrieving 10,000 records from a table, Dapper completes in seconds, while EF can be slower. Last month, I encountered a small mistake while integrating Dapper: I had entered the wrong password in the connection string and spent hours debugging. Fortunately, I resolved it with the NuGet package, just by executing Install-Package Dapper.
However, when using Dapper, be cautious with parameterized queries to avoid security issues. Here’s a simple example for a SELECT. First, add using System.Data; and using Dapper; to your project. Then, open a connection.
using (var connection = new NpgsqlConnection(connectionString))
{
var users = connection.Query<User>("SELECT * FROM users WHERE id = @Id", new { Id = 1 });
return users;
}Yes, it works nicely, right? This code connects to PostgreSQL and fetches data. I use this structure in my REST API built with .NET Core. Don’t forget the Npgsql package, the driver for PostgreSQL. Install it via NuGet with Install-Package Npgsql.Dapper, and you’re all set.
By the way, I had an idea last week at the market while my child was sleeping in the stroller. There was a man talking about embedded systems and electronics circuits. I explained my transition from PHP to Vue.js, and the chat went on. What does this have to do with code? 🙂 Coding ideas come to me everywhere, even during family walks.
The best thing about Dapper is its extension methods. Query, QueryAsync, and async programming are perfect. I always use async in my APIs because I don’t want users waiting. For example, during an API POST request to add data:
public async Task<int> AddUserAsync(User user)
{
using var connection = new NpgsqlConnection(connectionString);
var sql = "INSERT INTO users (name, email) VALUES (@Name, @Email) RETURNING id;";
var id = await connection.ExecuteScalarAsync<int>(sql, user);
return id;
}As you see, the RETURNING clause is used to get the ID, thanks to PostgreSQL. This makes my API much faster. But be careful—sometimes I forget to keep the connection open during transactions, which causes data inconsistency and delays. I remember when I was translating my Vue frontend from jQuery, things got complicated then.
Configuring the Connection String
You put the connection string in appsettings.json. Something like “Host=localhost;Database=mydb;Username=user;Password=pass”. Inject it via IConfiguration into your controller. It’s simple, right? But in production, use environment variables for security. I use localhost during local development in Bursa, but when deploying, I set it for Azure or other cloud services.
Honestly, integrating Dapper with PostgreSQL is essential in my daily coding routine. My wife sometimes gets annoyed and asks, “Coding again?” but generating ideas is necessary. I remember, even at age 42, I once got up in the middle of the night to fix a bug. My child was awake, and I was writing a Dapper query on my laptop while lying next to him. It failed because of a column name case sensitivity issue in PostgreSQL. I had to write “username” instead of “UserName”. That’s coding for you 🙂
By the way, if I’m doing frontend with jQuery, I now prefer axios over $.ajax. My Vue projects are similar, but Dapper speeds up my backend. I tested it once, and I saw a performance difference of 3-4 times. Enough about that, let’s continue.
When using Dapper, match your class properties carefully with table columns. Use aliases, e.g., “SELECT name AS Name”. From my experience, these small details prevent big headaches. Sometimes I forget, but I debug thoroughly.
However, PostgreSQL’s advanced features are fun to integrate with Dapper. For example, array queries like “WHERE id = ANY(@Ids)”. Passing an int[] parameter works well. I employed this in a report API, and it was successful. That afternoon, we went on a family trip to Osmangazi, but my mind was still in the code. My wife noticed and said, “Again?” with a laugh. That’s passion for coding 🙂
Now, regarding error handling: use try-catch blocks, make sure the connection is disposed, and don’t forget to await in async methods to prevent deadlocks. I have a success story: after returning from Uludağ winter camp, I rewrote an API with Dapper. The previous EF version was slow—it took 5 seconds for a query. With Dapper, it decreased to 200ms, and the clients were very happy. I celebrated with coffee 🙂
Of course, perfection isn’t always possible. Dapper requires manual SQL for complex joins, unlike EF which handles automatic ORM mapping. I see this as a trade-off for speed. For small projects, EF might suffice. It’s similar to designing electronic circuits—simplicity matters.
Here’s a quick summary for starting: first, install the NuGet packages: Npgsql and Dapper. Second, set up your connection string in appsettings. Finally, wrap data access in a repository pattern for clean code. It takes just 10 minutes to start.
In conclusion, using C# with Dapper and PostgreSQL is a standard setup in my REST APIs. If you’re new, check out NuGet Dapper page or search for “C# Dapper PostgreSQL example” on Google. Microsoft’s documentation at learn.microsoft.com is helpful too. Reddit’s r/csharp community discusses these topics often. I recommend experimenting with small APIs, and jotting down ideas even during family time. I believe it’s a healthy coding habit 🙂
Last month, I encountered a connection timeout issue that required restarting the server. It wasn’t a big problem, but annoying. I realized I needed to increase the max connections in PostgreSQL’s configuration. Now, I fixed it.
All in all, Dapper with PostgreSQL is an excellent combo for developers like me. Give it a try, and if you face issues, leave a comment—I’ll help. That’s all, see you later 🙂