API for serverless access?

[JamesPerreault]

The feature list shows that there is an API for "serverless" access. I can find no documentation on that. Is that feature implemented?

[justinclift]

@JamesPerreault Not yet. It's the main thing I want to get done next.

Haven't really designed it out though (eg the first proper steps).

If you're ok for sharing, what sort of stuff are you looking for? We'll likely use that as the initial use case (depending on details), to make sure it works well, then expand out from there. :smile:

[JamesPerreault]

First, let me give you my use case. I want to port a desktop recipe application to a web enabled application. The application's dbackend is a sqlite database.

So at the minimum I would need a query interface. An sql like interface would probably work. (I.e., I specify the select statement, the results are returned; possibly in chunks).

Updates would be handy, but I think I could use the existing command line tool for that.

[justinclift]

Awesome. :smile:

Thinking that through (out loud)...

Being able to execute arbitrary (read only) SQL (eg SELECT statements) should be pretty easy. The server side code for doing the core piece of that is already written and working decently well for our recent visualisation feature.

It should be fairly straight forward to make a "version 1" api using that. Would you be for trying it out, likely this weekend?

---

Thinking about updates, two main potential approaches spring to mind:

1. Uploading a new database (or database revision)
2. Running (say) INSERT, UPDATE or DELETE statements

It should be ~reasonably easy to add support for uploading new databases, and/or new revisions of an existing one. That's also code which already exists, so could be leveraged to suit in a timely manner.

Running SQL statements that modify a database (eg INSERT, etc) would probably be a lot more tricky, as the entire server side is version controlling everything at present.

So we'd either need to figure out a way to store databases in a "live" (non-version-controlled) fashion... or automatically create new revisions each time an "updating type of statement" is run. Doable, but it could be fairly complex to make happen.

If you're ok with the first approach (uploading complete new database), via the API, that's probably the one to go for initially.

Thoughts?

[JamesPerreault]

Yes, I would be able to try out version 1 of the api this weekend.

As for updates, option 1 works for me.

Thanks!

[justinclift]

Excellent. Will get that happening.

Out of curiosity, do you do much API programming?

Also, what's the primary programming language(s?) and/or frameworks(s?) you're likely to use?

All that info is optional of course, it's just I'll probably do a bit of reading up on it to try and make sure things "work easily". :smile:

[JamesPerreault]

I'll be using Node.js / Javascript for the query interface. The thick app is written in Python, so inserts and updates will likely be in that language. But I'm still thinking about that part.

I have not done any API development, although I do know C/C++.

[justinclift]

No worries. We'll get something working for this weekend, and can iterate on it until we're both happy. :smile:

[chrisjlocke]

Back in March (that seems months ago now ... when it was cold and windy...) I was doing a lot of API work in VB (both XML and JSON data) so happy to look at this. JSON is preferred... But yes, can send/receive API requests to help with this.

[justinclift]

@JamesPerreault Working on getting a basic query interface happening now. :smile:

[justinclift]

Well, that turned out to be a lot more fiddly to get the pieces in place than I was expecting. :wink:

That being said, there's a very (very) bare bones API server running that people can query their own databases (and any public ones) one.

The server name is https://api.dbhub.io. As an initial starting point, it only does one thing... run a given SQL query on the desired database. There's no upload, delete, "get properties", "get list of databases", (etc).

For running a SQL query, the address is https://api.dbhub.io/v1/query and there are four (POST) parameters:

• dbowner - The owner of the database to query
• dbname - The name of the database to query
• commit - (optional) If you want to run the query on a specific database version rather than whichever is the latest, pass in it's commit ID via this parameter
• sql - The SQL query to be run, base64 encoded

As a useful way to test it, the command line utility curl seems to work well. Here's an example for running a query against one of the public example databases in my account:

curl -F apikey="YOUR_API_KEY_HERE" -F dbowner="justinclift" -F dbname="Join Testing.sqlite" \
  -F sql="U0VMRUNUIHRhYmxlMS5OYW1lLCB0YWJsZTIudmFsdWUKRlJPTSB0YWJsZTEgSk9JTiB0YWJsZTIKVVNJTkcgKGlkKQpPUkRFUiBCWSB0YWJsZTEuaWQ7"  \
  https://api.dbhub.io/v1/query

You can get an API key by logging into the DBHub.io website interface as normal, then going into the user Settings page (top right corner after logging in). There should be an obvious "Generate API key" button available now. :wink: (note that there's not yet any way to delete or disable keys. That's a task for another day)

Running the above SQL query should return a result as JSON. The structure it's being returned will probably need adjusting though, as it's just something I threw together ages ago for our web interface to use. I'm happy to change the output format/structure (etc) if needed.

