database/sql: Support for database, catalog, schema and table level metadata

[gopherbot]

by glen.newton:

database/sql does not offer the ability to dynamically peruse databases, catalogs,
schemas and tables and their underlying metadata at runtime. Without this, it is not
possible to make, for example, a Go program that can copy arbitrary tables from a
database, by examining their metadata at runtime.

What is needed is the equivalent of Java JDBC's DatabaseMetaData
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html

While JDBC's DatabaseMetaData may seem be overkill, being able to examine a table's
metadata in the same manner as the table's data is attractive. In order to support rich
and complex interactions with sql databases, emulating JDBC would not be a bad idea.


As an initial step however, I would suggest the following minimum extensions to the sql
package:

func (db *DB) GetSchemas() (*Rows, error)
JDBC equivalent & explanation:
  ResultSet getSchemas()   
  http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getSchemas%28%29

func (db *DB) GetCatalogs() (*Rows, error)
JDBC equivalent & explanation:
  ResultSet     getCatalogs()
  http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCatalogs%28%29


func (db *DB) GetTables(catalog String, schemaPattern String, tableNamePattern String,
types String[]) (*Rows, error)
JDBC equivalent & explanation:
  ResultSet     getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
  http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]%29


func (db *DB) getColumns(catalog String, schemaPattern String, tableNamePattern String,
columnNamePattern String) (*Rows, error)
JDBC equivalent & explanation:
  ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
  http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29

The following are important and should also be considered:
getAttributes(...)
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getAttributes(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

getCrossReference(...)
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getCrossReference%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29

getIndexInfo(...)
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)

getPrimaryKeys(...)
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String,%20java.lang.String,%20java.lang.String)


I would also suggest that bug https://golang.org/issue/5606 be
solved in a similar and consistent fashion with something like
http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html

Apologies for using Java as an example, but JDBC has done its homework and I think it
represents best practices. How it maps into a Go context is of course to be debated and
I am willing to accept that my very direct mapping suggestion may not be the best or the
most acceptable to the community.

[rsc]

Comment 1:

Labels changed: added release-none.

Status changed to Accepted.

[gopherbot]

Comment 2 by arnehormann:

I disagree with the premise that we need this in database/sql. It is needed, but it
belongs in the drivers.
database/sql already has problems with its level of abstraction.
Some examples:
* no unified form for different sql dialects (including the wildcard for prepared
statements)
* return value of Exec is unavailable in PostgreSQL, one has to use Query with a special
format
* no support for bulk-uploading from files
* anemic metadata (no column types etc)
I think everything you mention above belongs in the drivers, as they can provide the
maximum amount of details and database/sql does to much as is (see bullets above).
Users know which database they use, ORM developers want access to the maximum amount of
metadata and need more than what you describe can offer.
Also, I really don't want Go to have something like "UnsupportedOperationException" when
one dbms doesn't support one of the operations. The Exec-problem with PostgreSQL comes
close enough.
For column metadata, https://golang.org/cl/43510044 adds the fix for issue #5606
so the drivers can access it. Schema discovery etc. could be added to database/sql, but
I think this also belongs into the driver or support libraries.
Putting it all into database/sql will bloat it and shadow interesting details - just
think of unified error handling using just the stuff common to all drivers and losing
all additional information.

[gopherbot]

Comment 3 by freeman.wes:

+1 on drivers doing this. As an author of a driver, I loved the simple API to conform to
in database/sql, and most of the operations you mention wouldn't make sense for my
driver.
However, what is the best/normal practice for doing this sort of thing through drivers,
as you usually import the driver with _, so it doesn't expose things.

[gopherbot]

Comment 4 by arnehormann:

I'm co-maintainer of a driver, too.
In github.com/go-sql-driver/mysql, we provide some functionality for named imports.
The list: NullTime, CSV-bulk-uploading for LOAD DATA LOCAL INFILE and configuration of
TLS and critical error logging.
See http://godoc.org/github.com/go-sql-driver/mysql.
For simple scenarios, users don't have to care and can use the _ import. For those
needing the functionality, they can make it a regular import.
I built a workaround based on unsafe to test my concept for more column metadata:
http://godoc.org/github.com/arnehormann/sqlinternals and
http://godoc.org/github.com/arnehormann/sqlinternals/mysqlinternals
It still lacks a lot of information and is a little complicated because we don't store
parts of the available data as it can't be used anywhere. But all of this could be
improved with my CL.

[griesemer]

Comment 5:

Labels changed: added repo-main.

[davidsarmstrong]

