external-libs for database queries

[stmarier]

AFAICT this relates heavily to #140.

I stopped by the matrix chatroom and had a quick chat about this + we decided to move to GH issues so that it isn't lost to chat history.

Summary of the chat:

• there isn't a great story today for external libs
• the things that work today that are most adjacent to this functionality are curl, yaml/load, json/load, load
• yamlscript would like to do better than lein or deps.edn includes
• something like babashka pods (which looks like exactly what I am looking for)

Bluntly, I had to look up what a lot of this stuff was. I think most of the value I can provide here is being a stand-in for other people who would also have to look up what this stuff is.

---

Ultimately, I'm looking to be able to do something like this:

enabled: true
expression:: db.sqlite("select prop1, prop2 from things where [...]", "/path/to/database.db")

Being able to evaluate this locally would be really useful, and the functionality is intuitive. I provide some path to a sqlite database, the database is queried, and I either get an error or the result.

What happens if I --binary it and then ship that binary to another server? Do I need to also ship the database, or is the result stored in the resulting binary? Maybe the entire database can be stored in the binary?

All 3 of these sound useful, as an end user I think I would just need to know which one will happen so I can build the rest of my contraptions with that understanding.

I think it makes sense to figure out sqlite first since it's very easy to test. I also suspect that the sqlite external lib would be a sufficiently complicated reference to assist with other external-lib style contributions or plugins. In particular, I would use it as reference material and attempt to create a similar implementation for an aerospike query.

[ingydotnet]

YS supports pods and iirc I had a demo that used sqlite. Trying to find it.

[ingydotnet]

found it:

$ cat pod-test.ys 
!yamlscript/v0

use-pod:
  q(org::babashka/go-sqlite3)
  "0.2.3"

require pod::babashka::go-sqlite3:
  => sqlite

result =:
  sqlite/query(
    ':memory:'
    ["SELECT 41 + 1 AS sum"]
  )

say: result.0.sum
$ ys pod-test.ys 
42

[ingydotnet]

What happens if I --binary it and then ship that binary to another server? Do I need to also ship the database, or is the result stored in the resulting binary? Maybe the entire database can be stored in the binary?

Obviously you could base64 encode the db file and then expand it to a place on disk or something like that.
That would work with --binary of course.

[ingydotnet]

Let me know how far that gets you. Still a lot to make this silky smooth...

[stmarier]

I had some time to experiment with this today, it not only works perfectly for my needs but the on-ramp for adding more stuff that I would need (other databases, etc) is clear. 🙂