Perl module representing database URIs
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
lib/URI
t Add DBI tests for Snowflake URI. Jul 17, 2018
.gitignore
.travis.yml
Build.PL
Changes Increment to v0.20. Jul 19, 2018
MANIFEST.SKIP
README
README.md

README.md

Database URI

CPAN version Build Status

This project proposes a standard for database connection URIs and provides a simple Perl implementation. This figure summarizes the definition syntax and for database URIs (illustration adapted from RFC 3986 --- STD 66, chapter 3):

  db:engine://username:password@example.com:8042/widgets.db?tz=utc&charset=utf8#users
  \/ \____/   \_______________/ \_________/ \__/ \________/ \/ \__/ \____/ \__/\____/
   |    |             |              |       |        |     |    |     |    |     |
   |    |         userinfo        hostname  port      |    key   |    key   |     |
   |    |     \________________________________/      |          |          |     |
   |    |                      |                      |        value      value   |
   |  engine                   |                      |     \_________________/   |
scheme  |                  authority         db name or path         |            |
 name   |     \___________________________________________/        query       fragment
   |    |                           |
   |    |                   hierarchical part
   |    |
   |    |      db name or path       query    fragment
   |  __|_   ________|________    _____|____  ____|____
  /\ /    \ /                 \  /          \/         \
  db:engine:my_big_fat_database?encoding=big5#log.animals

Notes on this syntax:

  • The Database URI scheme is db. Consequently, database URIs always start with db:. This is the URI scheme that defines a database URI.

  • Next comes the database engine. This part is a string naming the type of database engine for the database. It must always be followed by a colon, :. There is no formal list of supported engines, though certain implementations may specify engine-specific semantics, such as a default port.

  • The authority part is separated from the engine by a double slash, //, and terminated by the next slash or end of the URI. It consists of an optional user-information part, terminated by @ (e.g., username:password@); a host address (e.g., domain name or IP address); and an optional port number, preceded by a colon, :.

  • The path part specifies the database name or path. It must be separated from the authority, if the authority is present, by a single slash, /. If the database name is a full path, it may start with an additional slash.

  • The optional query part, separated by a question mark, ?, contains key=value pairs separated by a semicolon, ;, or ampersand, &. These parameters may be used to configure a database connection with parameters not directly supported by the rest of the URI format.

  • The optional fragment part, separted by a hash mark, #, contains additional context information, such as a table or view name.

Here are some database URIs without an authority part, which is typical for non-server engines such as SQLite, where the path part is a relative or absolute file name:

  • db:sqlite:
  • db:sqlite:foo.db
  • db:sqlite:../foo.db
  • db:sqlite:/var/db/foo.sqlite

Other engines may use a database name rather than a file name:

  • db:ingres:mydb
  • db:postgresql:template1

When a URI includes an authority part, it must be preceded by a double slash:

  • db:postgresql://example.com/
  • db:mysql://root@localhost/
  • db:pg://postgres:secr3t@example.net

Formally, the authority part requires a host name, but some implementations, inspired by the file scheme, might allow an empty host to imply localhost.

  • db:mysql:/root@
  • db:postgres://postgres:secr3t@
  • db:sqlite:///

The path part contians the database name, separated from the authority by a single slash:

  • db:postgresql://example.com/template1
  • db:mongodb://localhost:27017/myDatabase
  • db:oracle://scott:tiger@foo.com/scott

Some databases, such as Firebird, take both a host name and a file path. Just put the relative or absolute path after that slash, as appropriate:

  • db:firebird://localhost/test.gdb - Relative
  • db:firebird://localhost/../test.gdb - Relative
  • db:firebird://localhost/C:/temp/test.gdb - Absolute
  • db:firebird://localhost/%2Ftmp/test.gdb - Absolute

Note the percent-encoded slash in the last example. Formally, an absolute path may not start with a slash, so we use its percent-encoded representation here. In practice, implementations may recognize a leading slash, anyway:

  • db:firebird://localhost//tmp/test.gdb

Any URI format may optionally have a query part containing key/value pairs:

  • db:sqlite:foo.db?foreign_keys=ON;journal_mode=WAL
  • db:pg://localhost:5433/postgres?client_encoding=utf8;connect_timeout=10

URIs may also have a fragment that names a specific database object. Since database URIs will generally be used for connecting, this part may be ignored.

  • db:sqlite:my.db#users
  • db:pg://localhost/postgres#pg_catalog.pg_class.

URI Compliance

Formally, a database URI as defined here is an opaque URI starting with db: followed by an embedded server-style URI. For example, this database URI:

db:pg://localhost/mydb

Is formally the URI pg://localhost/mydb embedded in an opaque db: URI. It adheres to this formal definition because the scheme part of a URI is not allowed to contain a sub-scheme (or subprotocol, in the JDBC parlance). It is therefore a legal URI embedded in a second legal URI.

Informally, it's simpler to think of a database URI as a single URI starting with the combination of the scheme and the engine, e.g., db:pg.

Some may recognize URIs as database URIs in the absence of the db: scheme, provided their schemes correspond to widely-recognized database engines, such as postgresql, mysql, sqlite, mssql, and oracle. These are not formally recognized as standard schemes, though they may be recognized as standard engines by the db: scheme specification.

Inspiration

The format here is inspired by a lot of prior art.

Author

David E. Wheeler

Contributers