npm package: @oasislabs/dpclient
Description: The PrivateSQL admin CLI is a command line interface for use by database and privacy administrators to configure the PrivateSQL system for data sharing. It works by interacting with the PrivateSQL service run by Oasis at `https://dp.oasislabs.com`.
Author: Oasis Labs
Latest version: 1.0.10

# dpclient

The PrivateSQL admin CLI is a command line interface for use by database and privacy administrators to configure the PrivateSQL
system for data sharing. It works by interacting with the PrivateSQL service run by Oasis at `https://dp.oasislabs.com`.

## Usage

```
Usage: dpclient <command> [options]

Commands:
  dpclient add-user                    Add user with their email address and
                                       desired role
  dpclient delete-user                 Delete user with user UUID
  dpclient get-schema                  Get the current PrivateSQL schema.
  dpclient set-schema                  Set the current PrivateSQL schema.
  dpclient get-schema-for-table        Get the current PrivateSQL schema for a
                                       given table.
  dpclient set-schema-for-table        Set the current PrivateSQL schema for a
                                       given table.
  dpclient delete-schema-for-table     Delete the current PrivateSQL schema for
                                       a given table.
  dpclient autogenerate                Autogenerate the PrivateSQL schema for a
                                       given table.
  dpclient get-budget                  Get the current privacy budget.
  dpclient set-budget                  Set the current privacy budget. Note this
                                       is db wide.
  dpclient query                       Run query
  dpclient preview                     Generate a preview table with
                                       differentially private synthetic data.
  dpclient queryPreview                Run query on a preview table.
  dpclient status                      Status of the server
  dpclient generate-completion-script  generate completion script

Options:
  --help     Show help                                                 [boolean]
  --version  Show version number                                       [boolean]
```

## Installing

To install `dpclient`:

1. Make sure that you've installed [Node.js](https://nodejs.org/en/) **v18 or higher**.

   ```sh
   $ node -v
   v19.5.0
   ```

2. Log in to npm locally. You'll need to be logged into NPM in order to
access dpclient, which will remain private and accessible only to the members of
the PrivateSQL admin community.

   ```sh
   npm login
   ```

3. Install the CLI

   ```sh
   npm i -g @oasislabs/dpclient
   ```

## Getting Started

### Configuring your credentials

Prior to using dpclient, you will need to set the following environment variables:

1. `DP_ADMIN_UUID`: the client UUID we sent you.

2. `DP_ADMIN_SECRET`: the secret that we sent you.

3. `PRIVATESQL_ENDPOINT`: either `https://dp.oasislabs.com` or `https://dp.oasiscloud-staging.net`.

_The rest of this README assumes that the values for these environment
variables have been set to the correct values for your identity._

## Running Commands

Now you're ready to start running commands and interacting with the PrivateSQL platform.
Remember, you will need to have your credentials exported as environment variables in order
for these commands to work. The CLI will read in your credentials and use them to
authenticate with the PrivateSQL server.

### User management

PrivateSQL supports the following roles, DB_ADMIN, PRIV_ADMIN, and RESEARCH. The `dpclient` CLI
is only available to the DB_ADMIN and PRIV_ADMIN roles. An admin can add other admins or
research roles and manage these users using the following commands:

<!-- markdownlint-disable line-length -->
```sh
dpclient add-user --email r@institute.com --role RESEARCH
# sample output:
{
  "id": "c307e697-8773-4304-ac40-5927f02f022a",
  "secret": "dxzkfm5l6btpu8uxl2nzqxum7wu9kw"
}
```

```sh
dpclient delete-user -i c307e697-8773-4304-ac40-5927f02f022a
# output:
"ok"
```
<!-- markdownlint-enable line-length -->

#### Schema management

PrivateSQL requires a schema to be specified for each of the tables that are shared. For more on the Schema specification please refer to our Schema parameters documentation. The following commands can be used to manage PrivateSQL schemas:

<!-- markdownlint-disable line-length -->
```sh
dpclient get-schema --db osfdb
# sample output:
{
  "database": "osfdb",
  "dialect": "postgresql",
  "namespace": "",
  "tables": [
    {
      "table": "diseaseinfo_startdate_domain",
...
        {
          "name": "age",
          "clipUpper": 76,
          "binsStart": 42,
          "binsDistribution": "constant",
          "binsEnd": 76,
          "typeName": "INTEGER",
          "clipLower": 42,
          "binsStep": 0.34
        }
      ]
    }
  ]
}
```

```sh
dpclient set-schema --schema schema.json
# output:
"ok"
```
<!-- markdownlint-enable line-length -->

You can also get and set the schema for a specific table that is shared using the commands
`get-schema-for-table`, `set-schema-for-table`, and `delete-schema-for-table`.

PrivateSQL supports an autogeneration capability to automatically generate a PrivateSQL
schema. You can do this using the following command:

<!-- markdownlint-disable line-length -->
```sh
dpclient autogenerate --database osfdb --table diseaseinfo
# output:
<The PrivateSQL schema for the diseaseinfo table>
```
<!-- markdownlint-enable line-length -->

### Budget management

You can get and set the current privacy budget parameters. Note that at the moment these
parameters are database wide and apply to all researchers that query the database.

<!-- markdownlint-disable line-length -->
```sh
dpclient get-budget
# output:
{
  "epsilon": 0.1,
  "total": 100
}
```

```sh
dpclient set-budget -b '{"epsilon": 0.2, "total": 50}'
# output:
"ok"
```
<!-- markdownlint-enable line-length -->

### Run queries

<!-- markdownlint-disable line-length -->
```sh
dpclient query --db osfdb --query "select avg(age), min(age), max(age) from CovidOncology.data group by race"
# output:
[
  [ '_agg0', '_agg1', '_agg2' ],
  [ '59', '55', '66' ],
  [ '60', '55', '68' ],
  [ '51', '43', '58' ],
  [ '53', '43', '65' ],
  [ '61', '44', '64' ],
  [ '59', '53', '67' ]
]
```
<!-- markdownlint-enable line-length -->

### Preview capabilities

PrivateSQL supports a preview capability that can be used to generate synthetic samples from the
actual data using differential privacy. For more on our preview capabilities please refer to 
the documentation at Preview generation.

The following command can be used to generate previews:

<!-- markdownlint-disable line-length -->
```sh
dpclient preview -d osfdb -t diseaseinfo -p diseaseinfo_preview -n 1000
# output:
"ok"
```
<!-- markdownlint-enable line-length -->

A researcher can query the preview tables with any SQL query that is supported by the 
underlying database. There are no restrictions on the types of queries that can be run
on preview tables as they are synthetic samples generated using differentially private
mechanisms. Furthermore, there is no cost in privacy budget to run queries on the preview
tables.

### Utility functions

The dpclient CLI also supports a few utility functions as described below.

#### Server status

You can get the server status at any time using the following command:

<!-- markdownlint-disable line-length -->
```sh
csclient status
# sample output:
{
  "status": "PrivateSQL server initialized",
  <put more stuff here>
}
```
<!-- markdownlint-enable line-length -->

### Local builds with github access

To build the client from sources please run the following commands, once you have node installed per instructions at the
head of this document.

<!-- markdownlint-disable line-length -->
```sh
cd qr/tools
yarn
yarn build
```
<!-- markdownlint-enable line-length -->