This is the beginning of a series of posts where I’ll explore various lessons — choices — that can be made to better leverage PostgreSQL for your own projects. In this series I’ll go over some loose principles that I always keep present and that hopefully, will be of help.
With certain frequency I keep hearing the mantra "… ${former_employer} doesn’t allow ${handy_database_feature} …" or similar, referring to anything from named types to database triggers. It would seem that if one were to follow all of those, the database would be relegated to a dumb store.
I believe this kind of cargo cult has a grain of truth: If you’re trying to remain database agnostic or reduce the skill requirements on your staff, then these recommendations might apply to a certain extent. I suppose restraining the set of usable features could be a way for some organizations to keep the code — and their platform — within the grasp of their existing talent.
That said, once a project goes beyond the trial phase for a given technology — after the decision to jump with two feet has been taken — there’s nothing to gain by passing on whatever goodies that technology provides. The key is to judiciously employ available technology to make sure everything falls into place. In this series of posts I’ll try and dissect my thoughts and experience on this topic, specifically on the use of PostgreSQL, covering these areas:
- Types as ubiquitous data validation
- Recording the intention of a data transformation
- Keeping data safe with triggers
- The role of data integrity constraints
- Using Domains to provide safe base types
- Writing useful database tests in SQL
I don’t think I’ll say anything new in this series, at least for somewhat experienced developers. Hopefully, this series will highlight some ideas worth considering for newcomers. If this helps them become more productive faster, then my job is done.
My focus is on applications that capture user input, perform a data transformation — recording the relevant state information — and provide a response. I won’t be paying too much attention to the UI. After all, by working at the database level these considerations can take the rear seat, which is precisely what I like the most: If you do your design work correctly, then the data will remain sane no matter what happens with the pesky client code.