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 type { ColumnInfo, DatabaseType } from "@/types/database";
import type { SqlFormatterCase } from "@/lib/sql/sqlFormatterConfig";
import { metricRangeQuery, qualifiedTableName, quoteTableDataIdentifier, quoteTableIdentifierIfNeeded } from "@/lib/table/tableSelectSql";
export interface TableSqlTemplateOptions {
databaseType?: DatabaseType;
driverProfile?: string;
identifierQuote?: string;
schema?: string;
catalog?: string;
database?: string;
includeDatabaseName?: boolean;
/** Connection default schema; the PostgreSQL family qualifies tables outside it (#13235). */
defaultSchema?: string;
/** Omit optional identifier quotes while retaining quotes required by the dialect. */
quoteIdentifiers?: boolean;
keywordCase?: SqlFormatterCase;
tableName: string;
columns?: ColumnInfo[];
tableType?: string;
/** Number of INSERT statements to generate; values are numbered per row when greater than 1. */
rowCount?: number;
}
/** Upper bound for INSERT template row generation, shared with the row-count dialog. */
export const INSERT_TEMPLATE_MAX_ROW_COUNT = 1000;
function normalizeInsertTemplateRowCount(rowCount: number | undefined): number {
if (!rowCount || !Number.isFinite(rowCount) || rowCount <= 1) return 1;
return Math.min(Math.floor(rowCount), INSERT_TEMPLATE_MAX_ROW_COUNT);
}
function insertTemplateStatements(rowCount: number, buildStatement: (rowIndex?: number) => string): string {
if (rowCount <= 1) return buildStatement();
const statements: string[] = [];
for (let rowIndex = 0; rowIndex < rowCount; rowIndex += 1) statements.push(buildStatement(rowIndex));
return statements.join("\n\n");
}
export function buildTableSelectTemplate(options: TableSqlTemplateOptions): string {
if (options.databaseType === "victoriametrics") return metricRangeQuery(options.tableName);
const tableName = templateTableName(options);
const columns = options.columns ?? [];
const lower = options.keywordCase === "lower";
const selectKw = lower ? "select" : "SELECT";
const fromKw = lower ? "from" : "FROM";
if (!columns.length) {
return `${selectKw} *\n${fromKw} ${tableName};`;
}
const selectColumns = columns.map((column) => templateIdentifier(options, column.name));
return `${selectKw} ${selectColumns.join(", ")}\n${fromKw} ${tableName};`;
}
export function buildTableInsertTemplate(options: TableSqlTemplateOptions): string {
const strategy = options.databaseType ? insertTemplateStrategies[options.databaseType] : undefined;
return (strategy ?? buildDefaultInsertTemplate)(options);
}
type InsertTemplateStrategy = (options: TableSqlTemplateOptions) => string;
function buildDefaultInsertTemplate(options: TableSqlTemplateOptions): string {
const tableName = templateTableName(options);
const columns = (options.columns ?? []).filter((column) => !isAutoGeneratedColumn(column));
const rowCount = normalizeInsertTemplateRowCount(options.rowCount);
// TDengine does not support INSERT INTO t(col1, col2) VALUES(...) syntax
if (options.databaseType === "tdengine") {
if (!columns.length) {
return `INSERT INTO ${tableName}\nVALUES (/* TODO: add values */);`;
}
return insertTemplateStatements(rowCount, (rowIndex) => `INSERT INTO ${tableName}\nVALUES (${columns.map((column) => columnPlaceholderValue(column, options.databaseType, rowIndex)).join(", ")});`);
}
if (!columns.length) {
return `INSERT INTO ${tableName}\n/* TODO: add column values */\nVALUES ();`;
}
const columnNames = columns.map((column) => templateIdentifier(options, column.name));
return insertTemplateStatements(rowCount, (rowIndex) => `INSERT INTO ${tableName} (${columnNames.join(", ")})\nVALUES (${columns.map((column) => columnPlaceholderValue(column, options.databaseType, rowIndex)).join(", ")});`);
}
function buildTdengineInsertTemplate(options: TableSqlTemplateOptions): string {
const rowCount = normalizeInsertTemplateRowCount(options.rowCount);
const columns = options.columns ?? [];
const isTagColumn = (column: ColumnInfo) => (column.comment ?? "").toUpperCase() === "TAG" || (column.extra ?? "").toUpperCase().includes("TAG");
const tagColumns = columns.filter((column) => isTagColumn(column));
const normalizedTableType = (options.tableType ?? "").toUpperCase();
const shouldUseStableTemplate = normalizedTableType === "STABLE" || (!normalizedTableType && tagColumns.length > 0);
if (!shouldUseStableTemplate) {
return buildDefaultInsertTemplate(options);
}
const valueColumns = columns.filter((column) => !isTagColumn(column)).filter((column) => !isAutoGeneratedColumn(column));
const tagPlaceholders = tagColumns.map((column) => columnPlaceholderValue(column, options.databaseType));
const tagsSql = tagPlaceholders.length > 0 ? `TAGS (${tagPlaceholders.join(", ")})` : "TAGS (/* TODO: add tag values */)";
const stableNameForUsing = templateTableName(options).replace(/^`|`$/g, "");
if (!valueColumns.length) {
return `INSERT INTO /* child_table_name */ USING ${stableNameForUsing}\n${tagsSql}\nVALUES (/* TODO: add values */);`;
}
return insertTemplateStatements(rowCount, (rowIndex) => `INSERT INTO /* child_table_name */ USING ${stableNameForUsing}\n${tagsSql}\nVALUES (${valueColumns.map((column) => columnPlaceholderValue(column, options.databaseType, rowIndex)).join(", ")});`);
}
const insertTemplateStrategies: Partial<Record<DatabaseType, InsertTemplateStrategy>> = {
tdengine: buildTdengineInsertTemplate,
};
export function buildTableUpdateTemplate(options: TableSqlTemplateOptions): string {
const tableName = templateTableName(options);
const columns = options.columns ?? [];
const primaryKeys = columns.filter((column) => column.is_primary_key);
const primaryKeyNames = new Set(primaryKeys.map((column) => column.name));
const updateColumns = columns.filter((column) => !primaryKeyNames.has(column.name));
const setClause = updateColumns.length ? updateColumns.map((column) => `${templateIdentifier(options, column.name)} = ${columnPlaceholderValue(column, options.databaseType)}`).join(",\n ") : "/* TODO: set column = value */";
return `UPDATE ${tableName}\nSET ${setClause}\n${buildWhereClause(options, primaryKeys)};`;
}
export function buildTableDeleteTemplate(options: TableSqlTemplateOptions): string {
const tableName = templateTableName(options);
const primaryKeys = (options.columns ?? []).filter((column) => column.is_primary_key);
return `DELETE FROM ${tableName}\n${buildWhereClause(options, primaryKeys)};`;
}
export interface InsertTemplatePkConflict {
column: string;
reason: "numeric-cap" | "static-value";
cap?: number;
}
/**
* Primary-key columns that cannot hold `rowCount` distinct placeholder values:
* narrow numeric keys capped below the row count, and date/time keys whose
* placeholder stays static across rows. Auto-generated keys are skipped
* because they are omitted from the generated INSERT column list.
*/
export function insertTemplatePrimaryKeyConflicts(columns: ColumnInfo[], rowCount: number): InsertTemplatePkConflict[] {
if (rowCount <= 1) return [];
const conflicts: InsertTemplatePkConflict[] = [];
for (const column of columns) {
if (!column.is_primary_key || isAutoGeneratedColumn(column)) continue;
const dataType = (column.data_type ?? "").trim();
if (typeLooksNumeric(dataType)) {
const cap = numericRowValueCap(column);
if (cap !== null && cap < rowCount) conflicts.push({ column: column.name, reason: "numeric-cap", cap });
} else if (typeLooksDateOnly(dataType) || typeLooksTimeOnly(dataType) || typeLooksTimestamp(dataType)) {
conflicts.push({ column: column.name, reason: "static-value" });
}
}
return conflicts;
}
function templateTableName(options: TableSqlTemplateOptions): string {
return qualifiedTableName({
databaseType: options.databaseType,
driverProfile: options.driverProfile,
identifierQuote: options.identifierQuote,
catalog: options.catalog,
database: options.database,
includeDatabaseName: options.includeDatabaseName,
defaultSchema: options.defaultSchema,
quoteIdentifiers: options.quoteIdentifiers,
schema: options.schema,
tableName: options.tableName,
});
}
function templateIdentifier(options: TableSqlTemplateOptions, name: string): string {
return options.quoteIdentifiers === false ? quoteTableIdentifierIfNeeded(options.databaseType, name, options.identifierQuote) : quoteTableDataIdentifier(options.databaseType, name, options.identifierQuote);
}
function buildWhereClause(options: TableSqlTemplateOptions, primaryKeys: ColumnInfo[]): string {
if (!primaryKeys.length) return "WHERE /* TODO: add WHERE clause */";
const conditions = primaryKeys.map((column) => `${templateIdentifier(options, column.name)} = ${columnPlaceholderValue(column, options.databaseType)}`);
return `WHERE ${conditions.join(" AND ")}`;
}
function isAutoGeneratedColumn(column: ColumnInfo): boolean {
const extra = (column.extra ?? "").toLowerCase().trim();
const colDefault = (column.column_default ?? "").toLowerCase().trim();
const dataType = (column.data_type ?? "").trim();
if (typeLooksPostgresTextSearchVector(dataType)) return true;
if (extra.includes("auto_increment")) return true;
if (extra.includes("autoincrement")) return true;
if (extra.includes("identity")) return true;
if (extra.includes("generated") && (extra.includes("as identity") || extra.includes("always as"))) return true;
if (colDefault.includes("nextval(")) return true;
if (colDefault.includes(".nextval")) return true;
if ((extra.includes("virtual") || extra.includes("computed")) && extra.includes("generated")) return true;
return false;
}
function columnPlaceholderValue(column: ColumnInfo, databaseType?: DatabaseType, rowIndex?: number): string {
const dataType = (column.data_type ?? "").trim();
const colName = column.name;
const rowNumber = rowIndex === undefined ? undefined : rowIndex + 1;
if (typeLooksNumeric(dataType)) {
if (rowNumber === undefined) return "0";
const cap = numericRowValueCap(column);
return String(cap === null ? rowNumber : Math.min(rowNumber, cap));
}
if (typeLooksBoolean(dataType)) return databaseType === "mysql" || databaseType === "sqlite" || databaseType === "rqlite" || databaseType === "cloudflare-d1" ? "1" : "TRUE";
if (typeLooksDateOnly(dataType)) return "'2024-01-01'";
if (typeLooksTimeOnly(dataType)) return "'12:00:00'";
if (typeLooksTimestamp(dataType)) return databaseType === "tdengine" ? "NOW" : "CURRENT_TIMESTAMP";
if (typeLooksJson(dataType)) return databaseType === "postgres" || databaseType === "redshift" ? "'{}'::jsonb" : "'{}'";
if (typeLooksBinary(dataType)) return "''";
if (typeLooksUuid(dataType)) {
const uuid = rowNumber === undefined ? "00000000-0000-0000-0000-000000000000" : randomUuidValue();
return databaseType === "postgres" || databaseType === "redshift" ? `'${uuid}'::uuid` : `'${uuid}'`;
}
if (typeLooksXml(dataType)) return "'<root/>'";
if (typeLooksSpatial(dataType)) return "NULL";
if (typeLooksArray(dataType)) return "'{}'";
return stringPlaceholderValue(colName, rowNumber, column.character_maximum_length);
}
// Byte-aware budget so byte-length columns (Oracle VARCHAR2 N BYTE) stay safe
// even when the column name carries multi-byte characters.
function utf8Length(value: string): number {
let length = 0;
for (const char of value) {
const code = char.codePointAt(0)!;
length += code < 0x80 ? 1 : code < 0x800 ? 2 : code < 0x10000 ? 3 : 4;
}
return length;
}
/**
* `col_value[_row]` placeholder clipped to the column's declared length: the
* name part shrinks so the row-suffix (the per-row distinct part) survives.
* When even the suffix cannot fit, degrade to the bare row number.
*/
function stringPlaceholderValue(colName: string, rowNumber: number | undefined, maxLength: number | null | undefined): string {
const suffix = `_value${rowNumber === undefined ? "" : `_${rowNumber}`}`;
const limit = typeof maxLength === "number" && Number.isFinite(maxLength) && maxLength > 0 ? maxLength : null;
if (limit === null) return `'${colName}${suffix}'`;
const suffixLength = utf8Length(suffix);
if (suffixLength > limit) {
if (rowNumber === undefined) return `'${"_value".slice(0, limit)}'`;
const fallback = String(rowNumber);
return `'${utf8Length(fallback) <= limit ? fallback : [...fallback].slice(0, Math.max(1, limit)).join("")}'`;
}
const nameBudget = limit - suffixLength;
let name = "";
let used = 0;
for (const char of colName) {
const charLength = utf8Length(char);
if (used + charLength > nameBudget) break;
name += char;
used += charLength;
}
return `'${name}${suffix}'`;
}
function randomUuidValue(): string {
const uuid = globalThis.crypto?.randomUUID?.();
if (uuid) return uuid;
return "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx".replace(/[xy]/g, (char) => {
const random = (Math.random() * 16) | 0;
const value = char === "x" ? random : (random & 0x3) | 0x8;
return value.toString(16);
});
}
// Unsigned metadata is not exposed per column, so narrow integer types use the
// conservative signed upper bound when capping per-row placeholder numbers.
function numericRowValueCap(column: ColumnInfo): number | null {
const dataType = (column.data_type ?? "").trim().toLowerCase();
if (dataType === "tinyint" || dataType === "int8") return 127;
const precision = column.numeric_precision;
if (typeof precision !== "number" || !Number.isFinite(precision) || precision <= 0) return null;
const scale = typeof column.numeric_scale === "number" && Number.isFinite(column.numeric_scale) && column.numeric_scale > 0 ? column.numeric_scale : 0;
const integerDigits = precision - scale;
// A pure-fraction column (scale >= precision) cannot hold any nonzero integer; keep 0 so statements stay valid.
if (integerDigits <= 0) return 0;
return 10 ** integerDigits - 1;
}
function typeLooksNumeric(dataType: string): boolean {
return /^(tinyint|smallint|mediumint|int\d*|integer\d*|bigint|smallserial|serial\d*|bigserial|number|numeric|decimal|dec|float\d*|double|real|double\s+precision|money|smallmoney|fixed)/i.test(dataType.trim());
}
function typeLooksBoolean(dataType: string): boolean {
return /^(bool|boolean|bit\s*\(\s*1\s*\))$/i.test(dataType.trim());
}
function typeLooksDateOnly(dataType: string): boolean {
return /^date$/i.test(dataType.trim());
}
function typeLooksTimeOnly(dataType: string): boolean {
return /^time\b(?!stamp|stamptz|with|without)/i.test(dataType.trim());
}
function typeLooksTimestamp(dataType: string): boolean {
return /^(datetime|timestamp|timestamptz|timestamp\s+with\s+time\s+zone|timestamp\s+without\s+time\s+zone|smalldatetime|datetime2|datetimeoffset)/i.test(dataType.trim());
}
function typeLooksJson(dataType: string): boolean {
return /^(json|jsonb)$/i.test(dataType.trim());
}
function typeLooksBinary(dataType: string): boolean {
return /^(bytea|blob|binary|varbinary|image|raw|longblob|mediumblob|tinyblob|long\s+raw|bfile)$/i.test(dataType.trim());
}
function typeLooksUuid(dataType: string): boolean {
return /^uuid$/i.test(dataType.trim());
}
function typeLooksXml(dataType: string): boolean {
return /^xml$/i.test(dataType.trim());
}
function typeLooksSpatial(dataType: string): boolean {
return /^(geometry|geography|point|polygon|linestring|box|circle|path|line|lseg)/i.test(dataType.trim());
}
function typeLooksArray(dataType: string): boolean {
return /^(array|vector|_float\d*|_int\d*|_text|_varchar|_bool|_uuid)/i.test(dataType.trim());
}
function typeLooksPostgresTextSearchVector(dataType: string): boolean {
const normalized = dataType
.trim()
.replace(/^"+|"+$/g, "")
.toLowerCase();
return normalized === "tsvector" || normalized.endsWith(".tsvector");
}