Thread: My humble tribute to psql -- usql v0.5.0
Hi All,
I apologize in advance if this is somewhat off-topic, but I thought I
would inform the people (ie, psql users) about usql, a
universal-command line tool that aims to work the same way psql does,
but with every database (not just PostgreSQL).
usql is built in Go, and as of today supports all the major databases
(PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle Database)
and more! Additionally, with v0.5.0, released today, usql now has
implemented most of the basic, and much of the advanced features of
psql:
* Interpolated variables (ie, via \set, \unset, etc.)
* Backslash commands, and similar shell escapes/evaluation (ie, \echo
`date` style)
* Working with a query buffer (ie, \e, \p, \r, etc.)
* Password (.usqlpass) / RC (.usqlrc) files
If you happen to have a Go tool chain installed, you can simply install with:
go get -u github.com/knq/usql
Alternatively, you may download a binary release for Windows/Linux/OSX here:
https://github.com/knq/usql/releases
I just released usql v0.5.0 and feel that it is now ready for a wider
audience, and I thought what better audience than those already
familiar with the type/style of cli interface psql offers. I'm hoping
that there are those of you on this list that might like to have a
tool in their toolbox that works with every other database in a
similar way to how psql does.
Over the last 15 years that I've been a heavy PostgreSQL user, and I
have likely used the psql command line for -- literally -- thousands
of hours! Over that same period of time, I have continually found
myself needing to reread documentation for all the various cli clients
out there. usql is the tool I wish I had 10+ years ago.
I built usql primarily due to my frustration with having to work with
5 different, broken CLI interfaces for all the different/major
databases on a relatively routine basis. I am posting this here,
because I imagine many of the other users on this list on occasion
have had, on occasion, needed to work with other databases, but are
stymied/annoyed/stopped/frustrated-to-no-ends with having to use
multiple, incompatible clients.
At least now, while you might be forced to use some other, inferior
database, at least you can still hit the ground running. I appreciate
any feedback/questions/etc you might have!
And thank you all to the PostgreSQL + psql developers out there! Don't
forget that imitation is the most sincerest form of flattery ...
cheers!
-Ken
(ps: usql is brand new; it's great for doing many common tasks, minor
to medium sized database administration tasks and development at the
moment; in time, it'll be ready for anything, but CAVEAT USER until
then ...)
On 04/02/2017 07:55 AM, Kenneth Shaw wrote: > Hi All, > > > usql is built in Go, and as of today supports all the major databases > (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle Database) > and more! Additionally, with v0.5.0, released today, usql now has > implemented most of the basic, and much of the advanced features of > psql: > > > -Ken I have to admit, I have typed \d at the sqlite3 prompt before. And I am being forced to support a mysql database againstmy will, so I'll be trying this out. Thanks! -Andy
Unfortunately, the \d* commands aren't implemented yet, as there's a lot of research that needs to be done for all the key databases, which I just haven't had time to do yet. But that's coming very soon (tm)!** The main work done so far has been just getting the cli up to something usable. ** for certain definitions of "very soon" -Ken On Sun, Apr 2, 2017 at 8:36 PM, Andy Colson <andy@squeakycode.net> wrote: > On 04/02/2017 07:55 AM, Kenneth Shaw wrote: >> >> Hi All, >> >> >> usql is built in Go, and as of today supports all the major databases >> (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle Database) >> and more! Additionally, with v0.5.0, released today, usql now has >> implemented most of the basic, and much of the advanced features of >> psql: >> >> >> -Ken > > > I have to admit, I have typed \d at the sqlite3 prompt before. And I am > being forced to support a mysql database against my will, so I'll be trying > this out. Thanks! > > -Andy > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Sun, Apr 2, 2017 at 7:55 AM, Kenneth Shaw <kenshaw@gmail.com> wrote: > Hi All, > > I apologize in advance if this is somewhat off-topic, but I thought I > would inform the people (ie, psql users) about usql, a > universal-command line tool that aims to work the same way psql does, > but with every database (not just PostgreSQL). > > usql is built in Go, and as of today supports all the major databases > (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle Database) > and more! Additionally, with v0.5.0, released today, usql now has > implemented most of the basic, and much of the advanced features of > psql: > > * Interpolated variables (ie, via \set, \unset, etc.) > * Backslash commands, and similar shell escapes/evaluation (ie, \echo > `date` style) > * Working with a query buffer (ie, \e, \p, \r, etc.) > * Password (.usqlpass) / RC (.usqlrc) files > > If you happen to have a Go tool chain installed, you can simply install with: > > go get -u github.com/knq/usql > > Alternatively, you may download a binary release for Windows/Linux/OSX here: > > https://github.com/knq/usql/releases > > I just released usql v0.5.0 and feel that it is now ready for a wider > audience, and I thought what better audience than those already > familiar with the type/style of cli interface psql offers. I'm hoping > that there are those of you on this list that might like to have a > tool in their toolbox that works with every other database in a > similar way to how psql does. > > Over the last 15 years that I've been a heavy PostgreSQL user, and I > have likely used the psql command line for -- literally -- thousands > of hours! Over that same period of time, I have continually found > myself needing to reread documentation for all the various cli clients > out there. usql is the tool I wish I had 10+ years ago. > > I built usql primarily due to my frustration with having to work with > 5 different, broken CLI interfaces for all the different/major > databases on a relatively routine basis. I am posting this here, > because I imagine many of the other users on this list on occasion > have had, on occasion, needed to work with other databases, but are > stymied/annoyed/stopped/frustrated-to-no-ends with having to use > multiple, incompatible clients. > > At least now, while you might be forced to use some other, inferior > database, at least you can still hit the ground running. I appreciate > any feedback/questions/etc you might have! > > And thank you all to the PostgreSQL + psql developers out there! Don't > forget that imitation is the most sincerest form of flattery ... > cheers! > > -Ken > > (ps: usql is brand new; it's great for doing many common tasks, minor > to medium sized database administration tasks and development at the > moment; in time, it'll be ready for anything, but CAVEAT USER until > then ...) Wow! this is _fantastic_. I use "sqsh" for connecting to sql server -- this is already a significant improvement in many ways (based on playing around for around 10 minutes). This app would fill a real need for me. Here is some feedback: *) Does UTF8 support work? It appears not to for me: => select '这是一个'; col0 +------+ ???? (1 rows) *) Is there any way to disable SSL to postgres in the DSN? I get, error: pq: SSL is not enabled on the server *) Interacting with PAGER is a really essential feature *) Ditto \copy (to my limited understanding this would be a fairly big development challenge) *) Advise reserving -h command line switch for 'host', not --help *) \g switch is not working FWICT (it outputs to stdout) -- this makes it hard to benchmark performance since output to display is the main bottlenect. First impressions though is that the tool is pretty fast. merlin
On Mon, Apr 3, 2017 at 8:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > Wow! this is _fantastic_. I use "sqsh" for connecting to sql server > -- this is already a significant improvement in many ways (based on > playing around for around 10 minutes). This app would fill a real > need for me. Here is some feedback: Cool, I'm glad it will be useful to more than just myself. > > *) Does UTF8 support work? It appears not to for me: > => select '这是一个'; > col0 > +------+ > ???? > (1 rows) It should work. What database did you try that with? I haven't tried to do heavy / extensive utf8 tests, but all of Go (including the "readline" package that is used for capturing input are native utf8. The problem is potentially a connect option in the DSN for the database needs to be manually added, or some other reason I'm not aware of. However, this is 100% part/parcel to usql, so I'd like to figure it out ASAP. I also plan to provide the \encoding equivalents for the various databases, but just haven't gotten to that yet. > > *) Is there any way to disable SSL to postgres in the DSN? I get, > error: pq: SSL is not enabled on the server usql pg://user:pass@host/?sslmode=disable > > *) Interacting with PAGER is a really essential feature Yes, for me too! I just haven't had a chance to get to that. This is actually a bit more complicated in general. > > *) Ditto \copy (to my limited understanding this would be a fairly big > development challenge) So, it's not "too difficult" as things go -- for pg at least, this is built into the protocol, for the other databases, however, the issue is that they don't necessarily have the equivalent in their protocol or possible in the underlying Go driver. I'm likely going to do this slightly differently than relying on the protocol version, and the plan is to bring in support when I add in the ability to simultaneously work with multiple connections. The point would be to essentially write a "migration" script that would be something like: \copy -N connection1 (SELECT * FROM mytable) to -N connection2 mytable I'm still in the process of figuring out what this is > > *) Advise reserving -h command line switch for 'host', not --help Not all the various command line options are there yet. It's better to just use the URL style for connections. > > *) \g switch is not working FWICT (it outputs to stdout) -- this makes > it hard to benchmark performance since output to display is the main > bottlenect. First impressions though is that the tool is pretty fast. Yeah -- output redirection (\o) is also not available yet. This is would have been part of v0.5.0, but I just didn't have enough time, and wanted to get the variable stuff out. Look for this in the next couple days. Anyways, I really appreciate the feedback! I'm hoping this will grow into its own project, but personally, I'm just glad I took the time (why did I not do this 10 years ago??) to juts build out something that works the same with every database.
On Tue, Apr 04, 2017 at 03:48:16AM +0700, Kenneth Shaw wrote: > It should work. What database did you try that with? I haven't tried > to do heavy / extensive utf8 tests, but all of Go (including the > "readline" package that is used for capturing input are native utf8. > The problem is potentially a connect option in the DSN for the > database needs to be manually added Do you "SET client_encoding" ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
This doesn't seem to be a problem with PostgreSQL, MySQL, Oracle, or SQLite3 databases. It was a problem with MSSQL in the limited check that I just did. I don't see any driver option for the MSSQL Go driver. I'll poke around and see if there's a "client encoding" option. -Ken On Tue, Apr 4, 2017 at 4:08 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > On Tue, Apr 04, 2017 at 03:48:16AM +0700, Kenneth Shaw wrote: > >> It should work. What database did you try that with? I haven't tried >> to do heavy / extensive utf8 tests, but all of Go (including the >> "readline" package that is used for capturing input are native utf8. >> The problem is potentially a connect option in the DSN for the >> database needs to be manually added > > Do you "SET client_encoding" ? > > Karsten > -- > GPG key ID E4071346 @ eu.pool.sks-keyservers.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
UTF-8 works just fine with MS SQL:
ms:booktest@192.168.1.5=> select N'这是一个';
col0
+----------+
这是一个
(1 rows)
You need to use a "unicode" string, as indicated here using N''. You
might be able to change the default behavior in SQL Server to treat
strings as unicode strings, by changing the default collation settings
(which is latin1 or windows' 1252 encoding or whatever it is). If
you're working with a table, when columns are N* column types, it will
work fine.
-Ken
On Tue, Apr 4, 2017 at 4:20 AM, Kenneth Shaw <kenshaw@gmail.com> wrote:
> This doesn't seem to be a problem with PostgreSQL, MySQL, Oracle, or
> SQLite3 databases. It was a problem with MSSQL in the limited check
> that I just did. I don't see any driver option for the MSSQL Go
> driver. I'll poke around and see if there's a "client encoding"
> option.
>
> -Ken
>
>
> On Tue, Apr 4, 2017 at 4:08 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>> On Tue, Apr 04, 2017 at 03:48:16AM +0700, Kenneth Shaw wrote:
>>
>>> It should work. What database did you try that with? I haven't tried
>>> to do heavy / extensive utf8 tests, but all of Go (including the
>>> "readline" package that is used for capturing input are native utf8.
>>> The problem is potentially a connect option in the DSN for the
>>> database needs to be manually added
>>
>> Do you "SET client_encoding" ?
>>
>> Karsten
>> --
>> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
>> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
On Mon, Apr 3, 2017 at 4:42 PM, Kenneth Shaw <kenshaw@gmail.com> wrote: > UTF-8 works just fine with MS SQL: > > ms:booktest@192.168.1.5=> select N'这是一个'; > col0 > +----------+ > 这是一个 > (1 rows) confirmed! merlin