This extension allows application schemas to be managed easily, both during development and deployment.
This extension provides
migrate_from_fs function that executes SQL migrations from a file system (provided by the
It returns a set of
text with each element being the file name executed.
The above invocation is most useful for development environment or deployment that is done with the backing of a local file system. In the near future, more file systems will be added, and that will facilitate more ergonomic scenarios.
Can't define a new filesystem?
You can specify
path optional parameter to indicate where the migrations reside
on the file system:
This function will recursively find all files with
.sql extension and apply them ordered by path name, excluding those that were already applied before. For this purpose, it maintains the
|name||text||Migration (file) name|
|migration||text||The source code of the migration|
|applied_at||timestamp||Time of migration application 1|
For certain types of schema objects , it is possible to reload their contents without having to create a migration every time they change (which is fairly suboptimal, especially when it comes to tracking their changes in a version control system.) The types supported are:
This extension provides
load_from_fs function that will reload all such
objects from a local on a file system (provided by the
Its return type and parameters are currently identical to those
In order to avoid loading particular files that match a language or a tool
filename pattern, one can put
omni_schema[[ignore]] somewhere inside such
file (for example, in a comment) and
omni_schema will not load it.
Object reloading functionality allows one to load functions from '.sql' files which can contain SQL or PL/pgSQL functions defined verbatim:
Such files can contain multiple function definitions.
One can note, however, that SQL or PL/pgSQL is not always the best fit for a particular problem. This is reflected in the fact that Postgres has an ecosystem of other programming languages. However, writing code in those languages inside of SQL files is a sub-par development experience: syntax highlighting, auto-completion may not work; external tools that work with this languages are unaware of this embedding technique.
To address this, this extension provides extensible support for custom languages.
There are two components to this:
- in-file function signature directive (conceptually similar to shebang)
omni_schema.languagestables that defines mapping of languages
The directive part is pretty simple: anywhere in the file, typically a comment
you can put a snippet that looks like this, enclosed within
The extension is syntax-agnostic, so it'll look for this type of line anywhere,
comments, or code. It will then match the extension of the file to the language
and append the given
create function line with an
language ... as construct and pass the contents of the entire file
In the future, we want to be able to provide a more sophisticated
functionality to supported languages, like allowing to define multiple
functions per file, use native language annotation/type systems to
infer the SQL function signature or detect language when file extensions
are ambiguous (like
.pl for Perl and Prolog).
Currently supported languages:
- SQL and PL/pgSQL (
- Python (
- Perl (
- Tcl (
- Rust (
If an extension required for the support of a language is not installed, files in that language will be skipped and a notice will be given, similar to this one:
The support for languages is configurable through
|file_extension||text||Filename extension without the preceding
|language||name||Language identifier to be used
|extension||name||Extension that implements the language, if required|
The timestamp defaults to
now()which means that migrations applied in the same transaction all get the same value of
applied_at, which can be used for grouping them together. ↩
This means you can only define one function per file at the moment. ↩
SQL language is always supported, even if corresponding entry is removed from
omni_schema.languages. This behavior may change in the future. ↩