Getting Started with SingleStore Kai
On this page
You can run MongoDB queries on your SingleStore Kai ("the API") enabled workspaces from MongoDB clients/tools after performing these minimal steps:
-
Ensure that the prerequisites are met.
-
Enable the API endpoint for your workspace.
-
Access the API using supported tools/applications.
Disclaimer:
SingleStore is not a MongoDB® partner.
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:
-
On the Cloud Portal, select Create Workspace Group.
You can also create a new workspace in an existing workspace group. -
On the Create Workspace Group page, enter or select the configuration settings, and then select Next.
-
On the Create Workspace page, under Advanced Settings, enable the SingleStore Kai toggle switch.
-
Select Create Workspace.
The API is now enabled for your 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.
Clients/applications must connect to the API in load-balanced mode.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.
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:
-
Any MongoDB client that uses the MongoDB wire protocol or standard MongoDB driver.
Here are a few examples: -
Mongoose.
js -
Prisma
-
Spring Boot
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 |
1. |
|
C++ |
3. |
|
C# / . |
2. |
|
Go |
1. |
|
Java |
4. |
|
Kotlin |
4. |
|
PHP |
1. |
|
Python |
3. |
|
Ruby |
2. |
|
Rust |
2. |
|
Scala |
4. |
|
Swift |
1. |
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:
-
Install the MongoDB for VS Code extension.
-
On the navigation pane, select the MongoDB icon.
-
Under Connections, select Add Connection.
-
Under Connect with Connection String, select Connect.
-
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 dbExampleshow 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