xo / dburl Goto Github PK

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

License: MIT License

Go 100.00%

dburl's Introduction

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.

Database Connection URL Overview

Supported database connection 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, /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://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://user:pass@localhost/dbname
pg://user:pass@localhost/dbname?sslmode=disable
mysql://user:pass@localhost/dbname
mysql:/var/run/mysqld/mysqld.sock
sqlserver://user:[email protected]/dbname
mssql://user:[email protected]/instance/dbname
ms://user:[email protected]:port/instance/dbname?keepAlive=10
oracle://user:[email protected]/sid
sap://user:pass@localhost/dbname
sqlite:/path/to/file.db
file:myfile.sqlite3?loc=auto
odbc+postgres://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:// can be used in place of protocol://, 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://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:

Directories will resolve as postgres: URLs
Unix sockets will resolve as mysql: URLs
Regular files will have their headers checked to determine if they are either sqlite3: or duckdb: files
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

dburl's People

Contributors

Stargazers

Watchers

dburl's Issues

Modify the query parameters?

I was wondering if there's anyway to add query parameters with dburl other than parsing twice?

My use case is always adding some pragmas to the SQLite path.

// Parse the dbPath, in this case sqlite::memory:
url, err := dburl.Parse(dbPath)
if err != nil {
	return nil, err
}

// Add _foreign_keys pragma
values := url.Query()
values.Set("_foreign_keys", "on")
url.RawQuery = values.Encode()

// url.DSN wasn't updated
db, err := sql.Open("sqlite3", url.DSN)
if err != nil {
	return nil, err
}

Plans for a CLI?

If not, I can see myself building an external library that builds upon this.

This project looks great!

Issue with MySQL URLs in go >= 1.12.8

dburl.Parse returns an error on MySQL urls with a hostname format like tcp(host:port) (as seen in the examples for the mysql driver).

package main

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

func main() {
	_, err := dburl.Parse("mysql://user:password@tcp(localhost:3306)/db")
	if err != nil {
		panic(err)
	}
}

bash-5.0$ go1.12.7 run main.go
bash-5.0$ go1.12.8 run main.go
panic: parse mysql://user:password@tcp(mysql:3306)/db: invalid port ":3306)" after host

goroutine 1 [running]:
main.main()
        /Users/will.gorman/main.go:10 +0x63
exit status 2

There appears to have been a recent change to the standard library to more closely follow RFC 3986 that caused this.

Should dburl handle that case or is that not a style of URL that dburl was intended to support either?

Fails to parse passwords with '#' for mssql.

Tried with usql:
usql "ms://user:pw#[email protected]/base"
error: invalid port

This happens only with mssql. For postgres and mysql '#' doesn't cause any errors.

panic: runtime error: slice bounds out of range

