www.voj-tech.net


vt-sqlinit

Synopsis

vt-sqlinit is a really simple Bash script allowing for convenient initialisation of relational databases using SQL files organised in a prescribed directory structure.

The files are placed in directories which represent tables of the initialised schema. Each directory can contain four different SQL files. The script first executes all the create.sql files, second it executes all the data.sql files, third it executes all the foreign_keys.sql files and last it executes all the triggers.sql files found across all the directories, thereby avoiding foreign key violations and unwanted trigger executions whilst keeping a separation between the DDL and DML files for each table. There is no need to define the order in which tables should be created or populated with data.

Code Example

#!/usr/bin/env bash

mkdir countries cities

cat > countries/create.sql <<'EOF'
CREATE TABLE countries (
    id integer PRIMARY KEY,
    name text,
    code text
);
EOF

cat > cities/create.sql <<'EOF'
CREATE TABLE cities (
    id integer PRIMARY KEY,
    country_id integer NOT NULL,
    name text
);
EOF

cat > cities/foreign_keys.sql <<'EOF'
ALTER TABLE cities ADD CONSTRAINT cities_country_id_fkey
FOREIGN KEY (country_id) REFERENCES countries (id);
EOF

echo "INSERT INTO countries (id, name, code) VALUES (1, 'Czechia', 'CZ');" > countries/data.sql
echo "INSERT INTO cities (id, country_id, name) VALUES (1, 1, 'Prague');" > cities/data.sql

export sqlexec='psql -f %f my_database'
vt-sqlinit.sh

# Output:
# 
# CREATE TABLE
# CREATE TABLE
# INSERT 0 1
# INSERT 0 1
# ALTER TABLE

Motivation

I was initially using the script as part of a web project to aid deployment. It can however be used with any project involving a relational SQL database and so the script is now distributed as a standalone project.

Installation

Prerequisites

It is required that your environment features reasonably new versions of:

  • the find command line utility
  • the GNU core utilities
  • Bash

It should be noted that the script has been tested using findutils 4.6.0 (provides the find utility), coreutils 8.30 and Bash 4.4.23 on Fedora 29.

Installation Steps

No actual installation is needed. To start using the script follow these steps:

  1. Download a ZIP file with the required version of the script from this page. Most likely you want the latest version.

  2. Unpack the downloaded file and add the execute mode bit to the vt-sqlinit.sh script file mode bits.

    unzip vt-sqlinit-0.8.2.zip && chmod +x vt-sqlinit-0.8.2/vt-sqlinit.sh
    
  3. Optionally copy the script to a directory which is in your PATH environment variable for convenience. This might be the ~/bin directory for example.

    cp vt-sqlinit-0.8.2/vt-sqlinit.sh ~/bin
    

Reference

Usage

export sqlexec=COMMAND
[...]
./vt-sqlinit.sh [DIRECTORY...]

Initialise relational database(s) by executing SQL files in DIRECTORY(ies) (the current directory by default) using COMMAND in a logical sequence.

Description

For each specified directory vt-sqlinit initialises a database by executing SQL files found within the directory following a prescribed directory structure and filenames. It looks for files with the names create.sql, data.sql, foreign_keys.sql and triggers.sql, in this order, placed directly in direct subdirectories of the currently processed directory. First all create.sql files are executed, second all data.sql files are executed and so on. The execution is done using the sqlexec environment variable which is a shell command format and is required to contain the '%f' substring. All occurrences of the string '%f' are replaced with the full SQL filename before executing the resulting command.

Environment Variables

sqlexec is a required variable and provides the shell command format to execute for a given SQL file. The variable is required to contain the '%f' substring. All occurrences of the string '%f' in the format are replaced with the full SQL filename before executing the resulting command.

Exit Status

Typically the vt-sqlinit.sh script exits with the status of the last command which is to remove a temporary file used internally by the script. This is 0 if it succeeds, otherwise it is a value greater than 0. However the script exits with status 1 if the sqlexec environment variable is not set or does not contain the '%f' substring.

Bugs

Suggestions and bug reports are more than welcome at voj-tech@voj-tech.net.