7 ms·

Usql – Universal command-line interface for SQL databases

160 points by pjf 8y ago

jug 8y ago
https://news.ycombinator.com/item?id=17299356 https://news.ycombinator.com/item?id=17299356
joelthelion 8y ago
It's missing completion for now, unless I'm mistaken? Definitely a project to follow, though.
- dmoreno 8y ago
  For me it's also a deal breaker, but I will keep an eye on it.
arendtio 8y ago
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 8y ago
  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 8y ago
    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 8y ago
      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 8y ago
        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?
        GFischer 8y ago
        Maybe on what you got started on. I started with T-SQL, then PL/SQL and now back to T-SQL, so you can guess which I prefer.
- nicoburns 8y ago
  Depends which databases you use. This is super useful for Postgres/Mysql/SQLite, which aren't completely compatible, but have a large amount of crossover (they all use LIMIT for example).
- sixdimensional 8y ago
  I have a story to tell about this. I worked on a project for a company once, that mapped a single custom SQL dialect to nearly every other dialect. We wrote a translator / mapper that converted the AST of that single dialect of SQL to every platform specific as close as we could. That included as many function mappings, data type mappings, SQL query expressions, as we could. Then we had custom drivers that could be used which parsed that SQL. One of the most difficult projects I've done, but it worked reasonably well, for most basic functions. We struggled with custom platform specific features of course, but even figured out workarounds for them. Of course, if a database didn't support a base level of the SQL standard (say SQL-92), for example, Cassandra - it was impossible for the translator to take a complex expression and handle it on its own - there was no target syntax to translate to. Anyways, point being - been there tried this, and it is possible. An open source implementation of what I did would have been awesome, alas, it was for a proprietary project. And ultimately, if you want one specific syntax, unless you find creative solutions around missing features between platforms (possible but much different problem that goes beyond syntax) - it is never quite as powerful for each platform. I still think every day about starting some kind of open source SQL gateway client project to normalize it and sit in front of databases. It's a big project though, and like I said, I question the value sometimes. Normalizing the syntax for databases that support SQL >=SQL99 (or even 2003 standard) would not be too bad though.
cryptos 8y ago
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 ...".
- nicoburns 8y ago
  It's a benefit to me over it being a Ruby/Node/Python CLI. Means it's likely to be easy to install, and performant.
- nolok 8y ago
  nodejs and rust comes to mind as language getting included the same way. For me "in go" is useful information since I infer "easy to deploy" from it
- Thaxll 8y ago
  I've never seen a modern C++ CLI compiled without dependencies.
- georgyo 8y ago
  People are fanatic about using tools written in a language they like. If people think that a program written in C will be faster than a program written in Python then it is a feature to them that may or may not be valid. However modern languages have a lot of trade offs in deployment strategies that are just easier to say by stating the language. Golang is normally fully statically compiled so getting this tool on a host requires nothing other than coping the completed binary. It often requires nothing at all from the system it is running on. This is an implied feature of the language. C/C++ code can be that simple, but static binaries are not always possible. And then you require the libaries on the remote machine. There is a good chance Python will be on a remote machine, but deployment could be complicated. Psql support requires the the libaries to be on that machine for example. And if the tool was written in node, a ton dependencies and work would be required for a normal user to use it. I agree that the language should not be a feature, but it is. Containers/snap/flatpak sorta help with this, but they are even more work for the user currently.
- koolba 8y ago
int0x80 8y ago
Looks good. I miss beeing able to pipe sql to the client via stdin. At least I didnt see it in the README.
- projektfu 8y ago
  I didn't run it, but in the code it appears to take commands from stdin, but if they're coming from a TTY (interactive), they are processed through readline. The "-f -" trick didn't seem to be supported by the code.
- kenshaw 8y ago
  Yes, it reads from STDIN. If it doesn't, it's a bug, and please file an issue on GitHub.
  - int0x80 8y ago
    Thanks! Didnt try it, just couldnt find it in the examples.
rjbwork 8y ago
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-sql/u-sql-language-reference https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u...
Dowwie 8y ago
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?
- dmoreno 8y ago
  Multiple database vendor support, not only postgres.
- cbcoutinho 8y ago
  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
dkns 8y ago
Looks nice. It would be incredible if it supported autocompletion of table names and queries.
flas9sd 8y ago
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 8y ago
  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 8y ago
    %page dir(object), %pinfo or %help object are your friends.
    - vram22 8y ago
      Great, will check those out, thanks.
tannhaeuser 8y ago
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.
kerng 8y ago
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-sql/u-sql-language-reference https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u... Hint: its not related!
kyberias 8y ago
SQL Server's own tools are so good one doesn't really need this.
- pjmlp 8y ago
  SQL Developer for Oracle is also quite good.
- dstroot 8y ago
  Unless you are using MySQL or PostgreSQL. ;)
