ETL::Yertl::Help::ysql - A guide to using ysql to work with SQL databases
version 0.044
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
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).
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".
dbi:mysql:host=dev;database=mydb
$ 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
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
Since SQL is a standard language, and can be verbose at times, ysql provides some helpers for generating queries quickly.
To quickly select all the data in a table, we can use the --select helper:
--select
$ ysql prod --select users
To filter our results, we can add a --where helper:
--where
$ ysql prod --select users --where 'name = "preaction"'
The --order-by helper can sort our results for us:
--order-by
$ ysql prod --select users --order-by id
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:
STDIN
--insert
$ 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.
INSERT INTO
Much like selecting data, we can delete data using the --delete helper.
--delete
$ ysql prod --delete users
The --where helper can limit which rows get deleted:
$ ysql prod --delete users --where 'name = "postaction"'
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).
xargs(1)
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...
$.id
$.name
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'
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" }
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:
--save
$ 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
The full documentation for the ysql command
The main documentation
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.