NAME

ETL::Yertl::Help::ysql - A guide to using ysql to work with SQL databases

DESCRIPTION

ysql is a simple utility for working with SQL databases. Any database that Perl's DBI library has a DBD driver for will work with this utility.

ysql allows for selecting, inserting, updating, and running arbitrary SQL on a database. It also stores database configurations and even queries for later use (so you don't have to re-type that long query over and over).

Configure Databases

To use ysql, first we have to configure a database. This saves us from having to type the full DBI data source name (dbi:mysql:host=dev;database=mydb) every time. Instead, we can refer to our database by a nice name, like "dev", or "prod".

    $ ysql --config dev dbi:SQLite:database.db

Later, we can update our configuration if we need to:

    $ ysql --config dev --database=dev.db

We can examine our configuration as a YAML document:

    $ ysql --config dev
    ---
    database: dev.db
    driver: SQLite

Let's add a production database as well:

    $ ysql --config prod --driver=SQLite --database=prod.db

And now we can check both of our configs:

    $ ysql --config
    ---
    dev:
      database: dev.db
      driver: SQLite
    prod:
      database: prod.db
      driver: SQLite

Working with Data

Now that we've configured some databases, let's insert some data. First we need to make some tables:

    $ ysql prod 'CREATE TABLE users ( id INTEGER PRIMARY KEY \
        AUTOINCREMENT, name VARCHAR, email VARCHAR )'
    $ ysql dev 'CREATE TABLE users ( id INTEGER PRIMARY KEY \
        AUTOINCREMENT, name VARCHAR, email VARCHAR )'

Next let's insert some data using plain SQL:

    $ ysql prod 'INSERT INTO users ( name, email ) \
        VALUES ( "preaction", "preaction@example.com" )'
    $ ysql prod 'INSERT INTO users ( name, email ) \
        VALUES ( "postaction", "postaction@example.com" )'

Now, let's query for our data:

    $ ysql prod 'SELECT * FROM users'
    ---
    email: preaction@example.com
    id: 1
    name: preaction
    ---
    email: postaction.example.com
    id: 2
    name: postaction

SQL Helpers

Since SQL is a standard language, and can be verbose at times, ysql provides some helpers for generating queries quickly.

Selecting Data

To quickly select all the data in a table, we can use the --select helper:

    $ ysql prod --select users

To filter our results, we can add a --where helper:

    $ ysql prod --select users --where 'name = "preaction"'

The --order-by helper can sort our results for us:

    $ ysql prod --select users --order-by id

Inserting Data

Now, lets say we want to copy our production database to dev for testing. To do that, Yertl allows us to read YAML from STDIN and execute a query for each YAML document. The --insert helper makes this easy:

    $ ysql prod --select users | ysql dev --insert users

The insert helper does all the work of determining what fields the input has and executing the correct INSERT INTO SQL query for each document to insert.

Deleting Data

Much like selecting data, we can delete data using the --delete helper.

    $ ysql prod --delete users

The --where helper can limit which rows get deleted:

    $ ysql prod --delete users --where 'name = "postaction"'

Placeholders

Additional arguments on the command-line are treated as placeholder values, which fill in any ? in the query.

    $ ysql prod 'SELECT * FROM users WHERE name=?' postaction

This is especially helpful when using saved queries, below, or when using xargs(1).

Interpolation

When piping in data and running custom SQL, Yertl allows a special interpolation syntax (starting with a $) to pick parts of the document to fill in the query. So, to copy our users from prod to dev again, we could do:

    $ ysql prod 'SELECT * FROM users' |
        ysql dev 'INSERT INTO users ( id, name, email ) \
            VALUES ( $.id, $.name, $.email )'

$.id picks the "id" field, $.name the "name" field, etc...

We can also pipe in data and run a select query, useful when we need to find related information, or see if our two databases have the same user names:

    $ ysql prod --select users |
        ysql dev 'SELECT * FROM users WHERE name=$.name'

Converting Formats

Yertl uses YAML as its default output, but we can easily convert to JSON or CSV using the yto utility.

    $ ysql prod 'SELECT * FROM users' | yto csv
    email,id,name
    preaction@example.com,1,preaction
    postaction@example.com,2,postaction

    $ ysql prod 'SELECT * FROM users' | yto json
    {
       "email" : "preaction@example.com",
       "id" : "1",
       "name" : "preaction"
    }
    {
       "email" : "postaction@example.com",
       "id" : "2",
       "name" : "postaction"
    }

Saved Queries

All these long queries would be a bear to type over and over again (imagine if we had a bunch of joins to do). So, ysql allows you to save queries for later use using the --save option:

    $ ysql prod --save users 'SELECT * FROM users'
    $ ysql dev --save update_users 'UPDATE users SET \
        name=$.name, email=$.email WHERE id=$.id'

Then we can recall our query by the name we gave to the --save option:

    $ ysql prod users | ysql dev update_users

If our saved query has a placeholder, we can give it a value as the next argument:

    $ ysql prod --save find_user 'SELECT * FROM users WHERE name=?'
    $ ysql prod find_user preaction