ysql - Query SQL databases in a Yertl workflow
version 0.044
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.
query
SELECT
value
?
$ 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
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:
ysql
$.
# 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
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:
SELECT * FROM person
--select
$ ysql db_name --select person
And we can combine it with the --where and --order-by helpers:
--where
--order-by
$ 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:
SELECT COUNT(*) FROM person
--count
$ ysql db_name --count person $ ysql db_name --count person --where 'dept = "Science"'
And for simple inserts, we can use the --insert helper:
--insert
# 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:
--delete
$ ysql db_name --delete person --where 'dept = "Wildlife"'
The saved database name to use. Add and edit databases using the --config option.
--config
The SQL query to run against the database.
The name of a saved query. Add and edit saved queries using the --save option.
--save
Generate a SELECT * FROM table query for the given table. Also accepts the --where and --order-by options.
SELECT * FROM table
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.
INSERT INTO table ( fields ) VALUES ( values )
Generate a DELETE FROM table query for the given table. Also accepts the --where option.
DELETE FROM table
Add a WHERE clause to a --select query.
WHERE
Add an ORDER BY clause to a --select query.
ORDER BY
View, add, and edit database configuration.
Save a query to run later.
Edit the named query in your text editor (taken from the EDITOR environment variable).
EDITOR
The database driver to use, corresponding to a DBD::* module. Some examples: SQLite, mysql, Pg.
SQLite
mysql
Pg
The name of the database. For SQLite, the name of the database file.
The hostname to connect to. Optional.
The port to connect to. Optional.
The user to connect as. Optional.
The password to connect with. Optional. This is stored in plain text, so beware.
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
List the available drivers
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.
yaml
json
Doug Bell <preaction@cpan.org>
This software is copyright (c) 2018 by Doug Bell.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install ETL::Yertl, copy and paste the appropriate command in to your terminal.
cpanm
cpanm ETL::Yertl
CPAN shell
perl -MCPAN -e shell install ETL::Yertl
For more information on module installation, please visit the detailed CPAN module installation guide.