Getting Started with SingleStore Kai

You can run MongoDB queries on your SingleStore Kai ("the API") enabled workspaces from MongoDB clients/tools after performing these minimal steps:

Disclaimer:

SingleStore is not a MongoDB® partner. SingleStore Kai™ is an API that offers compatibility and enhanced performance for applications built on MongoDB.

Prerequisites

  • A workspace group running SingleStoreDB version 8.1.

    • Run the following command to check the version:

      SELECT @@memsql_version;
  • A workspace group deployed in the supported availability regions.

  • A SingleStore Kai-enabled workspace. Refer to Enable the API for more information.

  • Any MongoDB client that uses the MongoDB wire protocol or standard MongoDB driver that supports the loadBalanced mode.

Enable the API

To enable the API for your SingleStoreDB Cloud workspace, perform the following tasks:

  1. On the Cloud Portal, select Create Workspace Group. You can also create a new workspace in an existing workspace group.

  2. On the Create Workspace Group page, enter or select the configuration settings, and then select Next.

  3. On the Create Workspace page, under Advanced Settings, enable the SingleStore Kai toggle switch.

  4. Select Create Workspace.

The API is now enabled for your workspace. You can only enable or disable the API endpoint while creating a workspace.

Note: The following global compatibility configuration variables are automatically set when the API is enabled for a workspace:

  • json_compatibility_level: '8.0'

  • data_conversion_compatibility_level: '8.0'

  • regexp_format: 'advanced'

If any of these variables cannot be set to the values specified above for a workspace, the API rejects any user authentication requests.

Access the API

A SingleStore Kai-enabled workspace has two different endpoints:

  • mongodb://

  • mysql://

For example:

  • mongodb://<user>:<password>@svc-XXXX.svc.singlestore.com:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true
  • mysql://svc-XXXX.svc.singlestore.com:3306

Connect to the mongodb:// endpoint to run MongoDB queries. This endpoint supports SASL/PLAIN authentication mechanism.

Clients/applications must connect to the API in load-balanced mode. If a client/application connects to the API without specifying loadBalanced=true in the connection string, the API rejects the authentication request with an explanatory error message.

Manage Users and Permissions

To access a workspace's mongodb:// endpoint, a user must have the EXECUTE permission to the cluster database. You can add users and grant permissions only through the SQL endpoint, using SQL commands.

The following example shows how to grant the EXECUTE permission to a user:

GRANT EXECUTE ON cluster.* TO joe;

Refer to Manage Database Users for more information.

Supported Tools

You can connect to your SingleStore Kai-enabled workspace's mongodb:// endpoint using the following tools:

You can also connect to SingleStore Kai in load-balanced mode via various application development tools using the following MongoDB drivers:

Language/Tool

Driver

Version

C

mongo-c-driver

1.19.0

C++

mongo-c++-driver

3.7.0

C# / .NET

mongo-csharp-driver

2.13.0

Go

mongo-go-driver

1.6.2

Java

mongo-java-driver

4.3.0

Kotlin

mongo-java-driver

4.3.0

PHP

mongo-php-driver

1.11.0

Python

mongo-python-driver

3.12.0

Ruby

mongo-ruby-driver

2.16.0

Rust

mongo-rust-driver

2.1.0

Scala

mongo-java-driver

4.3.0

Swift

mongo-swift-driver

1.2.0

Connect with MongoDB Shell mongosh

To connect using the mongosh shell, use the following command:

mongosh "mongodb://<username>@<host>:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true"

For example:

mongosh "mongodb://admin@svc-XXXX.svc.singlestore.com:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true"

At the next prompt, enter the password to access the API endpoint.

Connect with MongoDB for Visual Studio Code Extension

To connect to the API endpoint from Visual Studio Code, perform the following tasks:

  1. Install the MongoDB for VS Code extension.

  2. On the navigation pane, select the MongoDB icon.

  3. Under Connections, select Add Connection.

  4. Under Connect with Connection String, select Connect.

  5. Enter the connection string in the following format:

    mongodb://<username>:<password>@<host>:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true

Once the connection is successful, a Connected to: <host>:27017 message is displayed along with a MongoDB connection successful popup message.

Examples

The following examples perform CRUD operations using the API endpoint.

Create a Database

The following command creates a database named dbExample.

use dbExample
show dbs
mongo               0.00 B
dbExample           0.00 B
information_schema  0.00 B

Create a Collection and Insert Documents

Run the following command to create a collection named exampleCollection and add multiple documents to this collection:

db.exampleCollection.insertMany( [
{ _id: 1, Code: "xv1f", Qty: 45 },
{ _id: 2, Code: "nm3w", Qty: 30 },
{ _id: 3, Code: "qoma", Qty: 20 },
{ _id: 4, Code: "hr3k", Qty: 15 } ] )
{ acknowledged: true, insertedIds: { '0': 1, '1': 2, '2': 3, '3': 4 } }

Read Values from a Collection

The following command returns all the documents stored in exampleCollection:

db.exampleCollection.find()
[
  { _id: 1, Code: 'xv1f', Qty: 45 },
  { _id: 2, Code: 'nm3w', Qty: 30 },
  { _id: 3, Code: 'qoma', Qty: 20 },
  { _id: 4, Code: 'hr3k', Qty: 15 }
]

Update a Collection

The following example updates the first document in exampleCollection where Code equals "xv1f":

db.exampleCollection.updateOne(
{ Code: "xv1f" },
{ $set: { Qty: 40 } }
)
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
db.exampleCollection.find()
[
  { _id: 1, Code: 'xv1f', Qty: 40 },
  { _id: 2, Code: 'nm3w', Qty: 30 },
  { _id: 3, Code: 'qoma', Qty: 20 },
  { _id: 4, Code: 'hr3k', Qty: 15 }
]

The following example updates all the documents in exampleCollection where Qty is less than or equal to 25.

db.exampleCollection.updateMany(
{ Qty: {$lte: 25 } },
{ $set: { Comment: "Restock required" }, $inc: { Qty: -10 } }
)
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 2,
  modifiedCount: 2,
  upsertedCount: 0
}
db.exampleCollection.find()
[
  { _id: 1, Code: 'xv1f', Qty: 40 },
  { _id: 2, Code: 'nm3w', Qty: 30 },
  { _id: 3, Code: 'qoma', Qty: 10, Comment: 'Restock required' },
  { _id: 4, Code: 'hr3k', Qty: 5, Comment: 'Restock required' }
]

Delete a Document

The following command deletes all the documents from exampleCollection that match the specified condition:

db.exampleCollection.deleteMany(
{ Comment: "Restock required", Qty: { $lt: 10 } }
)
{ acknowledged: true, deletedCount: 1 }

Last modified: August 24, 2023

Was this article helpful?