
Usql – A universal command-line interface for SQL databases - kenshaw
https://github.com/knq/usql
======
amjith
I'm the author of pgcli and mycli. I was tempted to write a universal cli that
supported both postgres and mysql, but then I decided to keep them separate
for the following reasons:

1\. Psql comes with backslash commands that are implemented at the client
level and not in the database. So you can't simply send `\dt` to the database.
That will fail. The same is true for mysql as well (such as `\u`).

2\. There wasn't a huge overlap in users who use Postgres and Mysql at the
same time. Even for the minority that do, I tried to keep pgcli and mycli
behave similarly so they can switch between them seamlessly.

3\. The code complexity needed to support multiple database backends would
make it hard for a newcomer to contribute to the project. This is just my
guess and I don't have data to back this up.

4\. I didn't think I could do a good enough job of maintaining parity with
psql and mysql's default client if I had a universal client. I still don't
have complete parity but it is close enough that users don't feel like their
compromising a lot.

I'm happy you're taking on this challenge and I'll be glad to give you
pointers on how the sql completion engine works in pgcli and mycli.

Cheers!

~~~
makmanalp
Man, what I really would love is some ipython console with SQLAlchemy stuff
built in that auto-sets up the connections and session objects, and that has
convenience functions for table / database info and dumping data out to
CSV/TSV - that'd come close, I think.

