dburl

package module
v0.23.2 Latest Latest
Warning

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

 Go to latest Published: Jun 10, 2024 License: MIT Imports: 12 Imported by: 483

README

About dburl

Package dburl provides a standard, URL style mechanism for parsing and opening SQL database connection strings for Go. Provides standardized way to parse and open URLs for popular databases PostgreSQL, MySQL, SQLite3, Oracle Database, Microsoft SQL Server, in addition to most other SQL databases with a publicly available Go driver.

Overview | Quickstart | Examples | Schemes | Installing | Using | About

Unit Tests Go Reference Discord Discussion

Database Connection URL Overview

Supported database connection URLs are of the form:

protocol+transport:https://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, /dbname can be /instance/dbname, where /instance is optional. For Oracle Database, /dbname is of the form /service/dbname where /service is the service name or SID, and /dbname is optional. Please see below for examples.

Quickstart

Database connection URLs in the above format can be parsed with the dburl.Parse func as such:

import (
    "github.com/xo/dburl"
)

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

Additionally, a simple helper, dburl.Open, is provided that will parse, open, and return a standard sql.DB database connection:

import (
    "github.com/xo/dburl"
)

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

Example URLs

The following are example database connection URLs that can be handled by dburl.Parse and dburl.Open:

postgres:https://user:pass@localhost/dbname
pg:https://user:pass@localhost/dbname?sslmode=disable
mysql:https://user:pass@localhost/dbname
mysql:/var/run/mysqld/mysqld.sock
sqlserver:https://user:[email protected]/dbname
mssql:https://user:[email protected]/instance/dbname
ms:https://user:[email protected]:port/instance/dbname?keepAlive=10
oracle:https://user:[email protected]/sid
sap:https://user:pass@localhost/dbname
sqlite:/path/to/file.db
file:myfile.sqlite3?loc=auto
odbc+postgres:https://user:pass@localhost:port/dbname?option1=

Database Schemes, Aliases, and Drivers

The following table lists the supported dburl protocol schemes (ie, driver), additional aliases, and the related Go driver:

Database Scheme / Tag Scheme Aliases Driver Package / Notes
PostgreSQL postgres pg, pgsql, postgresql github.com/lib/pq
MySQL mysql my, maria, aurora, mariadb, percona github.com/go-sql-driver/mysql
Microsoft SQL Server sqlserver ms, mssql, azuresql github.com/microsoft/go-mssqldb
Oracle Database oracle or, ora, oci, oci8, odpi, odpi-c github.com/sijms/go-ora/v2
SQLite3 sqlite3 sq, sqlite, file github.com/mattn/go-sqlite3 †
ClickHouse clickhouse ch github.com/ClickHouse/clickhouse-go/v2
CSVQ csvq cs, csv, tsv, json github.com/mithrandie/csvq-driver
Alibaba MaxCompute maxcompute mc sqlflow.org/gomaxcompute
Alibaba Tablestore ots ot, tablestore github.com/aliyun/aliyun-tablestore-go-sql-driver
Apache Avatica avatica av, phoenix github.com/apache/calcite-avatica-go/v5
Apache H2 h2 github.com/jmrobles/h2go
Apache Hive hive hi, hive2 sqlflow.org/gohive
Apache Ignite ignite ig, gridgain github.com/amsokol/ignite-go-client/sql
AWS Athena athena s3, aws, awsathena github.com/uber/athenadriver/go
Azure CosmosDB cosmos cm github.com/btnguyen2k/gocosmos
Cassandra cassandra ca, scy, scylla, datastax, cql github.com/MichaelS11/go-cql-driver
ChaiSQL chai ci, genji, chaisql github.com/chaisql/chai/driver
Couchbase couchbase n1, n1ql github.com/couchbase/go_n1ql
Cznic QL ql cznic, cznicql modernc.org/ql
Databend databend dd, bend github.com/datafuselabs/databend-go
Databricks databricks br, brick, bricks, databrick github.com/databricks/databricks-sql-go
DuckDB duckdb dk, ddb, duck, file github.com/marcboeker/go-duckdb †
DynamoDb dynamodb dy, dyn, dynamo, dynamodb github.com/btnguyen2k/godynamo
Exasol exasol ex, exa github.com/exasol/exasol-driver-go
Firebird firebird fb, firebirdsql github.com/nakagami/firebirdsql
FlightSQL flightsql fl, flight github.com/apache/arrow/go/v12/arrow/flight/flightsql/driver
Google BigQuery bigquery bq gorm.io/driver/bigquery/driver
Google Spanner spanner sp github.com/googleapis/go-sql-spanner
Microsoft ADODB adodb ad, ado github.com/mattn/go-adodb
ModernC SQLite3 moderncsqlite mq, modernsqlite modernc.org/sqlite
MySQL MyMySQL mymysql zm, mymy github.com/ziutek/mymysql/godrv
Netezza netezza nz, nzgo github.com/IBM/nzgo/v12
PostgreSQL PGX pgx px github.com/jackc/pgx/v5/stdlib
Presto presto pr, prs, prestos, prestodb, prestodbs github.com/prestodb/presto-go-client/presto
RamSQL ramsql rm, ram github.com/proullon/ramsql/driver
SAP ASE sapase ax, ase, tds github.com/thda/tds
SAP HANA saphana sa, sap, hana, hdb github.com/SAP/go-hdb/driver
Snowflake snowflake sf github.com/snowflakedb/gosnowflake
Trino trino tr, trs, trinos github.com/trinodb/trino-go-client/trino
Vertica vertica ve github.com/vertica/vertica-sql-go
VoltDB voltdb vo, vdb, volt github.com/VoltDB/voltdb-client-go/voltdbclient
YDB ydb yd, yds, ydbs github.com/ydb-platform/ydb-go-sdk/v3
GO DRiver for ORacle godror gr github.com/godror/godror †
ODBC odbc od github.com/alexbrainman/odbc †
Amazon Redshift postgres rs, redshift github.com/lib/pq ‡
CockroachDB postgres cr, cdb, crdb, cockroach, cockroachdb github.com/lib/pq ‡
OLE ODBC adodb oo, ole, oleodbc github.com/mattn/go-adodb ‡
SingleStore MemSQL mysql me, memsql github.com/go-sql-driver/mysql ‡
TiDB mysql ti, tidb github.com/go-sql-driver/mysql ‡
Vitess Database mysql vt, vitess github.com/go-sql-driver/mysql ‡
Apache Impala impala im github.com/bippio/go-impala