goroutine 1 [running]:
github.com/knq/dburl.mssqlProcess(0xc8200c4000, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
/apps/go/src/github.com/knq/dburl/dburl.go:113 +0x886
github.com/knq/dburl.Parse(0x7ffc30de1e01, 0x35, 0xe0f1c0, 0x0, 0x0)
/apps/go/src/github.com/knq/dburl/dburl.go:79 +0x56a
main.openDB(0xc8201c2000, 0x0, 0x0)
/apps/go/src/github.com/knq/xo/xo.go:191 +0x63
main.main()
/apps/go/src/github.com/knq/xo/xo.go:43 +0x249

goroutine 17 [syscall, locked to thread]:
runtime.goexit()
/usr/local/go/src/runtime/asm_amd64.s:1696 +0x1

I think this happens because my password needs to be escaped???

my password is "!234#$";

if I pass an invalid password it says it cant connect which the expected result, however when I try the actual one it has this message.

I have tried it on a another DB (postgres) without any fancy password and it worked great, so I am guessing it has to do with the password?

Thanks.

couchbase error: n1ql: Connection failed Post

I tried with the following command

usql -c "SELECT username from login;" couchbase://pass:[email protected]/master_erp

I got the following error:

error: n1ql: Connection failed Post "http://user:[email protected]:9000/master_erp/query/service": dial tcp 213.255.246.203:9000: connectex: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
N1QL: Unable to connect to cluster endpoint http://user:[email protected]:9000/master_erp. Error Get "http://213.255.246.203:9000/pools": context deadline exceeded (Client.Timeout exceeded while awaiting headers)

Anyone can help me to fix the issue?

Support for New Relic integrations

New Relic database integrations rely on custom drivers (eg. nrpostgres, nrmysql, etc) to monitor database queries.

Since the list of drivers is predefined in scheme.go, supporting New Relic drivers require a manual overwrite after the URL is parsed.

I was thinking in opening a PR adding New Relic drivers as aliases for each supported driver, but I'm not sure if that's under the scope of the project. Another option could be use the sql.Drivers() to retrieve a list of registered drivers - but I'm not sure if we could get all the information from there.

Any thoughts on that?

no documentation on v0.16.0 Gen return parameters

34fab66 added a second string return parameter to Gen functions, but there doesn't appear to be any documentation on the meaning of the new value. I had to read the code to find out that it's the driver name.

A simple fix is to use named return parameters so that the meanings appear in the signature. I will send a PR soon.

Support socket files in mysql mode

I use this to run local MySQL servers, and I'd like to be able to send the Unix sockets one would use with it (db/mysql.sock, for instance) into xo. It seems the code explicitly rejects +unix protocols; is there some other way I can specify a Unix socket? If not, could it be added? Thanks!

Parse for {postgres,mysql}+unix schemes depend on filesystem state

Parsing of Postgres and MySQL schemes with a file path have different behavior depending if an element of the path exists on the filesystem and has specific properties. See functions resolveDir and resolveSocket in util.go.
This inconsistent behavior of Parse makes connection failures harder to debug. Worse: this behavior is not documented.

In general, it is very bad that a function called Parse has different results depending on the filesystem state, as it is not expected by a developer using this API.

Related to #6.

Parse returns postgres scheme for file protocol when directory in path exists

Since 0.19.1 dburl.Parse has been returning the postgres scheme when provided with a file: scheme path.
This only appears to occur when the path contains a directory that exists on the local filesystem.

u, _ := dburl.Parse("file:file.db")
fmt.Println(u) // prints: sqlite3:file.db

u, _ = dburl.Parse("file:/unknown/file.db")
fmt.Println(u) // prints: sqlite3:/unknown/file.db

u, _ = dburl.Parse("file:/etc/file.db")
fmt.Println(u) // prints: postgres:///etc/file.db

Support for MSSQL named pipe (np:) and shared memory (lpc:) DSNs

Hello, the Microsoft go-mssqldb module supports lpc: and np: DSNs. Could we get support for that here please? I'd like to use with sql_exporter and have raised this issue there as well.

Feature Request: redis dsn

redis://[pass@][ip\|host\|socket[:port]][/db-index]

duckdb support

duckdb is embedded sqlite-like database - https://duckdb.org/
golang driver - https://github.com/marcboeker/go-duckdb

Add support for arbitrary protocols

It would be great to be able to support arbitrary protocols. My use case is having different backend drivers for a given scheme.

TestParse failure if /var/run/postgresql is missing

$ git describe --always --dirty
cd61869
$ go test
--- FAIL: TestParse (0.00s)
	dburl_test.go:157: test 3 expected DSN `host=/var/run/postgresql`, got: `dbname=postgresql host=/var/run`
	dburl_test.go:157: test 4 expected DSN `dbname=mydb host=/var/run/postgresql port=6666`, got: `dbname=postgresql:6666/mydb host=/var/run`
	dburl_test.go:157: test 5 expected DSN `dbname=mydb host=/var/run/postgresql`, got: `dbname=postgresql/mydb host=/var/run`
	dburl_test.go:157: test 6 expected DSN `host=/var/run/postgresql port=7777`, got: `dbname=postgresql:7777 host=/var/run`
	dburl_test.go:157: test 7 expected DSN `dbname=booktest host=/var/run/postgresql port=4444`, got: `dbname=postgresql:4444/booktest host=/var/run`
	dburl_test.go:157: test 8 expected DSN `dbname=mydb host=/var/run/postgresql password=pass user=user`, got: `dbname=postgresql/mydb host=/var/run password=pass user=user`
	dburl_test.go:157: test 13 expected DSN `unix(/var/run/mysqld/mysqld.sock)/mydb?timeout=90`, got: `unix(/var/run/mysqld/mysqld.sock/mydb)/?timeout=90`
	dburl_test.go:157: test 14 expected DSN `unix(/var/run/mysqld/mysqld.sock)/mydb?timeout=90`, got: `unix(/var/run/mysqld/mysqld.sock/mydb)/?timeout=90`
	dburl_test.go:157: test 21 expected DSN `unix:/var/run/mysqld/mysqld.sock,test,timeout=90*mydb`, got: `unix:/var/run/mysqld/mysqld.sock/mydb,test,timeout=90*//`
	dburl_test.go:157: test 22 expected DSN `unix:/var/run/mysqld/mysqld.sock,timeout=90*mydb`, got: `unix:/var/run/mysqld/mysqld.sock/mydb,timeout=90*//`
FAIL
exit status 1
FAIL	github.com/xo/dburl	0.008s

This package only imports package from stdlib (according to go list -json), so this doesn't seem to be a version dependency issue.

This result is inconsistent with the build success on Travis, so I don't understand what's happening.

Special characters in password

In the .usqlpass format, If there are colon characters or pound characters in the password, the parsing module will get the wrong password, and then it will fail to connect.

Error parsing mysql DSN

DSN: mysql://root:secret@tcp(localhost:3306)/mysql

Error: parse "mysql://root:secret@tcp(mysql:3306)/mysql": invalid port ":3306)" after host

Can't register scheme starting with "my"

I would like to contribute a scheme for my driver called mylogin (see package github.com/dolmen-go/mylogin-driver/register.
I've started to code the integration, but I'm blocked by the following error:

panic: scheme my already registered

goroutine 1 [running]:
github.com/xo/dburl.registerAlias(0x1142ef4, 0x7, 0x1142ef4, 0x2, 0xc420089b00)
	.../src/github.com/xo/dburl/scheme.go:119 +0x3b7
github.com/xo/dburl.Register(0x1142ef4, 0x7, 0x114ca10, 0x0, 0x1, 0x0, 0x0, 0x0, 0x11425b0, 0x0)
	.../src/github.com/xo/dburl/scheme.go:167 +0x422
github.com/xo/dburl.init.0()
	.../src/github.com/xo/dburl/scheme.go:100 +0xee
FAIL	github.com/xo/dburl	0.010s
Error: Tests failed.

It appears that I can't register a second scheme that starts with the same 2 first letters as an existing scheme (scheme mysql is also automatically registered as my).

I think that this restriction should be removed: if a scheme has not defined a short name and a short name with the 2 letter prefix already exists, just skip registration of that auto-generated short name.