usql on YugabyteDB
Tags: yugabytedb, sql, database
Published: 2021-08-17 17:37:40

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](https://pkg.go.dev/github.com/lib/pq)). What I do here with the YSQL connection can be done the same on a PostgreSQL database.

## Install
I'll not reproduce the [install doc](https://github.com/xo/usql#installing). 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('https://chocolatey.org/install.ps1'))
```
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.

### Connect

[usql](https://github.com/xo/usql#using) parses the connection string with [dburl](https://github.com/xo/dburl#using) 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://franck:yugabyteDB@yb1.pachot.net:5433/yb_demo_northwind?sslmode=disable

Connected with driver postgres (PostgreSQL 11.2-YB-2.7.2.0-b0)
Type "help" for help.

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \l
 List of databases
      Catalog
-------------------
 postgres
 system_platform
 template0
 template1
 yb_demo_northwind
 yugabyte
(6 rows)

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \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)

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=>
```
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](https://docs.yugabyte.com/latest/quick-start/explore/ycql/#2-create-a-table)

```

connect cassandra://franck:switzerland@yb1.pachot.net:9402

ca:franck@yb1.pachot.net=> CREATE KEYSPACE myapp;
CREATE KEYSPACE
ca:franck@yb1.pachot.net=> CREATE TABLE myapp.stock_market (
ca:franck@yb1.pachot.net(>   stock_symbol text,
ca:franck@yb1.pachot.net(>   ts text,
ca:franck@yb1.pachot.net(>   current_price float,
ca:franck@yb1.pachot.net(>   PRIMARY KEY (stock_symbol, ts)
ca:franck@yb1.pachot.net(> );
CREATE TABLE
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 09:00:00',157.41);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 10:00:00',157);
INSERT
ca:franck@yb1.pachot.net=> 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
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 10:00:00',170.1);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 09:00:00',972.56);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 10:00:00',971.91);
INSERT
ca:franck@yb1.pachot.net=> 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):
```
ca:franck@yb1.pachot.net=> \connect postgres://franck:yugabyteDB@yb1.pachot.net:5433/yb_demo_northwind?sslmode=disable
Connected with driver postgres (PostgreSQL 11.2-YB-2.7.2.0-b0)
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> CREATE TABLE public.stock_market (
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   stock_symbol text,
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   ts text,
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   current_price float,
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   PRIMARY KEY (stock_symbol, ts)
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(> );
CREATE TABLE
```

Now ready to copy from one to the other:
```
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \set source cassandra://franck:switzerland@yb1.pachot.net
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \set target postgres://franck:yugabyteDB@yb1.pachot.net:5433/yb_demo_northwind?sslmode=disable
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \copy :source :target 'select * from myapp.stock_market' stock_market
\copy :source :target 'select * from myapp.stock_market' stock_market
COPY 6
```
And query the result:
```
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> 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.