
Usql – Universal command-line interface for SQL databases - pjf
https://github.com/xo/usql
======
rjbwork
An unfortunate name, given that MS has had a language called U-SQL released
for their data lake platform for a number of years now. The title of this post
made me think they were bringing the functionalities to their SQL Database at
a quick glance.

[https://msdn.microsoft.com/en-us/azure/data-lake-
analytics/u...](https://msdn.microsoft.com/en-us/azure/data-lake-
analytics/u-sql/u-sql-language-reference)

------
arendtio
So this looks like a nice client which can be used with different databases,
but do I still have to write database specific SQL?

The first example I can think of is the way you set the size of the result set
in for the different databases (TOP vs. LIMIT vs. ROWNUM)[1]. I mean having
one client to rule them all is good, but in my experience the harder problem
is to learn all the different dialects depending on what database you use.

[1]:
[https://www.w3schools.com/sqL/sql_top.asp](https://www.w3schools.com/sqL/sql_top.asp)

~~~
MarkusWinand
This is something we have to blame the vendors for.

There is an international SQL standard from ISO, it's just not commonly
followed.

However, sometimes the standard isn't very useful in itself. For this example,
FETCH FIRST x ROWS ONLY is the syntax mandated by the standard. Although some
databases accept this in the meanwhile, LIMIT might have been a better choice
as it is supported by more databases.

[https://www.slideshare.net/MarkusWinand/modern-
sql/120](https://www.slideshare.net/MarkusWinand/modern-sql/120)

 _Edit_ : ps.: Please don't use w3schools.com as a SQL reference. It's utterly
outdated, prefers vendor syntax over standard and is sometimes just straight
wrong.

~~~
laumars
There's a few areas I think ANSI SQL falls down compared to some of the vendor
syntax. eg I really hate the way table joins are done in ANSI SQL. I get the
logic behind the syntax but the PL/SQL syntax for table joins gives me far
less mental gymnastics. In fact it is probably the only thing about PL/SQL
that I actually like.

~~~
da_chicken
Most DBAs I know consider any comma join syntax difficult to maintain and
difficult to debug because it's often difficult to tell the difference between
join conditions and filter conditions. It's also very easy to mistakenly
create a CROSS JOIN with comma join syntax, too.

The real pain comes when you try comparing the old vendor specific OUTER JOIN
syntax for Oracle and SQL Server. I guarantee you'll love JOIN ... ON ... over
comma joins.

Oracle:

    
    
       SELECT * 
       FROM T1, T2
       WHERE T1.PK1 = T2.FK1(+)
    

SQL Server:

    
    
       SELECT * 
       FROM T1, T2
       WHERE T1.PK1 *= T2.FK1
    

Note that the outer indicator goes on the opposite side. Oh, and, of course,
if you flip the order of the fields around, you've got to remember to flip the
(+) operator. Which of these three are identical:

    
    
       SELECT * 
       FROM T1, T2
       WHERE T1.PK1(+) = T2.FK1
    
       SELECT * 
       FROM T1, T2
       WHERE T2.FK1(+) = T1.PK1
    
       SELECT * 
       FROM T1, T2
       WHERE T2.FK1 = T1.PK1(+)
    

Now imagine you're joining 5 tables and want to reuse the JOIN syntax.

Yeah. Fuck that. I'll take this any day:

    
    
       SELECT * 
       FROM T1
       LEFT JOIN T2
           ON T1.PK1 = T2.FK1

~~~
laumars
I have genuinly written a lot of complex SQL for both MySQL and Oracle and
honestly I do prefer Oracles syntax for joins. But I did spend several years
writing PL/SQL before learning ANSI SQL so I guess it might just be a question
of what you're used to?

~~~
da_chicken
I would just be aware that the prevailing opinion is that the ANSI syntax is
considered better because it's considered clearer, more maintainable, and more
functional. I was perfectly fine with comma joins until I had to interact with
both SQL Server and Oracle. Then I found all kinds of hidden benefits. Like if
I want to query the same set of tables, I can just copy the whole FROM clause
and reuse it whole-hog. The logical separation is just nice, too. My _tables_
know how they relate to each other. They know what type of join is being done.
I don't have to tell the _fields_ how they relate to each other. Even if I get
my join conditions wrong, the relationships are correct.

Even Oracle's own documentation[0] tells you to avoid the (+) syntax:

> Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than
> the Oracle join operator. Outer join queries that use the Oracle join
> operator (+) are subject to [several restrictions], which do not apply to
> the FROM clause OUTER JOIN syntax[.]

The doc itself lists all the issues, which is about 10 of them. None of these
are opinions, either. They're actually technical limitations with (+), though
they may not be ones that you encounter. The only advantage I know of for the
(+) syntax is some uncommon issues with materialized view optimizations.

[0]:
[https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri...](https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm)

------
jug
[https://news.ycombinator.com/item?id=17299356](https://news.ycombinator.com/item?id=17299356)

------
kerng
Was confused at first, since it uses the same name as Microsofts Azure Data
Lake language:

[https://msdn.microsoft.com/en-us/azure/data-lake-
analytics/u...](https://msdn.microsoft.com/en-us/azure/data-lake-
analytics/u-sql/u-sql-language-reference)

Hint: its not related!

------
flas9sd
I'm really waiting for "Todo -> General -> 13\. proper PAGER support". Because
the sqlite3 cli never had support if I remember correctly, so piping to less
was always neccessary. A pager is key to getting comfortable on the cli imho.

~~~
vram22
Yes, that's a good feature to have. IIRC, IPython does not have it either, so,
for example, if you do a dir(object_name) for some object, like a module,
class, function, etc., if it has too many attributes, they scroll off the
screen, more so since IPython prints the attributes one per line. The CPython
shell is better for this, since it prints multiple attributes per line. For
IPython, I have to do this:

    
    
        for at in dir(object_name):
            print at,
    

and that will still scroll off if it is too long (although in that case it
would scroll off in Python too). So a built-in pager would be useful for many
such apps, since it will allow you to stay in the app; if you pipe the app's
output to less, you cannot interact with the app via the keyboard, until you
quit the less invocation.

~~~
orf
%page dir(object), %pinfo or %help object are your friends.

~~~
vram22
Great, will check those out, thanks.

------
joelthelion
It's missing completion for now, unless I'm mistaken?

Definitely a project to follow, though.

~~~
dmoreno
For me it's also a deal breaker, but I will keep an eye on it.

------
tannhaeuser
Nice. IBM have for some time now provided an Oracle-style SQL*Plus command
line utility for DB/2 with almost complete compat. I wonder if that could be a
common syntax for such DB-specific utilities, or if the usql folks have
insight to share towards another proposal.

------
Dowwie
I already use pgcli, written in Python, in conjunction with pspg to tabulate
results. I can't imagine what Usql would offer above what is already available
through pgcli. Could anyone comment?

~~~
cbcoutinho
The creater of dbcli answered this question in the other usql thread [0]

> _usql is a great tool if you 're familiar with Postgres' psql client and
> wish you could use it for other databases like MySQL, Cassandra etc._

> _dbcli tools are designed to preserve the usage semantics of the existing
> tools but improve on them by providing auto-completion. For instance you can
> use `\d` in pgcli and `SHOW TABLES` in mycli. This was a conscious decision
> to make pgcli and mycli drop in replacements for of MySQL and psql. I was
> also working under the assumption that people rarely use multiple databases,
> you 're either a postgres shop or a MySQL shop. If you have a mix of both,
> there is a good chance that not a single person is interacting with both of
> them on a daily basis. You have different teams using different databases.
> But my reasoning there could be flawed._

> _There is nothing stopping someone from adding an adapter to the usql tool
> to make it behave like MySQL (because they like the mysql client better)
> based on a command line argument, for instance._

[0]
[https://news.ycombinator.com/item?id=17303625](https://news.ycombinator.com/item?id=17303625)

------
int0x80
Looks good. I miss beeing able to pipe sql to the client via stdin. At least I
didnt see it in the README.

~~~
kenshaw
Yes, it reads from STDIN. If it doesn't, it's a bug, and please file an issue
on GitHub.

~~~
int0x80
Thanks! Didnt try it, just couldnt find it in the examples.

------
dkns
Looks nice. It would be incredible if it supported autocompletion of table
names and queries.

------
kyberias
SQL Server's own tools are so good one doesn't really need this.

~~~
lostapathy
If you're developing against MS SQL server from linux or mac, you absolutely
need something like this.

~~~
amjith
[https://github.com/dbcli/mssql-cli](https://github.com/dbcli/mssql-cli)

------
cryptos
Has anyone noticed that it is always mentioned that a tool is written in Go,
even if this implementation detail has no relevance for the user?

Nobody would write "Universal C++ CLI ...".

~~~
koolba
It _does_ matter to the user.

A Java based tool requires installation of a JVM and likely has a non-trivial
startup time. A Python / Ruby tool requires a runtime and probably some kind
of mucking with virtualenv or rvm to work properly. A Node.js tool would
require (haha!) a Node runtime and crossing your fingers that the NPM
dependencies for what you're going to use to connect to your database haven't
been compromised since the last build.

A Go based tool would be statically compiled, start up quick, and not have any
external dependencies.

~~~
jamespo
not if you use GraalVM :D

~~~
oblio
Or if you bundle your JVM... you can do it with the regular Java stack.

