Skip to main content

Posts tagged with 'SQL'

This is a repost that originally appeared on the Couchbase Blog: Moving from SQL Server to Couchbase Part 3: App Migration.

In this series of blog posts, I’m going to lay out the considerations when moving to a document database when you have a relational background. Specifically, Microsoft SQL Server as compared to Couchbase Server.

In three parts, I’m going to cover:

The goal is to lay down some general guidelines that you can apply to your application planning and design.

If you would like to follow along, I’ve created an application that demonstrates Couchbase and SQL Server side-by-side. Get the source code from GitHub, and make sure to download a developer preview of Couchbase Server.

Migrate vs Rewrite

If you’re building a new web app, then Couchbase Server is a good choice to use as your system of record. Flexible data modeling, fast data access, ease of scaling all make it a good choice.

Couchbase Server can supplement SQL Server in your existing web application. It can be a session store or a cache store. You don’t have to replace your RDMBS to benefit from Couchbase Server. You can use it as your system of engagment.

However, if you’re considering making a document database your "system of record" for an existing app, then you need to plan what to do about that application (assuming you’ve already come up with a data modeling and data migration plan as covered in the earlier parts of this blog series). There are really two options:

  • Replace your data/service layer. If you’ve built your app in a way that decouples it from the underlying persistence, that’s going to benefit you tremendously when switching from SQL Server to Couchbase. If you are using an SOA, for instance, then you might not have to make very many changes to the web application.

  • Rebuild your application. If you don’t have a decoupled architecture, then you’ll likely have to bite the bullet and rewrite/refactor large portions of your application. This can be a significant cost that you’ll have to factor in when deciding whether or not to switch to a document database. I wish I could say it would be easier, that there was some magic potion you could use. But remember, even if the cost of a rebuild is too great, you can still use Couchbase Server in tandem with SQL Server.

The pieces of your stack that you might need to rebuild or replace include:

  • ADO.NET - If you are using plain ADO.NET or a micro-OR/M like Dapper, these can be replaced by the Couchbase .NET SDK.

  • OR/M - Entity framework, NHibernate, Linq2SQL, etc. These can be replaced by Linq2Couchbase

  • Any code that uses those directly - Any code that touches ADO.NET, OR/Ms, or other SQL Server code will need to be replaced to use Couchbase (and/or rewritten to introduce another layer of abstraction).

The rest of this blog post will be tips and guidelines that apply for rewriting, refactoring, or starting a new project.

What’s going to be covered

Document databases force business logic out of the database to a larger extent than relational databases. As nice as it would be if Couchbase Server had every feature under the sun, there are always tradeoffs.

In this blog post, we will cover the changes to application coding that come with using Couchbase. At a high level, here is what will be covered in this blog post. On the left, a SQL Server feature; on the right, the closest equivalent when using Couchbase Server.

SQL ServerCouchbase Server

tSQL

N1QL

Stored Procedures

Service tier

Triggers

Service tier

Views

Map/Reduce Views

Autonumber

Counter

OR/M (Object/relational mapper)

ODM (Object data model)

ACID

Single-document ACID

In addition, we’ll also be covering these important topics:

  • Serialization

  • Security

  • Concurrency

  • SSIS, SSRS, SSAS

Using N1QL

The N1QL (pronounced "nickel") query language is one of my favorite features of Couchbase Server. You are already comfortable with the SQL query language. With N1QL, you can apply your expertise to a document database.

Here are a few examples to show the similarities between N1QL and tSQL:

tSQLN1QL

DELETE FROM [table] WHERE val1 = 'foo'

DELETE FROM `bucket` WHERE val1 = 'foo'

SELECT * FROM [table]

SELECT * from `bucket`

SELECT t1.* , t2.* FROM [table1] t1 JOIN [table2] t2 ON t1.id = t2.id

SELECT b1.* , b2.* FROM `bucket` b1 JOIN `bucket` b2 ON KEYS b1.mykeys

INSERT INTO [table] (key, col1, col2) VALUES (1, 'val1','val2')

INSERT INTO `bucket` (KEY, VALUE) VALUES ('1', {"col1":"val1", "col2":"val2"})

