Feature: Column metadata harmonized with row result metadata

[Allam76]

database/sql forces drivers to use the ColumnType for query return metadata.
This is also used internally in usql.

Would it be possible to ask the column metadata from usql to also be able to conform to this format? Or at lease an extension point so that it can be done in userland? JDBC, ODBC, etc already have this mapping.

The drivers are forced to implement this conversion so if should not be too difficult. I could possibly volunteer:-).

This is useful when migrating from one database to another and for external tools. Also I understand this might not be a priority for the usql tool itself.

[kenshaw]

I'm not sure what the request is here? For what it's worth, how Go "sees" the types of those columns isn't what you want usql reporting on the metadata queries. You want the information as the database sees it. It's worth mentioning, as well, that the ColumnType you're referring to is only available after a query has been executed. This isn't information that is "queryable" from the perspective of usql.

[Allam76]

Consider JDBC for example. There the metadata is mapped to an external JDBC type system and then all client tools can use that rather than that each client tool must map to all possible databases.

I realize this might be a bit out of scope for a pure terminal-based front-end like usql. Unless if you want to add a data migration tool.

The ColumnType is as far as I know the only equivalent in go to the JDBC Type.

The implementation at least for postgres is rather trivial:
https://github.com/lib/pq/blob/8c6de565f76fb5cd40a5c1b8ce583fbc3ba1bd0e/rows.go#L24-L42

[kenshaw]

If you want to extract metadata about tables or queries from a SQL database, please use my xo tool. usql is meant to be a generic command-line client, and is a humble reimplementation of psql. While you might be able to use usql for something like a database migration, that's really not the design/intent of usql, and such tasks are best accomplished with individual database's provided toolsets and/or use more specific Go packages/tools that try to provide this kind of functionality.

Also, I'm still not sure what the feature request is? If it's easier to explain with code, we're always open to PRs!

[Allam76]

I'm working on a sql federated query engine. That is, a database without storage engine. So one needs to connect to other databases to fetch the data. Naturally many different databases should be supported so to stay sane some abstraction is needed.

usql seemed to have the part of the metadata: golang/go#7408 (comment)

In the same thread we have the exact feature request I was looking for: golang/go#7408 (comment) but shot down for inclusion in standard go.

I'll take a look at xo tool as well as schema:

[Allam76]

After quick investigation of real implementations, I realize that ColumnType as implemented by drivers will not be sufficient.

This is quite a bit far out to ask from you. I close this issue.

However, I still believe usql has the best approach to this. I'll see if I can get a PR on it.

Sorry for taking up your time.

[nineinchnick]

The ColumnType from database/sql is too limited for us. For example, it doesn't return column defaults. Since I had to implement types for all other schema objects, I didn't try to reuse it, mainly for consistency. Unfortunately, it's not an interface. So you'd have to rewrite the metadata.Column into the database/sql.ColumnType manually.

The metadata.Writer interface is modelled after https://github.com/postgres/postgres/blob/master/src/bin/psql/describe.c. It was created explicitly to support the \d (describe) commands. But when working on it, I knew we needed a more generic metadata.Reader interface to support multiple databases. It's also used in the completer.

When I was writing the metadata API, I knew it might be used outside of usql, that's why I posted in the go repo. But there wasn't a use case for it yet. I'm very excited you found one, but right now, there are no stability guarantees for it.

I'd suggest the following course of action. You can continue evaluating it, and I'd be grateful for any bug reports. If you have any major feature requests, we should consider creating a standalone repo to provide it as a library. That doesn't mean any changes in usql are required. We can make a decision to use it, or not, independently.

I'm interested in improving this API and also adding support for more databases, from which usql would benefit.

To wrap this up, I invite you to our Discord where it might be easier to discuss ideas. I'm very glad for the feedback you're providing.

[Allam76]

Thanks for the feedback and help. Much appreciated. I'll take a stab at it.