An example of using sqitch with cross-project dependencies

File this as yet another post of something I couldn’t find when searching the internet. I recently started using sqitch and despite the horrible spelling (dude, my brain always puts ‘u’ after ‘q’; not cool), the tool is incredibly helpful to bring order to my chaotic database mis-management practices.

This post isn’t about sqitch itself—there are lots of tutorials available for that—but rather about using sqitch to manage dependencies across projects. When learning sqitch I made a big project and dumped all of the deploy/revert/verify/test rules for each table/schema/function I needed for part of a database. Now that I have a little bit of a clue, I’m moving towards smaller, do-one-thing packages. But to enable that, I had to figure out how to enable cross-project dependencies in sqitch.

There isn’t an example that I could find anywhere, so I just hacked on the sqitch.plan file until things worked.

This is the original plan from the monolithic project. This snippet first adds a schema, then a counties table, then a city abbreviations table. The cities are located inside counties, and there are links between the two tables, so the cities sql needs to depend on the counties, and both need to depend on the schema.

%syntax-version=1.0.0
%project=ge0coding
%uri=git@example.com/hpms_geocode

appschema 2016-02-02T20:56:11Z James E. Marca <james@example.com> # Add schema for geocoding work.
counties_fips [appschema] 2016-02-02T23:23:13Z James E. Marca <james@example.com> # Add counties_fips table.
city_abbrevs [appschema counties_fips] 2016-02-04T17:57:02Z James E. Marca <james@example.com> # Add city abbreviations.

Splitting this into three projects, one for the schema, one for counties, and one for cities:

First, after initializing and adding for the geocode_schema package, the sqitch.plan looks like:

%syntax-version=1.0.0
%project=calvad_db_geocode_schema
%uri=git@example.com/a/jmarca/calvad_db_geocode_schema

geocode_schema 2016-03-16T16:30:54Z James E. Marca <james@example.com> # add schema for geocoding

Now when creating the county package, when I add the new sql I have to declare its dependency on the geocoding schema package:

sqitch add county_fips --requires calvad_db_geocode_schema:geocode_schema -n 'county_fips table'

Unlike in the sqitch tutorials, here the project-specific dependency has the form “project_name:change_name” instead of just “change_name”.

This creates the plan file something like the following:

%syntax-version=1.0.0
%project=calvad_db_county
%uri=git@example.com/a/jmarca/calvad_db_county

county_fips [calvad_db_geocode_schema:geocode_schema] 2016-03-16T17:02:35Z James E. Marca <james@example.com> # county_fips table

As usual, after slotting in a non-trivial deploy/county_fips.sql, etc, if I simply attempt to deploy this new addition to a database it will fail.

james@emma calvad_db_county[testingsqitch]$ sqitch deploy db:pg:sqitchtesting
Adding registry tables to db:pg:sqitchtesting
Deploying changes to db:pg:sqitchtesting
Missing required change: calvad_db_geocode_schema:geocode_schema

So the acid test: deploy the calvad_db_geocode_schema:geocode_schema project:

james@emma calvad_db_county[testingsqitch]$ cd ../calvad_db_geocode_schema/           
james@emma calvad_db_geocode_schema$ sqitch deploy --verify db:pg:sqitchtesting
Deploying changes to db:pg:sqitchtesting
  + geocode_schema .. ok
james@emma calvad_db_geocode_schema$ cd ../calvad_db_county/                   
james@emma calvad_db_county[testingsqitch]$ sqitch deploy --verify db:pg:sqitchtesting
Deploying changes to db:pg:sqitchtesting
  + county_fips .. ok

It worked!

Popping into the database and looking at the sqitch tables is also instructive:

psql (9.4.5)
Type "help" for help.

sqitchtesting=# \dt sqitch.
           List of relations
 Schema |     Name     | Type  | Owner 
--------+--------------+-------+-------
 sqitch | changes      | table | james
 sqitch | dependencies | table | james
 sqitch | events       | table | james
 sqitch | projects     | table | james
 sqitch | releases     | table | james
 sqitch | tags         | table | james
(6 rows)

sqitchtesting=# select change_id,change,project,note from sqitch.changes ;
                change_id                 |     change     |         project          |           note           
------------------------------------------+----------------+--------------------------+--------------------------
 f0964df0e223700ad34d9bd50bd48a8cde14d0f5 | geocode_schema | calvad_db_geocode_schema | add schema for geocoding
 e4f6cae819e3c6753518dac9c4922c18853f6d88 | county_fips    | calvad_db_county         | county_fips table
(2 rows)

sqitchtesting=# \d sqitch.projects
                            Table "sqitch.projects"
    Column     |           Type           |             Modifiers              
---------------+--------------------------+------------------------------------
 project       | text                     | not null
 uri           | text                     | 
 created_at    | timestamp with time zone | not null default clock_timestamp()
 creator_name  | text                     | not null
 creator_email | text                     | not null
Indexes:
    "projects_pkey" PRIMARY KEY, btree (project)
    "projects_uri_key" UNIQUE CONSTRAINT, btree (uri)
Referenced by:
    TABLE "sqitch.changes" CONSTRAINT "changes_project_fkey" FOREIGN KEY (project) REFERENCES sqitch.projects(project) ON UPDATE CASCADE
    TABLE "sqitch.events" CONSTRAINT "events_project_fkey" FOREIGN KEY (project) REFERENCES sqitch.projects(project) ON UPDATE CASCADE
    TABLE "sqitch.tags" CONSTRAINT "tags_project_fkey" FOREIGN KEY (project) REFERENCES sqitch.projects(project) ON UPDATE CASCADE

sqitchtesting=# select * from sqitch.projects;
         project          |                           uri                     |          created_at           |  creator_name  |      creator_email      
--------------------------+---------------------------------------------------+-------------------------------+----------------+-------------------------
 calvad_db_county         | git@example.com/a/jmarca/calvad_db_county         | 2016-03-16 10:38:05.402549-07 | James E. Marca | james@example.com
 calvad_db_geocode_schema | git@example.com/a/jmarca/calvad_db_geocode_schema | 2016-03-16 10:38:14.244119-07 | James E. Marca | james@example.com
(2 rows)

Actually I don’t like the design of the projects table at all. In my opinion, the unique key for projects should be the URI, not the project name. That quibble aside, it is clear that sqitch can indeed use dependencies that are defined in external projects.

Now the next step for me is to wire this up inside of npm to make npm install pull down sqitch dependencies from the sqitch URI and then deploy/verify them, so that the package is ready for its own deploy/verify/test dance.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s