- lostapathy 8y ago
  If you're developing against MS SQL server from linux or mac, you absolutely need something like this.
  - amjith 8y ago
    https://github.com/dbcli/mssql-cli https://github.com/dbcli/mssql-cli

160 points by pjf 8y ago

jug 8y ago

https://news.ycombinator.com/item?id=17299356 https://news.ycombinator.com/item?id=17299356

joelthelion 8y ago

It's missing completion for now, unless I'm mistaken? Definitely a project to follow, though.

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

arendtio 8y ago

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 8y ago
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 8y ago
  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 8y ago
    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 8y ago
      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?
      - GFischer 8y ago
        Maybe on what you got started on. I started with T-SQL, then PL/SQL and now back to T-SQL, so you can guess which I prefer.
nicoburns 8y ago
Depends which databases you use. This is super useful for Postgres/Mysql/SQLite, which aren't completely compatible, but have a large amount of crossover (they all use LIMIT for example).
sixdimensional 8y ago
I have a story to tell about this. I worked on a project for a company once, that mapped a single custom SQL dialect to nearly every other dialect. We wrote a translator / mapper that converted the AST of that single dialect of SQL to every platform specific as close as we could. That included as many function mappings, data type mappings, SQL query expressions, as we could. Then we had custom drivers that could be used which parsed that SQL. One of the most difficult projects I've done, but it worked reasonably well, for most basic functions. We struggled with custom platform specific features of course, but even figured out workarounds for them. Of course, if a database didn't support a base level of the SQL standard (say SQL-92), for example, Cassandra - it was impossible for the translator to take a complex expression and handle it on its own - there was no target syntax to translate to. Anyways, point being - been there tried this, and it is possible. An open source implementation of what I did would have been awesome, alas, it was for a proprietary project. And ultimately, if you want one specific syntax, unless you find creative solutions around missing features between platforms (possible but much different problem that goes beyond syntax) - it is never quite as powerful for each platform. I still think every day about starting some kind of open source SQL gateway client project to normalize it and sit in front of databases. It's a big project though, and like I said, I question the value sometimes. Normalizing the syntax for databases that support SQL >=SQL99 (or even 2003 standard) would not be too bad though.

cryptos 8y ago

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 ...".

nicoburns 8y ago
It's a benefit to me over it being a Ruby/Node/Python CLI. Means it's likely to be easy to install, and performant.
nolok 8y ago
nodejs and rust comes to mind as language getting included the same way. For me "in go" is useful information since I infer "easy to deploy" from it
Thaxll 8y ago
I've never seen a modern C++ CLI compiled without dependencies.
georgyo 8y ago
People are fanatic about using tools written in a language they like. If people think that a program written in C will be faster than a program written in Python then it is a feature to them that may or may not be valid. However modern languages have a lot of trade offs in deployment strategies that are just easier to say by stating the language. Golang is normally fully statically compiled so getting this tool on a host requires nothing other than coping the completed binary. It often requires nothing at all from the system it is running on. This is an implied feature of the language. C/C++ code can be that simple, but static binaries are not always possible. And then you require the libaries on the remote machine. There is a good chance Python will be on a remote machine, but deployment could be complicated. Psql support requires the the libaries to be on that machine for example. And if the tool was written in node, a ton dependencies and work would be required for a normal user to use it. I agree that the language should not be a feature, but it is. Containers/snap/flatpak sorta help with this, but they are even more work for the user currently.
koolba 8y ago

int0x80 8y ago

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

projektfu 8y ago
I didn't run it, but in the code it appears to take commands from stdin, but if they're coming from a TTY (interactive), they are processed through readline. The "-f -" trick didn't seem to be supported by the code.
kenshaw 8y ago
Yes, it reads from STDIN. If it doesn't, it's a bug, and please file an issue on GitHub.
- int0x80 8y ago
  Thanks! Didnt try it, just couldnt find it in the examples.

rjbwork 8y ago

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-sql/u-sql-language-reference https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u...

Dowwie 8y ago

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?

dmoreno 8y ago
Multiple database vendor support, not only postgres.
cbcoutinho 8y ago
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

dkns 8y ago

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

flas9sd 8y ago

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 8y ago
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 8y ago
  %page dir(object), %pinfo or %help object are your friends.
  - vram22 8y ago
    Great, will check those out, thanks.

tannhaeuser 8y ago

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.

kerng 8y ago

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-sql/u-sql-language-reference https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u... Hint: its not related!

kyberias 8y ago

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

pjmlp 8y ago
SQL Developer for Oracle is also quite good.
dstroot 8y ago
Unless you are using MySQL or PostgreSQL. ;)
lostapathy 8y ago
If you're developing against MS SQL server from linux or mac, you absolutely need something like this.
- amjith 8y ago
  https://github.com/dbcli/mssql-cli https://github.com/dbcli/mssql-cli