UPDATE [table] SET val1 = 'newvalue' WHERE val1 = 'foo'

UPDATE `bucket` SET val1 ='newvalue' WHERE val1 = 'foo'

Thanks to N1QL, migrating your SQL queries should be less difficult than other document databases. Your data model will be different, and not every function in tSQL is (yet) available in N1QL. But for the most part, your existing tSQL-writing expertise can be applied.

Back to the shopping cart, here’s an example of a simple tSQL query that would get shopping cart information for a given user:

SELECT c.Id, c.DateCreated, c.[User], i.Price, i.Quantity
FROM ShoppingCart c
INNER JOIN ShoppingCartItems i ON i.ShoppingCartID = c.Id
WHERE c.[User] = 'mschuster'

In Couchbase, a shopping cart could be modeled as a single document, so a roughly equivalent query would be:

SELECT META(c).id, c.dateCreated, c.items, c.`user`
FROM `sqltocb` c
WHERE c.type = 'ShoppingCart'
AND c.`user` = 'mschuster';

Notice that while N1QL has JOIN functionality, no JOIN is necessary in this shopping cart query. All the shopping cart data is in a single document, instead of being spread out amongst multiple tables and rows.

The results aren’t exactly the same: the N1QL query returns a more hierarchical result. But the query could be modified with an UNNEST to flatten out the results if necessary. UNNEST is an intra-document join, which is a feature that’s necessary when writing SQL for JSON.

In many document databases other than Couchbase, you would likely have to learn an API for creating queries, and you would not be able to apply your tSQL experience to help ramp up. I’m not saying that translation is always going to be a walk in the park, but it’s going to be relatively easy compared to the alternatives. If you’re starting a new project, then you’ll be happy to know that your SQL-writing skills will continue to be put to good use!

When writing C# to interact with N1QL, there are a couple key concepts that are important to know.

Scan Consistency. When executing a N1QL query, there are several scan consistency options. Scan consistency defines how your N1QL query should behave towards indexing. The default behavior is "Not Bounded", and it provides the best performance. At the other end of the spectrum is "RequestPlus", and it provides the best consistency. There is also "AtPlus", which is a good middle-ground, but takes a little more work. I blogged about Scan Consistency back in June, and it’s worth reviewing before you start writing N1QL in .NET.

Parameterization. If you are creating N1QL queries, it’s important to use parameterization to avoid SQL injection. There are two options with N1QL: positional (numbered) parameters and named parameters.

Here’s an example of both Scan Consistency and Parameterization used in C#:

var query = QueryRequest.Create(n1ql);
query.ScanConsistency(ScanConsistency.RequestPlus);
query.AddNamedParameter("userId", id);
var result = _bucket.Query<Update>(query);

I’m not going to dive into the N1QL query language any more than this, because it is such a deep topic. But you can check out the basics of N1QL and get started with the interactive N1QL tutorial.

SQL Stored Procedures

There is no equivalent of stored procedures (sprocs) in Couchbase. If you don’t already have a service tier, and you are using sprocs to share some logic across domains, I recommend that you create a service tier and move the logic there.

In fact, I wasn’t sure whether sprocs belonged in part 2 or part 3 of this blog series. Typical tiers in an enterprise application:

  • Web tier (UI - Angular/React/Traditional ASP.NET MVC)

  • Service tier (ASP.NET WebApi)

  • Database

Sprocs live in the database, but they contain logic. The service tier also contains business logic. So do they count as data or functionality?

I took a Twitter straw poll to decide.

Twitter straw poll on Stored Procedures

But my recommendation is that if you already have a service tier, move the sproc logic into that. If you don’t have a service tier, create one. This will live between the database and the UI.

In the source code for this series, I’ve created a single stored procedure.

CREATE PROCEDURE SP_SEARCH_SHOPPING_CART_BY_NAME
	@searchString NVARCHAR(50)
AS
BEGIN
	SELECT c.Id, c.[User], c.DateCreated
	FROM ShoppingCart c
	WHERE c.[User] LIKE '%' + @searchString + '%'
END
GO

This sproc can be executed from Entity Framework as follows:

