Introduction - Enterprise Backend as a Service
ZenQuery allows you to easily access databases through a REST API.
All you have to do is enter your database connection settings and ZenQuery will generate REST API endpoints for all your database tables and views. Moreover, you can easily add custom SQL queries for access via REST API calls.
ZenQuery provides SQL result sets as JSON, XML, HTML or CSV data.
ZenQuery is an Enterprise Backend as a Service along lines of Parse but for your own databases. It's like Heroku Dataclips but for non-cloud, on-premises databases.
ZenQuery is a JavaEE application that's securely hosted on your servers. No data ever leaves your company!
ZenQuery has the following minimum requirements:
- Java 6
- a Servlet container or application server (stand-alone option available)
- a database server (embedded H2 database available)
ZenQuery can be built and installed as a standard web application archive (WAR). Any JavaEE 7 or JavaEE 6 servlet container should work.
In order to start and run properly two settings need to be configured for ZenQuery:
- the database to store the application data in
- security preferences
If you're using a dedicated database for ZenQuery (as you probably should for production) you first need to run a DDL script that creates the necessary tables. DDL scripts for the following databases are included in src/main/resources/sql in the ZenQuery directory:
- H2
- MySQL
- Oracle Database
- PostgreSQL
Then you need to set the system property DATABASE_URL according to your database connection settings.
How you set this system property depends on your environment. A common way for doing so is using the -D command line option for Java, e.g.:
java -DDATABASE_URL=postgres://username:password@localhost:5432/ZenQuery ...
The format for the URL is
PROTOCOL://USERNAME:PASSWORD@HOST:PORT/DATABASE_NAME
A PostgreSQL DATABASE_URL for example would look like this:
DATABASE_URL=postgres://username:password@localhost:5432/ZenQuery
The following protocols / RDBMS are available. Please note that only H2, MySQL and PostgreSQL drivers are included with ZenQuery. If you want to use one of the other RDBMS you have to add the appropriate JDBC driver to the shared libraries directory of your Servlet container / application server:
- as400 (IBM DB2 AS400)
- db2 (IBM DB2)
- derby (Apache Derby)
- edbc (Ingres Database)
- firebirdsql (Firebird)
- h2 (H2)
- hsqldb (HSQLDB)
- mysql (MySQL)
- oracle (Oracle Database)
- postgres (PostgreSQL)
- sapdb (SAP DB)
- sqlserver (Microsoft SQL Server)
- sybase (Sybase)
Please note: Only H2, MySQL and PostgreSQL drivers are included with ZenQuery. If you want to use one of the other RDBMS you have to add the appropriate JDBC driver to the shared libraries directory of your Servlet container / application server.
In order to set your basic security preferences for ZenQuery the environment variable spring.profiles.active has to be set to either 'local' or 'public', i.e.:
spring.profiles.active=local
or
spring.profiles.active=public
'local' will disable HTTP basic authentication whereas 'public' will enable it. The default password is 'pP2XLieKb6'. This password can (and should be) changed in spring-security-public.xml in src/main/webapp in the ZenQuery directory. Please restart the server after having changed the password. Please note: If you turn HTTP basic authentication on your API calls will have to send an HTTP basic authentication header with the appropriate username / password, too.
How you set the spring.profiles.active system property depends on your environment. A common way for doing so is using the -D command line option for Java, e.g.:
java -Dspring.profiles.active=local ...
Using ZenQuery is simple. ZenQuery has 2 main views:
Here you can add and edit and your database connections. A valid database connection consists of:
- name
- URL
- username
- password
The URL has to be a valid JDBC connection URL such as:
jdbc:postgresql://localhost:5432/SomeDatabase
The following JDBC connection protocols / RDBMS are supported:
- jdbc:as400:// (IBM DB2 AS400)
- jdbc:db2:// (IBM DB2)
- jdbc:derby: (Apache Derby)
- jdbc:ingres:// (Ingres Database)
- jdbc:firebirdsql:// (Firebird)
- jdbc:h2: (H2)
- jdbc:hsqldb:mem: (HSQLDB)
- jdbc:JTurbo:// (Microsoft SQL Server, JTurbo driver)
- jdbc:mysql:// (MySQL)
- jdbc:oracle:thin:@ (Oracle Database)
- jdbc:postgresql:// (PostgreSQL)
- jdbc:sapdb:// (SAP DB)
- jdbc:microsoft:sqlserver (Microsoft SQL Server)
- jdbc:sybase:Tds: (Sybase)
Please note: Only H2, MySQL and PostgreSQL drivers are included with ZenQuery. If you want to use one of the other RDBMS you have to add the appropriate JDBC driver to the shared libraries directory of your Servlet container / application server.
After having entered a valid database connection ZenQuery will automatically create SELECT * FROM queries for each table and view in your database. If your database uses foreign keys ZenQuery will extend those queries to include links to referenced database entity resources.
Clicking on the 'Queries' button from the top menu will show all queries. Clicking the respective button for each database connection will show only the queries for that connection.
Clicking on 'Details' for a query will preview the result set for this query as well as reveal a few additional options:
- Execute
- Update
- New
- Previous versions
Alongside these options ZenQuery displays REST API links for this query above and below the result set preview.
ZenQuery allows you to use the ? operator for dynamically supplying one or multiple arguments to a query, e.g.:
SELECT * FROM table WHERE field = ?
SELECT * FROM table WHERE field = ? OR another_field = ?
These arguments can then be supplied as additional URL parameters to your API calls.
If your database tables make use of foreign keys for referencing entities ZenQuery will automatically link those to the referencing entity and add a link to the API resource for the referenced entity.
ZenQuery turns each SQL query into an easily accessible REST API endpoint that returns data in a variety of formats.
The ZenQuery REST API returns data in the following formats:
- JSON
- XML
- HTML
- CSV
These are a few example URLs:
- /api/v1/resultSetForQuery/6.json (JSON)
- /api/v1/resultSetForQuery/6.xml (XML)
- /api/v1/resultSetForQuery/6.csv (CSV)
- /api/v1/resultSetForQuery/vertical/false/6.html (HTML list)
- /api/v1/resultSetForQuery/vertical/true/6.html (styled HTML list)
- /api/v1/resultSetForQuery/horizontal/false/6.html (HTML table)
- /api/v1/resultSetForQuery/horizontal/true/6.html (styled HTML table)
ZenQuery also allows you to add arguments to an API call, which will be used for interpolating variables in the SQL query. The arguments are appended to the URL after the query ID. Multiple arguments are comma-separated. Moreover, you can also limit the size of the result set by adding a size parameter.
Again, these are a few example URLs:
- /api/v1/resultSetForQuery/6/45,SomeValue.json (JSON with interpolated variables)
- /api/v1/resultSetForQuery/6/size/3.json (JSON with number of results limited to 3)
- /api/v1/resultSetForQuery/6/45,SomeValue/size/3.json (JSON with interpolated variables and number of results limited to 3)
- Generate REST APIs from SQL queries.
- Access your data in JSON, XML or CSV formats.
- Supports all major RDBMS (including IBM DB2, Microsoft SQL Server, MySQL, Oracle Database and PostgreSQL).
- Entirely hosted on-premises. Your data stays with you all the time!
- Conveniently edit your SQL queries and preview your data.
- Version control for SQL queries.
- Snapshots (i.e. materialized views if supported by RDBMS).
- Transitive navigation (i.e. navigating entities referenced by foreign keys).
- Variable interpolation.
- Limit and filter query results.
The ZenQuery UI source code is available here.
If you have any questions, suggestions, problems or feature requests please contact us under zenquery-support@bjoernkw.com
Please also visit our website for further information.