ysql - Query SQL databases in a Yertl workflow


    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


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


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
    dept: Captain
    name: Quinn Quinlan
    dept: Science

    $ ysql db_name 'SELECT * FROM person WHERE dept = ?' Science
    name: Quinn Quinlan
    dept: Science


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
        dept: Pilot
    name: Debbie Dupree
        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
    dept: Engineering
    name: Jodene Sparks
    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"'



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


The SQL query to run against the database.


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


--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.


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"

    # A MySQL database named "example"

    # A Postgres database named "foo"


List the available drivers

-h | --help

Show this help document.


Print the current ysql and Perl versions.



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


ETL::Yertl::Help::ysql - Using ysql to work with SQL databases