public List<ShoppingCart> SearchForCartsByUserName(string searchString)
{
    var cmd = _context.Database.Connection.CreateCommand();
    cmd.CommandText = "SP_SEARCH_SHOPPING_CART_BY_NAME @searchString";
    cmd.Parameters.Add(new SqlParameter("@searchString", searchString));
    _context.Database.Connection.Open();
    var reader = cmd.ExecuteReader();

    var carts = ((IObjectContextAdapter) _context)
        .ObjectContext
        .Translate<ShoppingCart>(reader, "ShoppingCarts", MergeOption.AppendOnly);

    var result = carts.ToList();
    _context.Database.Connection.Close();
    return result;
}

By the way, that Entity Framework sproc code is ugly. Maybe I did it wrong? I’m not trying to slander EF, but generally, I haven’t used OR/Ms and sprocs together much in my career. Certainly a piece of ADO.NET or Dapper code would be shorter and cleaner.

This is a very simple sproc, but it introduces a basic search functionality. The benefits to such a sproc:

  • Reuse: The same sproc can be reused between different applications

  • Abstraction: The sproc implementation can be changed or improved. In this case, a basic LIKE could be switched out for a more robust full text search.

Any approach taken with introducing a service tier should provide the same benefits. I created an ASP.NET WebApi endpoint to take the place of the sproc.

[HttpGet]
[Route("api/searchByName/{searchString}")]
public IHttpActionResult SearchByName(string searchString)
{
    var n1ql = @"SELECT META(c).id, c.`user`
        FROM `sqltocb` c
        WHERE c.type = 'ShoppingCart'
        AND c.`user` LIKE $searchString";
    var query = QueryRequest.Create(n1ql);
    query.AddNamedParameter("searchString", "%" + searchString + "%");
    query.ScanConsistency(ScanConsistency.RequestPlus);
    var results = _bucket.Query<ShoppingCart>(query).Rows;

    return Json(results);
}

Note: for the sake of simplicity in the sample code, this endpoint actually lives in the same web project, but in production, it should be moved to its own project and deployed separately.

This endpoint holds a N1QL query that is similar in nature to the above sproc. Let’s see if it holds up to the same benefits:

  • Reuse? Yes. This endpoint can be deployed to its own server and be reused from other applications.

  • Abstraction? Again, yes. The implementation uses the naive LIKE approach, which we could improve by switching it to use Couchbase’s Full Text Search features without changing the API.

Instead of calling a sproc through Entity Framework, this endpoint would be called via HTTP. Here’s an example that uses the RestSharp library:

public List<ShoppingCart> SearchForCartsByUserName(string searchString)
{
    // typically there would be authentication/authorization with a REST call like this
    var client = new RestClient(_baseUrl);
    var request = new RestRequest("/api/searchByName/" + searchString);
    request.AddHeader("Accept", "application/json");
    var response = client.Execute<List<ShoppingCart>>(request);
    return response.Data;
}

If you are building a new project, I recommend that you create a service tier with the expectation of it being used across your enterprise. This allows you to have the same "shared code" that sprocs would normally provide without putting that code into the database.

This is also true for SQL Server functions, user defined types, rules, user-defined CLR objects.

Note: the above sproc example is a SELECT just to keep the example simple. In this case, you could potentially create a MapReduce View instead (which is discussed below). A MapReduce view cannot mutate documents though, so a service tier approach is a better general solution to replacing sprocs.

SQL Triggers

If sprocs weren’t already controversial enough, just bring up triggers in a conversation. As with stored procedures, I generally recommend that you move the trigger logic into the service tier, away from the database. If your software project depends on a lot of triggers, or complex triggers, or a lot of complex triggers, then you might want to wait for another project to try using Couchbase Server in.

That being said, there is some cutting-edge stuff that is being worked on that might be roughly equivalent to triggers. If you are interested in this, please contact me, and also stay tuned to the Couchbase Blog for the latest information.

Views

In SQL Server, Views are a way to store complex queries on the server, as well as provide some performance benefits. In Couchbase, Map/reduce views have been providing similar functionality for some time. For the most part, the functionality provided by views can be provided in a more expressive way with N1QL. However, views are not going away, and there are sometimes benefits to using them.

