An interpreter for SQL structure definitions in YAML (YamSql)
HamSql is a software that parses SQL structures defined in a YAML based language and deploys them on PostgreSQL servers. It allows to maintain PostgreSQL projects in a form more similar to other programming languages.
In contrast to the CREATE OR REPLACE FUNCTION approaches, residual structures are deleted, column properties are deleted without explicit definition of the migration and the ordering imposed by dependencies is resolved automatically.
HamSql is on hackage, which means you can build it with cabal install hamsql. Please note the build requirements and details at the bottom. HamSql binaries for Linux amd64 are available as build artifacts from our build server.
HamSql can be used for
- Neat SQL development with clearer versioning via "one object one file" principle
- More flexible development with features like function and table templates
- On site deployments of SQL structures
- Off-line computation of upgrade strategies for known status quo
- Documentation generation of SQL structures and APIs
hamsql installcommand that deploys the defined structurehamsql upgradecommand that updates existing structures- Per default no data are at risk during upgrade (no column/table deletion)
- Overwrite via
--permit-data-deletionpossible - SQL command ordering that avoids dependency conflicts
- Complicated dependencies are resolved via trial and error
hamsql doccreates a documentation of the complete sql structure- Custom templates can be provided using doctemplates known from pandoc
- The build-in template creates Sphinx and Read the Docs ready .rst files
- Code basis tailored for the support of many SQL features
- Basic documentation of the YamSql Language
- Support for views and triggers
- Warn about name conflicts for SQL objects before deployment
- Output defining YamSql file for each type of error
Those are all things on the radar but the exact requirements are unclear or the workforce is missing.
- Support for several other PostgreSQL features
- Stable definition of YamSql
- Support for renaming tables and columns
- Covering other strategies required for upgrade
- Unit and integration test as part of YamSql
- Define scenarios that are loaded into the database
- Define test and expected result for a function for a scenario
- Define operations with expected outcome (to tests triggers etc.)
The example project below could be deployed via
hamsql install -c postgresql:///dbname
Later changes can be pushed via
hamsql upgrade -c postgresql:///dbname
The default documentation can be written to docs/ via
hamsql doc
You can have a look at the output rendered via Sphinx.
Those are the YamSql files for the project:
# setup.yml
schemas:
- math
schema_dirs:
- ./schemas# schema/math/schema.yml
name: math
description: |
Some basic math in SQL---
# schema/math/function.d/factorial.plsql
name: factorial
description: |
Factorial function using the
``WITH RECURSIVE`` SQL feature.
Logical definition::
f(0) = 1
f(n) = n * f (n - 1)
parameters:
- name: p_n
type: int
returns: int
---
RETURN (
WITH RECURSIVE t AS (
SELECT 1 AS f, 0 AS n
UNION ALL
SELECT f * (n + 1), n + 1 FROM t
)
SELECT f FROM t WHERE n=p_n LIMIT 1
);---
# schema/math/function.d/erf.py
name: erf
description: |
Gauss error function
This function is a wrapper for the
Python 3 implementation.
parameters:
- name: x
type: float
returns: float
language: plpython3u
---
import math
return math.erf(x)
To completely build HamSql from source
apt install make ghc cabal-install libpq-dev happy
make
make install
To avoid compiling all the dependencies you can use the following set of debian packages instead of the above ones
apt install \
make \
ghc \
cabal-install \
libghc-aeson-dev \
libghc-file-embed-dev \
libghc-network-uri-dev \
libghc-optparse-applicative-dev \
libghc-pandoc-dev \
libghc-postgresql-simple-dev \
libghc-text-dev \
libghc-unordered-containers-dev \
libghc-yaml-dev