PostgreSQL experiments
PLpgSQL Ruby M4 Smarty
Switch branches/tags
Nothing to show
Clone or download
Latest commit 557fddf Aug 27, 2016
Permalink
Failed to load latest commit information.
array_intersect gitlab init Mar 8, 2016
changes gitlab init Mar 8, 2016
concepts gitlab init Mar 8, 2016
copylong gitlab init Mar 8, 2016
currencies add_money and moneysum test Mar 9, 2016
dynamicview gitlab init Mar 8, 2016
dynostatus gitlab init Mar 8, 2016
emailarray gitlab init Mar 8, 2016
emailnoquote email no quote Mar 13, 2016
emailreply gitlab init Mar 8, 2016
errors gitlab init Mar 8, 2016
fileoutput gitlab init Mar 8, 2016
jsonfield gitlab init Mar 8, 2016
jsoninsert gitlab init Mar 8, 2016
jsonreplace gitlab init Mar 8, 2016
jsonupdate gitlab init Mar 8, 2016
jsonupdate2 gitlab init Mar 8, 2016
jsonupdate3 gitlab init Mar 8, 2016
message_id gitlab init Mar 8, 2016
money basic add_money function works Mar 9, 2016
named gitlab init Mar 8, 2016
nodelete gitlab init Mar 8, 2016
notify gitlab init Mar 8, 2016
nullparam gitlab init Mar 8, 2016
parse gitlab init Mar 8, 2016
randomid gitlab init Mar 8, 2016
translate gitlab init Mar 8, 2016
urlclean gitlab init Mar 8, 2016
viewupdate gitlab init Mar 8, 2016
viewwith gitlab init Mar 8, 2016
xml + xml Apr 26, 2016
.gitignore gitlab init Mar 8, 2016
README.md changed gitlab to 50pop Aug 27, 2016

README.md

PostgreSQL experiments

I'm learning and testing PostgreSQL features.

Instead of always tying them into my big d50b database, it seemed better to keep experiments isolated with minimum context or baggage.

Context

Very inspired by Rich Hickey's “Simple Made Easy” talk. Especially these points:

  • Don't braid things together
  • Intertwined things must be considered together
  • Simple doesn't mean programmer ease and familiarity
  • gem/bower install "hairball" is not simplicity
  • Simple might mean making more things, not fewer
  • Simple might mean more learning curve (slower start) for later reward
  • ORM is unnecessary complication. Work with SQL directly.
  • Classes and Models (OOP) are unnecessary complication. Work with values directly (hash/map/table).
  • Especially if JSON API is the eventual interface. It's just data.
  • Information is simple. Don't hide it behind micro-language.

I have had the same database since 1998 (17 years!), but the required code around it has changed from Perl to PHP to Ruby to JavaScript and sometimes back again. How many hundreds of hours have I spent re-writing that necessary logic around the database?

Though the norm in this industry is to treat the database as dumb storage, I have always wanted a smarter, stricter database that would not allow anything but a well-formed email address into the email field, or would not allow a started project to be deleted.

One step further, using PL/pgSQL, now all the “business logic” that classes and ORM models were doing could be done in the database directly! No more depending on that ever-changing PHP, Ruby, JavaScript stuff around it.

One step further, now that PostgreSQL has JSON functions, even the job of a REST API webserver to convert table/row values into JSON documents could be done in the database.

Less intertwining. Less complecting. Less dependencies. Double-down on the database.

PL/pgSQL is not as beautiful as Ruby, but by having all this inside the database, I can easily switch to whatever language/tech around it is best at the time.

Right now OpenResty and Plug both look appealing, but I'm open to whatever. The API webserver doesn't have to do anything but map HTTP methods and URLs to PostgreSQL function calls, then directly pass the database response as an HTTP response.

Goal

As of now, I'm daydreaming of the dumbest possible webserver, and the smartest possible database, to make a great JSON REST API.

Every database call would be the same format:

pg_query("SELECT mime, js FROM some_function_name($1, $2)", [params[:id], params[:json]])

The webserver would just send the MIME type and JSON back as the response, even for errors.

I haven't tried this yet, but that's what these PostgreSQL experiments are leading towards.

For these tests:

createuser -s sivers ; createdb -E UTF8 -O sivers sivers