ysql

NAME

ysql - Query SQL databases in a Yertl workflow

SYNOPSIS

    ysql [<db_name> | --dsn <dsn>] [<query> | <query_name>] [<value>...]

    ysql <db_name> --select <table> [--where <clause>] [--order-by <clause>]
    ysql <db_name> --insert <table>
    ysql <db_name> --delete <table> [--where <clause>]
    ysql <db_name> --count <table> [--where <clause>]

    ysql <db_name> --save <query_name> <query>
    ysql <db_name> --edit <query_name>

    ysql --config <db_name> [--driver <driver>] [--database <database] [--host <host>]
        [--port <port>] [--user <user>] [--password <password>]
    ysql --config <db_name> [--dsn] <dsn> [--user <user>] [--password <password>]
    ysql --config [<db_name>]

    ysql -h|--help|--version

DESCRIPTION

This program queries a SQL database and prints the result as YAML.

Reading

Run a query to read data from the database. query is either the name of a saved query, or SQL (usually a SELECT). value is used to fill in ? placeholders.

    $ ysql db_name 'SELECT * FROM person'
    ---
    name: Hazel "Hank" Murphy
    email: captain@example.com
    dept: Captain
    ---
    name: Quinn Quinlan
    email: dr.quinn@example.com
    dept: Science

    $ ysql db_name 'SELECT * FROM person WHERE dept = ?' Science
    ---
    name: Quinn Quinlan
    email: dr.quinn@example.com
    dept: Science

Writing

If there are documents to read on STDIN, the query will be executed once for each document read. In order to map keys in the YAML document to fields in the SQL query, ysql supports named placeholders, which begin with $.. For example:

    # input.yml
    ---
    name: Stormy Waters
    email: stormy@example.com
    profile:
        dept: Pilot
    ---
    name: Debbie Dupree
    email: debbie@example.com
    profile:
        dept: Wildlife

    $ ysql db_name 'INSERT INTO person ( name, email, dept ) \
                    VALUES ( $.name, $.email, $.profile.dept )' \
                    < input.yml

Query Helpers

For common, simple SQL queries, there are some helper options that will help generate the SQL for you.

Instead of SELECT * FROM person, we can use the --select helper:

    $ ysql db_name --select person

And we can combine it with the --where and --order-by helpers:

    $ ysql db_name --select person --where 'dept = "Science"' --order-by name

Instead of SELECT COUNT(*) FROM person, we can use the --count helper, which also works with the --where helper:

    $ ysql db_name --count person
    $ ysql db_name --count person --where 'dept = "Science"'

And for simple inserts, we can use the --insert helper:

    # input.yml
    ---
    name: Hesh Hipplewhite
    email: hesh@example.com
    dept: Engineering
    ---
    name: Jodene Sparks
    email: overlord@example.com
    dept: Communication

    $ ysql db_name --insert person < input.yml

There is a --delete helper as well, which accepts the --where option:

    $ ysql db_name --delete person --where 'dept = "Wildlife"'

ARGUMENTS

db_name

The saved database name to use. Add and edit databases using the --config option.

query

The SQL query to run against the database.

query_name

The name of a saved query. Add and edit saved queries using the --save option.

OPTIONS

--select <table>

Generate a SELECT * FROM table query for the given table. Also accepts the --where and --order-by options.

--insert <table>

Generate an INSERT INTO table ( fields ) VALUES ( values ) query for the given table. For each document read on STDIN, the correct fields and values will be used.

--delete <table>

Generate a DELETE FROM table query for the given table. Also accepts the --where option.

--where <clause>

Add a WHERE clause to a --select query.

--order-by <clause> | --sort <clause>

Add an ORDER BY clause to a --select query.

--config

View, add, and edit database configuration.

--save <query_name>

Save a query to run later.

--edit <query_name>

Edit the named query in your text editor (taken from the EDITOR environment variable).

--driver <driver>

The database driver to use, corresponding to a DBD::* module. Some examples: SQLite, mysql, Pg.

--database <database>

The name of the database. For SQLite, the name of the database file.

--host <host>

The hostname to connect to. Optional.

--port <port>

The port to connect to. Optional.

--user <user>

The user to connect as. Optional.

--password <password>

The password to connect with. Optional. This is stored in plain text, so beware.

--dsn <dsn>

The DBI Data Source Name. A string that DBI uses to connect to the database.

Some examples:

    # A SQLite database in "test.db"
    dbi:SQLite:test.db

    # A MySQL database named "example"
    dbi:mysql:example

    # A Postgres database named "foo"
    dbi:Pg:foo

--drivers

List the available drivers

-h | --help

Show this help document.

--version

Print the current ysql and Perl versions.

ENVIRONMENT VARIABLES

YERTL_FORMAT: Specify the default format Yertl uses between commands. Defaults to yaml. Can be set to json for interoperability with other programs.