Map/reduce views can be defined and stored on the Couchbase cluster. To create them, you define a "map" function (with JavaScript) and optionally a "reduce" function (also in JavaScript).

In the Couchbase Console UI, go to Indexes → Views → Create View. Create a design document, and create a view within that design document.

Editing a Map/Reduce view in Couchbase
Figure 1. Screenshot of the Map/Reduce view editor in the latest Couchbase Console

At the center is the Map/Reduce code that you are working on. A sample document and its meta-data is also shown to give you some visual help, and at the bottom you have some options for executing your view.

For complete details on how views work, check out the MapReduce Views documentation.

As a quick example, I want to create a view that lists only the people who have an age greater than 21.

function (doc, meta) {
  if(doc.age > 21) {
  	emit(meta.id, doc.name);
  }
}

This view would emit the key of the document and the value of the "name" field. If my bucket contained the following documents:

foo1	{"age":17,"name":"Carmella Albert"}
foo2	{"age":25,"name":"Lara Salinas"}
foo3	{"age":35,"name":"Teresa Johns"}

Then the results of the view would look like:

KeyValue

"foo2"

"Lara Salinas"

"foo3"

"Teresa Johns"

The results of these views are updated automatically on an interval, and are also updated incrementally as documents are mutated. This means that, by default, the results of the views are eventually consistent with the actual documents. As a developer, you can specify the level of consistency (or staleness) you want. This will have an impact on performance.

Map/reduce views can be very helpful when you have very complex logic that’s easier to write in JavaScript than it is to write in N1QL. There can also be performance benefits when you are working with a write-heavy system.

Views can be accessed from .NET using ViewQuery.

var query = new ViewQuery().From("viewdesigndocument", "viewname").Limit(10);
var people = bucket.Query<dynamic>(query);
foreach (var person in people.Rows)
    Console.WriteLine(landmark.Key);

Alternatively, you could create N1QL queries instead of using Views. In many cases, N1QL will be easier to write, and the performance difference will be negligible. Unlike Views, the N1QL queries would live in the service tier. There is currently no way to store a "N1QL View" on the Couchbase Server cluster.

Serialization/deserialization

Whether you’re using N1QL, Views, or key/value operations, it’s important to consider how JSON is serialized and deserialized.

The .NET SDK uses Newtonson JSON.NET. If you are familiar with that tool (and who among .NET developers isn’t), then remember that you can use the same attributes (like JsonProperty, JsonConverter, etc). In some edge cases, it might be useful to create your own custom serializer, which is possible with the Couchbase .NET SDK. Check out the documentation on serialization and non-JSON documents for more information.

Security

Couchbase has role-based access control (RBAC) for administrators.

Couchbase can integrate with LDAP to manage Couchbase administrators and assign roles to users. Couchbase can also create read-only users internally.

There are some more robust changes and improvements coming to the Couchbase RBAC system, so stay tuned. In fact, I would recommend that you start checking out the monthly developer builds, as I expect to see some interesting improvements and features in this area soon!

Concurrency

Concurrency is something that you often have to deal with, especially in a web application. Multiple users could be taking actions that result in the same document being changed at the same time.

SQL Server uses pessimistic locking by default. This means that SQL Server expects rows to be in contention, and so it acts defensively. This is a sensible default for relational databases because denormalized data is spread across multiple tables and multiple rows. SQL Server does have the ability to use optimistic locking as well, through a variety of transaction levels.

Couchbase also offers two options to deal with concurrency: optimistic and pessimistic.

Optimisitic. This is called "optimistic" because it works best when it’s unlikely that a document will be in contention very often. You are making an optimistic assumption. On Couchbase, this is done with CAS (Compare And Swap). When you retrieve a document, it comes with meta data, including a CAS value (which is just a number). When you go to update that document, you can supply the CAS value. If the values match, then your optimism paid off, and the changes are saved. If they don’t match, then the operation fails, and you’ll have to handle it (a merge, an error message, etc). If you don’t supply a CAS value, then the changes will be saved no matter what.

