A Database.com/Force.com Foreign-Data Wrapper for PostgreSQL

Dan Farina, a developer at Heroku, recently clued me in to Multicorn, a PostgreSQL 9.1+ extension for writing foreign-data wrappers in Python. Foreign-data wrappers are a mechanism for exposing external data as PostgreSQL tables, allowing that data to be queried as if it were sitting in PostgreSQL just like any other table. That little nugget of information, and Josh’s Insanely Simple Python REST Script from earlier last week, inspired me to write my first Python script – a Multicorn foreign-data wrapper (FDW) for database.com (compatible with Force.com, of course, since they share the same API).

The README has full instructions, but, in summary, once you have PostgreSQL 9.1 or higher, Python and Multicorn all installed, you can create a Remote Access app in database.com/Force.com, build the database.com FDW, install it in the system-wide Python distribution and start using it. We’ll pick up the story here assuming you have completed the steps mentioned above. First, create the Multicore extension and a ‘foreign server’ in your database:

CREATE EXTENSION multicorn;

CREATE SERVER multicorn_force FOREIGN DATA WRAPPER multicorn
OPTIONS (
 wrapper 'forcefdw.DatabaseDotComForeignDataWrapper'
);

Now you can create a table. You can list any number of fields from a database.com sobject, but field names are (currently) case-sensitive and must be quoted:

CREATE FOREIGN TABLE contacts (
 firstname character varying,
 lastname character varying,
 email character varying
) SERVER multicorn_force OPTIONS (
 obj_type 'Contact',
 client_id 'CONSUMER_KEY_FROM_REMOTE_ACCESS_APP',
 client_secret 'CONSUMER_SECRET_FROM_REMOTE_ACCESS_APP',
 username 'user@domain.com',
 password '********'
);

At this point, you can query the foreign table as if it were any other PostgreSQL table. Note that FDWs are read-only, so you won’t be able to create, update or delete records.

postgres=# SELECT email FROM contacts WHERE lastname LIKE 'G%';
NOTICE:  SOQL query is SELECT lastname,email FROM Contact WHERE lastname LIKE 'G%'
       email       
-------------------
 rose@edge.com
 jane_gray@uoa.edu
 agreen@uog.com
(3 rows)

postgres=# SELECT favorite_color FROM example
 JOIN contacts ON example.email=contacts.email
 WHERE contacts.firstname = 'Rose';
NOTICE:  SOQL query is SELECT lastname,email,firstname FROM Contact WHERE firstname = 'Rose'
 favorite_color
----------------
 Red
(1 row)

Currently, the database.com FDW is very much prototype code. The main limitation right now is that the amount of data that you can read from database.com is somewhat limited, since the JSON response is parsed into a Python object in memory. If this looks interesting, then let me know in the comments and I’ll take a look at parsing the database.com response as a stream. Alternatively, if you’re a Pythonista and you’re interested in lending a hand, feel free to fork the project on Github and get to work!

UPDATE – the FDW now uses the yaml-py module to parse the JSON stream without reading it all into memory at once, and field names are no longer case sensitive.

Published
November 21, 2011
Topics:

Leave your comments...

A Database.com/Force.com Foreign-Data Wrapper for PostgreSQL