Lightweight database federation in oil-and-gas production with SQL Relay

Lightweight open source tool consolidates access for lean development operations

Conflicting requirements, such as those related to security, performance, and economy, multiply the challenge of integration developments typical in programming in the oil-and-gas industry. One technique that can help ease the burden on slimmed-down development teams is to centralize database accesses through the open source SQL Relay product.


Cameron Laird (, Vice President, Phaseit, Inc.

Photo of Cameron LairdCameron Laird has developed data-oriented solutions throughout the oil-and-gas stack, from proprietary data-management system software for geophysical exploration-and-production operations, to design of schemata to support end users in administrative roles in refining and distribution. While he most relishes times on-site in a refinery or off-shore, back in the office Cameron also is a prolific author of articles for developerWorks and other professional publications, as well as the Smart Development blog. Cameron consults as vice president of Phaseit, Inc., which he co-founded, and can be reached at

13 April 2010

Also available in Chinese

Several of the trends at play in programming for the oil-and-gas sector—industrial consolidation; aging, outsourced, and slimmed-down IT staffs; expanded governmental reporting requirements; re-purposed and aging refineries and other facilities; and even the sectoral shift in the United States to natural gas production—work at cross purposes. It's typical for a division to have to report on its operations quickly at the same time as it assimilates an acquisition and loses senior IT developers. SQL Relay eases one small aspect of these conflicts, the one having to do with database access.

A decade ago, the focus of IT was often on massively ambitious enterprise-wide roll-outs. More typical now is development which looks to leverage and integrate legacy operations, often by way of Web-based applications, and to do it on a tight budget. At the same time, though, jobs must be completed with slimmed-down staff and beefed-up requirements for security and "auditability," because of federal or state statutes on data accountability. What looks to the end user like a single application could easily pull together separate databases maintained, for example, for:

  • Divisional human resources
  • Unit engineering
  • Compliance and security
  • Site financials

One common outcome of these complex connections is inefficiency in development, testing, deployment, and operation: Each staff programmer must program around peculiarities of each database manager, including security arrangements, proxying through different subnetworks, load-balancing, and so on. Think, for instance, what happens on the IT side when, as has happened so often lately, a domestic facility escapes negative refining margins by conversion to a terminal facility. A development team will likely be pulled together that is experienced in storage or distribution user interfaces, probably exposed through the browser. The members of the team have no background, though, in the existing data-storage infrastructure of the site, even though they're expected to build atop it. This inevitably leads to a combinatoric explosion of database details needed by each developer or tester before he or she can begin to work with meaningful results.

"Client" in an SQL Relay architecture

Warning! The word "client" might surprise you in this article. From the viewpoint of an RDBMS, SQL Relay is a client program. Part of the configuration of an SQL Relay instance, for example, might involve licensing or installation of a specific client library for a particular RDBMS.

SQL Relay is a kind of middleware, though, so, from the standpoint of end-user applications as this article describes them, the application server is itself an SQL Relay client. Think with me through one concrete example I maintain: A Web application effectively reports on data from an Oracle database. To achieve this, a Web browser submits a client-side request to a Zope application server. The Zope instance passes the request through an SQL Relay client "adapter," which communicates with a central SQL Relay server. The SQL Relay server itself relies on an in-process Oracle client-side library to network with the off-premise database backing the application.

Conclusion: While this article uses "client" and "server" rigorously, middleware inevitably introduces the potential for confusion in trying to capture a three-tier architecture with only two labels.

With the Open Source SQL Relay product, though (see Resources, below, for details), all database access can be managed in a unified and consistent way. An individual programmer, for example, need not install separate command-line clients for Sybase, DB2, Postgres, Oracle, and so on, as part of the toolchain for his or her daily work (see sidebar). Instead, a single lightweight, unencumbered sqlrsh is enough to connect to the whole range of back ends that SQL Relay manages.

For sophisticated problems, of course, there's still enormous value in the developer toolboxes specific to each database manager. My team's experience, though, is that there are many situations in which an individual programmer needs consistency, simplicity, and reliability more than a "deep" development environment. SQL Relay allows one "liaison" from development to configure all database accesses in a standard way that is convenient for the team as a whole and collapses the combinatorics, giving the team the chance to focus on the algorithms and interfaces where it truly adds value.

Lightweight tool

Understand clearly that, for most organizations, both the costs and benefits of SQL Relay will be modest. Developers will eventually get their work done even without centralized access configuration, plenty of other products provide connection pooling, and so on. SQL Relay is dispensable, and not a radical "game-changer" in the way an Enterprise Resource Planning (ERP) or Customer Relations Management (CRM) system is.

On the other hand, it is so easy to install and begin to use SQL Relay, and it is so efficient and reliable, that it invites casual experimentation. Start by setting up one connection to one database source; you can continue to use all of your normal access methods at the same time as you stir SQL Relay into the mix. Incrementally move developers to an SQL Relay connection and away from reliance on personal accounts with the native database. You'll soon have an idea how SQL Relay works in your circumstances.

Example use cases

Match impedance to long-cycle database administration

Databases are serious things: They're vaults of what might be an organization's most precious assets. For this reason, access to them is and should be tightly controlled.

This fact interferes with developers' "agility" and impatience, of course. Database administration might have a week-long process for adding one new account, even if the purpose of that account is to allow a new maintenance developer to test a five-minute fix in an existing application.

SQL Relay can help bring both sides together. Arrange for a single "development" account in the native database; that's an arrangement likely to make sense to the database administrators. With SQL Relay, though, you can expose that access to as many or as few of your team members as you choose—SQL Relay controls access by, among other methods, client account and client IP address. You can open or close access at developer speed.

Missing native clients

Suppose you host your Web application on a "minority" operating system; you might rely on OpenBSD for its security, for example. Your next application involves datasources originally developed with Microsoft® Access, though. How do you effectively ship data from the latter to the former?

As it happens, there are several distinct possibilities. For my team, however, the quickest way to resolve the question and return developers to their daily work is to interpose SQL Relay. SQL Relay connects to all of our datasources and has good client libraries on all operating systems.

Isolate teams from administrative "churn"

Especially when dealing with legacy systems, we've sometimes had surprises in database access: catastrophic loss of hardware whose recovery involved unexpected drama, managerial reorganizations in database systems, and so on. SQL Relay has helped us get through these episodes. Rather than each developer having to track changes in database names, password resets, and so on, one SQL Relay "liaison" takes responsibility for maintenance of a consistent view, from the developers' perspective.

Applicability: platforms, licensing, and related considerations

While almost all of my own use of SQL Relay has been on Debian-based Linux®, SQL Relay apparently works quite well on all major Unix®es. For-profit company firstworks takes the lead on development and maintenance of the product (see Resources). I've found firstworks responsive when presented with fault reports.

Although it is also possible to host SQL Relay under Windows®, this alternative is less energetically supported. It requires Cygwin, moreover. SQL Relay itself is written in C++, but also builds in native client application programming interfaces (APIs) for C, Java™, Perl, Python, the Zope application framework, Ruby, PHP, and Tcl. Drivers are also available for such language-specific abstraction libraries as Perl's DBI.

SQL Relay is distributed under the General Public License (GPL). Roughly, this means there's no charge and no restriction on use within an organization.

Currently supported database sources include DB2, Interbase, Jet, mSQL, MySQL, PostgreSQL, ODBC, Oracle, SQLite, SQL Server, and Sybase.

Technical alternatives

Several products advertise features that overlap those of SQL Relay. There are, for example, specific libraries that manage connection pooling within a specific development language. These tools are often very lightweight—solving the problems of a far narrower niche than SQL Relay's—and correspondingly limited in their capabilities.

The IBM DB2® database manager takes an opposite approach: With its "federation" mechanisms, it unites access to a range of database back ends so that they're all uniformly viewed as DB2 objects. DB2 gives fine-grained control over performance and security characteristics of database access—and demands correspondingly careful attention. For a demanding production environment, DB2 provides all the advantages of enterprise-level support availability, high-end scalability, complete documentation, and so on, that keep SQL Relay in the latter's "lightweight" and "agile" niche.


SQL Relay has other faults and limitations. It is not perfect software, of course, even within the bounds of its goal. Specific releases have had such difficulties as:

  • A parsing error that left configuration files with only a single database connection unusable
  • A segfault that resulted when a specific logfile had wrong permissions
  • Mishandled money results for certain vendor-specific built-in database types
  • A poorly reproducible, sporadic error on complex Oracle queries
  • Default settings for Oracle retrievals that used far too much memory for our circumstances
  • Limits on nesting of Sybase queries when using the no-charge client Sybase library

A list of errors that long will alarm some readers. Our own experience is that workarounds have been relatively painless for our SQL Relay issues, and the benefits of SQL Relay far exceed the effort needed to identify and implement them. Successive releases do fix errors that matter to us, and, of course, the product as a whole is open source, so it's straightforward to correct at least some of the errors for ourselves.

We almost always run SQL Relay on dedicated virtual machines, to match our security requirements. If mishandled, SQL Relay creates a new single-point-of-failure weak-link in an application architecture, one that consumes extra CPU cycles and introduces delays and errors in database retrieval. For us, though, SQL Relay improves overall performance, simplifies development, testing, and deployment, and strengthens our security model.

There's actually much more to SQL Relay than this introduction can cover, including such topics as:

  • Use of SQL Relay with replicated databases
  • Load-balancing
  • Security refinements such as record-level locking
  • Configuration tuning for performance

The main goal of this article, though, is to illustrate how simple and appropriate it might be for you to begin to use SQL Relay on your own. When it begins to pay off for you, you'll be in a better position to judge how you can use the advanced features of SQL Relay.


SQL Relay has the potential to consolidate and streamline typical architectures involved in application development for oil-and-gas operations. It's a lightweight tool that centralizes management of database connection pooling, accounting, and security.



  • SQL Relay is a SourceForge project which provides "... database connection pooling, proxying and load balancing ..." While SQL Relay is open source, much of its maintenance is undertaken by for-profit firstworks.
  • "Fundamentals of IBM DB2 Federated Server and Relational Connect" (PDF) is a manual to use DB2 as a federating database which supports such back ends as Oracle, Sybase, and SQL Server.
  • Zope is a Python-oriented application server for which SQL Relay offers an adapter.
  • The developerWorks Web development zone specializes in articles covering various Web-based solutions.
  • The developerWorks Open source zone specializes in extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM products.

Get products and technologies



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Web development on developerWorks

Zone=Web development, Open source
ArticleTitle=Lightweight database federation in oil-and-gas production with SQL Relay