Pessimistic. This is called "pessimistic" because it works best when you know a document is going to be mutated a lot. You are making a pessimistic assumption, and are forcibly locking the document. If you use GetAndLock in the .NET SDK, the document will be locked, which means it can’t be modified. Documents are locked for a maximum of 15 seconds. You can set a lower value. You can also explicitly unlock a document, but you must keep track of the CAS value to do so.

For more detail, check out the documentation on Concurrent Document Mutations.

Autonumber

Couchbase Server does not currently offer any sort of automatic key generation or sequential key numbering.

However, you can use the Counter feature to do something similar. The idea is that a document is set aside as a special counter document. This document can be incremented as an atomic operation, and the number can be used as a partial or whole key of the new document being created.

Ratnopam Chakrabarti, a developer for Ericsson, recently wrote a guest blog post about how to create sequentially numbered keys with Couchbase Server. His example is in Java, but it easy enough to follow, so I won’t repeat his example here.

OR/Ms and ODMs

If you are using SQL Server, you might be familiar with OR/Ms (Object-relational mappers). Entity Framework, NHibernate, Linq2SQL, and many others are OR/Ms. OR/Ms attempt to bridge the gap between structured data in C# and normalized data in relational databases. They also typically provide other capabilities like Linq providers, unit of work, etc. I believe that OR/Ms follow the 80/20 rule. They can be very helpful 80% of the time, and a pain in the neck the other 20%.

For document databases, there is a much lower impedence mismatch, since C# objects can be serialized/deserialized to JSON, and don’t have to be broken up into a normalized set of tables.

However, the other functionality that OR/Ms provide can still be helpful in document databases. The equivalent tool is called an ODM (Object Document Model). These tools help you define a set of classes to map to documents. Ottoman and Linq2Couchbase are popular ODMs for Couchbase, for Node and .NET respectively.

Linq2Couchbase has a Linq provider. It’s not an officially supported project (yet), but it is one of the most complete Linq providers I’ve ever used, and is used in production by Couchbase customers.

Below is an example from the Linq2Couchbase documentation that should look somewhat familiar for users of Entity Framework and NHibernate.Linq:

var context = new BucketContext(ClusterHelper.GetBucket("travel-sample"));
var query = (from a in context.Query<AirLine>()
             where a.Country == "United Kingdom"
             select a).
             Take(10);

I also used Linq2Couchbase in the sample code for this blog series. Here’s an example for Shopping Carts:

var query = from c in _context.Query<ShoppingCart>()
    where c.Type == "ShoppingCart"  // could use DocumentFilter attribute instead of this Where
    orderby c.DateCreated descending
    select new {Cart = c, Id = N1QlFunctions.Meta(c).Id};
var results = query.ScanConsistency(ScanConsistency.RequestPlus)
    .Take(10)
    .ToList();

Beyond being a great Linq provider, Linq2Couchbase also has an experimental change tracking feature. It’s definitely worth checking out. Brant Burnett is one of the key contributers to the project, and he’s also a Couchbase Expert. He presented a session at Couchbase Connect 2016 called LINQing to data: Easing the transition from SQL.

Transactions

I’ve already covered pessimistic and optimistic locking for transactions on a single document. Because of those, we can say that Couchbase supports ACID transactions on a per-document level. Couchbase does not, at this time, support ACID transactions among multiple documents.

Thinking back to the first blog post on data modeling, the need for multi-document transactions is often reduced or eliminated, compared to a relational model. A concept (like shopping cart) may require rows in multiple tables in a relational model, but a single document model in Couchbase.

If you are following a referential model, as in the social media example from the first blog post, you might be concerned about the lack of transactions. This highlights the importance of thinking about your use cases while creating your data model. If transactions are vital to your use case, the data model can often be structured to accomodate. We are happy to help you through this, just ask!

Multi-document transaction support may come in the future if enough Couchbase developers and customers ask for it or need it. So, if you go through the exercise of designing a document database data model, and transactions are still a vital part of your project, then Couchbase may not be the best "system of record" for at least part of your project. Couchbase may still be the best "system of engagement", able to help with scaling, caching, performance, and flexibility where needed.

