Planning to use PostgreSQL with ASP.NET: bad idea?

Question

I'm currently planning the infrastructure for my future web project. I want to go the way Joel went with having one DB per client and now thinking which DB engine will be good for me. The best would be of course SQL Server, but I can't afford a full-blown version at this moment and I don't think SQL Server Express will be a good choice for the loaded service. Now I'm thinking of using PostgreSQL instead. Given that my development environment will be ASP.NET 3.5 with say NHibernate or LINQ to SQL, how much trouble will I have if I use PostgreSQL instead of SQL Server?

Thanks!

Answer 1

NHibernate works OK with PostgreSQL (whether the db is on Windows or UNIX-like OSes) and .NET works well with it using the Npgsql db provider.

The only "trouble" you'll get is of course PostgreSQL doesn't do T-SQL. In fact its PL/pgSQL stored proc language is closer to Oracle's PL/SQL than it is to MS SQL Server's T-SQL. So you'll have to recode your stored procs, and there will be some gotchas to watch out for if you do ADO.NET. If you use NHibernate, you probably won't have to worry much about that. No LINQ to SQL though, so tough luck for you.

PostgreSQL is scalable and works OK now with Windows (earlier versions didn't support Windows formally), and pgAdmin is a good management tool for it, you'll be able to do most of the stuff you can do with SQL Server's GUI tools with it in a short time.

Answer 2

I don't think it is a bad idea, but a great experience.

By the way NHibernate is the way to go Linq to Nhibernate is under heavy development and available in the trunk so if you do care "which I don't care" about Linq don't be scare to use it.