Migrations are a simple mechanism whereby you script out some change commands for your ORM, and that ORM then builds your database for you. To me, this is pure insanity. I dislike ORMs (accept, of course, for LLBLGenPro, which is astoundingly good). Trusting your ORM to build a proper database is ... kind of weird to me. SQL is terser, more expressive and (as it turns out) just right for the job.
There is no relational database better than PostgreSQL, and I'll tell you all about why.
In parts 1 and 2 of this little series I showed various ways to save a document and then update its search field. I also showed how to do a Bulk Saves of many documents transactionally. In this post I'll explore options for running queries.
In part 1 of this series I setup a nice save function, as well as another function to create an opinionated document storage table on the fly. This works well and does what's needed, but we can do so much more. Specifically: I want Full Text Indexing on the fly and bulk saves within a transaction.
It occurred to me the other day when someone asked on Twitter if I ever tried to work with Postgres in .NET that I never blogged about a course I did for Pluralsight. This course was particularly fun for me: lots of SQL and the challenge of getting it to run well on .NET.
With the release of Postgres 9.4 came the additional datatype
jsonb. This is binary JSON, the same type of thing that MongoDB uses for internal storage. Postgres has had the
json data type for a while, but
jsonb allows you to do something lovely: document indexing and specialized queries.
One thing that drives me absolutely over the cliff is how ORMs try so hard (and fail) to abstract the power and expressiveness of SQL. Before I write further let me say that Frans Bouma reminded me yesterday there's a difference between ORMs and the people that use them. They're just tools (the ORMs) - and I agree with that in the same way I agree that crappy fast food doesn't make people fat - it's the people that eat too much of it.
In the .NET world (and beyond), data access is a cluster-fucked echo chamber of half-assed rocket engineering and cargo cultism based on decade-old cathedralized thinking and corporate naval -gazing.
JSON, JSONB and Postgres 9.4
30 Seconds. Just 30 Seconds.
I always say - if you can't give me the pitch in a single sentence, in 30 seconds, your idea isn't worth what you think it is. So here's what I got for you:> PostGres will blow your mind; given its Enterprise features (Compression, partitioning, Full Text indexing, etc) , ease of use and configuration, and intelligent feature set - it's likely you'll want to use it tomorrow.