auth.conf

# Subquery that finds user data, happens for every resource request
# Also takes (and decodes) flash message from get parameters 
eval $meta {
  set_unescape_uri  $flash $arg_flash;
  postgres_pass    database;
  postgres_output  json;
  postgres_query   "SELECT 'user name' as biggles, nullif('$flash', '') as flash";
  postgres_rewrite GET no_rows 403;
} 

postgres_pass   database;
# Parse POST query string into json and assign to $input variable
set_form_input_json $input; 

resource.conf

    include '/Users/invizko/sites/data/views.conf';
    set $before "$views_header";
    set $html   "";
    set $after  "$views_footer";
    location /stylesheets {

      try_files $uri $uri/;
    }
    
    location / {
      autoindex on;
      postgres_output  json;           # Output json
      default_type text/html;
      eval_subrequest_in_memory off;   
      eval_escalate on;                # Allow preflight request to raise 403
      mustache on;                     # Process json with mustache

      location ~* /([a-z0-9_-]+)/new {
        include '/Users/invizko/sites/data/auth.conf';
        # New
        postgres_query   GET "SELECT 'Create new ' || singularize('$1') as title, 
                                     form_for('$1')                     as columns";
        postgres_rewrite GET rows $views_fieldset;
        
      }

      location ~* /([a-z0-9_-]+)/(\d+)/edit {
        include '/Users/invizko/sites/data/auth.conf';
        # Edit
        postgres_query   GET "WITH row as (SELECT * from $1 where id=$2)        
                              SELECT 'Edit ' || singularize('$1') as title,     
                                     form_for('$1', row_to_jsonb(row)) as columns
                              FROM row";
        postgres_rewrite GET HEAD rows $views_fieldset;
        postgres_rewrite GET HEAD no_rows 404;
      }

      location ~* /([a-z0-9_-]+)/(\d+)/? {
        include '/Users/invizko/sites/data/auth.conf';

        # Show
        postgres_query   GET "WITH row as (SELECT * from $1 where id=$2) 
                              SELECT 'View ' || row.email as title,  
                                     view_for('$1', row_to_json(row)::jsonb) as columns
                              FROM row";
        postgres_rewrite GET rows    $views_fields;
        postgres_rewrite GET no_rows 404;

        # Destroy
        postgres_query   DELETE "DELETE from $1 where id = $2";
        postgres_rewrite DELETE changes    /$1/;
        postgres_rewrite DELETE no_changes 404;

        # Update
        postgres_query   POST PUT  "WITH row as (                           -- Subquery
                                      SELECT from_json('$1',                -- Update into orders
                                        json_extract_path('$input',         -- json from order[] form prefix
                                          singularize('$1'))))              -- orders -> order
                                    SELECT 'Hello'                          as title, 
                                           'Updated ' || singularize($1) 
                                                      ||' successfully.'    as success,
                                           form_for('$1', row.from_json, $2)    as columns
                                    FROM row";
        postgres_rewrite POST PUT errors    $views_fieldset;
        postgres_rewrite POST PUT no_errors /$1/:id?flash=:success;

      }

      location ~* /([a-z0-9_-]+)/ {
        include '/Users/invizko/sites/data/auth.conf';

        # Index
        postgres_query   GET "SELECT * FROM $1 LIMIT 10";
        postgres_rewrite GET rows $views_orders;

        # Create
        postgres_query   POST  "WITH row as (                   
                                  SELECT from_json('$1',             -- INSERT into orders
                                    json_extract_path('$input',      -- json from order[] form prefix
                                      singularize('$1'))))           -- orders -> order
                                SELECT 'Hello'                       as title, 
                                       'Created ' || singularize('$1') 
                                                  ||' successfully.' as success,
                                       form_for('$1', row.from_json) as columns 
                                FROM row";
        postgres_rewrite POST errors $views_fieldset;
        postgres_rewrite POST no_errors /$1/:id/?flash=:success;

      }
    }

views_fields.html

