Query workbench [Developer Preview]

Query workbench [Developer Preview]

The query workbench provides a rich graphical user interface to prepare and execute simple to complex N1QL queries. It provides a convenient way to perform query development by enabling you to browse, create, and run N1QL statements, and view results.

Important: The Query Workbench works with Couchbase Server versions 4.x. The feature is still considered experimental and the UI or the functionality may change.
The query workbench is the graphical version of the command line tool cbq. It addresses some of the limitations seen when using the command line tool:
  • Provides a better way to view and edit complex queries by supporting features such as multi-line formatting, copy and paste, and easy cursor movement.
  • Enables you to choose to view the results in a more compact and readable format such as a table or a tree, in addition to the default JSON format.
  • Supports a preview of the N1QL describe command, which returns the structure of documents in a bucket which will help write better queries. You no longer have to select the documents at random and then guess the structure of the document.

Supported platforms

The query workbench is supported on Windows, Linux, and Mac OS X platforms.

Installing the query workbench

  1. Access the Downloads page and select the Tools tab under Couchbase Server. Download the query workbench archive file couchbase-query-workbench_dp3-<os> by choosing the appropriate package.

  2. Extract the query workbench archive. The necessary files are contained in an inner folder, couchbase-query-workbench, which may be optionally copied elsewhere.

Running the query workbench

  1. Open a terminal and navigate to the couchbase-query-workbench folder.
  2. The query workbench is configured to connect to your local datastore at 127.0.0.0:8091 using the default credentials. To connect to a different datastore, edit the launch-cbq-gui.sh file located in the extracted folder and update the variable COUCHBASE_URL.
  3. To launch the query workbench, run the following command:
    ./launch-cbq-gui.sh
  4. After the query web server starts successfully, you can now access query workbench UI from http://COUCHBASE_URL:8095. Look for a console message similar to the following:
    Go to /ui to use the query UI.
    Running: ./cbq-gui -datastore=http://127.0.0.1:8091 -localPort=:8095 -user= -pass=
    Launching query web service.
        Using CB Server at: http://127.0.0.1:8091
        Using N1QL query service on: 127.0.0.1:8093
        Using memcached service on: 127.0.0.1:11210
        Using web content at: ./static
    Launching UI server, to use, point browser at http://localhost:8095
    Hit enter to stop server:

Query workbench reference

The query workbench has three main sections: Metadata panel, N1QL query panel, and Results panel, as seen in the following image.
Figure 1. Query workbench

Metadata panel (on the left side of the web page)

The metadata panel lists all the buckets available on the datastore. The buckets are grouped into the following categories:
  • Fully Queryable Buckets, which lists all the buckets that have a primary index or primary and secondary index. These buckets support queries with no where clause, like "select * from `beer-sample`", or with a where clause on any field, like "select * from `beer-sample` where city = `Seattle`".
  • Queryable on Indexed Fields, which lists buckets that only have a secondary index. These buckets support queries with a where clause on the indexed fields.
  • Non-Indexed Buckets, which lists buckets that do not have any indexes. These buckets do not support queries. You must first define an index before querying these buckets.

You can expand the buckets in the metadata panel to view the schema for the JSON documents including the different types of documents and their attributes. This information is inferred by examining a random sample of documents in the bucket. The tool also learns the field names when you expand a bucket and uses this information to support auto-completion in the N1QL query panel.

N1QL query panel (located at the top of the web page)

The N1QL query panel is the query area where you can type a N1QL query. It supports the same N1QL as cbq and provides the following additional features:
  • Auto-completion

    When entering a word in the query panel, if you enter the tab key or Ctrl+Space, the tool offers a list of matching N1QL keywords and bucket names that are close to what you have typed so far. For names that have a space or a hyphen (-), the auto-complete option includes the back-quotes around the name. If you expand a bucket in the Metadata panel, the tool learns and includes the field names from the schema of the expanded bucket.

  • Query history

    The up and down arrows, , at the top of the N1QL query panel enable you to walk through the history of queries executed since the web page was loaded. If you edit a previous query and then execute it, the new query is stored at the end of the history.

  • Result limit

    You can limit the size of the result by specifying the value for this field. This ensures that the result set is not larger than the specified value. Note that large result sets can slow the responsiveness of the interface.

  • Support for N1QL describe queries
    The query workbench supports a preview of the N1QL describe command. This command infers a "schema" for a bucket by examining a random sample of documents. Because this command is based on a random sample, the results may vary slightly from run to run. The default sample size is 1000 documents. The syntax for the command is:
    describe <bucket_name> [ limit <sample size> ]

Results panel (located at the bottom of the web page)

The results panel displays the results of a query or the error message in case the query failed in your chosen format. You can choose to view the results in one of the following formats:
  • Raw JSON (default), where the results are formatted to make the data more readable. You can also expand and collapse objects and array values using the little arrow icons next to the line numbers.

  • Table, where the data is presented in a tabular format. It converts the JSON arrays to HTML tables, and presents sub-objects or sub-arrays as sub-tables. This format works well for queries that return an array of objects, like "select `beer-sample`.* from `beer-sample`". It is not suitable for queries that return an array of objects where each object has a sub-object, like "select * from `beer-sample`".

  • Tree (or list), where the JSON data is presented in an outline form and each sub-array or sub-object is displayed as a sub-list.

  • The Save button saves the raw JSON text to a file.
    Note: When using Safari, clicking Save loads the data into a new window. You have to save the file manually using the File > Save As menu.