Driver development

Background

This page is to describe goals and guidelines for developing PostgreSQL drivers based on libpq for various host languages.

Note: Right now this is based on my (Jeff Davis's) experiences developing ruby-pg, but I hope that others add comments here as well.

Project

Developing a driver is a project that can involve more than just code:

Testing (should be able to test fully on GNU/Linux, Mac, and windows)
Build system
Project page
Mailing list(s)
Releases
Bug reporting of some kind (even if just mailing lists)
Work with language and framework communities to ensure that the driver is supported.
Be nice to people in those communities so that they feel comfortable developing against PostgreSQL.

Goals

Provide all capabilities of libpq, including parameterized queries, non-blocking calls, COPY support, etc.
Provide stable, reliable interface
Be portable (to multiple platforms, multiple versions of the host language, and multiple versions of PostgreSQL)
Handle low-level issues that have clear solutions, such as text encoding
Translate errors into the host language usefully (e.g. turn them into exceptions)
Match language style to a reasonable extent

Non-Goals

Not meant to be database-agnostic
Not meant for non-libpq PostgreSQL drivers (like the JDBC driver)
Not meant to be an ORM
Not meant to be creative, clever, or anticipate what "most users want to do"

The above issues are all important, but they should be solved by another layer (e.g. ActiveRecord), or by optional functions provided by the driver that aren't necessary for complete libpq functionality.

Guidelines

Supported Versions

For a driver, ideally many versions of both the host language and PostgreSQL would be supported. However, libpq has changed substantially over the years, so the build system, testing, code complexity, and conditionally-compiled code all become a significant burden quickly. It seems most reasonable to stick to the PostgreSQL Release Support Policy. If someone needs to connect to an old server, they can download an old version of the driver.

What versions of the host language to support is more dependent on the language. Choose the versions that are reasonable to support given the developer resources available.

Wrapping libpq

It's important to stick to the libpq API closely and expose all libpq functionality in the driver's API. It's tempting to try to adapt it to the host language in creative ways, but that is likely to cause two problems:

Useful libpq functionality will be left out.
Creativity has no end, so there will be endless tweaks and revisiting of the API.

Creativity is important, but it should be done above the driver level (e.g. SQLAlchemy, ActiveRecord, etc.).

That being said, small conveniences that make the API calls fit more fluidly in the language are a good idea. It should be easy to pass parameters so that PQexecParams (and others) can be used, avoiding SQL injection risks. The important thing is to maintain close to a one-to-one mapping between libpq and the driver's API, and to provide all of the functionality of libpq.

For example, in ruby-pg it's easy to pass parameters in the simple case:

 conn.exec("INSERT INTO foo VALUES($1, $2)", ["Jeff", "Davis"])

and possible in the complex cases:

 # insert some BYTEA data using binary format and explicit type oid
 conn.exec("INSERT INTO bar VALUES($1, $2)", ["Jeff", {:value => filedata, :format => 1, :type => 17}])

I believe that offers a good balance of full libpq functionality, while reasonably maintaining ruby style, and offering convenience for the typical cases.

It's OK to leave out some libpq functionality if it's particularly obscure, such as PQprint(). However, it should be clear what is left out so that new developers can implement that functionality if they need it.

Abstraction Layers

Many languages have their own standardized abstraction layers, such as DBI for perl. Supporting these abstraction layers is highly encouraged. However, such abstraction layers should not be treated as a substitute for fully supporting all libpq functionality (which is important).

Type Conversions

In general, type conversions should be left to a higher layer (e.g. ActiveRecord) or optional convenience functions provided by the driver (which may be important for performance in some special cases). The type conversions have to be done at some point, but the developer needs to be able to easily control such conversions in the cases where it goes wrong.

Converting from postgresql types to types in the host language (and vice versa) should be done in a controlled way:

Avoid "magic".
Remember that results may be returned in binary mode, making it less feasible to translate to a type in the host language (but can also be very useful for efficiency reasons).
The most direct analogues to libpq functions, such as PQgetvalue(), should return the original representation (be it text or binary, depending on the mode). Any type conversions should be done as part of a separate convenience routine.
Note that conversions are almost always imperfect, even for seemingly similar types.
Note that you can never provide conversions for all of the types, because there could be user-defined types or types that simply don't have a corresponding type (or at least not a fully defined one) in the host language.
Account for errors during the conversion process. For instance, an array in Ruby might contain both text and integers, and converting to an equivalent PostgreSQL array is impossible.
If handling conversions for user-defined types at all (including those in contrib, like citext or hstore; and 3rd-party types like geometry from PostGIS), be aware that the OID of the type (or its presence) might change at any time.
Type conversions are likely to require constant tweaking and updating, possibly compromising the stability of the API.
Due to the imperfect nature of these type conversions, I don't think there will ever be a "standard" way to do type conversion. There will only be convenience functions that work most of the time.

Wrapping returned results with a "magical" object may be a good approach, so long as:

The "magical" object always allows you to retrieve the originally-returned value (text or binary format).
The API of these wrapper objects is sufficiently stable.

Text Encoding

If the result set is in binary format: Do nothing. The user is explicitly circumventing issues like encoding. Any returned value should be a byte sequence, not a character sequence.

If strings in the host language are sequences of bytes (like C, Ruby 1.8, or Python 2.X): Most encoding issues can be ignored. You just pass the bytes through to postgresql, and if it rejects them, it's the fault of the application for not obeying it's own setting for client_encoding (or perhaps postgresql is unable to transcode between the client_encoding and server_encoding for some reason).

If strings in the host language are sequences of characters (like Python 3.X): When sending data to postgresql, encode the string using client_encoding. When receiving data from postgresql, decode the string using the value of client_encoding as it was at the time the result object was returned.

If strings in the host language can be of any encoding (like Ruby 1.9): When sending data to postgresql, transcode the string from its encoding to the client_encoding. When receiving data from postgresql, associate the string with the value of client_encoding as it was at the time the result object was returned (and optionally transcode it to some other encoding).

Original binary representation

The philosophy of type conversions could reasonably be extended to text encodings as well -- in other words, always return the byte sequence rather than the string. The byte sequence should be available somehow, in case the developer is doing something out of the ordinary (such as doing a lossy conversion between two encodings for which there is no lossless conversion).

However, handling encoded strings should be the simplest way to interact with the driver's API, and handling byte sequences need only be possible (not necessarily easy). The reason that handling string encoding automatically is not "magical" is because there's a standard, lossless way to perform the conversion and it is nearly always desirable to do so.

Result object and client_encoding

The client_encoding can change after a result object is returned, but the bytes inside of the result object obviously won't change. So, you must attach the value of client_encoding to the result object as soon as it is returned from libpq. Then, use that encoding when reading data from the result object, rather than the current value of client_encoding.

It may seem far-fetched that the client_encoding changes at inopportune times, but it's actually quite reasonable. Consider using COPY on a text file in a different encoding.

Encoding names

libpq provides the function PQclientEncoding(), which returns an encoding ID (which apparently matches the code page number), and pg_encoding_to_char(), which returns an encoding name from an encoding ID. These can be used as a reliable way to select the correct encoding from the driver code.

Utility functions

The best way to approach encoding is to define two utility functions (internal to the driver):

pg_str_encode(string object) -> bytes: Encode a string object from the host language to the client_encoding. Only use directly before sending to PostgreSQL, ensuring that the client_encoding doesn't change beforehand.
pg_str_decode(bytes, result object) -> string object: Decode a string received from postgresql by using the client_encoding stored in the result object (not the current client_encoding). This also may be a convenient place to taint the object, if the language supports it (Note: tainting will be required in other places as well; for instance if the result set is in binary format).

With these two functions, you are able to centralize:

Conditionally-compiled code (which may be necessary to support multiple language versions)
Code to match up encoding names properly between the host language and PostgreSQL (if required).

Tainting

For languages that support "tainting" a variable, remember to taint all variables holding data returned from postgresql.

Non-Blocking libpq Calls

Don't use blocking libpq interface functions. Instead, use the non-blocking versions of those functions, and block within the functions using the host language's preferred blocking method. For instance, in Ruby, you can use rb_thread_select(), which allows the ruby threading implementation to perform other tasks while waiting for postgresql to finish its task.

This does not limit the functionality of the driver at all, and the API within the host language should still match one-to-one with libpq. The difference is that blocking calls in the host language will internally use non-blocking calls to libpq.

Note that you need to be prepared for commands to fail because the server is busy already.

Also note that this is orthogonal from the issue of thread safety. It's possible to be thread-safe (or not) using blocking calls or non-blocking calls to libpq.

Error Handling

PostgreSQL provides a lot of useful information when an error happens, and that should be exposed by the driver. However, in a host language that supports exceptions, the connection and result objects may be out of scope by the time the error is being handled. References to the connection and result object (if applicable) should be held along with the error (usually an exception object), thus allowing full access to the information about the error.

Connection and Result Objects

It is important to support calling PQfinish() on a connection object to disconnect immediately, without waiting for the garbage collector. The garbage collector of the host language should only be relied upon to release memory, and we certainly don't want to encourage leaving idle connections open waiting for them to be garbage-collected.

Similarly, it's advisable to allow the result object to be explicitly cleared, because it may be a significant amount of memory.

Either of these introduce the possibility of having a "zombie" connection (or result) object, and most operations on these objects should raise a useful error. However, as noted above, these objects may still contain useful information about errors.