DataChomp

"Postgres? WTF man, you're a SQL Server DBA!" It's true, I am a SQL Server DBA but even I have to admit that the Postgres team is doing some wildy exciting stuff. With that in mind, lets take a quick look at just how quickly we can replace SQL Server with a free/open full bore Enterprise database system like Postgres.

One of the first things you will notice is Postgres is way easier to install than SQL Server. The SQL Server install is born from a truck stop romance between TFS and Sharepoint that someone found in a garbage bag in a dumpster and burned to a DVD. Postgres takes a different angle and opts for a 'less is more' type of approach. One of the big things you will want to pay attention to is the password you give the default 'postgres' account. Think of him as the SA account in SQL Server as he too wields a mighty big stick.

Once we finish the installer, lets open up pgAdmin III (henceforth known as pgAdmin). If you are accustomed to SSMS with SQL Server and always wished it went on a diet... then you might like pgAdmin. Be warned though, pgAdmin isn't SSMS on a diet... it is like SSMS with Anorexia. Once pgAdmin is opened and you are connected, then drill down to 'databases' and do a 'new database' and use the GUI to make your fancy new demo database, or just run the raw sql:

 
CREATE DATABASE "Demo"
  WITH ENCODING='UTF8'
       OWNER=postgres
       CONNECTION LIMIT=-1;
 

Next, lets hop in that database and throw up some tables/data:

 
CREATE TABLE "Colors"
(
	colorid serial NOT NULL,
	colorname character varying(50) NOT NULL,
	constraint pk_colorid PRIMARY KEY (colorid)
)
WITH (OIDS=false);
 
INSERT INTO "Colors" (colorname)
VALUES ('red');
INSERT INTO "Colors" (colorname)
VALUES ('blue');
SELECT * FROM "Colors";
 

So why the quotes around the table names you might be asking... well, SQL Server has a bit of Honey Badger approach to what case you assign names and Postgres treats it like a pretty big deal. Luckily, overcoming it isn't a big deal, we just double quote it. Now, if you don't have years of tsql or camelcasing drilled into your brain, you can easy just do everything in lower case and not fight the double quote dragon.
What else sticks out there... oh Data Types! You will have to adjust to some variance in data types, but in a good way. Postgres has a lavish assortment of data types for you to fall in love with and one of which is using 'serial' instead of the whole IDENTITY(1,1) you might be used to in SQL Server. There are also some really cool things called Sequences in Postgres which are blazing fast and give you the value of the ID before you insert it. DBAs like them because it can get you AppDevs away from fatty GUID primary keys.

Fire up a new MVC3 app and hop into the Nuget console. From there, we're going to add our Postgre provider with "Install-Package Npgsql". Now that we have our vital provider assemblies, lets go mess around in the web.config.

 
<configuration>
	<system.data>
		<DbProviderFactories>
			<add name="Npgsql Data Provider"
        invariant="Npgsql"
        support="FF"
        description=".Net Framework Data Provider for Postgresql Server"
        type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
		</DbProviderFactories>
	</system.data>
 
	<connectionStrings>
		<add name="pg" connectionString="Server=127.0.0.1;Port=5432;Database=Demo;User Id=postgres;Password=dontusethisaccount;CommandTimeout=20;" providerName="Npgsql" />
	</connectionStrings>
 

Yeah! We have our provider factory rocking and our connection string. Yes, I am using the postgres account in this demo and yes that is wrong/stupid, but I'm having so much fun playing with the Postgres engine that I decided to take a shortcut there for this entry.

So our color app is pretty quick and dirty. In keeping with that spirit, I'm going to use Massive as my ORM of choice. It already has a Postgres version ready to go. PetaPoco has some very nice Postgres options in it, but Massive is very quick/easy and the Expandos makes all the strong-typers uncomfortable and I love a good code-trolling.

In the Model folder of our app, I create a new class file called Massive and I copy/paste the Postgres version into it, and I also created a new class file called "Colors" that I pasted the following in:

 
	public class Color : DynamicModel
	{
		public Color() : base("pg", "Colors", "colorid") { }
 
	}
 

All wired up! In the home controller, we'll throw some code to play with our table in:

 
var table = new Color();
var colors = table.All();
ViewBag.colors = colors;
 

And then in our view:

 
@foreach (var color in ViewBag.colors)
{
	 @: @color.colorid @color.colorname
}
 

F5 and what do we get? BOOM!!! "Error 42P01: relation "colors" does not exist"
So remember when I started our Colors table with a capital C? This is where it can come back to bite you... and also one of the reasons I absolutely love Micro-ORMs. Fast, malleable, and so easy a DBA can use it. Let's hop into Massive and throw some quotes around our table access:

 
string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM \"{1}\" " : "SELECT {0} FROM \"{1}\" ";
 

and Viola! It works! In pretty much the time it takes to install SQL Server, we're already rocking our stunning and highly valuable colors app!