As a side note, it may be worth checking out the NDescribe project, as it includes an SDK that works on top of the Couchbase SDK and provides a transaction system. (Note that this is not an officially supported tool).

SSIS, SSAS, SSRS

Not everyone uses SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS), but these are powerful features that SQL Server has for integration, reporting, and analysis.

I can’t give you a blanket "use X instead of Y" for these, because it depends very much on your use case. I can point you in the direction of some of the tools available for Couchbase that revolve around data processing, data transformation, reporting, and analysis.

  • Kafka is an open source data streaming tool. Some of the popular use cases for Kafka include messaging, website activity tracking, metrics, and more.

  • Spark is a data procesessing engine, intended for large-scale data processing and ETL.

  • Hadoop is a big data framework for distributed storage and processing.

Couchbase has connectors that support each of these three popular tools.

Finally, Couchbase Analytics is currently in developer preview. It is intended as a data management engine that runs parallel to Couchbase Server. It’s a developer preview, and is not yet recommended to be used in production, but you can download Couchbase Analytics and Kafka, Spark, Hadoop extensions (click the Extensions tab) and try them out.

Summary

We’ve covered data modeling, data migration, and application migration through the lens of SQL Server. This is a good starting point for your next project, and will give you something to think about if you are considering migrating.

The Couchbase Developer Portal contains more details and information about every aspect of Couchbase Server.

I want to hear from you about what Couchbase can do to make your transition easier, whether you’re migrating or starting fresh. Did I miss something? Do you have a tool or system that you recommend? Have questions? Check out the Couchbase Forums, email me at [email protected] or find me on Twitter @mgroves.

Jeremy Miller is working on a PostgreSQL-backed document database.

This episode was recorded at CodeMash 2017 in a massive dining room, so the audio is a bit different than normal.

Show Notes:

Jeremy Miller is on Twitter

Want to be on the next episode? You can! All you need is the willingness to talk about something technical.

Theme music is "Crosscutting Concerns" by The Dirty Truckers, check out their music on Amazon or iTunes.

This is a repost that originally appeared on the Couchbase Blog: A tour of the new Couchbase Web Console (video).

Earlier this month, we introduced Developer Builds. In this video, I take you on a quick tour of the new Couchbase Web Console.

If you have questions or feedback, please contact me at [email protected], or on @mgroves at Twitter, or just leave a comment below.

This is a repost that originally appeared on the Couchbase Blog: A tour of the new Couchbase Web Console.

Change is coming to the Couchbase Web Console that you know and love. But don’t worry, the old UI will still be available (for a while).

In this blog post, I’m going to take you on a tour of the new UI. I’ll be pointing out some of the differences and similarities. If you have anxiety about change, don’t worry: the purpose of this change is to improve usability, performance, and efficiency. The new UI gives Couchbase Web Console room to grow with new features that are coming down the road.

An important note!

This blog and the screenshots in it were taken from an early developer build. There may be other changes and improvements that show up by the time the release is in your hands, so the final product might be different. Feel free to point out any differences in the comments below.

Installation and Setup

