Yesterday, the query team here at Couchbase released the long-awaited N1QL Developer Preview 4! If you haven’t heard of N1QL, it’s an all new SQL-like query language that Couchbase is developing for querying non-relational data-stores like Couchbase. The goal is to bring the familiarity of SQL to the flexible, schema-less world of JSON. Today we are releasing a “preview” of the support for N1QL DP4 provided by the Couchbase .NET SDK 2.0.

What’s that means is that we’re providing a pre-release, untested, don’t run in production version of the SDK that includes much of functionality provided by the N1QL DP4! Let me say that one more time: DO NOT USE IN PRODUCTION!!!! Now that the fun-police have had their peace and ruined the party, let’s get down to what is in N1QL DP4 and what is currently supported by this preview release of the SDK!

What is in N1QL Developer Preview 4?

N1QL DP4 builds upon the great work delivered in DP’s 1-3, delivering the full and complete N1QL syntax and language of elements. Additionally, it provides sub-queries, experimental support for Data Manipulation Language (DML) constructs like INSERT, UPDATE, UPSERT, and DELETE, and importantly for the SDK, a brand-new REST API!

The REST API has gotten a major face-lift and is much closer to being ready for prime time.  A partial list of features that the new REST API supports includes:

  • Prepared statement support (sort of like query-plan caching)
  • Positional and Named parameters
  • An improved and more robust error API
  • Query metrics
  • Query timeouts

Finally, the documentation has been revamped and updated and can be found here.

Installing DP4

Unfortunately while N1QL DP4 is nearly completely integrated with Couchbase Server, it isn’t yet packaged with it. What the means is that to use DP4, you will have to download the bits and start the service and associate it with a Couchbase Server instance. Fortunately doing this is relatively easy:

  • First, download N1QL DP4 here.
  • Then, unzip it to a directory on your hard drive
  • Finally, run the following command to start the service and associate it with a Couchbase Server instance: .cbq-engine -datastore=http://[server_name]:8091/