† Requires CGO
‡ Wire compatible (see respective driver)

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

Installing

Install in the usual Go fashion:

$ go get github.com/xo/dburl@latest

Using

dburl does not import any of Go's SQL drivers, as it only provides a way to parse and open database URL stylized connection strings. As such, it is necessary to explicitly import the relevant SQL driver:

import (
    // import Microsoft SQL Server driver
    _ "github.com/microsoft/go-mssqldb"
)

See the database schemes table above for a list of the expected Go driver import's.

Additional examples and API details can be found in the dburl package documentation.

URL Parsing Rules

dburl.Parse and dburl.Open rely primarily on Go's standard net/url.URL type, and as such, parsing or opening database connection URLs with dburl are subject to the same rules, conventions, and semantics as Go's net/url.Parse func.

Example

A full example for reference:

// _example/example.go
package main

import (
	"fmt"
	"log"

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

func main() {
	db, err := dburl.Open("sqlserver:https://user:pass@localhost/dbname")
	if err != nil {
		log.Fatal(err)
	}
	var name string
	if err := db.QueryRow(`SELECT name FROM mytable WHERE id=10`).Scan(&name); err != nil {
		log.Fatal(err)
	}
	fmt.Println("name:", name)
}

Scheme Resolution

By default on non-Windows systems, dburl will resolve paths on disk, and URLs with file: schemes to an appropriate database driver:

  1. Directories will resolve as postgres: URLs
  2. Unix sockets will resolve as mysql: URLs
  3. Regular files will have their headers checked to determine if they are either sqlite3: or duckdb: files
  4. Non-existent files will test their file extension against well-known sqlite3: and duckdb: file extensions and open with the appropriate scheme

If this behavior is undesired, it can be disabled by providing different implementations for dburl.Stat and dburl.OpenFile, or alternately by setting dburl.ResolveSchemeType to false:

import "github.com/xo/dburl"

func init() {
    dburl.ResolveSchemeType = false
}

About

dburl was built primarily to support these projects:

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

Documentation

Overview

Package dburl provides a standard, net/url.URL style mechanism for parsing and opening SQL database connection strings for Go. Provides standardized way to parse and open URL's for popular databases PostgreSQL, MySQL, SQLite3, Oracle Database, Microsoft SQL Server, in addition to most other SQL databases with a publicly available Go driver.

See the package documentation README section for more details.

Example 
package main

import (
	"log"

	"github.com/xo/dburl"
)

func main() {
	db, err := dburl.Open("my:https://user:pass@host:1234/dbname")
	if err != nil {
		log.Fatal(err)
	}
	res, err := db.Query("SELECT ...")
	if err != nil {
		log.Fatal(err)
	}
	for res.Next() {
		/* ... */
	}
	if err := res.Err(); err != nil {
		log.Fatal(err)
	}
}

Output:
Example (Parse) 
package main

import (
	"database/sql"
	"log"

	"github.com/xo/dburl"
)

func main() {
	u, err := dburl.Parse("pg:https://user:pass@host:1234/dbname")
	if err != nil {
		log.Fatal(err)
	}
	db, err := sql.Open(u.Driver, u.DSN)
	if err != nil {
		log.Fatal(err)
	}
	res, err := db.Query("SELECT ...")
	if err != nil {
		log.Fatal(err)
	}
	for res.Next() {
		/* ... */
	}
	if err := res.Err(); err != nil {
		log.Fatal(err)
	}
}

Output:

Index

Examples

Constants

This section is empty.

Variables

View Source 
var OdbcIgnoreQueryPrefixes []string

OdbcIgnoreQueryPrefixes are the query prefixes to ignore when generating the odbc DSN. Used by GenOdbc

View Source 
var OpenFile = func(name string) (fs.File, error) {
	f, err := os.OpenFile(name, os.O_RDONLY, 0)
	if err != nil {
		return nil, err
	}
	return f, nil
}

OpenFile is the default open file func.

Used internally to read file headers.

View Source 
var ResolveSchemeType = true

ResolveSchemeType is a configuration setting to open paths on disk using SchemeType, Stat, and OpenFile. Set this to false in an `init()` func in order to disable this behavior.

View Source 
var Stat = func(name string) (fs.FileInfo, error) {
	return fs.Stat(os.DirFS(filepath.Dir(name)), filepath.Base(name))
}

Stat is the default stat func.

Used internally to stat files, and used when generating the DSNs for postgres:https://, mysql:https://, file:https:// schemes, and opaque URL's.

Functions

func BuildURL added in v0.23.0 

func BuildURL(components map[string]interface{}) (string, error)

BuildURL creates a dsn using the mapped components.

Recognized components are: 

protocol, proto, scheme
transport
username, user
password, pass
hostname, host
port
path, file, opaque
database, dbname, db
instance
parameters, params, options, opts, query, q

See BuildURL for more information.

func FileTypes added in v0.18.0 

func FileTypes() []string

FileTypes returns the registered file types.

func GenAdodb added in v0.9.0 

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

GenAdodb generates a adodb DSN from the passed URL.

func GenCassandra 

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

GenCassandra generates a cassandra DSN from the passed URL.

func GenClickhouse 

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

GenClickhouse generates a clickhouse DSN from the passed URL.

func GenCosmos added in v0.2.0 

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

GenCosmos generates a cosmos DSN from the passed URL.

func GenDatabend added in v0.12.5 

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

GenDatabend generates a databend DSN from the passed URL.

func GenDatabricks added in v0.19.0 

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

GenDatabricks generates a databricks DSN from the passed URL.

func GenDynamo added in v0.19.0 

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

GenDynamo generates a dynamo DSN from the passed URL.

func GenExasol added in v0.8.5 

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

GenExasol generates a exasol DSN from the passed URL.

func GenFirebird 

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

GenFirebird generates a firebird DSN from the passed URL.

func GenFromURL 

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

GenFromURL returns a func that generates a DSN based on parameters of the passed URL.

func GenGodror added in v0.9.0 

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

GenGodror generates a godror DSN from the passed URL.

func GenIgnite 

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

GenIgnite generates an ignite DSN from the passed URL.

func GenMymysql added in v0.9.0 

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

GenMymysql generates a mymysql DSN from the passed URL.

func GenMysql added in v0.9.0 

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

GenMysql generates a mysql DSN from the passed URL.

func GenOdbc added in v0.9.0 

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

GenOdbc generates a odbc DSN from the passed URL.

func GenOleodbc added in v0.9.0 

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

GenOleodbc generates a oleodbc DSN from the passed URL.

func GenOpaque 

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

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

func GenPostgres 

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

GenPostgres generates a postgres DSN from the passed URL.

func GenPresto 

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

GenPresto generates a presto DSN from the passed URL.

func GenScheme 

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

GenScheme returns a generator that will generate a scheme based on the passed scheme DSN.

func GenSnowflake 

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

GenSnowflake generates a snowflake DSN from the passed URL.

func GenSpanner added in v0.2.0 

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

GenSpanner generates a spanner DSN from the passed URL.

func GenSqlserver added in v0.9.0 

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

GenSqlserver generates a sqlserver DSN from the passed URL.

func GenTableStore added in v0.11.0 

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

GenTableStore generates a tablestore DSN from the passed URL.

func GenVoltdb added in v0.9.0 

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

GenVoltdb generates a voltdb DSN from the passed URL.

func GenYDB added in v0.19.0 

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

GenYDB generates a ydb dsn from the passed URL.

func Open 

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

Open takes a URL string, also known as a DSN, in the form of "protocol+transport:https://user:pass@host/dbname?option1=a&option2=b" and opens a standard sql.DB connection.

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

func OpenMap added in v0.23.0 

func OpenMap(components map[string]interface{}) (*sql.DB, error)

OpenMap takes a map of URL components and opens a standard sql.DB connection.

See BuildURL for information on the recognized map components.

func Protocols added in v0.12.0 

func Protocols(name string) []string

Protocols returns list of all valid protocol aliases for a registered Scheme name.

func Register 

func Register(scheme Scheme)

Register registers a Scheme.

func RegisterAlias 

func RegisterAlias(name, alias string)

RegisterAlias registers an additional alias for a registered scheme.

func RegisterFileType added in v0.18.0 

func RegisterFileType(driver string, f func([]byte) bool, ext string)

RegisterFileType registers a file header recognition func, and extension regexp.

func SchemeDriverAndAliases 

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

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

func SchemeType added in v0.17.0 

func SchemeType(name string) (string, error)

SchemeType returns the scheme type for a path.

func ShortAlias added in v0.12.4 

func ShortAlias(name string) string

ShortAlias returns the short alias for the scheme name.

Types

type Error 

type Error string

Error is an 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"
	// ErrUnknownFileHeader is the unknown file header error.
	ErrUnknownFileHeader Error = "unknown file header"
	// ErrUnknownFileExtension is the unknown file extension error.
	ErrUnknownFileExtension Error = "unknown file extension"
	// 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"
	// ErrMissingUser is the missing user error.
	ErrMissingUser Error = "missing user"
	// ErrInvalidQuery is the invalid query error.
	ErrInvalidQuery Error = "invalid query"
)

