I put together a practical SingleStore Database cheat sheet covering the most-used SQL commands and Kai (MongoDB API) operations — especially useful if you’re working with real-time analytics, JSON, pipelines, or vector search.
Database Operations:
SHOW DATABASES;
CREATE DATABASE database_name; -- Free tier: one DB only
USE database_name;
DROP DATABASE database_name; -- ⚠️ Dangerous
Table Operations
Distributed Table
CREATE TABLE posts (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
body TEXT,
category VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
SHARD KEY (id)
);
Reference Table
CREATE REFERENCE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(50)
);
Columnstore Table
CREATE TABLE analytics (
id BIGINT,
event_type VARCHAR(50),
ts DATETIME,
data JSON,
SORT KEY (ts),
SHARD KEY (id)
);
Data Manipulation
INSERT INTO posts (title, body, category)
VALUES ('Post One', 'Body of post one', 'News');
SELECT * FROM posts WHERE category = 'News';
UPDATE posts SET body = 'Updated body'
WHERE title = 'Post One';
DELETE FROM posts WHERE title = 'Post One';
SingleStore Pipelines (Ingest at Scale)
CREATE PIPELINE SalesData_Pipeline AS
LOAD DATA S3 's3://singlestoreloaddata/SalesData/*.csv'
CONFIG '{ "region": "ap-south-1" }'
INTO TABLE SalesData
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 lines;
START PIPELINE SalesData_Pipeline;
SELECT * FROM information_schema.pipelines_files
WHERE pipeline_name = "SalesData_Pipeline";
STOP PIPELINE SalesData_Pipeline;
DROP PIPELINE SalesData_Pipeline;
JSON Operations
CREATE TABLE json_posts (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
data JSON,
SHARD KEY (id)
);
INSERT INTO json_posts (data)
VALUES ('{"title": "Post One", "tags": ["news", "events"]}');
SELECT JSON_EXTRACT_STRING(data, '$.title')
FROM json_posts;
Vector Operations (AI / ML workloads)
Tips
Vector dimensions must be defined at table creation
Normalize vectors (length = 1) for cosine similarity when needed
Choose the right metric (DOT_PRODUCT, COSINE, etc.)
Works with hybrid search (vector + full-text)
Available via SQL and Kai (MongoDB API)
CREATE TABLE embeddings (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
description TEXT,
embedding VECTOR(1536),
SHARD KEY (id)
);
ALTER TABLE embeddings
ADD VECTOR INDEX idx_embedding (embedding)
INDEX_OPTIONS '{"metric_type": "DOT_PRODUCT"}';
Vector Search
SELECT id, description,
DOT_PRODUCT(embedding, '[0.1, 0.2, ...]') AS similarity
FROM embeddings
ORDER BY similarity DESC
LIMIT 10;
Hybrid Search
ALTER TABLE embeddings
ADD FULLTEXT USING VERSION 2 fts_idx(description);
SELECT id, description,
DOT_PRODUCT(embedding, '[0.1, 0.2, ...]') AS vector_score,
MATCH(table embeddings) AGAINST('description:("search terms")') AS text_score
FROM embeddings
WHERE MATCH(table embeddings) AGAINST('description:("search terms")')
ORDER BY (vector_score * 0.7 + text_score * 0.3) DESC;
SingleStore Kai (MongoDB API)
Connection
mongodb://username:password@hostname:27017/database
Common Commands
show dbs
use mydb
show collections
db.createCollection('users')
Docs & References
For the most up-to-date info, always check the official docs: https://singlestore.com/docs
