API to call any SQL: either pass SQL directly OR predefine in GUI and give it a nickname


(Anton) #1

It would cover a lot of custom embedding cases if we were able to make an API call and pass in just any SQL, and get JSON results back.

One good use-case is integrating with WordPress.

First, install a plugin that fetches JSON and provides a simple templating language to render the JSON data the way you need:

Next, let the plugin make an API call and pass an SQL query IN and gett a JSON result OUT. This would allow to extract whatever data you need directly from the database.


Now, to get it even further, a simple SQL API section in the settings might be helpful. It would allow us to create custom SQL queries and assign names to them. Then through API we could just use the SQL name instead of passing the whole sql:

/custom_sql.json?name=abc

Fairly, the implementation does not sound as difficult from the programming point of view.
Anyone interested? Should be a plugin or part of core functionality?


(Jeff Atwood) #2

This would be very dangerous, if you passed in SQL to query out the cookies / auth table you could then log in as any user.

In general raw access to the database is a highly privileged state, suitable only for known, verified admins.


(Anton) #3

If queries are named and configured by admin, it should not:

/custom_sql.json?name=abc

I mean, if I need access to database, I’ll anyway gain it. So it would be good if we simplified the way I do it. Instead of creating a proxy script on the server that connects to the database in a Docker container, I’d prefer to input my queries in GUI and give them names, then just use standard API to call them. Makes any sense?


(eriko) #4

What kind of queries are you thinking about? Is that data not available in the existing API.


(Anton) #5

It is not. I need to fetch the list of users ordered by some special scoring criteria.

Still, even if it was available through the API, I believe there will be many cases when it would be useful. Come one, guys, why to limit customizations and embedding! What I’m proposing is something that might cover many various cases that you can never predict. Just give a tool to make integration as flexible as possible and watch users invent how to use it. Why not.


(Jeff Atwood) #6

You can eventually use the SQL query tool @riking is building. But it won’t be through an API per se.


(Anton) #7

Oh, that’s interesting! So, half job done.

Still, please reflect about the idea of giving SQL queries names and making them callable through API by name only (no raw SQL through API!). Should be a moderately simple addition, but very useful, and allows for many custom scenarios in integrating Discourse with 3rd party web applications.

My use-case, as mentioned above, is the following:

I need to fetch the list of users ordered by some special scoring criteria.

I’d utilize this feature straight away!


(Kane York) #8

Actually, it perfectly fits the second scenario in the title. You define the query in advance through the admin UI, then you can programmatically POST /admin/plugins/explorer/queries/:id/run with any parametrized values.

You will need admin credentials to do so, though.

There’s no concern about arbitrary SQL other than shooting yourself in the foot with a ton of cross joins - it runs in a read only transaction, and semicolons are not allowed.


(Anton) #9

Do you mean there will be a way to get output as JSON?

Do you mean I will need to generate an API key in my (admin) profile and use it with my POST request?


(Kane York) #10

The response looks roughly like this (SUBJECT TO CHANGE):

{

  • success - true/false. if false, inspect the errors value.
  • errors - array of strings.
  • duration - float. Time to execute the query, in milliseconds to 1 decimal.
  • columns - array of strings. Titles of the returned columns, in order.
  • explain - string. (Optional - pass explain=true in the request) Postgres query plan, UNIX newlines.
  • rows - array of array of strings. Results of the query. In the same order as columns.

}


(Anton) #11

Any chances it will be able to “expand” the results at least one level?

E.g. ?expand=user_id would create a user key which is an array of all user fields.

Actually, you could adopt the existing Facebook’s GraphQL design.
But that’s probably too much complexity…

If GraphQL design is adopted, then we will be able to build ReactJS interfaces (or use our existing ReactJS applications) and connect React components to the this endpoint, which you’re developing. What a nice integration option for ReactJS applications, especially taking into account the growing popularity of React Native.

For me personally, it would be amazing to have such an endpoint to which I could connect my ReactJS interfaces and use GraphQL to query data from Discourse. Maybe something to consider putting in the roadmap.


(Kane York) #12

Uhhh, graphQL is a way to get specific fields out of a data store. This is supposed to be for running analytics queries.

This is a very bad idea. Get CORS set up and just use the existing endpoints. Running Data Explorer queries requires admin, and that will not be changing.


(Anton) #13

Please could you explain what CORS means?


(Anton) #14

Have been any updates to that tool since then?


(Kane York) #15

The data explorer API has been updated, but it still requires admin.