dburl

package module
v0.0.3 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 5, 2018 License: MIT Imports: 7 Imported by: 7

README

About dburl

Package dburl provides a standard, URL style mechanism for parsing and opening SQL database connection strings for Go, supporting standard URLs for the standard databases PostgreSQL, MySQL, SQLite3, Oracle, Microsoft SQL Server, and most other databases with a publicly available Go driver.

Database URL Connection Strings

Supported database URLs are of the form:

   protocol+transport://user:pass@host/dbname?opt1=a&opt2=b
   protocol:/path/to/file

Where:

Component Description
protocol driver name or alias (see below)
transport "tcp", "udp", "unix" or driver name (odbc/oleodbc)
user username
pass password
host host
dbname* database, instance, or service name/ID to connect to
?opt1=... additional database driver options (see respective SQL driver for available options)

* for Microsoft SQL Server, the syntax to supply an instance and database name is /instance/dbname, where /instance is optional. For Oracle databases, /dbname is the unique database ID (SID). Please see below for examples.

Quickstart

Database connection URLs (as described below) can be parsed with Parse as such:

u, err := dburl.Parse("postgresql://user:pass@localhost/mydatabase/?sslmode=disable")
if err != nil { /* ... */ }

Additionally, a simple helper func Open, is available to quickly parse, open, and return a standard SQL database connection:

db, err := dburl.Open("sqlite:mydatabase.sqlite3?loc=auto")
if err != nil { /* ... */ }

Example URLs

The following are URLs that can be handled with a call to Open or Parse:

   postgres://user:pass@localhost/dbname
   pg://user:pass@localhost/dbname?sslmode=disable
   mysql://user:pass@localhost/dbname
   mysql:/var/run/mysqld/mysqld.sock
   sqlserver://user:pass@remote-host.com/dbname
   mssql://user:pass@remote-host.com/instance/dbname
   ms://user:pass@remote-host.com:port/instance/dbname?keepAlive=10
   oracle://user:pass@somehost.com/oracledb
   sap://user:pass@localhost/dbname
   sqlite:/path/to/file.db
   file:myfile.sqlite3?loc=auto
   odbc+postgres://user:pass@localhost:port/dbname?option1=

Protocol Schemes and Aliases

The following protocols schemes (ie, driver) and their associated aliases are supported out of the box:

Database (scheme/driver) Protocol Aliases [real driver]
Microsoft SQL Server (mssql) ms, sqlserver
MySQL (mysql) my, mariadb, maria, percona, aurora
Oracle (ora) or, oracle, oci8, oci
PostgreSQL (postgres) pg, postgresql, pgsql
SQLite3 (sqlite3) sq, sqlite, file
Amazon Redshift (redshift) rs [postgres]
CockroachDB (cockroachdb) cr, cockroach, crdb, cdb [postgres]
MemSQL (memsql) me [mysql]
TiDB (tidb) ti [mysql]
Vitess (vitess) vt [mysql]
Google Spanner (spanner) gs, google, span (not yet public)
MySQL (mymysql) zm, mymy
PostgreSQL (pgx) px
Apache Avatica (avatica) av, phoenix
Apache Ignite (ignite) ig, gridgain
Cassandra (cql) ca, cassandra, datastax, scy, scylla
ClickHouse (clickhouse) ch
Couchbase (n1ql) n1, couchbase
Cznic QL (ql) ql, cznic, cznicql
Firebird SQL (firebirdsql) fb, firebird
Microsoft ADODB (adodb) ad, ado
ODBC (odbc) od
OLE ODBC (oleodbc) oo, ole, oleodbc [adodb]
Presto (presto) pr, prestodb, prestos, prs, prestodbs
SAP HANA (hdb) sa, saphana, sap, hana
Snowflake (snowflake) sf
VoltDB (voltdb) vo, volt, vdb

Any protocol scheme alias:// can be used in place of protocol://, and will work identically with Parse/Open.

Installation

Install in the usual Go fashion:

go get -u github.com/xo/dburl

Usage

Please note that the dburl package does not import actual SQL drivers, and only provides a standard way to parse/open respective database connection URLs.

For reference, these are the following "expected" SQL drivers that would need to be imported:

Database (driver) Package
Microsoft SQL Server (mssql) github.com/denisenkom/go-mssqldb
MySQL (mysql) github.com/go-sql-driver/mysql
Oracle (ora) gopkg.in/rana/ora.v4
PostgreSQL (postgres) github.com/lib/pq
SQLite3 (sqlite3) github.com/mattn/go-sqlite3
Amazon Redshift (redshift) github.com/lib/pq
CockroachDB (cockroachdb) github.com/lib/pq
MemSQL (memsql) github.com/go-sql-driver/mysql
TiDB (tidb) github.com/go-sql-driver/mysql
Vitess (vitess) github.com/go-sql-driver/mysql
Google Spanner (spanner) github.com/xo/spanner (not yet public)
MySQL (mymysql) github.com/ziutek/mymysql/godrv
PostgreSQL (pgx) github.com/jackc/pgx/stdlib
Apache Avatica (avatica) github.com/Boostport/avatica
Apache Ignite (ignite) github.com/amsokol/ignite-go-client/sql
Cassandra (cql) github.com/MichaelS11/go-cql-driver
ClickHouse (clickhouse) github.com/kshvakov/clickhouse
Couchbase (n1ql) github.com/couchbase/go_n1ql
Cznic QL (ql) github.com/cznic/ql
Firebird SQL (firebirdsql) github.com/nakagami/firebirdsql
Microsoft ADODB (adodb) github.com/mattn/go-adodb
ODBC (odbc) github.com/alexbrainman/odbc
OLE ODBC (oleodbc) github.com/mattn/go-adodb
Presto (presto) github.com/prestodb/presto-go-client/presto
SAP HANA (hdb) github.com/SAP/go-hdb/driver
Snowflake (snowflake) github.com/snowflakedb/gosnowflake
VoltDB (voltdb) github.com/VoltDB/voltdb-client-go/voltdbclient

Please see the GoDoc API page for a full API listing.

URL Parsing Rules

Parse and Open rely heavily on the standard net/url.URL type, as such parsing rules have the same conventions/semantics as any URL parsed by the standard library's net/url.Parse.

Full Example

A full example for reference:

// example/example.go
package main

import (
    "fmt"
    "log"

    _ "github.com/denisenkom/go-mssqldb"
    "github.com/xo/dburl"
)

func main() {
    db, err := dburl.Open("sqlserver://user:pass@localhost/dbname")
    if err != nil {
        log.Fatal(err)
    }

    var name string
    err = db.QueryRow(`SELECT name FROM mytable WHERE id=10`).Scan(&name)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf(">> got: %s\n", name)
}

The dburl package was built primarily to support these projects:

  • usql - a universal command-line interface for SQL databases
  • xo - a command-line tool to generate Go code from a database schema

Documentation

Overview

Package dburl provides a standard, URL style mechanism for parsing and opening SQL database connection strings.

Database URL Connection Strings

Supported database URLs are of the form: 

protocol+transport://user:pass@host/dbname?opt1=a&opt2=b
protocol:/path/to/file

Where: 

protocol  - driver name or alias (see below)
transport - "tcp", "udp", "unix" or driver name (odbc/oleodbc)                                  |
user      - username
pass      - password
host      - host
dbname*   - database, instance, or service name/id to connect to
?opt1=... - additional database driver options
              (see respective SQL driver for available options)

* for Microsoft SQL Server, the syntax to supply an instance and database name is /instance/dbname, where /instance is optional. For Oracle databases, /dbname is the unique database ID (SID). Please see below for examples.

Quickstart

URLs in the above format can be parsed with Parse as such: 

u, err := dburl.Parse("postgresql://user:pass@localhost/mydatabase/?sslmode=disable")
if err != nil { /* ... */ }

Additionally, a simple helper func, Open, is available to quickly parse, open, and return a standard SQL database connection: 

db, err := dburl.Open("sqlite:mydatabase.sqlite3?loc=auto")
if err != nil { /* ... */ }

Example URLs

The following are URLs that can be handled with a call to Open or Parse: 

postgres://user:pass@localhost/dbname
pg://user:pass@localhost/dbname?sslmode=disable
mysql://user:pass@localhost/dbname
mysql:/var/run/mysqld/mysqld.sock
sqlserver://user:pass@remote-host.com/dbname
mssql://user:pass@remote-host.com/instance/dbname
ms://user:pass@remote-host.com:port/instance/dbname?keepAlive=10
oracle://user:pass@somehost.com/oracledb
goracle://user:pass@somehost.com/oracledb
sap://user:pass@localhost/dbname
sqlite:/path/to/file.db
file:myfile.sqlite3?loc=auto
odbc+postgres://user:pass@localhost:port/dbname?option1=