Golang had the promise of becoming the next multi-platform tool of choice with, in my opinion, so many advantages over Java (compiled code, more efficient runtime, much quicker startup times and so on). For a lot of business applications it's important to remain DB-independent but also a lot of our code depends on being able to go directly from a result set to output. For example, we create HTML tables directly from the SQL in many cases. Not having access to the resultset metadata means having to hard code every column. It's not just that you have to set up variables of the Null variant to receive the data, you also have to describe the for each one and check for nulls on each column. You can't automate any of that anymore.

So we've decided to stay with Java (or C++) for now. I understand you can do some of this with interfaces but it doesn't seem to work with "time" results.

Perhaps there is a way to write a layer on top of each DB driver to get the meta data, but doesn't it make sense for that to be part of the spec. After all, it is SQL we are talking about. It has a standard.

[mageddo]

@davidsarmstrong On real world application with considerable size is impossible to be DB independent, for business applications I admit that Java is better than Golang but not by this reason

[simonegiacomelli]

I'm using a small web server written in go to generate json data from sql tables (firebird for now). In the invoker side I dynamically create a form to edit some data.
Without metadata information I can't handle basic constraint. The first I would implement is string length checking. In other words, if a field is varchar(5) I'll stop the input to 5 length char.
This feature would be very useful to my project.

[davidsarmstrong]

@mageddo Our applications is about 2 million lines of code and the application itself was moved successfully from MySQL to Postgresql. You have to be careful about your use of standard SQL but you can do it (as we did). In any case, there are a lot of applications which need meta data and it's just short-sighted, in my opinion, not to include that as part of the sql package. And there is no reason it couldn't be included as in the same way it is done with JDBC.

[kardianos]

I'm of the opinion this should be closed as "Won't fix". There is no intrinsic reason I'm aware of this could not be provided by a third party package. The api for this package could look something like:

SchemaNames(db *sql.DB) ([]string, error)
SchemaObject(db *sql.DB, name string) ([]sql.ColumnType, error)
Schema(db *sql.DB) (map[string][]sql.ColumnType, error)

Internally it would look at *sql.DB.Driver() to determine the database type (or run a query), then run database specific queries to return the schema type.

If this is implemented as a third party package, I think we could re-open this. But until then I believe this should be closed.

NOTE: This issue is for returning database schema metadata, not for returning query metadata. Query metadata does need support from database/sql and is being considered.

[kardianos]

Before considering adding any API that does this, I'd like to see a normal package implement this first. Please post any stoppers to making such a package here.

[jimsmart]

Greetings,

I saw this issue a couple of days ago while googling for a solution for similar. I read the discussion and decided to implement something like @kardianos suggestion in #7408 (comment)

So far 'package schema' supports Postgres, SQL Server, SQLite, Oracle, MySQL dialects, with all tests passing (if you can get all the dbs set up) and it recognises a good handful of popular drivers for those dbs. Driver detection is currently (fragile) stringified-type matching. Tests only cover expected API usage, so please be gentle!

https://github.com/jimsmart/schema

The godoc has usage examples.
https://godoc.org/github.com/jimsmart/schema

— Thoughts?

[kardianos]

@jimsmart This looks real good. I haven't looked at the code closely yet, but API looks nice. Great work!

I could see putting this into a common org repo like https://github.com/golang-sql and setting up an on-demand testing server for this and the drivers. I'm not sure if we would look to integrate the API into the std lib or not, but I think those might be some good next steps.

[jimsmart]

Thanks for the feedback.

Yesterday I set up the repo to use Travis, which does support a handful of these dbs, although I've not yet had a chance to look at configuring them there yet, it's on my todo list. Even if you don't use Travis on the other repo, this work would still be useful because it would codify the necessary test setup commands, etc.

[gnewton]

@jimsmart I am just circling back to this (sorry!), but this looks great! I will be building a small application to test this out over the next few weeks and provide you with some feedback. Thanks! :-)

Looking at your API your schema.Table(..) does not expose a Table.PrimaryKeys(), which I would certainly find useful (similar to https://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String,%20java.lang.String,%20java.lang.String). I would also find a Table.ForeignKeys() to be useful. 👍 :-) Thanks.

[jimsmart]

@gnewton good idea! — Not sure when I'll get a chance to look at implementing it, but feel free to track the issue I just opened. Thanks!

[nineinchnick]

We needed something like this in usql to implements commands describing different database objects. I wanted an API that returns more metadata than just object names, so I decided to write our own. I started by looking at the JDBC API but then unified the arguments to use a single structure.

https://pkg.go.dev/github.com/xo/usql/drivers/metadata

There are 4 implementations of those interfaces:

• a generic one querying information_schema
• DB specific implementations for PostgreSQL, Oracle and SQLite

I'm posting this because it might be relevant. I stumbled upon this thread when looking for existing solutions. I'm not saying it's the best design ever. If you have comments, please create issues in the usql repo.