<h1>{{title}}</h1>
{{#columns}}
	<dl>
	{{#type:string}}
		{{#value}}
			<dt>{{name}}</dt>
			<dd>{{value}}</dd>
		{{/value}}
	{{/type:string}}
	</dl>
{{/columns}}

views_fieldset.html

{{#action}}
	<form action="{{action}}" method="POST">
{{/action}}
{{^action}}
	<form action="/orders/" method="POST">
{{/action}}
<h1>{{title}}</h1>

{{#columns}}
	{{#error}}
		<span style="color: red">{{error}}</span>
	{{/error}}
	{{#is_editable}}
		{{#type:integer}}
			<label for="{{prefix}}_{{name}}_number">
				{{name}}:
			</label>
			<input type="number" id="{{prefix}}_{{name}}_number" name="{{prefix}}[{{name}}]" value="{{value}}" />
		<br>

		{{/type:integer}}

		{{#type:string}}
			<label for="{{prefix}}_{{name}}_text">
				{{name}}:
			</label>
			<input type="text" id="{{prefix}}_{{name}}_text" name="{{prefix}}[{{name}}]" value="{{value}}" />
		<br>

		{{/type:string}}

		{{#options}}
			123123123
			<label for="{{prefix}}_{{name}}_select">
				{{name}}:123123
			</label>
		{{/options}}

	{{/is_editable}}
	{{^is_editable}}
		{{#value}}
			<strong>{{name}}</strong>: {{value}}<br>
		{{/value}}
	{{/is_editable}}
{{/columns}}

<input type="submit" />
</form>

views_header.html

<head>
	<title>Postgres</title>

	<link rel="stylesheet" href="/stylesheets/style.css"
</head>

<body>
	<header>
		{{#biggles}}
			<aside class="personal">
			<img src="/">
			<a href="#" class="profile_link">{{biggles}}</a>
			<a href="#" class="sign_out_link">Sign out</a>
			</aside>
		{{/biggles}}


		<h1>Our site is great </h1>
	</header>

	{{#flash}}
		<div class="flash">{{flash}}</div>
	{{/flash}}

zpostgre_helpers.sql

CREATE OR REPLACE FUNCTION
view_for(text, jsonb DEFAULT '{}', text DEFAULT NULL) returns json language sql AS $$
  SELECT json_agg(result) FROM (

    -- Select columns
    SELECT  column_name                                      as name,
            v.value                                          as value,
            CASE WHEN position('character' in data_type) > 0
            THEN 'string'
            ELSE data_type END                               as type,
            character_maximum_length                         as maxlength,
            singularize($1)                                  as prefix,
            --(CASE WHEN position('_id' in column_name) > 0 and position('root_id' in column_name) = 0 THEN
            --   json_from(replace(column_name, '_id', ''), v.value::text::int)->'json_agg'
            -- END)                                            as options,
            (position('id' in column_name) = 0 and 
             position('version' in column_name) = 0) or NULL as is_editable

    -- turn selected data row into value column
    FROM
      INFORMATION_SCHEMA.COLUMNS columns


    -- Join values
    LEFT JOIN (
      SELECT j.key as key, j.value as value FROM jsonb_each($2) j
    ) v

    ON (v.key = column_name)

    where table_name = $1
  ) result;
$$;-- Turns DELETEs into INSERTS with deleted_at timestamp



-- insert data from json and return 
CREATE OR REPLACE FUNCTION from_json(relname text, reljson json)
  RETURNS jsonb AS
$BODY$DECLARE
 ret RECORD;
 inputstring text;
BEGIN
  SELECT string_agg(quote_ident(key),',') INTO inputstring
    FROM json_object_keys(reljson) AS X (key);
  EXECUTE 'INSERT INTO '|| quote_ident(relname) 
    || '(' || inputstring || ') SELECT ' ||  inputstring 
    || ' FROM json_populate_record( NULL::' || quote_ident(relname) || ' , $1) RETURNING *'
    INTO ret USING reljson;
  RETURN row_to_json(ret)::jsonb;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;