Protocol Schemes and Aliases

The following protocols schemes (ie, driver) and their associated aliases are supported out of the box: 

Database (scheme/driver)     | Protocol Aliases [real driver]
-----------------------------|-------------------------------------------
Microsoft SQL Server (mssql) | ms, sqlserver
MySQL (mysql)                | my, mariadb, maria, percona, aurora
Oracle (ora)                 | or, oracle, oci8, oci
Oracle (goracle)             | goracle
PostgreSQL (postgres)        | pg, postgresql, pgsql
SQLite3 (sqlite3)            | sq, sqlite, file
-----------------------------|-------------------------------------------
Amazon Redshift (redshift)   | rs [postgres]
CockroachDB (cockroachdb)    | cr, cockroach, crdb, cdb [postgres]
MemSQL (memsql)              | me [mysql]
TiDB (tidb)                  | ti [mysql]
Vitess (vitess)              | vt [mysql]
-----------------------------|-------------------------------------------
Google Spanner (spanner)     | gs, google, span (not yet public)
-----------------------------|-------------------------------------------
MySQL (mymysql)              | zm, mymy
PostgreSQL (pgx)             | px
-----------------------------|-------------------------------------------
Apache Avatica (avatica)     | av, phoenix
Apache Ignite (ignite)       | ig, gridgain
Cassandra (cql)              | ca, cassandra, datastax, scy, scylla
ClickHouse (clickhouse)      | ch
Couchbase (n1ql)             | n1, couchbase
Cznic QL (ql)                | ql, cznic, cznicql
Firebird SQL (firebirdsql)   | fb, firebird
Microsoft ADODB (adodb)      | ad, ado
ODBC (odbc)                  | od
OLE ODBC (oleodbc)           | oo, ole, oleodbc [adodb]
Presto (presto)              | pr, prestodb, prestos, prs, prestodbs
SAP HANA (hdb)               | sa, saphana, sap, hana
Snowflake (snowflake)        | sf
VoltDB (voltdb)              | vo, volt, vdb

Any protocol scheme alias:// can be used in place of protocol://, and will work identically with Parse/Open.

Usage

Please note that the dburl package does not import actual SQL drivers, and only provides a standard way to parse/open respective database connection URLs.

For reference, these are the following "expected" SQL drivers that would need to be imported: 

Database (scheme/driver)     | Package
-----------------------------|-------------------------------------------------
Microsoft SQL Server (mssql) | github.com/denisenkom/go-mssqldb
MySQL (mysql)                | github.com/go-sql-driver/mysql
Oracle (ora)                 | gopkg.in/rana/ora.v4
Oracle (goracle)             | gopkg.in/goracle.v2
PostgreSQL (postgres)        | github.com/lib/pq
SQLite3 (sqlite3)            | github.com/mattn/go-sqlite3
-----------------------------|-------------------------------------------------
Amazon Redshift (redshift)   | github.com/lib/pq
CockroachDB (cockroachdb)    | github.com/lib/pq
MemSQL (memsql)              | github.com/go-sql-driver/mysql
TiDB (tidb)                  | github.com/go-sql-driver/mysql
Vitess (vitess)              | github.com/go-sql-driver/mysql
-----------------------------|-------------------------------------------------
Google Spanner (spanner)     | github.com/xo/spanner (not yet public)
-----------------------------|-------------------------------------------------
MySQL (mymysql)              | github.com/ziutek/mymysql/godrv
PostgreSQL (pgx)             | github.com/jackc/pgx/stdlib
-----------------------------|-------------------------------------------------
Apache Avatica (avatica)     | github.com/Boostport/avatica
Apache Ignite (ignite)       | github.com/amsokol/ignite-go-client/sql
Cassandra (cql)              | github.com/MichaelS11/go-cql-driver
ClickHouse (clickhouse)      | github.com/kshvakov/clickhouse
Couchbase (n1ql)             | github.com/couchbase/go_n1ql
Cznic QL (ql)                | github.com/cznic/ql
Firebird SQL (firebirdsql)   | github.com/nakagami/firebirdsql
Microsoft ADODB (adodb)      | github.com/mattn/go-adodb
ODBC (odbc)                  | github.com/alexbrainman/odbc
OLE ODBC (oleodbc)*          | github.com/mattn/go-adodb
Presto (presto)              | github.com/prestodb/presto-go-client
SAP HANA (hdb)               | github.com/SAP/go-hdb/driver
Snowflake (snowflake)        | github.com/snowflakedb/gosnowflake
VoltDB (voltdb)              | github.com/VoltDB/voltdb-client-go/voltdbclient
  • OLE ODBC is not an actual protocol, but instead is an alias for using the "MSDASQL.1" OLE provider with the ADODB driver, and the DSN will be an ADODB DSN, but with "Extended Properties" for the respective ODBC parameters, including the underlying transport prootocol. As such, "oleodbc+protocol://user:pass@host/dbname" URLs are equivalent to "adodb://MSDASQL.1/?Extended+Properties=...". Please see the documentation for GenOLEODBC for information regarding how URL components are mapped/passed to ADODB's Extended Properties parameter.

