As we all know, code is expensive to maintain—the more complex the code, the higher the cost of maintaining it. Therefore, since the beginning of time, software developers have worked very hard to achieve the goal of code reusability.
Couchbase Mobile 3.0 introduced support for SQL++ queries. As a developer, the first thought that came to mind was sharing queries used in projects targeting both Couchbase Server and Couchbase Mobile applications.
Is this a good idea? Let’s explore that question using sample data and a proof-of-concept mobile application.
Sample data
All the code in this article is available on GitHub. See the README for information on how to get the projects set up on your computer.
The sample data comes from the OpenStreetMap project and is licensed under the Open Data Commons Open Database License (ODbL) by the OpenStreetMap Foundation.
The data set contains all the shops that sell ice cream in the United States. An example JSON document is listed below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
{ "type": "Feature", "id": "node/472242349", "properties": { "addrCity": "Austin", "addrHousenumber": "4477", "addrPostcode": "78745", "addrState": "TX", "addrStreet": "South Lamar Boulevard", "addrUnit": "#790", "amenity": "ice_cream", "cuisine": "ice_cream", "name": "Amy's Ice Creams", "phone": "+1-512-891-0573", "id": "node/472242349" }, "geometry": { "type": "Point", "coordinates": [ -97.7998856, 30.230688 ] } } |
As you can see, this is a versatile dataset for testing because it has properties embedded into the documents.
Sample mobile application
The sample mobile application was written in SwiftUI with Combine and is an iOS/macOS application that displays all the various shops that sell ice cream in the United States. The application includes a pre-built database with the same JSON documents already imported into it for convenience. This mobile application uses the latest version of the Couchbase Lite SDK. You can open the project in XCode from the project file in the src folder to follow along.
A simple query comparison
To start our testing, we can write a simple query, for Couchbase Server, to show all the ice cream shop addresses and order them by the shop name:
|
1 2 3 4 5 6 |
SELECT id, properties.addrCity, properties.addrHousenumber, properties.addrPostcode, properties.addrStreet, properties.addrState, properties.name FROM icecream WHERE properties.addrCity <> "" AND type = "Feature" ORDER BY properties.name |
This query has a filter that only returns documents where the property’s object addrCity field has a value and where the document type is equal to Feature. If you wonder why we have the addrCity filter, it’s because of dirty data or documents without names or missing information in this dataset. Using a type attribute is common in mobile applications to filter out different types of documents.
We can use the Query Editor from the Couchbase Server Web Console to run this query. When I ran this on my personal Macbook Pro M1 Max, the result came back in 38.4ms. This query uses the adv_properties_addrCity_type index, which you can see by clicking the Index Advisor button.
Porting the query to Couchbase Lite
Now let’s look at this same query running on our mobile app. To view the mobile app, you can use XCode to open up the IceCreamLocator.xcodeproj file located in the src folder. Once opened in Xcode, find the IceCreamLocationRepository swift file in the Shared\Data folder.
The init function turns on verbose logging for the database to inspect the query information. Using verbose logging, you can review what’s going on within Couchbase Lite, including query performance.
Let’s review the simple query from Couchbase Server and see how it works in Swift with the new SQL++ Query API. The function for the simple query is named getListByTypeCityOrderName, and the code is displayed below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
func getListByTypeCityOrderName() -> Void { do { if let query = try _db?.createQuery("SELECT id, properties.addrCity, properties.addrHousenumber, properties.addrPostcode, properties.addrStreet, properties.addrState, properties.name FROM _ WHERE properties.addrCity <> \"\" AND type = \"Feature\" ORDER BY properties.name") { var results: [IceCreamLocation] = [] let explain = try query.explain() print ("**EXPLAIN** \(explain)") for result in try query.execute() { if let data = result.toJSON().data(using: .utf8){ let location = try JSONDecoder().decode(IceCreamLocation.self, from: data) results.append(location) } } self.iceCreamLocationList.send(results) self.iceCreamLocationList.send(completion: .finished) } } catch { print("**Error**: \(error)") self.iceCreamLocationList.send(completion: .failure(DbError.unknown)) } } |
There are a few ways to make an SQL++ query in Swift. In this example, we use the database createQuery function to create the query. In Couchbase Lite, I’m using the underscore character (_) as the database name before the FROM statement. Changing the database name is one of the expected changes to reuse SQL++ statements. Also, to escape the quotation marks in the WHERE clause, I had to add the Swift string escape character (\) before each quotation mark.
To test this query while running, you can open the IceCreamListViewModel file found in the ViewModels folder. Locate the init function and uncomment out the first example line located at the bottom of the function that calls the getListByTypeCityOrderName function from the _repository variable:
|
1 2 |
//first example _repository.getListByTypeCityOrderName() |
Now you can use Xcode to run the application on an iOS Simulator or a macOS app. Next, open the Debug Console in Xcode by clicking the Show Debugging icon in the lower right corner of the Xcode editor, as highlighted below.
Verbose logging will display a massive amount of logging information. To help find the correct information in the logs, use the filter box below the console. For example, enter the text “Query Info” and then hit the enter key on your keyboard to see information about the query. The debug console will filter the data that Couchbase Lite returns.
Inspecting the logs will show you the Query Info log entry and the number of documents found by the query, how many bytes and how long the query took. Remember, the numbers you are looking at are from a simulator, not an actual device. For performance tuning of mobile apps, you should use tools from Apple with a real device.
The speed at which this query ran is impressive. Even more impressive, there haven’t been any indexes created yet, so this query runs without an index and does a table scan which means it has to look through the database at each document to see if it matches the requirements of the query. Couchbase Lite doesn’t require indexes and will do table scans if needed, whereas Couchbase Server does require indexes because table scans can kill the performance of the entire cluster.
In several cases adding indexes can significantly improve the performance of your queries, so let’s stop running the application and add the proper index and recheck performance.
Open the IceCreamLocationRepository and locate the createIceCreamIndexes function. Uncomment the first block of code for creating the first query index:
|
1 2 |
let simpleIndex = ValueIndexConfiguration(["properties.name", "properties.addrCity", "type"]) try _db?.createIndex(simpleIndex, name: "idx_location_type_city_name") |
How do we test to see if the query used the index we created? In the getListByTypeCityOrderName function, you will see two lines that prints results from the explain function to the console:
|
1 2 |
let explain = try query.explain() print ("**EXPLAIN** \(explain)") |
The query explain function will run the equivalent of the SQLite EXPLAIN QUERY PLAN command and store the results in a string you send to the console using the print function. You can filter for SCAN TABLE in your debug console to find the results. When reviewing the results, make sure you remove your previous filter from the console window. Now rerun the application and look through the logs to check the performance change.
You should see the USING INDEX with the created index name, which tells you that the query uses the proper index. Note that while indexes can help queries, they can also slow down insert and updates to documents. You can find more information about indexing in the Couchbase Lite documentation.
Another query comparison
In our second comparison, we filter out results only to show ice cream shops in the State of Georgia, ordered by City and State:
|
1 2 3 4 5 6 7 8 |
SELECT id, properties.addrCity, properties.addHousenumber, properties.addrPostcode, properties.addrStreet, properties.addrState, properties.name FROM icecream WHERE properties.addrCity IS NOT NULL AND properties.addrState = 'GA' AND type = "Feature" ORDER BY properties.name |
This query returns 15 documents and uses a different query since we use the properties.addrState and properties.addrCity attributes in our WHERE clause.
Note that we switched out the way we check the properties.addrCity attribute and use the IS NOT NULL statement now. This shows the flexibility of the SQL++ language and how developers can use different syntax to produce the same results.
Now, let’s go back to our mobile app to look at any differences. The function for our second query is named getListByStateGeorgia, and the code is displayed below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
func getListByStateGeorgia() -> Void { do { if let query = try _db?.createQuery("SELECT id, properties.addrCity, properties.addrHousenumber, properties.addrPostcode, properties.addrStreet, properties.addrState, properties.name FROM _ WHERE properties.addrCity NOT NULL AND properties.addrState = \"GA\" AND type=\"Feature\" ORDER BY properties.name") { var results: [IceCreamLocation] = [] let explain = try query.explain() print ("**EXPLAIN** \(explain)") for result in try query.execute() { if let data = result.toJSON().data(using: .utf8){ let location = try JSONDecoder().decode(IceCreamLocation.self, from: data) results.append(location) } } self.iceCreamLocationList.send(results) self.iceCreamLocationList.send(completion: .finished) } } catch { print("**Error**: \(error)") self.iceCreamLocationList.send(completion: .failure(DbError.unknown)) } } |
To test this new query, you can open the IceCreamListViewModel. Locate the init function and comment out the first example line located at the bottom of the function that calls the getListByTypeCityOrderName function from the _repository variable and uncomment out the line that calls our new function:
|
1 2 3 4 5 |
//first example //_repository.getListByTypeCityOrderName() //second example _repository.getListByStateGeorgia() |
You can debug the app using Xcode and check the debug area. It shows the same 15 documents that were returned by the query ran on Couchbase Server, but we are back to doing a table scan which isn’t the behavior we want—we changed the query, but we didn’t create any new indexes yet.
To fix this, we can go back to the IceCreamLocationRepository and locate the createIceCreamIndexes function. Uncomment the second block of code for creating the second query index:
|
1 2 3 |
let secondIndex = ValueIndexConfiguration(["properties.name","properties.addrCity", "type", "properties.addrState"]) try _db?.createIndex(secondIndex, name: "idx_location_type_city_state_name") |
We found that we could reuse the code in these two more simple queries, but we had to add the indexes to avoid table scans.
Catches with reusing queries
Couchbase Server and Couchbase Mobile don’t use the same storage or query engine. Therefore, you might run into Couchbase Server queries that need slight modifications to be optimized for Couchbase Mobile.
For more information on Couchbase Server, check out the documentation on the Cost-Based Optimizer that the query engine uses. The Couchbase Developer portal also has a good article on improving query performance on Couchbase Server that can explain why some queries are written in a specific way.
I recommend reading the Couchbase Mobile documentation on troubleshooting queries as it has helped me write optimized queries and further explains the SQLite query plan output.
Summary
As shown in our code examples with the Couchbase Mobile 3.0 release, it is possible to reuse SQL++ code between Server and Mobile. However, you may need to modify queries and indexes for optimal performance. Given this, I highly recommend reading through the excellent documentation on SQL++ for Mobile – Differences from SQL++ for Server and SQL++ Query Strings. It has valuable information on changes you might need to make to your queries to use them in your mobile applications.
Here are some of the primary resources referenced throughout the post:
- Code samples from GitHub
- OpenStreetMap project
- Couchbase Mobile – Prebuilt database docs
- Couchbase Lite on Swift – Quickstart docs
- Couchbase Lite – Indexing docs
- Couchbase Server – Cost-based Optimizer docs
- Couchbase Server – Tuning Tips and Advice
- Couchbase Mobile – Troubleshooting Queries
English