Support for raw SQL when using PostgreSQL or MariaDB/MySQL

[mphuie]

It's not clear how the DB integration works-

Do you have to use the built in methods to write/access data or can you write SQL queries? Or do you have to write Lua code to make the connection? Also, how to imports/require work for external Lua modules?

Thanks

[xyproto]

Thanks for reporting! I'll update the documentation.

Currently, I think the built-in methods must be used. It's also possible to use a plugin (that could use SQL), but I think that would be a bit cumbersome.

External Lua code can be used if you use the require function, but there is currently no support for external Lua modules in the same fasion as ie. LuaRocks

What is the specific use case that you want to solve?

[mphuie]

Just trying to wrap my head around how to use Algernon with it's DB functionality, code samples with how to do standard CRUD operations would be great. I haven't had much luck with the help in the REPL, which the documentation seems to mirror.

[xyproto]

I should document this in more detail, but here is one example for using a list, regardless of which database backend is in use:

https://github.com/xyproto/algernon/blob/master/samples/list/index.lua

[mphuie]

Thanks, what if I have a database with existing schema, how do I query/filter/sort it? Also are there any more comprehensive examples on JFile and JNode?

[ismailadegbenga]

Hello xyproto,
Thanks for all the hard work you've put into this project.
As regards this issue. In more general terms, is it possible to get the postgresql database connection object from lua? So arbitrary SQL statements can be executed against the database from lua.
Thank you.

[xyproto]

Currently, there is no support from running SQL from Lua, but it can be done by writing a plugin for Algernon.

[mphuie]

Thanks, IMO without being able to run SQL queries against databases, it severely limits the usefulness of the server.

[xyproto]

The database backends are interchangeable, and the PostgreSQL and MariaDB/MySQL dialects are different (and PostgreSQL has the HSTORE feature).

Currently, the same Lua code can be used regardless of which database backend is in use, whether it's PostgreSQL, Redis, BoltDB or a future backend that has not been added yet.

Which SQL dialect do you think should be supported and which use case is not currently covered, either through the existing Lua functions or by using an Algernon plugin?

[ismailadegbenga]

It will be nice if you can provide an example plugin for raw database access to postgresql.
Exposing the database connection API so that raw sql queries can be executed against the database is really important for potential users of this project.
Thanks for the good work.

[xyproto]

Added a function for querying PostgreSQL databases. This does not have to be the same database as is used internally by Algernon, it can be any remote database as well.

[mphuie]

I'm not able to get it to work. My query doesn't execute to run and the results are {"1":"PostgreSQL 9.6.11 on x86_64-pc-linux-gnu (Debian 9.6.11-1.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"}. This is the output of SELECT VERSION()

local query = "select * from resource"
local results = PQ(query, connString)
print(json(results))

[xyproto]

@mphuie Thanks for testing, It was a bug. It's fixed in master now.

[mphuie]

I can now query, but only can select a single column.

This query works - select id from resource
This does not - select id, name from resource, nor does select * from resource

My code

local query = "select id from resource"
local results = PQ(query, connString)
print(dir(results))

[xyproto]

Thanks for testing! What would be the ideal return format, a Lua table of strings? A Lua table of strings and numbers? Or a Lua object that could be further queried? Or JSON? What is it typically being used for next, for inserting into a template or generating a table? If so, I could add a function for returning a Markdown table and one for returning a HTML table, for instance.

The idea is to have quick and easy PostgreSQL support, with the option of writing a plugin for Algernon for more specialized usage.

[mphuie]

Just the ability to get full query results would be great, for me personally I just want to output JSON, however I'm fine with writing the code from table -> JSON if you can fix the results.

[mphuie]

It looks like the results of a query can only be a []string, which makes queries very limiting.

algernon/lua/pquery/pquery.go

Line 110 in 895ebb2

values []string

[mphuie]

Possibly instead of using Golang to do the execution, possibly include a Lua module/library instead? Or allow the user to import a library themselves?

[xyproto]

I will look into adding support for loading Lua modules. I think this will give more flexibility, while the PQ function can possibly be nice to keep just for quick queries.