URL Parsing Rules

Parse and Open rely heavily on the standard net/url.URL type, as such parsing rules have the same conventions/semantics as any URL parsed by the standard library's net/url.Parse.

This package was written mainly to support xo (https://github.com/xo/xo) and usql (https://github.com/xo/usql).

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func GenADODB 

func GenADODB(u *URL) (string, error)

GenADODB generates a adodb DSN from the passed URL.

func GenCassandra 

func GenCassandra(u *URL) (string, error)

GenCassandra generates a cassandra DSN from the passed URL.

func GenClickhouse 

func GenClickhouse(u *URL) (string, error)

GenClickhouse generates a clickhouse DSN from the passed URL.

func GenFirebird 

func GenFirebird(u *URL) (string, error)

GenFirebird generates a firebirdsql DSN from the passed URL.

func GenFromURL 

func GenFromURL(urlstr string) func(*URL) (string, error)

GenFromURL returns a func that generates a DSN using urlstr as the default URL parameters, overriding the values only if when in the passed URL.

func GenIgnite 

func GenIgnite(u *URL) (string, error)

GenIgnite generates an ignite DSN from the passed URL.

func GenMyMySQL 

func GenMyMySQL(u *URL) (string, error)

GenMyMySQL generates a MyMySQL MySQL DSN from the passed URL.

func GenMySQL 

func GenMySQL(u *URL) (string, error)

GenMySQL generates a mysql DSN from the passed URL.

func GenODBC 

func GenODBC(u *URL) (string, error)

GenODBC generates a odbc DSN from the passed URL.

func GenOLEODBC 

func GenOLEODBC(u *URL) (string, error)

GenOLEODBC generates a oleodbc DSN from the passed URL.

func GenOpaque 

func GenOpaque(u *URL) (string, error)

GenOpaque generates a opaque file path DSN from the passed URL.

func GenOracle 

func GenOracle(u *URL) (string, error)

GenOracle generates a ora DSN from the passed URL.

func GenPostgres 

func GenPostgres(u *URL) (string, error)

GenPostgres generates a postgres DSN from the passed URL.

func GenPresto 

func GenPresto(u *URL) (string, error)

GenPresto generates a Presto DSN from the passed URL.

func GenSQLServer 

func GenSQLServer(u *URL) (string, error)

GenSQLServer generates a mssql DSN from the passed URL.

func GenScheme 

func GenScheme(scheme string) func(*URL) (string, error)

GenScheme returns a func that generates a scheme:// style DSN from the passed URL.

func GenSnowflake 

func GenSnowflake(u *URL) (string, error)

GenSnowflake generates a snowflake DSN from the passed URL.

func GenVoltDB 

func GenVoltDB(u *URL) (string, error)

GenVoltDB generates a VoltDB DSN from the passed URL.

func Open 

func Open(urlstr string) (*sql.DB, error)

Open takes a urlstr like "protocol+transport://user:pass@host/dbname?option1=a&option2=b" and creates a standard sql.DB connection.

See Parse for information on formatting URLs to work properly with Open.

func Register 

func Register(scheme Scheme)

Register registers a Scheme.

func RegisterAlias 

func RegisterAlias(name, alias string)

RegisterAlias registers a alias for an already registered Scheme.h

func SchemeDriverAndAliases 

func SchemeDriverAndAliases(name string) (string, []string)

SchemeDriverAndAliases returns the registered driver and aliases for a database scheme.

Types

type Error 

type Error string

Error is a dburl error. 

const (
	// ErrInvalidDatabaseScheme is the invalid database scheme error.
	ErrInvalidDatabaseScheme Error = "invalid database scheme"

	// ErrUnknownDatabaseScheme is the unknown database type error.
	ErrUnknownDatabaseScheme Error = "unknown database scheme"

	// ErrInvalidTransportProtocol is the invalid transport protocol error.
	ErrInvalidTransportProtocol Error = "invalid transport protocol"

	// ErrRelativePathNotSupported is the relative paths not supported error.
	ErrRelativePathNotSupported Error = "relative path not supported"

	// ErrMissingHost is the missing host error.
	ErrMissingHost Error = "missing host"

	// ErrMissingPath is the missing path error.
	ErrMissingPath Error = "missing path"
)

func (Error) Error 

func (err Error) Error() string

Error satisfies the error interface.

type Proto 

type Proto uint

Proto are the allowed transport protocol types in a database URL scheme. 

const (
	ProtoNone Proto = 0
	ProtoTCP  Proto = 1
	ProtoUDP  Proto = 2
	ProtoUnix Proto = 4
	ProtoAny  Proto = 8
)

Proto types.

type Scheme 

type Scheme struct {
	// Driver is the name of the SQL driver that will set as the Scheme in
	// Parse'd URLs, and is the driver name expected by the standard sql.Open
	// calls.
	//
	// Note: a 2 letter alias will always be registered for the Driver as the
	// first 2 characters of the Driver, unless one of the Aliases includes an
	// alias that is 2 characters.
	Driver string

	// Generator is the func responsible for generating a DSN based on parsed
	// URL information.
	//
	// Note: this func should not modify the passed URL.
	Generator func(*URL) (string, error)

	// Proto are allowed protocol types for the scheme.
	Proto Proto

	// Opaque toggles Parse to not re-process URLs with an "opaque" component.
	Opaque bool

	// Aliases are any additional aliases for the scheme.
	Aliases []string

	// Override is the Go SQL driver to use instead of Driver.
	Override string
}

Scheme wraps information used for registering a URL scheme with Parse/Open.

func BaseSchemes 

func BaseSchemes() []Scheme

BaseSchemes returns the supported base schemes.

func Unregister 

func Unregister(name string) *Scheme

Unregister unregisters a Scheme and all associated aliases.

type URL 

type URL struct {
	// URL is the base net/url/URL.
	url.URL

	// OriginalScheme is the original parsed scheme (ie, "sq", "mysql+unix", "sap", etc).
	OriginalScheme string

	// Proto is the specified protocol (ie, "tcp", "udp", "unix"), if provided.
	Proto string

	// Driver is the non-aliased SQL driver name that should be used in a call
	// to sql/Open.
	Driver string

	// Unaliased is the unaliased driver name.
	Unaliased string

	// DSN is the built connection "data source name" that can be used in a
	// call to sql/Open.
	DSN string
	// contains filtered or unexported fields
}

URL wraps the standard net/url.URL type, adding OriginalScheme, Proto, Driver, and DSN strings.

func Parse 

func Parse(urlstr string) (*URL, error)

Parse parses urlstr, returning a URL with the OriginalScheme, Proto, Driver, Unaliased, and DSN fields populated.

Note: if urlstr has a Opaque component (ie, URLs not specified as "scheme://" but "scheme:"), and the database scheme does not support opaque components, then Parse will attempt to re-process the URL as "scheme://<opaque>" using the OriginalScheme.

func (*URL) Normalize 

func (u *URL) Normalize(sep, empty string, cut int) string

Normalize returns the driver, host, port, database, and user name of a URL, joined with sep, populating blank fields with empty.

func (*URL) Short 

func (u *URL) Short() string

Short provides a short description of the user, host, and database.

func (*URL) String 

func (u *URL) String() string

String satisfies the stringer interface.

Source Files

View all Source files

Directories

Path Synopsis
example/example.go
 example/example.go

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.