Once you have done this (of course change [server name] to be an IP to your Couchbase instance, you’ll need to create an index for each bucket you wish to use. To do this, you can run the interactive query command line tool and create the indexes using it, or you can download the Couchbase .NET 2.0 SDK with N1QL DP4 “preview”. I’ll show first how to do with the interactive shell and later show how to do it programmatically with the SDK.

  • Navigate to the directory where you unzipped N1QL DP4 and type the following, changing the with your Couchbase server instance: .cbq -engine=http://:8093/
  • The type the following: CREATE PRIMARY INDEX ON [bucket name]
  • Hit enter (note to change [bucket name] to a valid bucket name on your Couchbase server instance.

I will be using the “beer sample” sample bucket that comes pre-installed with Couchbase later, so you should probably do the same if you wish to follow along. Once you have downloaded and installed N1QL DP4, we can use the Couchbase .NET SDK 2.0 to create indexes and run queries.

Using the SDK with N1QL DP4

In order to use the SDK you will need Visual Studio installed along with .NET Framework 4.5 or greater. If you using Mono, I haven’t tested it, but you should be able to follow along as well assuming you’re using a version of Mono that is functionally equivalent to the .NET Framework 4.5. Specifically support for async/await, the dynamic keyword and some the new .NET libraries.

Downloading and referencing the bits

To get up and running quickly with the SDK, follow these steps:

  • The special preview release of the SDK can be found here. Download and unzip it somewhere on your hard drive.
  • Open VS and create a new “Console Application” project, call it N1QL-DP4-Preview or whatever suits you.
  • Right click on the references folder in the “Solution Explorer” on the right and then “Add Reference”
  • Use the “browse” button to find the dll’s you downloaded and unzipped in the first step and add references to the following: Couchbase.NetClient.dll, Common.Logging.dll and Newtonsoft.Json.dll.

Once you have gotten this far, you can start writing code. Note that I will be assuming that your Couchbase server instance is running on localhost. If not you’ll have to configure your client to point to your remote installation.

A New QueryRequest API

The SDK support for N1QL Developer Preview 3 (DP3) was rather minimal: only ad-hoc string literal queries were supported. With the new REST API, a new QueryClient class was created and integrated with the CouchbaseBucket class which provides a wrapper API for most of the new REST API features – I say “most” because some features have not yet been completed.

This new API includes a class for constructing queries using a fluent-like interface. This is very similar to the ViewQuery class/API the client also supports that was released as part of SDK GA for querying Views. It has the following signature:

You can chain together the various REST API parameters constructing a query request using a N1QL statement. The N1QL statement itself is still a literal string, but you can provide options that allow you to control how the query service handles the query. I won’t go into each of these parameters in this post; I’ll leave that for another blog post.

Note that the QueryRequest has well, really nothing to do with the N1QL language? That is intentional. There is another project in the works that deals with creating the N1QL querying: a Language Integrated Query (LINQ) provider for N1QL, which is some ways off developmentally.

What this object does it make it easy to write a query request without having to create it manually using the IDE’s intellisense and the dot “.” Operator. For example:

Creating Indexes

In the following example, I will show how to use the QueryRequest above and the Couchbase .NET SDK 2.0 N1QL DP4 Preview to create and remove indexes on the fly:

Here we are first querying the system “catalog” if you will to determine, which bucket have indexes. A bucket with an index will be returned in this list. If the index exists, we are then dropping it (so we can conveniently recreate it). Finally, we are recreating the index. Note that you normally wouldn’t do this in production, but we are here to illustrate the functionality provided by N1QL and the SDK.

Using Named Parameters and Positional Parameters

N1QL DP4 introduces a feature for creating parametrized queries. There exists functionality for named parameters, which are like name/value pairs, and also for positional parameters where position of the parameter matches its ordinal.
Here is an example of a query using positional parameters:

        static void PositionalParameters(IBucket bucket)
        {
            var queryRequest = new QueryRequest()
                .Statement(“SELECT * FROM beer-sample WHERE type=$1 LIMIT $2″)
                .AddPositionalParameter(“beer”)
                .AddPositionalParameter(10);

            var result = bucket.Query(queryRequest);
            foreach (var row in result.Rows)
            {
                Console.WriteLine(row);
            }
        }

Note that at this time positional parameters are not zero-indexed! So if you were to replace $1 and $2 with $0 and $1, you would receive an error message.
Named parameters once again are like name/value pairs and unlike positional parameters, order is not important. Here is the same query above using named parameters:

        static void NamedParameters(IBucket bucket)
        {
            var queryRequest = new QueryRequest()
                .Statement(“SELECT * FROM beer-sample WHERE type=$type LIMIT $limit”)
                .AddNamedParameter(“limit”, 10)
                .AddNamedParameter(“type”, “beer”);

            var result = bucket.Query(queryRequest);
            foreach (var row in result.Rows)
            {
                Console.WriteLine(row);
            }
        }

Note that this is the same statement as before, but I reordered the parameters when I created the QueryRequest to show that order is not important.

Prepared Statements

Prepared statements are somewhat like cached query plans in relational databases. What they do is allow you to only incur the cost of parsing the query one time and then then prepared statement can be used over and over again. While this is included in N1QL DP4, the SDK support for this feature is incomplete (it simply doesn’t work yet). Expect this to be working in a later release when the preview code has been merged with the master branch of the SDK.

In Conclusion

So, that’s it for the Couchbase .NET SDK 2.0 N1QL DP4 preview! There is much, much more to go over and I will be posting more blogs as development gets further along and we get closer to N1QL GA!

If you have any feedback or run into issues or bugs, please leave a comment or post a bug in Jira here. Also, the code that was included in this blog can be found on github here.

Posted by Jeff Morris, Software Engineer, Couchbase

4 Comments

  1. This is McDonalds! (I\’m lovin\’ it!)

  2. Any news – when the commercial release of N1QL will happen?

  3. Ersoy Vatansever April 1, 2015 at 10:08 am

    if i cant use in production why are you writing these ????
    When release commercial version ?

    1. Matt Ingenthron April 1, 2015 at 4:53 pm

      Couchbase releases DPs (Developer Previews) to get feedback from those who are interested in helping guide the development of the features before we lock down the interface, and get to production. That\’s the idea.

      With respect to the release, this was talked about quite a bit last week– there\’s a DP of Couchbase Server 4.0 coming soon, followed by beta and GA. Check this out for one: http://www.couchbase.com/press

Leave a reply