NAME
ETL::Yertl::Help::ysql - A guide to using ysql to work with SQL databases
VERSION
version 0.032
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
SEE ALSO
- ysql
-
The full documentation for the
ysql
command - ETL::Yertl
-
The main documentation
AUTHOR
Doug Bell <preaction@cpan.org>
COPYRIGHT AND LICENSE
This software is copyright (c) 2016 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.