~~~
amjith
You know this actually exists: [https://github.com/catherinedevlin/ipython-
sql](https://github.com/catherinedevlin/ipython-sql)

I found out about this from one of the core-devs of pgcli. He made it possible
to embed pgcli inside ipython. We wrote a blog post about how to use this:
[http://pgcli.com/embedding-pgcli-in-ipython.html](http://pgcli.com/embedding-
pgcli-in-ipython.html)

~~~
makmanalp
Wow, I even had this repo starred but somehow ignored the contents entirely.
Thank you, kind stranger! This'll be so useful to me.

------
kenshaw
This is a new tool (literally only started in the last 24 hours), but already
provides a common, usable interface to PostgreSQL, MySQL, Microsoft SQL
Server, SQLite3, and Oracle databases. Plans to add support for other NewSQL
databases (VoltDB, etc) in the future, as well as to make it have feature
parity (and the same interface as) postgres' psql command line tool.

~~~
abtinf
My sincere thanks for your work on xo. This project looks promising as well.

------
recursive
Is there a way to try this without figuring out how to set up a go build
environment? I've never used go, but wanted to try this out.

I installed go 1.8, and then issued the prescribed command line. It tells me
`# github.com/mattn/go-sqlite3 exec: "gcc": executable file not found in
%PATH%`. This looks like a rabbit hole of unknown depth that I'd prefer to
stay out of.

~~~
kenshaw
This project is only roughly a day and a half old. However, I will make binary
releases available in the future.

------
rjbwork
Might run into some copyright/trademark issues [https://docs.microsoft.com/en-
us/azure/data-lake-analytics/d...](https://docs.microsoft.com/en-
us/azure/data-lake-analytics/data-lake-analytics-u-sql-get-started)

~~~
yAnonymous
Is it even possible to copyright/trademark that when 75% of the name is a
technology they do not own?

------
gigatexal
This is an ambitious goal since the supported databases are so different.
Postgres supports window functions for example whereas SQLite does not.

~~~
ak39
Is the goal of this project to homogenize syntax or homogenize the call level
interface (api)? I can understand the need for the latter, not the former.

~~~
kenshaw
This is not about making a generic SQL implementation. This is simply about
being able to execute SQL consistently, and to use the same CLI interface for
doing so. The user would still need to know the individual SQL dialects for
each database they are using. However, at least then if you become familiar
with, let's say, mysql by using this tool, the same
configuration/settings/etc. will be used for all the other databases.

~~~
nbevans
Oh I see - so it is an adapter not an abstractor. I'm curious what the
advantages will be over the widely supported ODBC API. Will you add ODBC
support?

~~~
kenshaw
I've already looked into adding ODBC support, and will try to add it. The real
limitation here is that I am not working with a ODBC setup locally, so
writing/testing anything for it is not really possible at the moment. Also, I
believe all the ODBC Go driver implementations require the use of a C lib/dll,
so it would need to be similar to the Oracle support and not "out of the box".
The beauty of this, is that at the moment you can simply do a 'go get -u
github.com/knq/usql' and have a working SQL client that is exactly the same
for the 4 "natively supported" databases, and with Oracle support if you have
the right local client libraries.

------
AtlasLion
Reminds me of sqlline (used it like 8 years ago, not sure if still active). A
Java based SQL command line client.
[http://sqlline.sourceforge.net](http://sqlline.sourceforge.net)

~~~
leoh
Came here to mention this. It is the superior solution because it will take an
arbitrary JDBC driver thereby supporting far more databases.

------
peteretep
It's a shame no-one has used DBI and SQL::Abstract etc to build something
similar in Perl.

~~~
carl1234
see GNU sql, it comes with GNU parallel
[https://www.gnu.org/software/parallel/](https://www.gnu.org/software/parallel/)
"GNU sql aims to give a simple, unified interface for accessing databases
through all the different databases' command line clients. So far the focus
has been on giving a common way to specify login information (protocol,
username, password, hostname, and port number), size (database and table
size), and running queries.

The database is addressed using a DBURL. If commands are left out you will get
that database's interactive shell.

When using GNU SQL for a publication please cite:

O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different
Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.

~~~
cryptarch
Ah, it's that GPL + nag/guilt-trip model again...

Following that rationale, why doesn't any sane person cite Matlab, R, Tex or
MS Word on their publications?

------
mdf
This is pretty neat!

What would really make a difference in how we perceive the SQL CLI world would
be an IPython-like interface – complete with syntax highlighting, tab-
autocomplete menus, easy multiline backlog entry editing etc.

~~~
kenshaw
Yes, I already have a working branch with syntax highlighting and autocomplete
hacked together, but the issue is that it breaks for some of the databases.
When I can get it stable, I'll make available publicly.

------
koolba
A few years back I toyed with the idea of building something like this. It was
a dark time that I was stuck using a database besides Postgres.

If you want to really make this usable here's what you need to add:

\- usql -f foo.sql

\- Allow @path/to/file.sql and @@path/to/relative/file.sql references

\- Common syntax for referencing env variables in SQL (each DB's utilities
have slightly different syntaxes so have to pick one)

\- External transaction demarcation to ensure a set of commands get executed
together (ex: psql --single-transaction)

\- _Some_ autocompletion (if you plan on this being used for interactive use)

~~~
kenshaw
Auto-completion will come in time. I don't know if I would allow "relative"
pathing via @@, I'm not sure why you wouldn't just use general paths. As for
the other options, such as -f, etc., yes those will be put in place. I am
hoping to make this a 100% drop in replacement for psql, so whatever psql
supports on the command line, as well as the various backslash commands (ie,
\f) will be added.

~~~
koolba
Relative paths are a must. I don't think I ever use non-relative paths.

Say you have a folder structure like this:

    
    
        sql/
          tables/
            foo.sql
            bar.sql
          main.sql
    

And the contents of main.sql are something like this:

    
    
          @@tables/foo.sql
          @@tables/foo.sql
    

Then you can run main.sql via: usql -f sql/main.sql

If script file resolution is not relative pathed to the parent, it gets very
fragile as you must invoke the parent from a specific location, or, you have
to hard code full pathes in your scripts (arguably even uglier).

~~~
kenshaw
This is a feature in psql? I must admit if it is, I'm not familiar with it.

~~~
koolba
Yes psql supports it. The syntax is "\i path/to/file.sql" or "\ir
relative/path/to/file.sql". The latter is resolved relative to the parent
script. Makes nesting scripts possible without losing your sanity.

~~~
kenshaw
Yes, if it's in psql, I'll get it into usql in the near future.

(for certain large values of near).

------
Flimm
Slightly off topic, but I was recently looking for a tool like mytop but for
Postgresql. Does any one know if it exists?

------
TurlochOTierney
Oracle sqlcl and sqldeveloper has/had multiple SQL support. You have to use a
hint on occasion and it is biassed

~~~
TurlochOTierney
Biased towards oracle SQL. I can check it out it was mostly for migration to
Oracle Database.

~~~
TurlochOTierney
Usql requires go. Sqlcl is currently 12mb.

~~~
gjem97
It doesn't require Go any more than nginx requires C. Go executables are for
the most part static, and depend on very little, making them highly portable
across distros.

------
fiatjaf
Is this just running Go's database/sql with various adapters?

If it is that you can even include Neo4j support.

~~~
kenshaw
Yes, the plan is to add support for any database (relational or otherwise)
that has the ability to be controlled through a "command and query" style
interface. This project is only about 1 day old at this point, but I'll make a
point to rapidly integrate as many other DBs that are supported by Go as fast
as humanly possible.

Btw, it would be relatively straightforward to add support on your own. PRs
always are welcome!

------
mollyporph
Somewhat confusing since azure data lake analytics uses a sql-esque language
called u-sql

~~~
ulber
Even searching for usql on GitHub [1] returns an Azure U-SQL related result
first.

[1]:
[https://github.com/search?utf8=%E2%9C%93&q=usql](https://github.com/search?utf8=%E2%9C%93&q=usql)

------
nbevans
Is this like ODBC but with its own SQL dialect to abstract away the underlying
dialect?

~~~
kenshaw
No. You need to use the native SQL dialects for each database. It provides a
standard readline-style interface and uses various Go SQL database drivers to
talk to the actual databases.

------
mxuribe
I can see this being a very handy tool! Kudos on the work being done here!

