9 ms·
Show HN: SQLite query inside a Bash function
Part of the workflow for building my website is the generation of a table in
tab-separated column format (.tsv). The source data is found in four other .tsv
files. I use an SQLite query to perform a 4-way join and write out the new table. For
convenience, I wrote a script that encapsulates the query inside a Bash function.
The example below illustrates this technique.
repertoire() {
pushd $CMM_SOURCES/_data
sqlite3 <<EOS
.headers on
.mode tabs
.import category.tsv category
.import composition.tsv composition
.import concert.tsv concert
.import program.tsv program
.once repertoire.tsv
SELECT
category.name AS category,
composition.key,
composition.composer,
composition.name AS composition,
concert.name AS concert
FROM
category,
concert,
composition,
program
WHERE
julianday(concert.date) < julianday('now')
AND composition.category = category.name
AND program.key = composition.key
AND program.date = concert.date
ORDER BY
category.sequence,
composition.key
;
EOS
popd
}
- mohanmca 5y agoThanks.
- VWWHFSfQ 5y agoneat
- dinedal 5y agoYou can do this with https://github.com/dinedal/textql https://github.com/dinedal/textql as well, and a little easier as you don't need to import the data explicitly. SQL_QUERY=<<-EOS SELECT category.name AS category, composition.key, composition.composer, composition.name AS composition, concert.name AS concert FROM category, concert, composition, program WHERE julianday(concert.date) < julianday('now') AND composition.category = category.name AND program.key = composition.key AND program.date = concert.date ORDER BY category.sequence, composition.key ; EOS textql --header --dlm=tab --sql $SQL_QUERY category.tsv composition.tsv concert.tsv program.tsv
- kingosticks 5y agoOr with https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/ https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/
- seppel 5y agoOr with http://harelba.github.io/q/ http://harelba.github.io/q/
- hnjst 5y agoOr with https://csvkit.readthedocs.io/en/latest/ https://csvkit.readthedocs.io/en/latest/
- zimpenfish 5y ago+1 for csvkit because it is extremely useful when dealing with people who love spreadsheets to the point of insanity.
- SahAssar 5y agoSeems like textql is a wrapper around sqlite, I'm guessing staying with sqlite directly makes it easier to interact with other tooling. One nitpick with textql is that it says "sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.", but that's not true, you just need to tell it to use stdin by doing: sqlite3 '.import /dev/stdin {youtablename}' I do this all the time for using gzip and sqlite to get compressed import with progress. For example filtering a compressed CSV from a SQL query with progress for import: pv icons.csv.gz | gzip -dc | sqlite3 ':memory:' -csv '.import /dev/stdin data' '.output result.csv' '.headers on' "SELECT name, keywords from data where name = 'chart-area'"
- tyingq 5y agoDo keep in mind that /dev/stdin{out,err} aren't completely portable. Bash emulates them if they don't exist, but this usage bypasses Bash. So there are unix like platforms where this trick won't work. They do, though, seem to be supported on the ones that are most popular now (Linux, MacOS, FreeBSD).
- sigmonsays 5y agoIf you're trying to put sql queries into a bash script, I beg the question to rewrite said script in a programming language. There are of course a bunch of other factors that would justify a rewrite so this isn't a hard rule. However, having written hundreds of lines of bash gluing systems together for various CI jobs and automations, at some point you need to stop writing bash and switch to something with a richer data model and easier to maintain/test.
- 0xbadcafebee 5y agohttps://en.wikipedia.org/wiki/Begging_the_question https://en.wikipedia.org/wiki/Begging_the_question
- _russross 5y agoIn English, meaning is defined by common usage. I know the formal logic hipsters have mounted a persistent campaign to make an exception here, but the fact remains that "begging the question" means exactly what ordinary people think it means.
- gxs 5y agoWhile I agree with you in principle, I think it’s more than permissible to make this distinction on this forum given the audience, IMO, even if the person who made the comment admittedly lacked tact in their approach.
- knorker 5y agoI've spoken and written English for over three decades, and this is the first time I've seen "begging the question" be used to mean this. It's usually meant as "raises the question", but language lawyers insist it means what the wiki page said. But this is neither of those two. And if you're the only one who uses a phrase a certain way, and it means other things to everyone else, then that's actually wrong, even in a descriptive language.
- handrous 5y agoThis is one of the cases for which, when writing for a broad audience, it's best to avoid the phrase in question, in either of its senses—precisely to avoid confusion or distraction like what's happening in this thread, with the common definition, and to avoid confusing the uninitiated with its more "correct", but jargony, sense—but also to accept the common meaning in others' language without complaint (ahem).
- chubot 5y agoYup, I do this all the time! :) I write Python programs that use sqlite and set up the DB state with bash.
- e12e 5y agoIf you don't intend to fill in/expand variables - you probably want to quote the HEREDOC tag,so rather than: sqlite3 <<EOS ... WHERE user = $(rm /etc/shadow)... EOS You might want: sqlite3 <<'EOS' ... WHERE user = $(rm /etc/shadow)... EOS
- elwell 5y agoSpeaking of SQLite, this CoRecursive podcast a few days ago was enjoyable: https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
- _benj 5y agosecond this! I read the transcript and it was quite enjoyable indeed
- sgt 5y agoQuite agree. And he seems like such an agreeable person too. Listening to Richard Hipp and his thought process felt familiar in the sense that he is the traditional old school programmer, and I know a few of those.
- rahimiali 5y agoIf you're sweating quoting issues in here-documents, you can put the sqlite query in a separate file and tell sqlite to execute that file instead. Here's a helpful example from SO: https://stackoverflow.com/a/21759264/711585 https://stackoverflow.com/a/21759264/711585
- dashezup 5y agoTrue, I don't see the benefits of writing it in bash/shell script in this case. Plus you can get proper syntax highlighting for SQL in your text editor for ".sql" file. sqlite3 database.db <query.sql Some other DBMS have similar things, for MariadDB you could write it in a ".sql" file as well, setting variables and use it so that you can write a generic sql file. (seems you can't use the variable thing with sqlite3) MariaDB> SET @username='UserName@localhost'; MariaDB> SET @hostname='localhost'; MariaDB> SOURCE create-user.sql; Such things is usually cleaner/simpler than writing in bash.
- nerdponx 5y agoFor what it's worth, you can have "nested" syntax highlighting of SQL inside Bash scripts, with Tree Sitter in Neovim. You could also read the contents of the query file into a shell parameter with the `read` builtin.
- nerdponx 5y agoFun fact: you can do this with Sed and Awk, too. No more hideous unreadable one-liners.
- lxe 5y agoYou can encapsulate almost anything in a bash function via heredocs like that. The disadvantage is that editor features like indentation and syntax highlighting rarely work.
- tyingq 5y agoThe "here document" (<<EOS) is a nice workaround to keep from having to correctly quote a bunch of SQL syntax. I do agree, though, with some of the other comments that you will probably want a "real language" other than bash if this grows at all. Passing in string data for a where clause, for example, would get cumbersome and error prone. Python or Perl would be good choices if it goes there.
- chmaynard 5y agoI wasn't aware that '<<' is a shell directive. That often happens when I mindlessly copy code snippets from somewhere else. https://tldp.org/LDP/abs/html/here-docs.html https://tldp.org/LDP/abs/html/here-docs.html
- 1vuio0pswjnm7 5y agoWhy not sh instead of bash. This is a script; it is not an interactive shell. sh is faster as a scripting language. Don't take my word for it. If you are using a UNIX-like OS such as BSD or GNU/Linux, sh has been chosen as the default scripting shell, not bash. Bash might be the default login shell (interactive use) but it is not the default scripting shell (non-interactive use). This is only one function. We cannot see the rest of the script. Maybe there is a compelling reason1 to to use pushd and popd instead of using sh features. 1. i.e., no way to achieve same result in sh
- chmaynard 5y agoFYI, here is the entire script: https://raw.githubusercontent.com/chmaynard/Sources/core/workflow.sh https://raw.githubusercontent.com/chmaynard/Sources/core/wor...
- nerdponx 5y agoI would add a `#!/usr/bin/env bash` at the top, so readers know that it's meant for Bash.
- AlphaSite 5y agoIs comparability with sh really a worthwhile goal anymore? I haven’t seen many places with sh but not full bash (busybox being the main exemptions but that breaks most things due to muslc anyway).
- 1vuio0pswjnm7 5y agoWhat OS are you using. It may use bash for login, but I would be willing to bet it does not use bash for scripting (non-interactive use). It is possible that many shell script authors do not understand the difference between a login shell (optimised for interactive use) and a scripting shell (optimised for non-interactive use). Thus the comparison could be worthwhile. sh is faster, more portable and arguably presents fewer potential gotchas. FWIW, shellschock affected bash but not sh. NetBSD uses ash-derived sh as both login and scripting shell. No bash. Debian and many, many other GNU/Linux distributions (including musl-based Void) all use NetBSD-derived sh called "dash" as the default scripting shell. I am not aware of any Berkeley distribution that uses bash as the default scripting shell.
- remram 5y agoYou are not using any variables or anything from bash in that query. Wouldn't it make more sense to extract it to a .sql file and just run that, rather than your (rather long) heredoc?
- chmaynard 5y agoAgreed. I like having the query in situ during development because it simplifies editing. But there are disadvantages that you and others have identified.
- manx 5y agoI recommend adding a little trick to ignore existing ~/.sqliterc files, which makes the script more portable: cat <<EOF | sqlite3 -init <(echo "") "$DBFILE" ...your sql... EOF For one-line queries I use: q() { echo "$@" | sqlite3 -init <(echo "") "$DBFILE"; }
- tyingq 5y agoInteresting. I noticed that "-init" doesn't error out if you give it a filename that can't be a file, like . or /. Using /dev/null seems to work also.
- t0astbread 5y agoThe cat in the first script can be omitted I think (by using the heredoc on sqlite directly).
- _benj 5y agothis is really cool! Would you mind if I ask about "performance"? I'm working on a toy project with luajit and I can either use ffi and SQLite C API (which can be a little complicated) or just use the CLI and capture the output of a command as a string and parse that. As I'm writing this I'm thinking that I should just run o=some tests using both approaches, but, how's the performance for you? Any noticeable delays or something of the sort?
- chmaynard 5y agoMy guess is that the query itself takes the same amount of time either way, C API or CLI. There may be some I/O overhead when using sqlite3, but I doubt if it's anything to worry about. The beauty of using sqlite3 is that it hides the complexity of the C API, which is procedural.
- deleted 5y ago[deleted]
- tkinom 5y agoPersonally, I like to see sqlite builds into the bash. For features such as: Continuous logging the bash commands, env, results, exec time in a sqlite database for security/auditing and regression testing. The db file can be kept opened, no need to spawn a separate process for each command logged. Anyone else interests in such approach. Anyone thinks of any potential issue for such integration? If there are enough interests, I can start a open source project for this.
- chmaynard 5y agoInteresting idea. How would you add sqlite bindings to bash? Via a plugin?
- nerdponx 5y agoYou could do it in Zsh with a "module".
- tkinom 5y agoProbably something like the following: 1) Add sqlite_log.[ch] files to bash source repo. 2.a) Add sqlite_init_db(...) to init a database per bash_sqldb_{date}_{pid}.db per bash pid on ~user/.bash_db/ dir. 2.b) Add sqlite_log_cmd(...) to log the cmd, evn, pid, to the opened db. 2.c) have the bash command exec routine to call the sqlite_log_cmd(...) on each cmd execution. 3) Query, report of the commands execution will be handled outside with bash scripts/sub commands/web intf. * Should be very low overhead (micro-seconds) for each command. Now I think this out a bit more, maybe it is better to write ebpf script and pipe all the cmds exec to one centralized sqlitedb (instead of per user/pid and only for bash). Likely more useful from system security auditing POV, easier to extend by adding network connetions, file open, privilege escalation type events to the DB.
- ddgflorida 5y agoThis is a perfect use case for a shell script and sqlite. No need to complicate with anything else.