25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
import { describe, it, expect } from "vitest";
import { buildSnippetItemsForTest, buildSqlCompletionItems } from "@/lib/sql/sqlCompletion";
import { DEFAULT_SQL_SNIPPETS, MANTICORESEARCH_SQL_SNIPPETS } from "@/lib/sql/sqlSnippetTemplates";
import type { SqlSnippet } from "@/types/database";
const TEST_SNIPPETS: SqlSnippet[] = [
{ id: "1", label: "select all", prefix: "sel", body: "SELECT *\nFROM my_table;" },
{ id: "2", label: "insert row", prefix: "ins", body: "INSERT INTO my_table VALUES (1);" },
];
const BUILTIN_SELECT: SqlSnippet = { id: "builtin-sel", label: "select *", prefix: "sel", body: "SELECT *\nFROM table\nLIMIT 100;" };
const BUILTIN_CREATE_TABLE: SqlSnippet = { id: "builtin-ct", label: "create table", prefix: "ct", body: "CREATE TABLE table (\n column type\n);" };
const BUILTIN_UPDATE: SqlSnippet = { id: "builtin-upd", label: "update set", prefix: "upd", body: "UPDATE table\nSET column = value\nWHERE condition;" };
const BUILTIN_CTE: SqlSnippet = { id: "builtin-cte", label: "common table expression", prefix: "cte", body: "WITH name AS (\n SELECT columns\n FROM table\n)\nSELECT *\nFROM name;" };
const BUILTIN_INSERT: SqlSnippet = { id: "builtin-ins", label: "insert into", prefix: "ins", body: "INSERT INTO table (columns)\nVALUES (values);" };
const BUILTIN_JOIN: SqlSnippet = { id: "builtin-join", label: "join", prefix: "join", body: "JOIN table ON left_column = right_column" };
const BUILTIN_CASE: SqlSnippet = { id: "builtin-case", label: "case when", prefix: "case", body: "CASE\n WHEN condition THEN value\n ELSE default\nEND" };
const BUILTIN_ALTER_TABLE: SqlSnippet = { id: "builtin-at", label: "alter table add column", prefix: "at", body: "ALTER TABLE table\nADD COLUMN column type;" };
const BUILTIN_CREATE_INDEX: SqlSnippet = { id: "builtin-ci", label: "create index", prefix: "ci", body: "CREATE INDEX idx_name\nON table (column);" };
describe("buildSnippetItems", () => {
it("keeps the global and Manticore-only built-in snippet inventories explicit", () => {
expect(DEFAULT_SQL_SNIPPETS.map(({ id, prefix }) => [id, prefix])).toEqual([
["builtin-sel", "sel"],
["builtin-ins", "ins"],
["builtin-upd", "upd"],
["builtin-cte", "cte"],
["builtin-join", "join"],
["builtin-case", "case"],
["builtin-ct", "ct"],
["builtin-ex", "ex"],
["builtin-nex", "nex"],
["builtin-at", "at"],
["builtin-ci", "ci"],
]);
expect(MANTICORESEARCH_SQL_SNIPPETS.map(({ id, prefix }) => [id, prefix])).toEqual([
["builtin-manticore-match", "match"],
["builtin-manticore-facet", "facet"],
["builtin-manticore-show-meta", "m"],
["builtin-manticore-show-tables", "tab"],
["builtin-manticore-call-pq", "p"],
]);
});
it("returns matching snippet by prefix", () => {
const items = buildSnippetItemsForTest("sel", TEST_SNIPPETS);
expect(items).toHaveLength(1);
expect(items[0].label).toBe("select all");
expect(items[0].detail).toBe("SELECT *\nFROM my_table;");
expect(items[0].apply).toBe("SELECT *\nFROM my_table;");
});
it("keeps a distinct trigger available to CodeMirror filtering", () => {
const items = buildSnippetItemsForTest("ssf", [{ id: "custom", label: "SELECT *", prefix: "ssf", body: "SELECT * FROM" }]);
expect(items).toEqual([
expect.objectContaining({
label: "SELECT *",
filterText: "ssf",
type: "snippet",
}),
]);
});
it("uses CodeMirror placeholders for the built-in select snippet", () => {
const items = buildSnippetItemsForTest("sel", [BUILTIN_SELECT]);
expect(items[0].detail).toBe("SELECT *\nFROM table\nLIMIT 100;");
expect(items[0].apply).toBe("SELECT *\nFROM ${table}\nLIMIT 100;");
});
it.each([
["mysql", "SELECT *\nFROM table\nLIMIT 100;", "SELECT *\nFROM ${table}\nLIMIT 100;"],
["oracle", "SELECT *\nFROM table\nWHERE ROWNUM <= 100;", "SELECT *\nFROM ${table}\nWHERE ROWNUM <= 100;"],
["oceanbase-oracle", "SELECT *\nFROM table\nWHERE ROWNUM <= 100;", "SELECT *\nFROM ${table}\nWHERE ROWNUM <= 100;"],
["oscar", "SELECT *\nFROM table\nWHERE ROWNUM <= 100;", "SELECT *\nFROM ${table}\nWHERE ROWNUM <= 100;"],
["xugu", "SELECT *\nFROM table\nLIMIT 100;", "SELECT *\nFROM ${table}\nLIMIT 100;"],
["yashandb", "SELECT *\nFROM table\nLIMIT 100;", "SELECT *\nFROM ${table}\nLIMIT 100;"],
["dameng", "SELECT *\nFROM table\nWHERE ROWNUM <= 100;", "SELECT *\nFROM ${table}\nWHERE ROWNUM <= 100;"],
["db2", "SELECT *\nFROM table\nFETCH FIRST 100 ROWS ONLY;", "SELECT *\nFROM ${table}\nFETCH FIRST 100 ROWS ONLY;"],
["sqlserver", "SELECT TOP 100 *\nFROM table;", "SELECT TOP 100 *\nFROM ${table};"],
["access", "SELECT TOP 100 *\nFROM table;", "SELECT TOP 100 *\nFROM ${table};"],
["iris", "SELECT TOP 100 *\nFROM table;", "SELECT TOP 100 *\nFROM ${table};"],
["teradata", "SELECT TOP 100 *\nFROM table;", "SELECT TOP 100 *\nFROM ${table};"],
["informix", "SELECT FIRST 100 *\nFROM table;", "SELECT FIRST 100 *\nFROM ${table};"],
["firebird", "SELECT *\nFROM table\nROWS 100;", "SELECT *\nFROM ${table}\nROWS 100;"],
["jdbc", "SELECT *\nFROM table;", "SELECT *\nFROM ${table};"],
] as const)("uses the %s row limit syntax in the built-in select snippet", (databaseType, detail, apply) => {
const items = buildSnippetItemsForTest("sel", [BUILTIN_SELECT], undefined, databaseType);
expect(items[0].detail).toBe(detail);
expect(items[0].apply).toBe(apply);
});
it("passes the active database type through the SQL completion provider", () => {
const items = buildSqlCompletionItems("sel", 3, {
tables: [],
columnsByTable: new Map(),
databaseType: "oracle",
});
const snippet = items.find((item) => item.type === "snippet" && item.label === "select *");
expect(snippet?.detail).toBe("SELECT *\nFROM table\nWHERE ROWNUM <= 100;");
expect(snippet?.apply).toBe("SELECT *\nFROM ${table}\nWHERE ROWNUM <= 100;");
});
it("ranks an exact snippet prefix ahead of an exact function name", () => {
const items = buildSqlCompletionItems("sf", 2, {
databaseType: "oracle",
snippets: [{ id: "custom-sf", label: "select * from table", prefix: "sf", body: "SELECT *\nFROM table;" }],
objects: [{ name: "SF", type: "function" }],
tables: [],
columnsByTable: new Map(),
});
expect(items[0]).toEqual(expect.objectContaining({ label: "select * from table", type: "snippet", exactMatch: true }));
expect(items.findIndex((item) => item.label === "SF" && item.type === "function")).toBeGreaterThan(0);
});
it("ranks an exact snippet prefix ahead of an exact keyword", () => {
const items = buildSqlCompletionItems("select", 6, {
snippets: [{ id: "custom-select", label: "select all rows", prefix: "select", body: "SELECT *\nFROM table;" }],
tables: [],
columnsByTable: new Map(),
});
expect(items[0]).toEqual(expect.objectContaining({ label: "select all rows", type: "snippet", exactMatch: true }));
expect(items.findIndex((item) => item.label === "SELECT" && item.type === "keyword")).toBeGreaterThan(0);
});
it("keeps an exact custom snippet first when matching columns are preferred", () => {
const sql = "SELECT * FROM users WHERE cc";
const items = buildSqlCompletionItems(sql, sql.length, {
snippets: [{ id: "custom-count", label: "count zero", prefix: "cc", body: "count(0)" }],
tables: [{ name: "users", type: "table" }],
columnsByTable: new Map([["users", [{ name: "country_code", table: "users" }]]]),
databaseType: "mysql",
});
expect(items[0]).toEqual(
expect.objectContaining({
label: "count zero",
filterText: "cc",
type: "snippet",
apply: "count(0)",
exactMatch: true,
}),
);
});
it("keeps an exact custom snippet ahead of an exact table alias", () => {
const sql = "SELECT * FROM country_codes cc WHERE cc";
const items = buildSqlCompletionItems(sql, sql.length, {
snippets: [{ id: "custom-count", label: "count zero", prefix: "cc", body: "count(0)" }],
tables: [{ name: "country_codes", type: "table" }],
columnsByTable: new Map(),
databaseType: "mysql",
});
expect(items[0]).toEqual(expect.objectContaining({ label: "count zero", type: "snippet", exactMatch: true }));
});
it("offers custom snippets in preferred SQL value contexts", () => {
const sql = "SELECT DATEADD(cc";
const items = buildSqlCompletionItems(sql, sql.length, {
snippets: [{ id: "custom-count", label: "count zero", prefix: "cc", body: "count(0)" }],
tables: [],
columnsByTable: new Map(),
databaseType: "sqlserver",
});
expect(items[0]).toEqual(expect.objectContaining({ label: "count zero", type: "snippet", apply: "count(0)", exactMatch: true }));
});
it("keeps an exact database routine ahead of its generic function", () => {
const sql = "SELECT COUNT";
const items = buildSqlCompletionItems(sql, sql.length, {
objects: [{ name: "COUNT", type: "function", signature: "value" }],
tables: [],
columnsByTable: new Map(),
databaseType: "mysql",
});
expect(items.find((item) => item.label === "COUNT")?.apply).toBe("`COUNT`(${1:value})");
});
it("keeps the real keyword first after typing past a snippet prefix", () => {
const items = buildSqlCompletionItems("sele", 4, {
snippets: [{ id: "custom-sel", label: "select all rows", prefix: "sel", body: "SELECT *\nFROM table;" }],
tables: [],
columnsByTable: new Map(),
});
expect(items[0]).toEqual(expect.objectContaining({ label: "SELECT", type: "keyword" }));
expect(items.find((item) => item.label === "select all rows" && item.type === "snippet")?.exactMatch).not.toBe(true);
});
it("preserves a customized built-in select body instead of replacing it with a dialect default", () => {
const customized = { ...BUILTIN_SELECT, body: "SELECT *\nFROM table\nLIMIT 7;" };
const items = buildSnippetItemsForTest("sel", [customized], undefined, "oracle");
expect(items[0].detail).toBe("SELECT *\nFROM table\nLIMIT 7;");
expect(items[0].apply).toBe("SELECT *\nFROM ${table}\nLIMIT 7;");
});
it("replaces placeholder words but preserves uppercase SQL keywords", () => {
const items = buildSnippetItemsForTest("ct", [BUILTIN_CREATE_TABLE]);
expect(items[0].apply).toBe("CREATE TABLE ${table} (\n ${column} ${type}\n);");
});
it("adds placeholders across built-in update snippets", () => {
const items = buildSnippetItemsForTest("upd", [BUILTIN_UPDATE]);
expect(items[0].apply).toBe("UPDATE ${table}\nSET ${column} = ${value}\nWHERE ${condition};");
});
it("uses ALTER TABLE UPDATE for ClickHouse", () => {
const items = buildSnippetItemsForTest("upd", [BUILTIN_UPDATE], undefined, "clickhouse");
expect(items[0].detail).toBe("ALTER TABLE table\nUPDATE column = value\nWHERE condition;");
expect(items[0].apply).toBe("ALTER TABLE ${table}\nUPDATE ${column} = ${value}\nWHERE ${condition};");
});
it.each([
["mysql", "ALTER TABLE table\nADD COLUMN column type;", "ALTER TABLE ${table}\nADD COLUMN ${column} ${type};"],
["oracle", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["oceanbase-oracle", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["oscar", "ALTER TABLE table\nADD COLUMN column type;", "ALTER TABLE ${table}\nADD COLUMN ${column} ${type};"],
["yashandb", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["xugu", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["dameng", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["iris", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["informix", "ALTER TABLE table\nADD (column type);", "ALTER TABLE ${table}\nADD (${column} ${type});"],
["sqlserver", "ALTER TABLE table\nADD column type;", "ALTER TABLE ${table}\nADD ${column} ${type};"],
["kingbase", "ALTER TABLE table\nADD column type;", "ALTER TABLE ${table}\nADD ${column} ${type};"],
["cassandra", "ALTER TABLE table\nADD column type;", "ALTER TABLE ${table}\nADD ${column} ${type};"],
["teradata", "ALTER TABLE table\nADD column type;", "ALTER TABLE ${table}\nADD ${column} ${type};"],
] as const)("uses the %s ADD COLUMN syntax in the built-in alter-table snippet", (databaseType, detail, apply) => {
const items = buildSnippetItemsForTest("at", [BUILTIN_ALTER_TABLE], undefined, databaseType);
expect(items[0].detail).toBe(detail);
expect(items[0].apply).toBe(apply);
});
it("keeps keyword casing separate from editable placeholders", () => {
const items = buildSnippetItemsForTest("sel", [BUILTIN_SELECT], "lower");
expect(items[0].detail).toBe("select *\nfrom table\nlimit 100;");
expect(items[0].apply).toBe("select *\nfrom ${table}\nlimit 100;");
});
it("determines placeholders from original casing even when keywords are lowercased", () => {
const items = buildSnippetItemsForTest("ct", [BUILTIN_CREATE_TABLE], "lower");
expect(items[0].detail).toBe("create table table (\n column type\n);");
expect(items[0].apply).toBe("create table ${table} (\n ${column} ${type}\n);");
});
it("wraps repeated placeholder words in CTE snippets", () => {
const items = buildSnippetItemsForTest("cte", [BUILTIN_CTE]);
expect(items[0].apply).toBe("WITH ${name} AS (\n SELECT ${columns}\n FROM ${table}\n)\nSELECT *\nFROM ${name};");
});
it.each([
[BUILTIN_INSERT, "INSERT INTO ${table} (${columns})\nVALUES (${values});"],
[BUILTIN_JOIN, "JOIN ${table} ON ${left_column} = ${right_column}"],
[BUILTIN_CASE, "CASE\n WHEN ${condition} THEN ${value}\n ELSE ${default}\nEND"],
[BUILTIN_CREATE_INDEX, "CREATE INDEX ${idx_name}\nON ${table} (${column});"],
])("adds placeholders for built-in %s snippet", (snippet, expectedApply) => {
const items = buildSnippetItemsForTest(snippet.prefix, [snippet]);
expect(items[0].apply).toBe(expectedApply);
});
it("returns matching snippet by label substring", () => {
const items = buildSnippetItemsForTest("select", TEST_SNIPPETS);
expect(items).toHaveLength(1);
expect(items[0].label).toBe("select all");
});
it("does not return disabled snippets", () => {
const items = buildSnippetItemsForTest("sel", [{ ...TEST_SNIPPETS[0], enabled: false }, TEST_SNIPPETS[1]]);
expect(items).toEqual([]);
});
it("does not keep matching a renamed snippet by its old short label prefix", () => {
const items = buildSnippetItemsForTest("sel", [{ id: "1", label: "select all", prefix: "fff", body: "SELECT *\nFROM my_table;" }]);
expect(items).toEqual([]);
});
it("still matches a renamed snippet by label when typing a longer descriptive query", () => {
const items = buildSnippetItemsForTest("select", [{ id: "1", label: "select all", prefix: "fff", body: "SELECT *\nFROM my_table;" }]);
expect(items).toHaveLength(1);
expect(items[0].label).toBe("select all");
});
it("returns empty for no match", () => {
const items = buildSnippetItemsForTest("zzz", TEST_SNIPPETS);
expect(items).toEqual([]);
});
it("returns empty for empty prefix", () => {
const items = buildSnippetItemsForTest("", TEST_SNIPPETS);
expect(items).toEqual([]);
});
});
describe("snippet ranking", () => {
const SEQUENCE_OBJECTS = [
{ name: "SEQ_SEL_A", type: "sequence" as const },
{ name: "SEQ_SEL_B", type: "sequence" as const },
];
// Regression for #13115: sequences pin themselves to the top of the list while
// no columns are expected, so on an Oracle-family connection (Dameng here)
// typing the `sel` trigger listed every `SEL*` sequence above `select *` and
// Tab expanded a sequence instead of the snippet.
it("ranks an exactly matched snippet above sequences without a column context", () => {
const items = buildSqlCompletionItems("sel", 3, {
databaseType: "dameng",
tables: [],
columnsByTable: new Map(),
objects: SEQUENCE_OBJECTS,
});
expect(items[0]).toMatchObject({ type: "snippet", label: "select *", filterText: "sel" });
expect(items.filter((item) => item.type === "sequence").map((item) => item.label)).toEqual(["SEQ_SEL_A", "SEQ_SEL_B"]);
});
it("keeps sequences ahead of ordinary candidates when the prefix is only a partial match", () => {
const items = buildSqlCompletionItems("se", 2, {
databaseType: "dameng",
tables: [],
columnsByTable: new Map(),
objects: SEQUENCE_OBJECTS,
});
const firstSequence = items.findIndex((item) => item.type === "sequence");
const firstNonSequence = items.findIndex((item) => item.type !== "sequence");
expect(firstSequence).toBeGreaterThanOrEqual(0);
expect(firstSequence).toBeLessThan(firstNonSequence);
});
});