~nilium/sql

sql/sql.1.scd -rw-r--r-- 9.0 KiB
6dc63361Noel Cower Add jq::expr argument support 10 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
sql(1)

# Name

sql - query relational databases

# Synopsis

*sql* [options] _dsn_ {*query* [*args*...]...}

# Description

The sql(1) command allows you to make arbitrary queries against a database and
retrieve the results of those queries as JSON. It also has support for
transactions for multi-query execution where one query may fail.

The gist of sql(1) is that it's intended to be a semi-universal tool for
running queries against relational databases while getting data back in
a format that works well when piped to jq, mlr, and other tools. It relies
heavily on sqlx and Go's database and JSON packages to provide much of this.

## Options

*-f*, *--config*=_{file}_
	Load INI configuration for subsequent flags from a config file. Defaults to
	searching the current directory tree, followed by
	_$XDG_CONFIG_HOME/sql/sqlrc_ and _$HOME/.sqlrc_. If *--config* is passed,
	default search paths are ignored.

*-F*, *--no-config*
	Disable config file loading, including for any config files passed via
	*-f*, *--config*.

*-s*, *--sep*=_{sep}_ (default: *,*)
	Delimit queries with the given separator.
	Must not be an empty string or the same as *-d*, *--arg-sep*.

*-d*, *--arg-sep*=_{sep}_ (default: *,,*)
	Delimit groups of arguments to a query with the given separator.
	Must not be an empty string or the same as *-s*, *--sep*.

*-c*, *--compact*
	Print compacted JSON output, with each object or array separated only by
	a newline.

*-p*, *--pretty-print* (default)
	Print formatted and indented JSON output.

*-a*, *--array*
	Write output rows as JSON arrays, with one array per query execution.

*-A*, *--no-array* (default)
	Write output rows as a stream of JSON objects.

*-j*, *--json*
	Attempt to parse all fields as JSON. By default, only column types
	recognized as JSON are parsed as JSON.

*-J*, *--no-json*
	Parse no fields as JSON. If set, columns with known JSON types will not be
	parsed as JSON.

*-t*, *--time-format*=_{format}_
	Set the time format to one of the following time formats:
	- *ts*, *rfc3339*, *str* -- RFC 3339 formatted timestamp.
	- *unix*, *s*, *sec* -- A Unix integer timestamp in seconds.
	- *unixms*, *ms*, *msec* -- A Unix integer timestamp in milliseconds.
	- *unixus*, *us*, *usec* -- A Unix integer timestamp in microseconds.
	- *unixns*, *ns*, *nsec* -- A Unix integer timestamp in nanoseconds.
	- *unixf*, *fs*, *fsec*, *float* -- A Unix timestamp in floating point,
	  with sub-seconds represented as a fraction.
	- *format:{layout}*, *+{layout}* -- Render the timestamp using the given
	  _{layout}_. Must be a valid Go time layout.

*-x*, *--transaction*
	Run all queries inside of a transaction.

*-i*, *--isolation-level*=_{level}_ (default: DB preference)
	Set the transaction isolation level for all queries.
	Accepted isolation levels are:
	- *default* (or: *d*, *def*)
	- *read-uncommitted* (or: *readuncommitted*, *ru*, *dirty*, *dirty-read*,
	  *dirty-reads*)
	- *read-committed* (or: *readcommitted*, *rc*)
	- *write-committed* (or: *writecommitted*, *wc*)
	- *repeatable-read* (or: *repeatable*, *repeatableread*, *rr*)
	- *snapshot* (or: *snap*)
	- *serializable* (or: *s*, *sz*, *max*)
	- *linearizable* (or: *l*, *lin*, *lz*)

*-u*, *--username*=_{username}_, *-p*, *--password*=_{password}_
	Set or override the username or password on the DB=_{url}_.

*-h*, *--help*
	Print this usage text.

# File Substitution

Almost every option's value and other argument to sql(1) may be substituted by
a file by passing *@file* for its value, where file is either a path to a file
or *-* (a hyphen) for standard input. This will load each file as it appears on
the command line.

It is not recommended to use standard input more than once, as the order of
reads may not be what you expect, depending on parameter expansion.

# Output

All sql(1) output to standard output is in a JSON format. All output to
standard error is text, either for error messages or log messages about
unexpected behavior.

If the *--pretty-print* flag is set (by default, it is), then output will be
indented and broken up across multiple lines. This is the default behavior. To
compact the output, pass the *--compact* flag, which will write on JSON object
or array (if *--array* is set) per line.

# DSN URLs

The first non-optional argument to sql(1) is a DSN URL with the
following form:

	protocol://[userinfo@][hostname][:port][/database][?opts]

Where protocol is any of the supported protocols in the sql(1) program.
Currently, these are:

