couchdb_to_sql

Utility to listen to a CouchDB changes feed and automatically insert, update, or delete rows into an SQL database from matching key-value conditions of incoming documents.

couchdb_to_sql is heavily indebted to samlown's original couch_tap gem. We have added functionality needed for our particular use case, while still trying to keep it reasonably flexible and not too hardwired to the ember-pouch use case.

While CouchDB is awesome, business people probably won't be quite as impressed when they want to play around with the data. Regular SQL is generally accepted as being easy to use and much more widely supported by a larger range of commercial tools.

couchdb_to_sql will listen to incoming documents on a CouchDB server's _changes feed in continuous mode, and automatically update rows of the SQL database tables defined in the conversion schema. The changes feed uses a sequence number allowing synchronization to be started and stopped at will.

Sequel is used to provide the connection to the database. This library can also be used for migrations, which is important for frequently changing schemas.

couchdb_to_sql takes a simple two-step approach converting documents to rows. When a change event is received for a matching document definition, each associated row is completely deleted. If the change is anything other than a delete event, the rows will be re-created with the new data. This makes things much easier when trying to deal with multi-level documents (i.e. documents of documents) and one-to-many table relationships.

couchdb_to_sql requires a configuration or filter definition that will allow incoming document changes to be identified and dealt with. The configuration file can either be hand-written or generated dynamically. (For our particular use case with ember-pouch, we have chosen to generate it based on the Ember model metadata. The script for this is unfortunately not open source at this time.)

The following example attempts to outline most of the key features of the DSL.

# The couchdb database from which to request the changes feed
changes "http://user:pass@host:port/invoicing" do
  # # Optional flag which can be enabled to take advantage of Postgres 9.5's support for INSERT CONFLICT, e.g. upserts.
  # # Note: this only deals with the _couchdb_to_sql_sequences metadata table, not the actual CouchDB documents themselves.
  # upsert_mode

  # # Optional flag which can be enabled if ember-pouch is being used to populate the CouchDB database. ember-pouch uses a
  # # specially crafted format of the CouchDB documents, where all the data is placed in 'data' node and the 'id' follows a
  # # particular format. This flag makes couchdb_to_sql presume that all CouchDB documents for the given stream follow this format.
  # ember_pouch_mode

  # # Optional flag which can be enabled to enable a stricter mode, where processing will abort if an unhandled document is
  # # encountered.
  # fail_on_unhandled_document

  # # Optional path to a file containing a JSON array of sequences to skip. The 'seq' value of incoming documents will be compared
  # # to the values in this array.
  # skip_seqs_file 'skiplist.json'

  # The target database to which changes will be streamed.
  database "postgres://user:pass@localhost:5432/invoicing"

  # Simple automated copy, each property's value in the matching CouchDB document will be copied to the table field with the
  # same name.
  document 'type' => 'User' do
    table :users
  end

  document 'type' => 'Invoice' do

    table :invoices, :key => :invoice_id do

      # Copy columns from fields with different name
      column :updated_at, :updated_on
      column :created_at, :created_on

      # Manually set a value from document or fixed variable
      column :date, doc['date'].to_json
      column :added_at, Time.now

      # Set column values from a block.
      column :total do
        doc['items'].inject(0){ |sum,item| sum + item['total'] }
      end
    end
  end
end

Defines which CouchDB database should be used to request the changes feed.

After loading the rest of the configuration, the service will connect to the database using Event Machine. As new changes come into the system, they will be managed in the background.

The Sequel URL used to connect to the destination database. Behind the scenes, couchdb_to_sql will check for a table named couchdb_sequence that contains a single row for the current changes sequence id, much like a migration id typically seen in a Rails database.

As changes are received from CouchDB, the current sequence will be updated to match.

When a document is received from the changes feed, it will be passed through each document stanza looking for a match. Take the following example:

document :type => 'Invoice' do |doc|
  # ...
end

This will match all documents whose type property is equal to "Invoice". The document itself will be made available as a hash through the doc block variable.

document stanzas may be nested if required to provide further levels of filtering.

Each table stanza lets couchdb_to_sql know that all or part of the current document should be inserted into it. By default, the matching table's schema will be read and any field names that match a property in the top-level of the document will be inserted automatically.

One of the limitations of couchdb_to_sql is that all tables must have an id field as their primary key. In each row, the id's value will be copied from the _id of the document being imported. This is the only way that deleted documents can be reliably found and removed from the relational database.

CouchDB documents being deleted are not deleted in the SQL database, because this is typically not what you want to do from a data integrity/etc. point of view. Instead, it is marked as deleted.

For this to work, the following two columns must exist in the table (example given is from PostgreSQL):

fieldops_reports=# \d spare_parts
                       Table "public.spare_parts"
       Column       |           Type           |       Modifiers
--------------------+--------------------------+------------------------
 spare_part_id      | text                     | not null
 id                 | text                     |
 _deleted           | boolean                  | not null default false
 _deleted_timestamp | timestamp with time zone |

(spare_part_id is the primary key which will hold the CouchDB id. id holds the "Ember ID" in case you are using ember-pouch mode. _deleted* are the fields which indicate if the record is deleted, and if so, when it was marked as deleted.)

Run tests using rake, or individual tests as follows:

$ rake test TEST=test/unit/changes_test.rb

If you have disabled the "admin party" in CouchDB, you might have to manually specify the CouchDB URL. Like this:

$ COUCHDB_URL='http://admin:admin@127.0.0.1:5984/' bundle exec rake test

If you want to run tests towards a PostgreSQL database instead of CouchDB:

$ TEST_SQL_URL='postgres://localhost/couchdb_to_sql_test' bundle exec rake test

• SEQUEL_LOG_LEVEL=debug - set to enable logging of all SQL queries executed.

• Merge all relevant pull requests
• Bump the version in the VERSION file. Follow Semantic Versioning principles. Do not prepend the version with a v. You don't need to commit or push after this step, it gets done automatically by the next step.
• git release v1.0.x (brew install git-extras if you are missing the git release command.)
• bundle exec rake build release (builds the .gem file and pushes it to Rubygems.org)
• changelog-rs --latest to regenerate the changelog which can then be copy-pasted to the releases page. curl https://sh.rustup.rs -sSf | sh && cargo install changelog-rs if you don't have it installed. More info on its web page.