schema

README

schema

schema is a Go package providing access to database schema metadata, for database/sql drivers.

TODO more docs

Currently supporting the following database engines / SQL dialects:

• Microsoft SQL Server
• MySQL
• Oracle
• Postgres
• Snowflake
• SQLite
• Vitess

For a list of supported drivers, and their capabilities with regards to sql.ColumnType support, see drivercaps

Installation

go get github.com/jimsmart/schema

import "github.com/jimsmart/schema"

Dependencies

• A supported database driver.
• Standard library.
• Ginkgo and Gomega are used in the tests.
• Tests also require Docker Compose to be installed.

Example

See GoDocs for usage examples.

Documentation

GoDocs https://godoc.org/github.com/jimsmart/schema

Testing

Database services for testing against are hosted in Docker.

To bring up the database services: execute docker-compose up inside the project folder, and wait until all of the Docker services have completed their startup (i.e. there is no further output in the terminal), then open a second terminal. (In future one may choose to use docker-compose up -d instead)

To run the tests execute go test inside the project folder.

For a full coverage report, try:

go test -coverprofile=coverage.out && go tool cover -html=coverage.out

To shutdown the Docker services, execute docker-compose down -v inside the project folder.

Oracle Setup Checklist

Build Docker Image

Build a Docker image for Oracle, by executing script:

./build_oracle_docker_image.sh

Once the script has successfully completed, you are free to delete the folder used when creating the image:

rm -rf ./test/docker-oracle

Increase Docker's RAM limits

By default, Docker allocates 2gb RAM to each container. To prevent out-of-memory errors when running Oracle, increase Docker's RAM limits:

Docker -> Preferences -> Resources -> Advanced -> Memory, change to 4gb, click Apply & Restart.

Install Oracle Instant Client

Oracle database/sql drivers require dynamic libraries that are part of the Oracle Instant Client installation.

Mac

brew tap InstantClientTap/instantclient
brew install instantclient-basic

License

Package schema is copyright 2018-2021 by Jim Smart and released under the BSD 3-Clause License.

History

• v0.2.1: Added dialect alias for Vitess driver github.com/vitessio/vitess.
• v0.2.0: Replaced Table and View methods with ColumnTypes method.
• v0.1.0: Added schema name to methods and results.
• v0.0.8: Disabled Oracle tests on Travis.
• v0.0.7: Added PrimaryKey method. TableNames and ViewNames are now sorted. Improved Oracle testing. Refactored dialect handling.
• v0.0.6: Fix Oracle quoting strategy. Added support for driver github.com/godror/godror.
• v0.0.5: Added dialect alias for Snowflake driver github.com/snowflakedb/gosnowflake.
• v0.0.4: Improved error handling for unknown DB driver types. Test environment now uses Docker.
• v0.0.3: Minor code cleanups.
• v0.0.2: Added identifier escaping for methods that query sql.ColumnType.
• v0.0.1: Started using Go modules.
• 2019-11-04: Fix for renamed driver struct in github.com/mattn/go-oci8 (Oracle)
• 2019-11-04: Fix for renamed driver struct in github.com/denisenkom/go-mssqldb (MSSQL)

Documentation

Overview

Package schema provides access to database schema metadata, for database/sql drivers.

For further information about current driver support status, see https://github.com/jimsmart/schema

Table Metadata

The schema package works alongside database/sql and its underlying driver to provide schema metadata.

// Fetch names of all tables
tnames, err := schema.TableNames(db)
	...
// tnames is [][2]string
for i := range tnames {
	fmt.Println("Table:", tnames[i][1])
}

// Output:
// Table: employee_tbl
// Table: department_tbl
// Table: sales_tbl

Both user permissions and current database/schema effect table visibility.

Use schema.ColumnTypes() to query column type metadata for a single table:

// Fetch column metadata for given table
tcols, err := schema.ColumnTypes(db, "", "employee_tbl")
	...
// tcols is []*sql.ColumnType
for i := range tcols {
	fmt.Println("Column:", tcols[i].Name(), tcols[i].DatabaseTypeName())
}

// Output:
// Column: employee_id INTEGER
// Column: first_name TEXT
// Column: last_name TEXT
// Column: created_at TIMESTAMP

To query table names and column type metadata for all tables, use schema.Tables().

See also https://golang.org/pkg/database/sql/#ColumnType

Note: underlying support for column type metadata is driver implementation specific and somewhat variable.

View Metadata

The same metadata can also be queried for views also:

// Fetch names of all views
vnames, err := schema.ViewNames(db)
	...
// Fetch column metadata for given view
vcols, err := schema.ColumnTypes(db, "", "monthly_sales_view")
	...
// Fetch column metadata for all views
views, err := schema.Views(db)
	...

Primary Key Metadata

To obtain a list of columns making up the primary key for a given table:

// Fetch primary key for given table
pks, err := schema.PrimaryKey(db, "", "employee_tbl")
	...
// pks is []string
for i := range pks {
	fmt.Println("Primary Key:", pks[i])
}

// Output:
// Primary Key: employee_id

Index

• func ColumnTypes(db *sql.DB, schema, object string) ([]*sql.ColumnType, error)
• func PrimaryKey(db *sql.DB, schema, table string) ([]string, error)
• func TableNames(db *sql.DB) ([][2]string, error)
• func Tables(db *sql.DB) (map[[2]string][]*sql.ColumnType, error)
• func ViewNames(db *sql.DB) ([][2]string, error)
• func Views(db *sql.DB) (map[[2]string][]*sql.ColumnType, error)
• type UnknownDriverError
  • func (e UnknownDriverError) Error() string

Functions

func ColumnTypes

func ColumnTypes(db *sql.DB, schema, object string) ([]*sql.ColumnType, error)

ColumnTypes returns the column type metadata for the given object (table or view) in the given schema.

Setting schema to an empty string results in the current schema being used.

func PrimaryKey

func PrimaryKey(db *sql.DB, schema, table string) ([]string, error)

PrimaryKey returns a list of column names making up the primary key for the given table in the given schema.

func TableNames

func TableNames(db *sql.DB) ([][2]string, error)

TableNames returns a list of all table names.

Each name consists of a [2]string tuple: schema name, table name.

func Tables

func Tables(db *sql.DB) (map[[2]string][]*sql.ColumnType, error)

Tables returns column type metadata for all tables in the current schema.

The returned map is keyed by table name tuples.

func ViewNames

func ViewNames(db *sql.DB) ([][2]string, error)

ViewNames returns a list of all view names.

Each name consists of a [2]string tuple: schema name, view name.

func Views

func Views(db *sql.DB) (map[[2]string][]*sql.ColumnType, error)

Views returns column type metadata for all views in the current schema.

The returned map is keyed by view name tuples.

Types

type UnknownDriverError

type UnknownDriverError struct {
	Driver string
}

UnknownDriverError is returned when there is no matching database driver type name in the driverDialect table.

Errors of this kind are caused by using an unsupported database driver/dialect, or if/when a database driver developer renames the type underlying calls to db.Driver().

func (UnknownDriverError) Error

func (e UnknownDriverError) Error() string

Error returns a formatted string description.