The installation and setup process has not changed much, but it does get a fresh coat of paint. The installer itself appears the same (although the setup wizard is likely to be revised in future builds). Once the installer is complete, you’ll get redirected to a browser (if you’re installing Couchbase Server locally, this means a browser pointed to http://localhost:8091 will appear). Functionally, this part of the experience is the same as before, but it gets a new coat of paint.

Couchbase Server 5.0 Setup Screen
Figure 1. The setup screen forsCouchbase Server 5.0

After you click the "Setup" button, you’ll be taken through the installation wizard. Depending on which version you have been using, there may be some new options here that you haven’t seen. I’m not going to cover them in this post, but stay tuned for more blog posts about upcoming features in the latest releases.

Starting a new Couchbase cluster
Figure 2. Starting a new cluster in Couchbase Server setup

The sample buckets are still available. Note that these buckets (as always) are not password protected, and are meant for development only. Please check out the recent Advisory Note about Couchbase security.

Loading an optional sample bucket into Couchbase Server
Figure 3. Loading a sample bucket (optional) during Couchbase Server setup

You can also choose to create a bucket named "default" during setup. Again, this is not recommended for production (even if you put a password on it), since it’s a well-known bucket name that makes it a target. It’s also optional, you can click "Skip".

Optionally create a default bucket in Couchbase Server setup
Figure 4. Create a default bucket (optional) during Couchbase Server setup

Notification and Terms and Conditions are next. We are offering the full Enterprise edition in the developer build, so you can check out the Enterprise features.

Accept terms and conditions and sign up for notifications
Figure 5. Accept terms and conditions of the Enterprise Edition and sign up for notifications

Finally, create an admin user. Once again, I recommend not using "Administrator" as the user name in production, as it is a well-known default. Also, make sure to use a strong password.

Create administrator user in Couchbase Server setup
Figure 6. Create an administrator user for Couchbase Server

Using the new Couchbase Web Console

Once you’ve gone through the familiar install process, it’s time to see some of the substantive changes to the UI.

The first thing you’ll see is the dashboard screen, which has been streamlined.

New Couchbase Server Console UI
Figure 7. The new look of the Couchbase Server Web Console

Some things that jump out at me immediately:

The design. The new Couchbase Server UI is heavily influenced by Google’s Material Design. You will see more evidence of this as you navigate around the site. Material Design is meant to provide a "design language" that is geared toward "digital material" that can expand/change intelligently.

Main Navigation. The navigation is now vertical and on the left side of the page. Also notice that is is organized by "cluster" and "data".

Couchbase Console Main UI navigation
Figure 8. Main navigation is now vertical

Servers

Click on the "Servers" link to see the new look of the servers page.

Couchbase Servers list of nodes
Figure 9. The Servers page shows a list of nodes

This should look relatively familiar. Each node is listed here, with a list of services running on each node and system metrics. I only have one node in my example, but each node would be listed here in a production deployment. Notice that there are filters "Active Server" and "Pending Rebalance" that have a rounded highlight. Next to them are some buttons to manage servers and server groups which use rectangle shapes. These shapes help to provide visual cues no matter what the size of your screen is.

Security

Continuing the tour, click on the "Security" navigation link.

Couchbase Server Security
Figure 10. Couchbase Server Security UI

Not much different on this screen compared to the old UI, but I do want to draw your attention to the sub-navagation at the top: "Internal User/Roles", "Root Certificate", and "Audit". The secondary navigation across the site will be across the top of the page like this.

There are some new features coming soon for role-based security. I would expect the UI here to evolve in future versions to accomodate.

Query Workbench

As a developer, this is always my favorite part of the Couchbase Console, and the one I spend most of my time in. Besides the obvious design change, there are some minor improvements and changes here that I’d like to call attention to.

Couchbase Server Query Workbench
Figure 11. New design of the Query Workbench

No more Clear button. I use the clear button a lot, but my usage is probably atypical from a typical Couchbase user. The clear button is a frustrating button to hit accidentally while working on queries, so it’s been removed.

History. Click on "history" to see a list of all the queries that you’ve run. It’s searchable! Which can be very useful after a long day of query writing and data modeling. Remember that "clear" button? It’s here in the history popup; you can still access it, but you’re less likely to click it by accident.

Couchbase Query Workbench history
Figure 12. Query Workbench history with search

Data Bucket Insights. These have been moved to the right side of the screen. Personally, I think this is a better use of space. (This is an Enterprise Edition feature).

Plan and Plan Text options in results. By default, the queries you run will be EXPLAINed, and you can view the output of that in the Plan and Plan Text results. To turn that feature off, click the settings icon next to "Query Editor".

Couchbase Query Workbench Plan and Plan Text
Figure 13. Plan and Plan Text results tabs

Query Monitor. Notice the "Query Monitor" secondary navigation link at the top. This page shows you information about some of the system catalogs that were added in Couchbase Server 4.5.

Buckets

As a developer, I spend a lot of time in the Buckets section of the UI. The new version of this page isn’t wildly different, but I have found it a little snappier.

Couchbase Server Bucket UI
Figure 14. New Bucket management UI

Click on a row to expand information about the bucket (as well as to expose the Delete/Compact/Edit buttons).

Statistics and Charts

One significant area of change is the way that charts and statsitics are shown in the UI. Clicking a bucket name will still bring you to the analytics and information that you’re used to.

New Couchbase chart for N1QL requests
Figure 15. Chart that shows the number of N1QL requests

There aren’t any new charts that I’m aware of, but there have been changes made to improve usabilty of the charts as well as get them to adhere closer to the Material Design philosophy. They also take up the full space of the page, to maximize your view.

New Couchbase chart for HTTP requests
Figure 16. Chart that shows the number of HTTP requests

It’s difficult to demonstrate these changes in a blog post with static images (I may make a video walkthrough in the future), so I definitely recommend that you install the latest version of Couchbase Server 5.x and give them a try.

Why did you move my cheese!

Hopefully, these UI changes delight you and make your job easier. However, abrupt change can be difficult. This is why there is still an option to view the "Classic UI". Just click this link (at the top right of the page) and you’ll be albe to view the old UI. I don’t know how long the old UI is going to stick around.

Click Classic UI to go back to the old UI
Figure 17. Use this link to show the old "Classic UI"

To switch back to the new UI from the old UI, just click "New UI".

Click New UI to back to the new UI
Figure 18. Use this link to go back to the "New UI"

It’s likely that new features will only appear in the new UI.

Feedback and Summary

Stay tuned to the Couchbase Blog for information about what’s coming in the new UI in the next developer build.

I’ve given you the basic tour, but to really get a feel for the UI, I recommend you download and try it. Download Couchbase Server 5.0 today!

We want feedback! Developer releases are coming every month, so you have a chance to make a difference in what we are building.

Bugs: If you find a bug (something that is broken or doesn’t work how you’d expect), please file an issue in our JIRA system at issues.couchbase.com. Or, contact me with a description of the issue. I would be happy to help you or submit the bug for you (my Couchbase handlers give me a candy bar and pat me on the head every time I submit a good bug).

Feedback: Let me know what you think. Something you don’t like? Something you really like? Something missing?

In some cases, it may be tricky to decide if your feedback is a bug or a suggestion. Use your best judgement, or again, feel free to contact me for help. I want to hear from you. No suggestion is too small! The only stupid question is the one you don’t ask! The best way to contact me is either Twitter @mgroves or email me [email protected].

My job as a developer evangelist for Couchbase means than I work much less with SQL Server than I used to. However, it doesn't mean I don't keep up with it.

In fact, inspired by Couchbase, I decided to try out using SQL Server as a sort of document data store. SQL Server 2016 introduced some interesting new features that make this kinda possible: JSON_VALUEJSON_QUERY, and JSON_MODIFY.

I set up a "document" table, which is two fields: a guid and an nvarchar(max). This is kinda like a Couchbase bucket: a key and a JSON document to go with it.

I put fairly complex hierarchical documents in these fields, something like:

I say "fairly complex", because representing this in a standard normalized fashion would require at least two tables, foreign keys, constraints, and then data migration and schema migration as the model evolves.

Now, suppose I want to execute a query and find all the document that are "Show=true". With Couchbase and N1QL, I would just use something like "SELECT * FROM `bucket` WHERE show = true".

With SQL Server, there's a little more work. "SELECT t.key, t.doc FROM [table] t WHERE JSON_VALUE(t.SpeakingInfo,'$.Show') = 'true'". Notice that JSON_VALUE is being applied to a text field, and a JSON path is used within JSON_VALUE to get a specific value from within that JSON object.

For this simple project I'm doing, that's all I need. No idea yet what kind of performance level I can expect from JSON_VALUE and more complex JSON paths.

But, this is definitely an example of the kinds of database convergence I've been telling people about. The separations between NoSQL and SQL are becoming less strict, at least in terms of data modeling and querying.

You need to be using SQL Server 2016 or SQL Server Azure to take advantage of the JSON_* functions.

Matthew D. Groves

About the Author

Matthew D. Groves lives in Central Ohio. He works remotely, loves to code, and is a Microsoft MVP.

Latest Comments

Twitter