Sql api

Feature Requests
1

You guys should provide an SQL API, either by providing JDBC/ODBC drivers, or by exposing a MySQL- or Postgres-compatible endpoint.

1 Like
2

Hey Jen! 

That's a really interesting use case, and something I certainly agree Knack should make available. We've run into a similar situation where Knack's UI provides our team with more benefits that overshadow the lack of convenience for our data analytics and development teams.

Our core functional team has custom built a sudo-SQL "grabber" that lets you input your object / field requirements (and their connections / how they connect) to retrieve and export data in bulk, both quickly and efficiently. Basically aggregating the data similar to how you described "downloading each object", but with the connections pulling in, as you've defined them (depending on how they're set up).

The return value from our server is a JSON object of data arrays that can be mapped however you see fit. Ours return object gets mapped into a custom "excel-like" table that can be viewed within our app, or exported into a CSV file.

Our main use is for accounting records. An example of data we give to our accounting staff is in the format of a "Job Profit Report": 

  • Jobs (3500 records)
  • Work In Progress Transactions (~12000 records)
  • Revenue Transactions (~10000 records)
  • Accrual Transactions (~8000 records)
  • Cost Transactions (~9000 records)

This type of request takes about 30 seconds for our server to aggregate. Not sure if that provides a solution to what you're looking for, but I'd be happy to give you a demo if you're interested. 

3

I understand that Knack is investigating switching from Mongo to a SQL back end. Hopefully, this might open up further options for us to have (at least) direct read only access to our data. An ODBC connection to SQL would totally meet our needs. Also, to be specific, this would only be for Builders to have access to, not front end users.

 

In terms of problems it solves, the most straightforward one is that in order to export (or back up) all of our data for external analysis, I currently need to go in to each Object individually and export all records to CSV. It takes a while to do and I have to remember to do it for every Object in every App. Even an API-based solution would need IDs added for every new app/object, be limited to 1,000(?) record batches, and require lots of looping to ensure we capture everything. Read-only database access would be one way of resolving this lengthy process of downloading my data for direct querying purposes or to import into my own database. To be fair, another (perhaps easier) step towards a solution would be a comprehensive “Download ZIP(?) of all CSVs of all Objects” button for each app. (See Requests: Task for Scheduled Export and Improve Import and Export)

 

In terms of why I find Knack’s current reporting/querying tools insufficient for our needs, it has to do with “schema on write” (e.g. Knack) vs “schema on read” (my previous setup with SQL). This article explains both fairly succinctly: https://www.thomashenson.com/schema-read-vs-schema-write-explained/

 

The way our projects run, our clients generally just hand us a pile of data/documents and say “figure it out.” Accordingly, we don’t really know what schema setup, relationships, or even *fields* we will need at the start of any project until we start gathering and inputting data. It all kind of evolves as we go. The “schema on read” method I’d used in SQL allowed us to RAPIDLY get data entered into tables without the burdens of pre-defined foreign key constraints (e.g. Connections), but with the ability to use whichever fields and combinations of fields we wanted on the fly to create relationships between tables On Read (in the SELECT query) for whatever reporting we needed at the moment.

 

Per the “plan more, build less” presentation at KnackCon2019, Knack is really not built for this kind of “grab everything now, figure out how it all connects later” purpose. However, its UI is better for our users (and builders), so as long as I can easily access/export the data for more complex or on-the-fly analysis via queries, I can make some non-ideal compromises on how the data is set up in Knack in order to get projects up and running and collecting data faster.

 

As always, I’d always be happy to have a call and/or screen share at any time to discuss our use cases more specifically. I suspect that most of the features/improvements that would particularly help us (like “Download All CSVs”) would also be massively beneficial to other customers, particularly consultants.

4

Agreed. Currently there isn't a good mechanism to bring Knack into an organization's BI / DW solution.