You know the powerful psql command line to connect to a PostgreSQL database. In the YugabyteDB binaries, you find it under the name ysqlsh to be consistent with the ycsqlsh (YCQL the Cassandra-like API) but it is the same as the PostgreSQL one. The YSQL API is actually based on PostgreSQL code: same protocol, same SQL (and PL/pgSQL), and same open source license. But if you use many database, SQL or not, and want a common client, usql is for you, very similar to psql.

I’ll show how to connect to YugabyteDB and that’s also the occasion to connect to YB from the Go driver (pq). What I do here with the YSQL connection can be done the same on a PostgreSQL database.


I’ll not reproduce the install doc. Here I’m on Windows where I use Chocolatey package manager which I install from an admin PowerShell with:

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString(''))

I install usql from an admin prompt:

PS C:\WINDOWS\system32> choco install usql

and I’m ready to call usql from any command prompt.


usql parses the connection string with dburl which has many aliases for PostgreSQL (pg, postgresql, pgsql) and also special entries for wire compatible alternatives. But YugabyteDB is more than wire compatible: the query layer is PostgreSQL. So you just use the postgres connection string. Here I am connecting to my public demo database without SSL:

C:\Users\franck> usql postgres:// with driver postgres (PostgreSQL 11.2-YB-
Type "help" for help.> \l
List of databases
(6 rows)> \d
List of relations
Schema | Name | Type
public | bench0001 | BASE TABLE
public | benchruns | BASE TABLE
public | categories | BASE TABLE
public | customer_customer_demo | BASE TABLE
public | customer_demographics | BASE TABLE
public | customers | BASE TABLE
public | employee_territories | BASE TABLE
public | employees | BASE TABLE
public | order_details | BASE TABLE
public | orders | BASE TABLE
public | products | BASE TABLE
public | region | BASE TABLE
public | rep_check | BASE TABLE
public | shippers | BASE TABLE
public | suppliers | BASE TABLE
public | territories | BASE TABLE
public | us_states | BASE TABLE
public | benchruns_job_id_seq | SEQUENCE
(18 rows)>

Mostly all commands from psql are there.

copy between databases (NoSQL to SQL)

With YugabyteDB the same distributed database server is compatible with Cassandra (YCQL) and Postgres (YSQL). For this example, I’m connecting to the two endpoints of the same YB universe, but of course \copy can be used between any database. However, data goes through the client, so this is not a replacement for Foreign Data Wrapper.

I have loaded the example table from the YCQL documentation

connect cassandra://> CREATE KEYSPACE myapp;
CREATE KEYSPACE> CREATE TABLE myapp.stock_market (> stock_symbol text,> ts text,> current_price float,> PRIMARY KEY (stock_symbol, ts)> );
CREATE TABLE> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 09:00:00',157.41);
INSERT> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 10:00:00',157);
INSERT> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 09:00:00',170.63);
debug2: channel 2: window 992086 sent adjust 56490
INSERT> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 10:00:00',170.1);
INSERT> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 09:00:00',972.56);
INSERT> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 10:00:00',971.91);
INSERT> SELECT * FROM myapp.stock_market WHERE stock_symbol = 'AAPL';
stock_symbol | ts | current_price
AAPL | 2017-10-26 09:00:00 | 157.41
AAPL | 2017-10-26 10:00:00 | 157
(2 rows)

I’ve done all that from usql that I have installed on one YugabyteDB node here as I've setup local access only.

From the same usql interface I connect to YSQL and create the same table but in SQL):> \connect postgres://
Connected with driver postgres (PostgreSQL 11.2-YB-> CREATE TABLE public.stock_market (> stock_symbol text,> ts text,> current_price float,> PRIMARY KEY (stock_symbol, ts)> );

Now ready to copy from one to the other:> \set source cassandra://> \set target postgres://> \copy :source :target 'select * from myapp.stock_market' stock_market
\copy :source :target 'select * from myapp.stock_market' stock_market

And query the result:> SELECT * FROM public.stock_market WHERE stock_symbol = 'AAPL'; stock_symbol |         ts          |   current_price
AAPL | 2017-10-26 09:00:00 | 157.41000366210938
AAPL | 2017-10-26 10:00:00 | 157
(2 rows)

My data is there. Are you concerned by the little difference in the column value? Never store a price in float! For money, SQL, ACID and DECIMAL is a must.

Developer Advocate at Yugabyte, Open Source distributed SQL database. Incidentally Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member