Searching valtown via sql, jq, jo

valtown.md

Searching valtown via sql, jq, jo

In a recent discussion, a useful command line pipeline was shared for querying a database and processing the results. The original command looked like this:

echo "SELECT * FROM vals WHERE lower(name) LIKE '%feed%' and lower(name) like '%email%' LIMIT 100" | jq -R '{args: [.]} ' | xargs -0 -I {} curl -X POST "https://sqlite-execute.web.val.run" -H "Content-Type: application/json" -d {} | yq -P

This command effectively queries a database for records matching certain criteria and processes the results. However, there are several ways this pipeline can be improved for efficiency and clarity.

Recommendations for Improvement

1. Elimination of xargs for stdin input in curl: Instead of using xargs to pass input from stdin, curl supports -d '@-' where @ means file and - means stdin. This simplifies the command.

2. Use of --json flag in curl: Recent versions of curl support the --json flag, which automatically sets the correct HTTP headers for JSON content. This allows for a more straightforward command.

3. Avoid explicit setting of HTTP method in curl: Explicitly setting the HTTP method (e.g., -X POST) is considered bad practice when using data, form, and/or json flags. This is because it's unnecessary and can lead to unexpected side effects when more than one request is made behind the scenes.

4. Use of jo for JSON payload creation: While jq can be used to create a JSON payload, it's more common and cleaner to use jo for this purpose. jo is specifically designed for creating JSON objects and arrays.

Improved Command Examples

Using the recommendations above, the command can be rewritten as follows:

$ jo args="$(jo -a "SELECT * FROM vals WHERE lower(name) LIKE '%feed%' and lower(name) like '%email%' LIMIT 100")" \
  | curl -s --json '@-' 'https://sqlite-execute.web.val.run'

This command uses jo to create the JSON payload and then passes it to curl using the --json flag for processing.

For keeping the SQL statement as input to the pipeline, the command can be further refined:

$ echo "SELECT * FROM vals WHERE lower(name) LIKE '%feed%' and lower(name) like '%email%' LIMIT 100" \
  | jo -a '@-' \
  | jo args=':-' \
  | curl -s --json '@-' 'https://sqlite-execute.web.val.run'

In this version, @- means treat stdin as a string, and :- means treat stdin as JSON, allowing for dynamic input directly into the JSON payload.

Additional Resources

For those looking to deepen their understanding of curl and its capabilities, especially with JSON, it's highly recommended to read through Everything curl. This online book is full of useful tricks and insights that can significantly enhance one's command line data processing skills.

https://antonz.org/curl-by-example/ is an interactive curl tutorial.

---

References

https://discord.com/channels/1020432421243592714/1221021689627017236 https://chatcraft.org/api/share/tarasglek/7B_nXLYazAyEryn4Z9Yz0 https://github.com/neverstew/valtown-search/