{"id":2129,"date":"2022-03-14T10:23:48","date_gmt":"2022-03-14T17:23:48","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/reuse-sql-queries-between-server-mobile-app-databases\/"},"modified":"2022-03-14T10:23:48","modified_gmt":"2022-03-14T17:23:48","slug":"reuse-sql-queries-between-server-mobile-app-databases","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/pt\/reuse-sql-queries-between-server-mobile-app-databases\/","title":{"rendered":"Reuse SQL Queries between Server & Mobile App Databases"},"content":{"rendered":"\n

As we all know, code is expensive to maintain\u2014the 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 <\/span>code reusability<\/b>.<\/span><\/p>\n\n\n\n

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.\u00a0<\/span><\/p>\n\n\n\n

Is this a good idea? Let\u2019s explore that question using sample data and a proof-of-concept mobile application.<\/span><\/p>\n\n\n\n

Sample data<\/span><\/h4>\n\n\n\n

All the code in this article is available on <\/span>GitHub<\/span><\/a>. See the <\/span>README<\/span><\/i><\/a> for information on how to get the projects set up on your computer.<\/span><\/p>\n\n\n\n

The sample data comes from the <\/span>OpenStreetMap<\/span><\/a> project and is licensed under the <\/span>Open Data Commons Open Database License (ODbL)<\/span><\/a> by the OpenStreetMap Foundation.<\/span><\/p>\n\n\n\n

The data set contains all the shops that sell ice cream in the United States. An example JSON document is listed below:<\/span><\/p>\n\n\n\n

{\r\n "type": "Feature",\r\n "id": "node\/472242349",\r\n "properties": {\r\n "addrCity": "Austin",\r\n "addrHousenumber": "4477",\r\n "addrPostcode": "78745",\r\n "addrState": "TX",\r\n "addrStreet": "South Lamar Boulevard",\r\n "addrUnit": "#790",\r\n "amenity": "ice_cream",\r\n "cuisine": "ice_cream",\r\n "name": "Amy's Ice Creams",\r\n "phone": "+1-512-891-0573",\r\n "id": "node\/472242349"\r\n},\r\n  "geometry": {\r\n   "type": "Point",\r\n   "coordinates": [\r\n    -97.7998856,\r\n    30.230688\r\n  ]\r\n }\r\n}<\/code><\/pre>\n\n\n\n
As you can see, this is a versatile dataset for testing because it has properties embedded into the documents.<\/span><\/p>\n\n\n\n
Sample mobile application<\/span><\/h4>\n\n\n\n
The sample mobile application<\/a> was written in <\/span>SwiftUI<\/span><\/a> with <\/span>Combine<\/span><\/a> and is an iOS\/macOS application that displays all the various shops that sell ice cream in the United States. The application includes a <\/span>pre-built database<\/span><\/a> with the same JSON documents already imported into it for convenience. This mobile application uses the latest version of the <\/span>Couchbase Lite SDK<\/span><\/a>. You can open the project in XCode from the project file in the src folder to follow along.<\/span><\/p>\n\n\n\n
A simple query comparison<\/span><\/h4>\n\n\n\n
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:<\/span><\/p>\n\n\n
[crayon decode-attributes=”false” lang=”default” decode=”true”]SELECT id, properties.addrCity, properties.addrHousenumber, properties.addrPostcode,
\nproperties.addrStreet, properties.addrState, properties.name
\nFROM icecream
\nWHERE properties.addrCity <> “”
\nAND type = “Feature”
\nORDER BY properties.name[\/crayon]<\/p>\n\n\n\n
This query has a filter that only returns documents where the property’s object <\/span>addrCity <\/span><\/i>field has a value and where the document <\/span>type<\/span><\/i> is equal to <\/span>Feature<\/span><\/i>. If you wonder why we have the <\/span>addrCity<\/span><\/i> filter, it\u2019s because of dirty data or documents without names or missing information in this dataset. Using a <\/span>type<\/span><\/i> attribute is common in mobile applications to filter out different types of documents.<\/span><\/p>\n\n\n\n
We can use the <\/span>Query Editor<\/b> from the Couchbase Server<\/span> Web Console<\/b><\/a> 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 <\/span>adv_properties_addrCity_type<\/span><\/i> index, which you can see by clicking the <\/span>Index Advisor<\/b> button.<\/span><\/p>\n\n\n\n
Porting the query to Couchbase Lite<\/span><\/h4>\n\n\n\n
Now let\u2019s look at this same query running on our mobile app. To view the mobile app, you can use XCode to open up the <\/span>IceCreamLocator.xcodeproj<\/span><\/i> file located in the <\/span>src <\/span><\/i>folder. Once opened in Xcode, find the <\/span>IceCreamLocationRepository<\/span><\/i><\/a> swift file in the <\/span>SharedData<\/span><\/i> folder.\u00a0<\/span><\/p>\n\n\n\n
The <\/span>init <\/span><\/i>function turns on verbose logging for the database to inspect the query information. Using verbose logging, you can review what\u2019s going on within Couchbase Lite, including query performance.<\/span><\/p>\n\n\n\n
Let\u2019s 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 <\/span>getListByTypeCityOrderName<\/span><\/i><\/a>, and the code is displayed below:<\/span><\/p>\n\n\n
[crayon decode-attributes=”false” lang=”default” decode=”true”]  func getListByTypeCityOrderName() -> Void {
\n      do {
\n        if let query = try _db?.createQuery(“SELECT id,
\nproperties.addrCity, properties.addrHousenumber, properties.addrPostcode, properties.addrStreet,
\nproperties.addrState, properties.name FROM _ WHERE properties.addrCity <> “” AND
\ntype = “Feature” ORDER BY properties.name”) {
\n            var results: [IceCreamLocation] = []
\n            let explain = try query.explain()
\n            print (“**EXPLAIN** (explain)”)
\n            for result in try query.execute() {
\n                if let data = result.toJSON().data(using: .utf8){
\n                   let location = try
\nJSONDecoder().decode(IceCreamLocation.self, from: data)
\n                   results.append(location)
\n             }
\n           }
\n           self.iceCreamLocationList.send(results)
\n           self.iceCreamLocationList.send(completion: .finished)
\n         }
\n     } catch {
\n         print(“**Error**: (error)”)
\n         self.iceCreamLocationList.send(completion: .failure(DbError.unknown))
\n     }
\n}[\/crayon]<\/p>\n\n\n\n
There are a few ways to make an SQL++ query in Swift. In this example, we use the database <\/span>createQuery<\/span><\/i><\/a> function to create the query. In Couchbase Lite, I\u2019m using the underscore character (_) as the database name before the <\/span>FROM <\/span><\/i>statement. Changing the database name is one of the expected changes to reuse SQL++ statements. Also, to escape the quotation marks in the <\/span>WHERE <\/span><\/i>clause, I had to add the Swift string escape character (<\/span><\/span><\/i>) before each quotation mark.\u00a0<\/span><\/p>\n\n\n\n
To test this query while running, you can open the <\/span>IceCreamListViewModel<\/span><\/i><\/a> file found in the <\/span>ViewModels <\/span><\/i>folder. Locate the <\/span>init <\/span><\/i>function and uncomment out the first example line located at the bottom of the function that calls the <\/span>getListByTypeCityOrderName<\/span><\/i> function from the <\/span>_repository <\/span><\/i>variable:<\/span><\/p>\n\n\n\n
 \/\/first example\r\n_repository.getListByTypeCityOrderName()<\/code><\/pre>\n\n\n\n
Now you can use Xcode to run the application on an iOS Simulator or a macOS app. Next, open the <\/span>Debug Console <\/b>in Xcode by clicking the <\/span>Show Debugging <\/b>icon in the lower right corner of the Xcode editor, as highlighted below.<\/span><\/p>\n\n\n\n
\"\"<\/p>\n\n\n\n
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 \u201c<\/span>Query Info<\/span><\/i>\u201d 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.<\/span><\/p>\n\n\n\n
Inspecting the logs will show you the <\/span>Query Info<\/span><\/i> 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.<\/span><\/p>\n\n\n\n
The speed at which this query ran is impressive. Even more impressive, there haven\u2019t 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\u2019t 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.\u00a0<\/span><\/p>\n\n\n\n
In several cases adding indexes can significantly improve the performance of your queries, so let\u2019s stop running the application and add the proper index and recheck performance.<\/span><\/p>\n\n\n\n
Open the <\/span>IceCreamLocationRepository<\/span><\/i> and locate the <\/span>createIceCreamIndexes<\/span><\/i><\/a> function. Uncomment the first block of code for creating the first query index:<\/span><\/p>\n\n\n
[crayon lang=”default” decode=”true”]   let simpleIndex = ValueIndexConfiguration([“properties.name”, “properties.addrCity”, “type”])
\n   try _db?.createIndex(simpleIndex, name: “idx_location_type_city_name”)[\/crayon]<\/p>\n\n\n\n
How do we test to see if the query used the index we created? In the <\/span>getListByTypeCityOrderName<\/span><\/i> function, you will see two lines that prints results from the explain function to the console:<\/span><\/p>\n\n\n\n
let explain = try query.explain()\r\nprint ("**EXPLAIN** (explain)")<\/code><\/pre>\n\n\n\n
The query <\/span>explain<\/span><\/a> function will run the equivalent of the SQLite <\/span>EXPLAIN QUERY PLAN<\/span><\/i> command and store the results in a string you send to the console using the print function. You can filter for <\/span>SCAN TABLE <\/span><\/i>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.<\/span><\/p>\n\n\n\n
\"\"<\/p>\n\n\n\n
You should see the <\/span>USING INDEX <\/span><\/i>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 <\/span>documentation<\/span><\/a>.<\/span><\/p>\n\n\n\n
Another query comparison<\/span><\/h4>\n\n\n\n
In our second comparison, we filter out results only to show ice cream shops in the State of Georgia, ordered by City and State:<\/span><\/p>\n\n\n\n
SELECT id, properties.addrCity, properties.addHousenumber, properties.addrPostcode, \r\nproperties.addrStreet, properties.addrState, properties.name \r\nFROM icecream \r\nWHERE\r\nproperties.addrCity IS NOT NULL \r\nAND properties.addrState = 'GA'\r\nAND type = "Feature" \r\nORDER BY properties.name<\/code><\/pre>\n\n\n\n
This query returns 15 documents and uses a different query since we use the <\/span>properties.addrState <\/span><\/i>and <\/span>properties.addrCity<\/span><\/i> attributes in our <\/span>WHERE <\/span><\/i>clause.\u00a0<\/span><\/p>\n\n\n\n
Note that we switched out the way we check the <\/span>properties.addrCity<\/span><\/i> attribute and use the <\/span>IS NOT NULL<\/span><\/i> statement now. This shows the flexibility of the SQL++ language and how developers can use different syntax to produce the same results.<\/span><\/p>\n\n\n\n
\"\"<\/p>\n\n\n\n
Now, let\u2019s go back to our mobile app to look at any differences. The function for our second query is named <\/span>getListByStateGeorgia<\/span><\/i><\/a>, and the code is displayed below:<\/span><\/p>\n\n\n
[crayon decode-attributes=”false” lang=”default” decode=”true”]    func getListByStateGeorgia() -> Void {
\n        do {
\n           if let query = try _db?.createQuery(“SELECT id, properties.addrCity,
\nproperties.addrHousenumber, properties.addrPostcode, properties.addrStreet,
\nproperties.addrState, properties.name FROM _ WHERE properties.addrCity NOT NULL AND
\nproperties.addrState = “GA” AND type=”Feature” ORDER BY properties.name”) {
\n               var results: [IceCreamLocation] = []
\n               let explain = try query.explain()
\n               print (“**EXPLAIN** (explain)”)
\n               for result in try query.execute() {
\n                   if let data = result.toJSON().data(using: .utf8){
\n                      let location = try
\nJSONDecoder().decode(IceCreamLocation.self, from: data)
\n                      results.append(location)
\n                 }
\n              }
\n              self.iceCreamLocationList.send(results)
\n              self.iceCreamLocationList.send(completion: .finished)
\n          }
\n        } catch {
\n            print(“**Error**: (error)”)
\n            self.iceCreamLocationList.send(completion: .failure(DbError.unknown))
\n     }
\n   }[\/crayon]<\/p>\n\n\n\n
To test this new query, you can open the <\/span>IceCreamListViewModel<\/span><\/i>. Locate the <\/span>init<\/span><\/i><\/a> function and comment out the first example line located at the bottom of the function that calls the <\/span>getListByTypeCityOrderName <\/span><\/i>function from the <\/span>_repository<\/span><\/i> variable and uncomment out the line that calls our new function:<\/span><\/p>\n\n\n\n
\/\/first example\r\n\/\/_repository.getListByTypeCityOrderName()\r\n\r\n\/\/second example\r\n_repository.getListByStateGeorgia()<\/code><\/pre>\n\n\n\n
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\u2019t the behavior we want\u2014we changed the query, but we didn\u2019t create any new indexes yet.<\/span><\/p>\n\n\n\n
\"\"<\/p>\n\n\n\n
To fix this, we can go back to the <\/span>IceCreamLocationRepository<\/span><\/i><\/a> and locate the <\/span>createIceCreamIndexes<\/span><\/i><\/a> function. Uncomment the second block of code for creating the second query index:<\/span><\/p>\n\n\n\n
        let secondIndex = \r\nValueIndexConfiguration(["properties.name","properties.addrCity", "type", "properties.addrState"])\r\n        try _db?.createIndex(secondIndex, name: "idx_location_type_city_state_name")<\/code><\/pre>\n\n\n\n
\"\"<\/p>\n\n\n\n
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.<\/span><\/p>\n\n\n\n
Catches with reusing queries<\/span><\/h4>\n\n\n\n
Couchbase Server and Couchbase Mobile don\u2019t 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.<\/span><\/p>\n\n\n\n
For more information on Couchbase Server, check out the documentation on the <\/span>Cost-Based Optimizer<\/span><\/a> that the query engine uses. The Couchbase Developer portal also has a good article on improving <\/span>query performance<\/span><\/a> on Couchbase Server that can explain why some queries are written in a specific way.\u00a0<\/span><\/p>\n\n\n\n
I recommend reading the Couchbase Mobile documentation on troubleshooting <\/span>queries<\/span><\/a> as it has helped me write optimized queries and further explains the SQLite query plan output.<\/span><\/p>\n\n\n\n
Summary<\/span><\/h4>\n\n\n\n
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 <\/span>SQL++ for Mobile – Differences from SQL++ for Server<\/span><\/a> and <\/span>SQL++ Query Strings<\/span><\/a>. It has valuable information on changes you might need to make to your queries to use them in your mobile applications.<\/span><\/p>\n\n\n\n
Here are some of the primary resources referenced throughout the post:<\/span><\/p>\n\n\n\n