Thoughts?

[justinclift]

Oh, forgot to mention... the SQL query in the above example (prior to base64 encoding it) is:

SELECT table1.Name, table2.value
FROM table1 JOIN table2
USING (id)
ORDER BY table1.id;

[chrisjlocke]

Well done! Nice! I'll have a go at this - currently doing some API gubbins stuff, so ...

[chrisjlocke]

Whatever I throw at the URL, I get this error.

curl: (35) schannel: next InitializeSecurityContext failed: Unknown error (0x80092012) - The revocation function was unable to check revocation for the certificate.

Even copied and pasted your example (using my own API key) but got the same error.

[JamesPerreault]

I tried it out. It works find with curl but when I try to do it in a web page I get a CORS error:

Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://api.dbhub.io/v1/query. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing).

Can you make it CORS compliant? The Content type looks okay, so I think the header is all that is needed.

I think I can work with the existing JSON structure. If I run into issues, 'll let you know.

Also, my database has images, stored as BLOBS. I am developing without using them, but at some point I would like to use them. (Ideally, returned in a base64 encoded string).

Thanks!

[justinclift]

The revocation function was unable to check revocation for the certificate.

@chrisjlocke Try using curl with the -k option. That tells it to skip HTTPS certificate validation, which sounds like the piece that's failing.

The webserver is using a standard Lets Encrypt certificate, so there shouldn't (in theory) be issues with it. Is the version of curl you're using pretty old?

[justinclift]

Can you make it CORS compliant?

@JamesPerreault Good point. I've been able to avoid learning how CORS works so far. Looks like it's time to get that done. I'll have a go at it tonight after work. :smile:

With the BLOBs, no worries, that makes sense. It shouldn't be too hard to get working. :smile:

[JamesPerreault]

Actually, I've decided I don't want my credentials on the web page in the clear. By making the db queries server side, instead of in the browser on the client side, I can avoid that. I've gotten that working, so I'm good to go. You can work on the CORS stuff if you want, but I don't think I will be needing it.

Thanks!

[chrisjlocke]

Try using curl with the -k option. That tells it to skip HTTPS certificate validation, which sounds like the piece that's failing.

Doh! Didn't think of updating Curl. Doing a -V did imply it was an old version. Downloaded a newer one, and the error was indicating it didn't like the self-signed certificate. Using the -k option solved that. Thanks for the hand-holding! 👍 Works perfectly. Just got to test in a VB app now...

[justinclift]

Er... it's not using a self signed certificate. :wink:

[justinclift]

@JamesPerreault Cool. I'll skip the CORS stuff for now then. I'll take a look at the blobs, and see if there's a good way to return them in base64 format.

It won't be tonight though, I'm feeling pretty tired and want to hit the sack early instead. :wink:

[JamesPerreault]

I'm having an issue where I cannot query on numeric fields. Here is an example:

select unit,amount,rangeamount,item,optional,inggroup,position from ingredients WHERE deleted == FALSE AND recipe_id == 24; Here's what it looks like bse64 encoded: c2VsZWN0IHVuaXQsYW1vdW50LHJhbmdlYW1vdW50LGl0ZW0sb3B0aW9uYWwsaW5nZ3JvdXAscG9zaXRpb24gICAgICBmcm9tIGluZ3JlZGllbnRzIFdIRVJFIGRlbGV0ZWQgPT0gRkFMU0UgQU5EIHJlY2lwZV9pZCA9PSAyNDs=

I've no issues querying on string fields.

Ideas ?

[justinclift]

Thanks, investigating now. :smile:

[justinclift]

Ahhh, hang on. I think it's just a standard SQL error. This is showing up in the logs:

Error when preparing statement for database: near "'24'": syntax error
(select unit,amount,rangeamount,item,optional,inggroup,position      from ingredients WHERE deleted == FALSE
AND recipe_id  '24';) (SQL logic error)

Probably needs a single = symbol, rather than doubling them up?

Also, I'll see if there's a way to pass back error messages like this in a useful fashion. :smile:

[justinclift]

Hmmm, the == sign approach works in the sqlite3 command line shell. Interesting. It should work for the SQL query then.

I'll probably need to investigate this after work though, as I need to get ready for that now. :wink:

[justinclift]

k, I've replicated the problem in my local development environment, and will get back to figuring out a solution after work tonight. :smile:

[justinclift]

This is a weird bug. While this query works:

select unit, amount from ingredients WHERE deleted = FALSE AND recipe_id = 24

This one doesn't:

select unit, amount, rangeamount from ingredients WHERE deleted = FALSE AND recipe_id = 24

