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 SingleStore version 8.5 or later.

    • Run the following command to check the version:

      SELECT @@memsql_version;
  • 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 SingleStore Helios 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.

Kai Shell

The Kai Shell feature allows you to run MongoDB® queries on the Cloud Portal on a SingleStore Kai-enabled workspace. You can use any of the supported commands, data types, and operators in this shell similar to any other MongoDB® client.

Access the Kai Shell

To access the Kai Shell on the Cloud Portal, on the left navigation pane, select Develop -> Kai Shell. Select the SingleStore Kai-enabled workspace you want to run your MongoDB® queries against from the dropdown list at the top. The shell automatically connects to the selected workspace, eliminating the need for a connection string. Here's an example:

  1. Create a SingleStore Kai-enabled workspace on the Cloud Portal. Once the workspace is deployed, proceed to the next step.

  2. On the left navigation pane, under Develop, select Kai Shell.

  3. From the dropdown list at the top, select your workspace. You can now run MongoDB® commands on your workspace using this shell. For example, run the following commands to insert a document in a collection named exampleCollection, and then view the contents of this collection:

    use dbTest
    db.exampleCollection.insert({ _id: 1, message: "Hello there!"})
    db.exampleCollection.find()
    [ { _id: 1, message: 'Hello there!' } ]

Refer to Examples for CRUD examples.

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: June 5, 2024

Was this article helpful?