Error values.

func (Error) Error 

func (err Error) Error() string

Error satisfies the error interface.

type Scheme 

type Scheme struct {
	// Driver is the name of the SQL driver that is 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, string, error)
	// Transport are allowed protocol transport types for the scheme.
	Transport Transport
	// 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.
	//
	// Used for "wire compatible" driver schemes.
	Override string
}

Scheme wraps information used for registering a database URL scheme for use 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, returning the removed Scheme.

type Transport added in v0.3.0 

type Transport uint

Transport is the allowed transport protocol types in a database URL scheme. 

const (
	TransportNone Transport = 0
	TransportTCP  Transport = 1
	TransportUDP  Transport = 2
	TransportUnix Transport = 4
	TransportAny  Transport = 8
)

Transport types.

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
	// Transport is the specified transport protocol (ie, "tcp", "udp",
	// "unix", ...), if provided.
	Transport string
	// Driver is the non-aliased SQL driver name that should be used in a call
	// to [sql.Open].
	Driver string
	// GoDriver is the Go SQL driver name to use when opening a connection to
	// the database. Used by Microsoft SQL Server's azuresql:https:// URLs, as the
	// wire-compatible alias style uses a different syntax style.
	GoDriver string
	// UnaliasedDriver is the unaliased driver name.
	UnaliasedDriver 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, Transport, Driver, Unaliased, and DSN strings.

func FromMap added in v0.23.0 

func FromMap(components map[string]interface{}) (*URL, error)

FromMap creates a URL using the mapped components.

Recognized components are: 

protocol, proto, scheme
transport
username, user
password, pass
hostname, host
port
path, file, opaque
database, dbname, db
instance
parameters, params, options, opts, query, q

See BuildURL for more information.

func Parse 

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

Parse parses a URL string, similar to the standard net/url.Parse.

Handles parsing OriginalScheme, Transport, Driver, Unaliased, and DSN fields.

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

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 fmt.Stringer interface.

Source Files

View all Source files

Directories

Path Synopsis
_example/example.go
 _example/example.go
Package passfile provides a mechanism for reading database credentials from passfiles.
 Package passfile provides a mechanism for reading database credentials from passfiles.

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.