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
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
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 Client
Oracle database drivers require dynamic libraries that are part of the Oracle Client installation.
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.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.Table() to query column type metadata for a single table:
// Fetch column metadata for given table
tcols, err := schema.Table(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.View(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 PrimaryKey(db *sql.DB, schema, table string) ([]string, error)
- func Table(db *sql.DB, schema, table string) ([]*sql.ColumnType, error)
- func TableNames(db *sql.DB) ([][2]string, error)
- func Tables(db *sql.DB) (map[[2]string][]*sql.ColumnType, error)
- func View(db *sql.DB, schema, view 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
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func PrimaryKey ¶ added in v0.0.7
PrimaryKey returns a list of column names making up the primary key for the given table in the given schema.
func Table ¶
Table returns the column type metadata for the given table in the given schema.
Setting schema to an empty string results in the current schema being used.
func TableNames ¶
TableNames returns a list of all table names.
Each name consists of a [2]string tuple: schema name, table name.
func Tables ¶
Tables returns column type metadata for all tables in the current schema.
The returned map is keyed by table name tuples.
func View ¶
View returns the column type metadata for the given view in the given schema.
Setting schema to an empty string results in the current schema being used.
Types ¶
type UnknownDriverError ¶ added in v0.0.4
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 ¶ added in v0.0.4
func (e UnknownDriverError) Error() string
Error returns a formatted string description.
Source Files