or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

cli-commands.mdconfiguration.mdindex.mdmysql-api.mdpostgresql-api.mdsinglestore-api.mdsqlite-api.md
tile.json

singlestore-api.mddocs/

SingleStore Programmatic API

Programmatic API for SingleStore database operations including snapshot generation, migration creation, schema pushing, and Drizzle Studio server management.

Capabilities

Generate SingleStore Snapshot

Generates a SingleStore schema snapshot from TypeScript schema imports.

/**
 * Generate SingleStore snapshot from schema imports
 * @param imports - Record of schema module exports
 * @param prevId - Previous snapshot ID (optional)
 * @param casing - Serialization casing (optional)
 * @returns SingleStore schema snapshot
 */
async function generateSingleStoreDrizzleJson(
  imports: Record<string, unknown>,
  prevId?: string,
  casing?: 'camelCase' | 'snake_case'
): Promise<SingleStoreSchemaKit>;

Usage Example:

import { generateSingleStoreDrizzleJson } from 'drizzle-kit/api';
import * as schema from './schema';

// Generate snapshot
const snapshot = await generateSingleStoreDrizzleJson(schema);

// With previous ID (for migration chain)
const snapshot2 = await generateSingleStoreDrizzleJson(schema, snapshot.id);

// With snake_case naming
const snakeCase = await generateSingleStoreDrizzleJson(schema, undefined, 'snake_case');

Generate SingleStore Migration

Generates SQL migration statements by comparing two SingleStore snapshots.

/**
 * Generate SQL migration from two SingleStore snapshots
 * @param prev - Previous snapshot
 * @param cur - Current snapshot
 * @returns Array of SQL statements
 */
function generateSingleStoreMigration(
  prev: DrizzleSingleStoreSnapshotJSON,
  cur: DrizzleSingleStoreSnapshotJSON
): Promise<string[]>;

Usage Example:

import {
  generateSingleStoreDrizzleJson,
  generateSingleStoreMigration,
} from 'drizzle-kit/api';
import * as oldSchema from './old-schema';
import * as newSchema from './new-schema';

// Generate snapshots
const prevSnapshot = generateSingleStoreDrizzleJson(oldSchema);
const curSnapshot = generateSingleStoreDrizzleJson(newSchema);

// Generate migration SQL
const sqlStatements = await generateSingleStoreMigration(
  prevSnapshot,
  curSnapshot
);

// Execute or save migration
console.log(sqlStatements.join('\n'));

Push SingleStore Schema

Pushes schema changes directly to a SingleStore database.

/**
 * Push schema changes to SingleStore database
 * @param imports - Record of schema module exports
 * @param drizzleInstance - Drizzle SingleStore database instance
 * @param databaseName - Name of the database
 * @returns Push result with warnings and apply function
 */
function pushSingleStoreSchema(
  imports: Record<string, unknown>,
  drizzleInstance: SingleStoreDriverDatabase<any>,
  databaseName: string
): Promise<PushResult>;

interface PushResult {
  hasDataLoss: boolean;
  warnings: string[];
  statementsToExecute: string[];
  apply: () => Promise<void>;
}

Usage Example:

import { pushSingleStoreSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/singlestore';
import mysql from 'mysql2/promise';
import * as schema from './schema';

// Create SingleStore connection
const connection = await mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: 'password',
  database: 'mydb',
});

// Create Drizzle instance
const db = drizzle(connection);

// Push schema
const result = await pushSingleStoreSchema(schema, db, 'mydb');

// Check for warnings
if (result.hasDataLoss) {
  console.warn('Warning: Data loss may occur!');
  result.warnings.forEach(w => console.warn(w));
}

// Review statements
console.log('Statements to execute:');
result.statementsToExecute.forEach(s => console.log(s));

// Apply changes
if (confirm('Apply changes?')) {
  await result.apply();
  console.log('Schema pushed successfully');
}

// Clean up
await connection.end();

Start SingleStore Studio Server

Starts Drizzle Studio server for SingleStore database.

/**
 * Start Drizzle Studio server for SingleStore
 * @param imports - Record of schema module exports
 * @param credentials - SingleStore connection credentials
 * @param options - Server options (optional)
 */
function startStudioSingleStoreServer(
  imports: Record<string, unknown>,
  credentials: SingleStoreCredentials,
  options?: {
    host?: string;
    port?: number;
    casing?: 'camelCase' | 'snake_case';
  }
): Promise<void>;

Usage Example:

import { startStudioSingleStoreServer } from 'drizzle-kit/api';
import * as schema from './schema';

// Start Studio with connection URL
await startStudioSingleStoreServer(
  schema,
  {
    url: process.env.DATABASE_URL,
  }
);

// Start with connection parameters
const customServer = await startStudioSingleStoreServer(
  schema,
  {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'password',
    database: 'mydb',
  },
  {
    host: '0.0.0.0',
    port: 4983,
    casing: 'camelCase',
  }
);

console.log('Studio started at http://localhost:4983');

// With SSL
const sslServer = await startStudioSingleStoreServer(
  schema,
  {
    host: 'remote-host',
    database: 'mydb',
    ssl: {
      ca: '/path/to/ca-cert.pem',
      rejectUnauthorized: true,
    },
  }
);

Types

DrizzleSingleStoreSnapshotJSON

SingleStore schema snapshot type (alias for SingleStoreSchemaKit).

type DrizzleSingleStoreSnapshotJSON = SingleStoreSchemaKit;

interface SingleStoreSchemaKit {
  id: string;
  prevId: string;
  version: string;
  dialect: 'singlestore';
  tables: Record<string, Table>;
  _meta: {
    tables: Record<string, string>;
    columns: Record<string, string>;
  };
}

Credentials

SingleStore connection credentials with multiple variants.

type SingleStoreCredentials =
  // Standard connection
  | {
      host: string;
      port?: number;              // Default: 3306
      user?: string;
      password?: string;
      database: string;
      ssl?: string | SslOptions;
    }
  // Connection URL
  | {
      url: string;
    };

interface SslOptions {
  pfx?: string;                   // PFX/PKCS12 encoded private key and certificate
  key?: string;                   // Private key in PEM format
  passphrase?: string;            // Passphrase for private key
  cert?: string;                  // Certificate chain in PEM format
  ca?: string | string[];         // CA certificates in PEM format
  crl?: string | string[];        // Certificate revocation lists
  ciphers?: string;               // Cipher suite specification
  rejectUnauthorized?: boolean;   // Verify server certificate (default: true)
}

Schema Components

interface Table {
  name: string;
  columns: Record<string, Column>;
  indexes: Record<string, Index>;
  primaryKeys: Record<string, PrimaryKey>;
  uniqueConstraints: Record<string, UniqueConstraint>;
}

interface Column {
  name: string;
  type: string;
  primaryKey: boolean;
  notNull: boolean;
  autoincrement: boolean;
  default?: string;
  onUpdate?: string;
  generated?: {
    as: string;
    type: 'stored' | 'virtual';
  };
}

interface Index {
  name: string;
  columns: string[];
  isUnique: boolean;
  using?: 'btree' | 'hash';
}

interface PrimaryKey {
  name: string;
  columns: string[];
}

interface UniqueConstraint {
  name: string;
  columns: string[];
}

Supported Features

SingleStore API supports:

  • Tables: All SingleStore column types, auto_increment
  • Indexes: B-tree and hash indexes
  • Constraints: Primary keys, unique constraints
  • Generated Columns: Virtual and stored generated columns
  • ON UPDATE: Automatic timestamp updates

Note: SingleStore does not support:

  • Foreign keys (SingleStore has limited foreign key support)
  • Check constraints (not supported in SingleStore)
  • Views (not included in API)
  • Enums (use VARCHAR with application-level validation)
  • Schemas/namespaces (single database)
  • Sequences (use AUTO_INCREMENT)

Common Patterns

Custom Migration Tool

import {
  generateSingleStoreDrizzleJson,
  generateSingleStoreMigration,
} from 'drizzle-kit/api';
import { writeFileSync, readFileSync } from 'fs';
import * as schema from './schema';

async function createMigration(name: string) {
  // Load previous snapshot
  const prevSnapshot = JSON.parse(
    readFileSync('./snapshots/latest.json', 'utf8')
  );

  // Generate current snapshot
  const curSnapshot = generateSingleStoreDrizzleJson(schema);

  // Generate migration
  const sql = await generateSingleStoreMigration(prevSnapshot, curSnapshot);

  // Save migration file with breakpoints
  const timestamp = Date.now();
  const migrationContent = sql.join('\n--> statement-breakpoint\n');
  writeFileSync(
    `./migrations/${timestamp}_${name}.sql`,
    migrationContent
  );

  // Update latest snapshot
  writeFileSync(
    './snapshots/latest.json',
    JSON.stringify(curSnapshot, null, 2)
  );

  console.log(`Migration created: ${timestamp}_${name}.sql`);
}

Schema Validation

import { pushSingleStoreSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/singlestore';
import mysql from 'mysql2/promise';
import * as schema from './schema';

async function validateSchema() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  });

  const db = drizzle(connection);

  // Push schema in dry-run mode
  const result = await pushSingleStoreSchema(schema, db, process.env.DB_NAME!);

  // Check if schema is in sync
  if (result.statementsToExecute.length === 0) {
    console.log('✓ Schema is in sync');
    await connection.end();
    return true;
  }

  // Report differences
  console.log('✗ Schema is out of sync');
  console.log('Required changes:');
  result.statementsToExecute.forEach(stmt => console.log(`  ${stmt}`));

  await connection.end();
  return false;
}

Connection Pool Integration

import { pushSingleStoreSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/singlestore';
import mysql from 'mysql2/promise';
import * as schema from './schema';

// Create connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  connectionLimit: 10,
});

const db = drizzle(pool);

// Push schema using pool
const result = await pushSingleStoreSchema(schema, db, 'mydb');

if (!result.hasDataLoss) {
  await result.apply();
}

// Pool remains open for application use

SSL/TLS Connection

import { startStudioSingleStoreServer } from 'drizzle-kit/api';
import { readFileSync } from 'fs';
import * as schema from './schema';

const server = await startStudioSingleStoreServer(
  schema,
  {
    host: 'remote-host',
    port: 3306,
    user: 'dbuser',
    password: process.env.DB_PASSWORD,
    database: 'production',
    ssl: {
      ca: readFileSync('/path/to/ca-cert.pem', 'utf8'),
      cert: readFileSync('/path/to/client-cert.pem', 'utf8'),
      key: readFileSync('/path/to/client-key.pem', 'utf8'),
      rejectUnauthorized: true,
    },
  }
);

Migration Breakpoints

SingleStore requires statement breakpoints because it doesn't support transactional DDL:

import { generateSingleStoreMigration } from 'drizzle-kit/api';

// Generate migration
const statements = await generateSingleStoreMigration(prev, cur);

// Add breakpoints for sequential execution
const migration = statements.join('\n--> statement-breakpoint\n');

// Each statement will be executed separately
// This ensures partial success if a statement fails

High-Performance Applications

SingleStore is optimized for high-performance analytics and transactional workloads:

import { pushSingleStoreSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/singlestore';
import mysql from 'mysql2/promise';
import * as schema from './schema';

// Create connection with performance settings
const connection = await mysql.createConnection({
  host: 'singlestore-host',
  port: 3306,
  user: 'admin',
  password: process.env.PASSWORD,
  database: 'analytics',
  // SingleStore-specific optimizations
  multipleStatements: false,
  connectTimeout: 30000,
});

const db = drizzle(connection);

// Push schema optimized for SingleStore
const result = await pushSingleStoreSchema(schema, db, 'analytics');

// SingleStore handles large-scale data efficiently
if (!result.hasDataLoss) {
  await result.apply();
  console.log('Schema deployed to SingleStore cluster');
}

Distributed Database Support

SingleStore's distributed architecture works seamlessly with Drizzle Kit:

import { generateSingleStoreDrizzleJson } from 'drizzle-kit/api';
import * as schema from './schema';

// Generate snapshot
const snapshot = generateSingleStoreDrizzleJson(schema);

// SingleStore automatically distributes tables across cluster
// No special configuration needed in Drizzle Kit
// Tables with shard keys are automatically distributed

SingleStore-Specific Notes

No Foreign Keys

SingleStore has limited foreign key support. Use application-level validation instead:

// Define relationships in Drizzle ORM schema
// but they won't be enforced at database level in SingleStore

Columnstore vs Rowstore

SingleStore supports both columnstore and rowstore tables. Define table storage type in your Drizzle ORM schema, and Drizzle Kit will preserve it during migrations.

Distributed Architecture

SingleStore's distributed architecture means:

  • Tables are automatically sharded across nodes
  • Schema changes are propagated to all nodes
  • Migrations may take longer on large clusters
  • Use --verbose flag to monitor progress

Performance Considerations

  • SingleStore performs DDL operations faster than traditional databases
  • Large table alterations are handled efficiently
  • Consider using staging environments for testing schema changes on production-scale data