Quickly Creating DB namespaces with shell, emacs, and init.sql
Table of Contents
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-linesCREATE, thereby keeping only line “CREATE TABLE” lines - Execute
replace-regexpwith.*?"\(.*?\)".*->\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
I am now free to remove template.clj and maketables.sh and am all set.
