Passing command arguments using heredoc syntax#

This trick works for both Bash and zsh.

I wanted to pass the following as an argument to the sqlite-utils CLI tool:

1
insert into documents select
2
  substr(s3_ocr_etag, 2, 8) as id,
3
  key as title,
4
  key as path,
5
  replace(s3_ocr_etag, '"', '') as etag
6
from
7
  index2.ocr_jobs;

Problem: this contains both single AND double quotes, which makes string escaping a tiny bit tricky.

Solution: use heredoc syntax:

1
sqlite-utils sfms.db --attach index2 index.db "$(cat <<EOF
2
insert into documents select
3
  substr(s3_ocr_etag, 2, 8) as id,
4
  key as title,
5
  key as path,
6
  replace(s3_ocr_etag, '"', '') as etag
7
from
8
  index2.ocr_jobs;
9
EOF
10
)"

Breaking that apart: the main trick here is to use cat <<EOF ... EOF to wrap the literal chunk of text:

1
$(cat <<EOF
2
insert into documents select
3
  substr(s3_ocr_etag, 2, 8) as id,
4
  key as title,
5
  key as path,
6
  replace(s3_ocr_etag, '"', '') as etag
7
from
8
  index2.ocr_jobs;
9
EOF
10
)

Then to pass it as an argument to the sqlite-utils command use "$(cat ...)" - the double quotes around that ensure that tokens in that input are not treated as separate arguments by zsh/bash.