But only when called via the SQLite driver we're using. Both queries work fine from the command line. Am debugging now...

[justinclift]

@JamesPerreault This should be fixed now. It turned out to be a dumb bug on my part, where I'd blindly left the code ignoring any result rows which contain a null value.

On the plus side, there shouldn't be too many more dumb bugs around. Hopefully. :smile:

[JamesPerreault]

It's working! There is one bug, where null values are being returned as "<i>NULL</i>" . Not a big deal, as I can check for it and set it appropriately ( right now "" ). With that work around, my code is working.

Thanks for the quick work!

[justinclift]

Awesome! :smile:

For the NULL values, what would be better to return? Empty string, or something else, or ?

I'll take a look at getting the blobs-as-base64 piece done, probably tomorrow too. :smile:

[justinclift]

As a data point, that <i>NULL</i> is coming from this statement:

https://github.com/sqlitebrowser/dbhub.io/blob/14e1a31bf9bcf9e45ba922c61c0f75b4b567d2b5/common/sqlite.go#L875-L876

It should be pretty simple to change. :smile:

[justinclift]

BLOB values are handled just above that code fragment too, currently like this:

https://github.com/sqlitebrowser/dbhub.io/blob/14e1a31bf9bcf9e45ba922c61c0f75b4b567d2b5/common/sqlite.go#L864-L865

[MKleusberg]

For the NULL values, what would be better to return? Empty string, or something else, or ?

Since we're using JSON, I'd just use null for NULL values. This allows the user to differentiate NULL values and empty strings.

[justinclift]

@MKleusberg As in "null" as a direct text string?

Can make that change right away, unless you want to do it?

[MKleusberg]

null as in

{"Name":"Name","Type":3,"Value":null}

I'm currently looking through some other code. So just go ahead if you want to :smile:

[justinclift]

@JamesPerreault The server had the "ignore rows with binary data in them" option turned on, just like it was turned on for nulls before. That's now been turned off, so you might want to double check and make sure things are still ok on your end. :smile:

@MKleusberg I'll look at the null representation tomorrow. I need to figure out what internal representation to use in the data so it gets turned into null when the Go json.Marshall() function is called. It's probably super simple, but I'm yawning my head off so it's probably better I get sleep now instead. :wink:

[MKleusberg]

@justinclift The correct internal representation apparently is nil. I'm not sure about any side effects on the webui though. So I'll leave that to you :wink:

[JamesPerreault]

It's still working, as far as I can tell.

[justinclift]

@MKleusberg Just realised that yep, it's super simple. So made the change just now and restarted the server. :smile:

@JamesPerreault The output for nulls should be correct now, as per JSON spec. In theory. :angel:

[justinclift]

@MKleusberg Side effects in the webUI is a good point, as the visualisation code is using the same function. So we'd better check that. Not tonight though. :wink:

[justinclift]

@JamesPerreault Let me know when you'll be up for doing stuff with base64 encoded images. My suspicion is it'll be pretty simple to do the base64 encoding server side, but I have no idea what you'll need to do client side for it to be shown as an image.

[JamesPerreault]

I'm a bit busy at the moment, so it probably won't be until middle of next week.

[justinclift]

@JamesPerreault No worries. :smile:

[justinclift]

Just added initial support for base64 encoding the returned BLOB (binary) fields. Hopefully that turns out to be useful. :smile:

For good measure, also added initial docs of the available API end points (eg /v1/query), parameters, etc to the API server. They're viewable by just going to the root directory in a browser. eg: https://api.dbhub.io

[chrisjlocke]

Talking of 'docs' and stuff, should we/can we/may we use Swagger? https://swagger.io/tools/

Don't mind dabblin' with this, as its interesting to me.

[justinclift]

@chrisjlocke Go for it. Seen lots of mention of Swagger, but never really took the time to grok it. :smile:

I don't have much experience writing API's prior to this, so if there's standard (JSON) output formats or something we should comply with, that might be useful. eg be helpful for people wanting to use the API.

But, that's just what springs to mind. You might find other stuff that's useful too. :smile:

[JamesPerreault]

You can display base64 encoded images in html, by setting the image src tag to:

src="data:image/jpeg;base64, BASE64-ENCODED-IMAGE"

using the correct type for the image.

I just tried it out, and it works beautifully!

Thanks again for the quick work.

[justinclift]

@JamesPerreault Awesome! :smile:

Is your recipe application something we can point people at as a working example?

[JamesPerreault]

Yes. I haven't put it online yet, as all my development has been local. But as soon as I do, I'll let you know. It might be a few days.

[justinclift]

Cool. And no worries at all. :smile:

[justinclift]

Note to self - add some unit tests for the api. :smile: