SingleStore Dialect

SingleStore Dialect Reference documentation for the singlestore dialect. Source: singlestore Tests: singlestore, singlestore-crud Base: mysql 1. Overview SingleStore (formerly MemSQL) is a distributed, MySQL-wire-compatible database. Because it speaks the MySQL protocol, SingleStoreDialect extends MySQLDialect and reuses the exact same mysql2 driver, connection pooling, query execution, escaping, and the WHERE / ORDER / INSERT / UPDATE / DELETE / SELECT builders. Only the grammar that genuinely differs from stock MySQL is overridden. Concretely, the dialect: reports name === 'singlestore' but keeps library === 'mysql2' (verified in tests: dialect.name is 'singlestore', dialect.library is 'mysql2'); defaults the SQL port to 3306 — the constructor calls super({ port: 3306, ...config }), so the MySQL wire port is used unless you override it; overrides only: CREATE TABLE grammar (storage kind, SHARD KEY, SORT KEY, clustered columnstore key, reference tables), the upsert form, the GEOGRAPHY type mapping, and the foreign-key handling. SingleStoreDialectOptions is declared as interface SingleStoreDialectOptions extends MySQLDialectOptions {} — the connection options are identical to MySQL's, since the wire protocol and driver are shared. 2. Connection 'singlestore' is a registered dialect string in Prorm.createDialect; it is lazy-loaded (require('./dialects/singlestore').SingleStoreDialect) so the mysql2 driver is only required when the dialect is actually used. If port is omitted, the dialect constructor supplies 3306. 3. SingleStore-specific features These are configured through SingleStoreTableOptions, which is intersected with the shared TableOptions and passed to createTable(...) / buildCreateTableSQL(...): buildCreateTableSQL(tableName, columns, options) is a synchronous, no-I/O builder (so the grammar can be inspected/tested without a live connection); createTable(...) delegates to it and then creates any secondary indexes. SHARD KEY and SORT KEY (columnstore) SingleStore is distributed and shards data across leaf nodes by a shard key; columnstore tables also store rows ordered by a sort key. Emits (columnstore is the default — no ROWSTORE/REFERENCE keyword): (Tests assert the output contains CREATE TABLE events , SHARD KEY (userid) , and SORT KEY (createdat) . DATE maps to DATETIME — see the type table.) Keyless SHARD KEY (random sharding) An empty shardKey array emits the keyless SHARD KEY () form, which requests random sharding (also how a plain distributed table with no explicit shard key is declared). Note shardKey is emitted whenever it is not undefined, so [] still produces a clause: ROWSTORE tables SingleStore tables are columnstore by default. { rowstore: true } requests an in-memory rowstore table: REFERENCE (replicated) tables { reference: true } emits CREATE REFERENCE TABLE. Reference tables are fully replicated to every node rather than sharded — typically small dimension tables joined against large sharded fact tables (so a shard key is normally omitted): USING CLUSTERED COLUMNSTORE key { columnstoreKey: { fields: [...] } } emits the explicit clustered-columnstore key form. It is only emitted when fields is non-empty: Clause ordering Within the parenthesised body, buildCreateTableSQL appends parts in this order: column definitions → table constraints (PK/UNIQUE/CHECK) → SHARD KEY (...) → SORT KEY (...) → KEY (...) USING CLUSTERED COLUMNSTORE. Table-level options (DEFAULT CHARSET=, COLLATE, COMMENT=, and IF NOT EXISTS) are inherited-style and handled after the body. 4. Caveats Foreign keys are not supported — the dialect refuses to emit FK SQL SingleStore does not support foreign key constraints (a real server rejects FOREIGN KEY ... REFERENCES ...). Rather than generate statements that would fail, the dialect handles FKs as follows: CREATE TABLE, table-level FOREIGN KEY constraint — silently omitted. The private buildTableConstraintSQL returns null for FOREIGN KEY (PRIMARY KEY / UNIQUE / CHECK are emitted normally), so the table is still created, just without the FK clause. A test passes a FOREIGN KEY constraint and asserts the output contains neither FOREIGN KEY nor REFERENCES. CREATE TABLE, column-level references — also omitted. buildColumnSQL never emits a REFERENCES clause, so a column definition carrying references produces no REFERENCES in the DDL (verified by test). addColumn / changeColumn — strip any column-level references (via stripReferences) before delegating to the MySQL base, whose column builder would otherwise emit a REFERENCES ... clause SingleStore rejects. addForeignKey(...) — throws: SingleStore does not support foreign key constraints. Enforce referential integrity in application code instead. createConstraint(...) with type: 'FOREIGN KEY' — throws the same error. Other constraint types delegate to the MySQL base. Enforce referential integrity in application code instead. Unique constraints on columnstore tables must include the shard key This is a SingleStore data-distribution rule (a unique index on a columnstore table must contain the shard key columns). As documented in the source header, the ORM does not attempt to police this — it will emit the UNIQUE (...) you ask for; a real server enforces the rule. Plan your UNIQUE constraints/shardKey accordingly. No RETURNING clause Like MySQL, SingleStore has no RETURNING clause. buildUpsertQuery(..., { returning: true }) fails fast with an error matching /RETURNING/; re-fetch the row after the upsert instead. 5. Inherited MySQL behavior CRUD and standard DDL are inherited from MySQLDialect and behave identically, using backtick identifier quoting and ? placeholders. Verified in the CRUD tests: INSERT Literal values are inlined without a placeholder: UPDATE / DELETE / INCREMENT SELECT — inherited WHERE (AND / Op.or), ORDER BY, GROUP BY / HAVING, and LIMIT/OFFSET. MySQL's offset-only semantics are preserved: buildLimitOffset(undefined, 10) yields LIMIT 18446744073709551615 OFFSET 10. Standard DDL — IF NOT EXISTS, DEFAULT CHARSET=, COLLATE, and table COMMENT= all work through buildCreateTableSQL: PRIMARY KEY / UNIQUE / CHECK table constraints emit normally alongside a shard key: Upsert: ON DUPLICATE KEY UPDATE with the VALUES(col) form This is the one CRUD override. The MySQL base emits the MySQL 8.0.19 row-alias form (... AS newvals ... = newvals.col), which SingleStore does not support. SingleStoreDialect overrides both buildInsertQuery (upsert branch) and buildUpsertQuery to emit the classic VALUES(col) reference instead: updateOnDuplicate restricts the SET list; otherwise all inserted columns are updated: 6. Type mapping Scalar types are mapped exactly as in MySQL (getDataTypeSql calls super.getDataTypeSql) — with one override: the geospatial GEOGRAPHY type. SingleStore has a native GEOGRAPHY type, whereas stock MySQL has none and the base dialect degrades it to GEOMETRY. The dialect intercepts { key: 'GEOGRAPHY' } and returns the native type so geography columns round-trip correctly. ORM type (DataTypes. / { key }) | SingleStore SQL | Notes | --- | --- | --- | STRING | VARCHAR(255) | inherited from MySQL | BIGINT | BIGINT | inherited | INTEGER | INT | inherited | DOUBLE | DOUBLE | inherited | BOOLEAN | TINYINT(1) | inherited | JSON | JSON | inherited | DATE | DATETIME | inherited | UUID | CHAR(36) | inherited | TEXT | TEXT | inherited | DECIMAL (precision 10, scale 2) | DECIMAL(10,2) | inherited | GEOMETRY | GEOMETRY | inherited (unchanged) | GEOGRAPHY | GEOGRAPHY | SingleStore override (MySQL base would emit GEOMETRY) | All rows except GEOGRAPHY are asserted directly in the type-mapping tests; the GEOGRAPHY override and the GEOMETRY contrast are also asserted. The source header also mentions GEOGRAPHYPOINT as a SingleStore native type, but only the GEOGRAPHY key is intercepted in getDataTypeSql; there is no dedicated GEOGRAPHYPOINT mapping in the code. 7. Not yet verified against a live server Everything above about SQL string generation is verified by the unit tests, which exercise only the synchronous builders (buildCreateTableSQL, buildInsertQuery, buildUpsertQuery, buildSelectQuery, buildUpdateQuery, buildDeleteQuery, buildIncrementQuery, getDataTypeSql, buildLimitOffset) plus the FK-rejection error paths. No test opens a connection (connect() is never called). The following are not exercised against a real SingleStore instance and should be validated in a live environment before relying on them: Connectivity and the mysql2 driver against a real cluster — pool creation, authentication, retries, and query execution are inherited from the MySQL dialect and untested here against SingleStore specifically. Server acceptance of the generated DDL — that a live SingleStore server accepts SHARD KEY, SORT KEY, KEY (...) USING CLUSTERED COLUMNSTORE, CREATE ROWSTORE TABLE, and CREATE REFERENCE TABLE exactly as emitted. Data-distribution rules — that a UNIQUE constraint including the shard key is accepted, and that one omitting it is rejected by the server (the ORM does not police this). Runtime behavior of VALUES(col) upserts, GEOGRAPHY round-tripping, and the FK omission — that omitting foreign keys behaves acceptably for your integrity needs, and that the columnstore/rowstore/reference semantics match expectations at query time. Introspection helpers inherited from MySQL (showTables, describeTable, getCreateTable, showIndexes, showConstraints, INFORMATIONSCHEMA-based partition helpers, etc.) — these assume MySQL system-catalog shapes and have not been checked against SingleStore. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL