How to web-enable a single postgreSQL table

Overview of the rodakino directory structure

|-- app/

|    |-- authentication/

|    |-- database/

|    |-- roda_dbwindows/

|    |-- roda_windows/

|    |-- roda_frame/

|-- config/

|-- node_modules/

|-- sql/

|-- index.js

|-- package.json


For implementing a web interface for a postgres table, we just need the bold directories.


1. Create a new postgreSQL table

I use a table for zip codes for our example:

Create a new file mycrebas.sql in sql/, edit and save:

create table ZIPCODES (

   COUNTRY varchar(30),

   ZIPCODE varchar(10),

   CITY varchar(50),

   constraint PK_ZIPCODES primary key (COUNTRY, ZIPCODE)


grant select,insert,update,delete on ZIPCODES to test;


Open a shell and type:

$> cd ~/rodakino/sql

$> psql -U node node

node=# \i mycrebas.sql

node=# \d       # database should contain our table now

node=# \d ZIPCODES

node=# exit   # or use ctl-d instead


2. Create a new rodakino database window

$> cd ~/rodakino/app/roda_dbwindows

Create a new file rdbw_zipcodes.hbs for the rdbw here:

{{> roda_dbwindows/rdbw_zipcodes_css}}               <!-- include my stylesheet -->

<div id="rdbw_zipcodes">                                                       <!-- thats my name, must be unique among all rdbw's -->
  <div class="insert_button" rdbw="rdbw_zipcodes">Insert</div>         <!-- display an insert button which works on me -->
  <div class="delete_button" rdbw="rdbw_zipcodes">Delete</div>
  <div id="row_table">                                                                <!-- container for the rdbw, name must NOT be changed -->
    <div id="row_header">                                                          <!-- container for the headlines, name must NOT be changed -->
      <div class="r_header" >Country</div>
      <div class="r_header" >ZIP</div>
      <div class="r_header" >City</div>
    <div id="row_data">                                                             <!-- container for the data from postgres, name must NOT be changed -->
      <div id="row_template" class="roda_row", rownum="0" style="display:none">   <!-- template definition for one database row, name must NOT be changed -->
        <div class="roda_field"><input type="text" name="COUNTRY" maxlength="30" value=""/></div>
        <div class="roda_field"><input type="text" name="ZIPCODE"  maxlength="10" value=""/></div>

        <div class="roda_field"><input type="text" name="CITY"  maxlength="50" value=""/></div>
  <div id="query_header" style="display:none">                  <!-- container for the headlines of the query-by-example window name must NOT be changed -->
    <div class="q_header">Country</div>
    <div class="q_header">ZIP</div>
    <div class="q_header">City</div>
  <div id="query_template" class="query_row", rownum="0" style="display:none">         <!-- template definition for one row of the query window --> 
    <div class="q_field roda_field"><input type="text" name="COUNTRY" maxlength="50" value=""/></div>
    <div class="q_field roda_field"><input type="text" name="ZIPCODE"  maxlength="10" value=""/></div>
    <div class="q_field roda_field"><input type="text" name="CITY"  maxlength="30" value=""/></div>
  <div id="rowrows" style="display:none">No. %row% of %rows%</div>              <!-- display actual row and number rows retrieved -->


Create a new file rdbw_zipcodes_css.hbs for the stylesheet here:


  /* headlines of rdbw */

  .r_header {

    font-weight: bold;
    box-sizing: border-box;
    border: thin solid black;
    float: left;
    width: 33.3%;


  /* for query window */
  .q_header {
    float: left;
    width: 33.3%;

  .q_field {
    float: left;
    width: 33.3%;

  /* activate vertical scrollbar */
  #row_data {
    border:thin solid black;
    border-collapse: collapse;
    height: 300px;
    overflow-y: auto;

  /* stretch all inputs to 100% within their divs */
  input {
    width: 100%;
    box-sizing: border-box;
    background-color: transparent;

  /* describe the data fields */

  #rdbw_zipcodes .roda_row {
    min-height: 1.5em;

  .roda_field {

    float: left;

    width: 33.3%;




3. Create a new rodakino window

$> cd ~/rodakino/app/roda_windows

$> mkdir rw_zipcodes

$> cd rw_zipcodes

Create a new file index.js here:

module.exports = {
  init: require('./init')


Create a new file init.js here:

const passport = require('passport')

function initUser (app) {
  app.get('/rw_zipcodes', passport.authenticationMiddleware(), (req, res) => {
    res.render('roda_windows/rw_zipcodes/rw_zipcodes', {
      // dont use the default layout
      layout: false,

module.exports = initUser


Create a new file rw_zipcodes.hbs here:

<script type="text/javascript">
  // create instance of master dbwindow
  var rdbw_master = $("#rdbw_zipcodes").roda_dbwindow({
    sql: "select COUNTRY,ZIPCODE,CITY from ZIPCODES %WHERE_CLAUSE% order by 1,2",
    insSql: "insert into ZIPCODES values('%COUNTRY%','%ZIPCODE%','%CITY%')",
    updSql: "update ZIPCODES " +
              "set COUNTRY = '%COUNTRY%'," +
              "ZIPCODE = '%ZIPCODE%'," +
              "CITY = '%CITY%' " +
              "where COUNTRY = '%_COUNTRY%' " + // %_ ... means original value of db-field
              "and ZIPCODE = '%_ZIPCODE%' ",
    delSql: "delete from ZIPCODES " +
              "where COUNTRY = '%_COUNTRY%' " + // %_ ... means original value of db-field
              "and ZIPCODE = '%_ZIPCODE%' "
  function onDivLoad(myName) {
    // function is called on ajax success after load
    console.log('rw_roda_users.onDivLoad.1 myName: '+ myName);

    // i am the active roda_window. !! necessary for roda_menubar to work !!
    act_roda_window = myName;

    // resize the dialog

    // set master roda_dbwindow
    // call retrieve for master roda_dbwindow


<!-- HTML -->
<h4> Zipcodes</h4>

<!-- import Code of Master roda_dbwindow -->
{{> roda_dbwindows/rdbw_zipcodes }}


4. Declare the new rodakino window to the application

$> cd ~/rodakino/app

edit file index.js

add following line near the end



5. Add the rodakino window to the main menu

$> cd ~/rodakino/sql

edit file mycrebas.sql. This create a new menu level "My menu" and a new menu item for my zipcodes window within.


    values('mymenu','My menu','M','rodakino_menu',null,1005,'N');







execute the above statements in psql


6. Try it

$> cd ~/rodakino/

$> npm start


Browser: http://<ip-address>:3000

Login as user test, you see your new menu/rodakino window

Close browser, login as user node: you dont see your mymenu, because you did'nt define it in mycrebas.sql