yamltodb – generate SQL statements to update a PostgreSQL database to match the schema specified in a YAML file
yamltodb [option...] dbname [spec]
yamltodb is a utility for generating SQL statements to update a PostgreSQL database so that it will match the schema specified in an input YAML formatted specification file.
For example, given the input file shown under dbtoyaml - Database to YAML, yamltodb outputs the following SQL statements:
CREATE SCHEMA s1; CREATE TABLE t1 ( c1 integer NOT NULL, c2 smallint, c3 boolean DEFAULT false, c4 text); CREATE TABLE s1.t2 ( c21 integer NOT NULL, c22 character varying(16)); ALTER TABLE s1.t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (c21); ALTER TABLE t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (c1); ALTER TABLE t1 ADD CONSTRAINT t1_c2_fkey FOREIGN KEY (c2) REFERENCES s1.t2 (c21);
yamltodb accepts the following command-line arguments (in addition to the Common Command Line Options):
Specifies the name of the database whose schema is to analyzed.
Specifies the location of the YAML specification. If this is omitted or specified as a single or double dash, the specification is read from the program’s standard input. However, if the --multiple-files option is used, that takes precedence.
Specifies that input should be taken from YAML specification files present in a two-level (metadata) directory tree. See Multiple File Output under dbtoyaml - Database to YAML for further details.
Compare only a schema matching schema. By default, all schemas are compared. Multiple schemas can be compared by using multiple -n switches.
Wrap the generated statements in BEGIN/COMMIT. This ensures that either all the statements complete successfully, or no changes are applied.
Execute the generated statements against the database mentioned in dbname. This implies the --single-transaction option.
When generating SQL, use delimited (quoted) identifiers around reserved words used as identifiers, e.g., a table named “order”. Normally, only identifiers with embedded spaces or other disallowed characters are quoted.
Given a YAML file named moviesdb.yaml, to generate SQL statements to update a database called mymovies:
yamltodb mymovies moviesdb.yaml
To generate the statements as above and immediately update mymovies:
yamltodb mymovies moviesdb.yaml | psql mymovies
yamltodb --update mymovies moviesdb.yaml
To generate the statements directly from the ouput of dbtoyaml (against a different database), with statements enclosed in a single transaction, and save the statements in a file named mymovies.sql:
dbtoyaml devmovies | yamltodb -1 mymovies -o mymovies.sql