April 03, 2020

My project design includes creating a file/namespace for each database table, with a suite of CRUD operations applying to each table. The end result is that I have functions like db.my-table/CREATE , READ , UPDATE , and DELETE available for each table. The strategy is as follows:

Establish the template clj file that has all your CRUD operations Obtain all table names (e.g. from init.sql ) Use a script to create a matching clj file based on template (1) customized to refer to the tables from (2)

1. Template clj files

The following exists in the directory as template.clj , representing the boiler-plate for each table namespace.

( ns flats.db.TABLE ( :require [flats.db.core :as db])) ( def CREATE (partial db/CREATE :TABLE )) ( def READ (partial db/READ :TABLE_undeleted )) ( def READ-ALL (partial db/READ :TABLE )) ( def UPDATE (partial db/UPDATE :TABLE )) ( def DELETE (partial db/DELETE :TABLE )) ( def CLONE (partial db/CLONE :TABLE )) ( def PERMANENT-DELETE (partial db/PERMANENT-DELETE :TABLE ))

2. Obtain table names

In the database architecting portion of my development process I have already created an init.sql that includes all my table names, for example:

DROP TABLE IF EXISTS " users " CASCADE ; CREATE TABLE " users " ( " uuid " UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY , " deleted " TIMESTAMP DEFAULT NULL , " updated " TIMESTAMP DEFAULT NULL , " created " TIMESTAMP DEFAULT CURRENT_TIMESTAMP , " info " JSONB , " email " TEXT UNIQUE , " username " TEXT , " pass " TEXT ); DROP TABLE IF EXISTS " user_capabilities " CASCADE ; CREATE TABLE " user_capabilities " ( " uuid " UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY , " deleted " TIMESTAMP DEFAULT NULL , " updated " TIMESTAMP DEFAULT NULL , " created " TIMESTAMP DEFAULT CURRENT_TIMESTAMP , " info " JSONB , " name " TEXT );

Gather all the table names from this file as you please; in emacs it is easily done as follows:

Copy the whole file somewhere you can edit it Execute M-x keep-lines CREATE , thereby keeping only line “CREATE TABLE” lines Execute replace-regexp with .*?"\(.*?\)".* -> \1 , which will result in nothing but the table name included Use replace-string to remove the new-lines and replace them with spaces: (replace-string) C-q C-j <SPC> Copy that line into the arr= line of the forthcoming bash script, saving.

3. Complementary bash script

Gaining my list of tables from the above section, I use the following bash script:

#!/bin/bash # maketables.sh -- create a namespace file based on a template for every table listed below, and replace phrases in the template appropriately arr = ( users user_capabilities users_capability_assoc sources source_tags source_tags_assoc material material_tags material_tags_assoc products product_tags product_tags_assoc global_events global_entities product_global_entities product_global_events global_event_tags global_event_tags_assoc global_entity_tags global_entity_tags_assoc email_templates email_log ) ; for f in ${ arr[*] } do filename = " $f .clj " cp template.clj $filename; sed -i " s/TABLE/ $f /g " $filename; sed -i "s/_/-/g" $filename; done

Upon completion, I have a directory as follows:

core.clj email_log.clj email_templates.clj global_entities.clj global_entity_tags_assoc.clj global_entity_tags.clj global_events.clj global_event_tags_assoc.clj global_event_tags.clj maketables.sh material.clj material_tags_assoc.clj material_tags.clj migratus.clj product_global_entities.clj product_global_events.clj products.clj product_tags_assoc.clj product_tags.clj sources.clj source_tags_assoc.clj source_tags.clj template.clj user_capabilities.clj users_capability_assoc.clj users.clj