- *mysql://* -- Connect to MySQL over the network.

- *mysql+unix://[path[/database]]* -- Connect to MySQL via Unix Domain
  Socket. The final path component of the URL is the database name.

- *postgres://* or *pg://* -- Connect to Postgres. This URL follows the
  form used by the <https://github.com/lib/pq> driver.

- *sqlite://[path]?[options]* -- Open an SQLite3 database. Takes options
  defined by <https://github.com/mattn/go-sqlite3#connection-string>,
  but not all of them may be well-supported in sql(1). Path may be
  `:memory:`, in which case the opened database is in-memory only --
  this has limited use, but may help with testing.

# Query

All subsequent arguments to sql(1) are SQL queries / statements and
arguments to them.

Each query is separated by a delimiter (-s, --sep), by default a comma
(","). For example, to pass two queries:

```
$ sql ${dsn} 'select 1' , 'select 2'
{"1":1}
{"2":2}
```

In addition, it is possible to pass query arguments. By default, query
arguments are bound to *?* placeholders in query strings, but may differ
depending on the driver in use. If using *?*, sql(1) will attempt to
convert the placeholders to a format appropriate for the driver using
<https://github.com/jmoiron/sqlx>.

Query arguments follow a query string and must come before the query
delimiter (-s, --sep). For example:

```
$ sql ${dsn} 'select ?' 123456
{"__result_0":"123456"}
```

Note that in the above example, 123456 is interpreted as a string. This
is because no type hint was given to sql(1), so it will defer to the
exact data it was given. To provide a type hint, prefix the argument
with "TYPE" -- for example:

```
$ sql ${dsn} 'select ?' int123456
{"__result_0":123456}
```

This ensures that sql(1) will attempt to parse the value as an integer
before passing it to the database. If the value cannot be parsed, sql
will exit with an error message.

In addition, to pass arrays of values, you can group a set of parameters
using the *-{* and *}-* arguments:

```
$ sql ${dsn} 'select ?' -{ 1 2 3 }-
{"__result_0":"1","__result_1":"2","__result_2":"3"}
```

Note that an occurrence of a *?* placeholder that matches an array
parameter will be expanded to multiple comma-separated *?* placeholders.
This is also an effect of using sqlx, and may not be entirely accurate,
so exercise caution when using array parameters.

To execute a query multiple times with different parameters, you can pass the
argument separator (*--arg-sep*, defaults to *,,*) between sets of arguments.
For example:

```
$ sql ${dsn} 'select ?' str::1 ,, float::2 ,, int::3
{"__result_0":"1"}
{"__result_0":2}
{"__result_0":3}
```

Note in the above output that each row is for *\_\_result_0* (the default naming
for unnamed columns), instead of each being for a different columns.

# Types

Keep in mind that types are applied after file loading. So, it is possible to
give a file-based parameter a type by preceding it with that type. For example,
*int::@file* will load file and then parse its contents as an integer.

The following types are available for use in sql(1) parameters. This
list follows the format of

```
Type: name[, aliases]
example(s)
```

## Strings: *str*, *s* (default)

```
str::Foobar
```

## Bytes: *bytes*, *bs*

```
bytes::Foobar
```

## Signed int (64-bit): *int*, *i*, *l*

```
int::123 int::-456
```

## Unsigned int (64-bit): *uint*, *u*, *ul*

```
uint::123456
```

## jq expression: *jq*

```
jq::'$last[][].id'
```

The input to any jq query is an array of all prior results, with the following
levels: query, executions, rows. So, given an sql run like the following:

```
$ sql ${dsn} 'select id from things where name = ?' foo ,, bar , 'select top from pools'
```

The jq expression `.[0][1][2]` accesses the first query's result set (`select id
from things`), its second execution (`where name = ?` with `bar`), and its third
row.

A variable for the last query executed is available as `$last`. If this is the
first query, `$last` is null.

## JSON array: *array*, *a*

```
array::'[1,2,3,4]'
```

## JSON values: *json*, *j*

```
json::'1 2 3 4'
```

## Fields (quoted, strings): *sh*

```
sh::'"foo" "bar"'
```

## Fields (quoted, nested typing): *fields*, *fs*

```
fields::'int::1 str::2'
```

## File contents as a string: *openfile*, *of*

```
openfile::./data
(similar to @./data)
```

## File contents as bytes: *rawfile*, *rf*

```
rawfile::./data
(similar to bytes::@./data)
```

## Contents of a file descriptor: *fd*

```
fd::4
```

## Floats (64-bit): *float*, *double*, *single*, *real*, *d*, *f*

```
float::1.23456
```

## Booleans: *bool*, *boolean*, *b*

```
bool::true, bool::0, bool::FALSE (any form supported by Go's
strconv.ParseBool)
```