singlestore-drizzle

SingleStore and Drizzle Integration Guidelines

Overview

This guide covers the specific integration patterns and optimizations for using Drizzle ORM with SingleStore databases. Follow these guidelines to ensure efficient database operations with SingleStore's high-performance capabilities.

Dependencies

For SingleStore with Drizzle ORM integration, include these specific dependencies:

npm install drizzle-orm mysql2 dotenv
npm install -D drizzle-kit

Note: Ensure that the drizzle-orm version is greater or equal to 0.44.2 and drizzle-kit is greater or equal to ^0.31.0 to ensure compatibility with SingleStore.

Script commands

Migrations

To generate new migrations and apply them to the database, use the following commands:

# Generate new migrations
npx drizzle-kit generate

# Apply migrations to the database
npx drizzle-kit push

SingleStore Connection Configuration

• Always use the SingleStore connection string format:

DATABASE_URL=singlestore://username:password@host:port/database?ssl={}

Important: Keep the ssl={} at the end of the string, otherwise the connection won't work

• Store this in .env or .env.local file

SingleStore Connection Setup

When connecting to SingleStore:

• Use mysql2 to establish a connection to the SingleStore database
• Use the singlestore drizzle driver from drizzle-orm/singlestore to create the Drizzle instance

// src/index.ts
import dotenv from "dotenv";

import mysql from "mysql2/promise";
import { drizzle } from "drizzle-orm/singlestore";
import * as schema from './db/schema';

// Load environment variables from .env file
dotenv.config();

if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is not defined');
}

const pool = mysql.createPool(process.env.DATABASE_URL);

const db = drizzle({ client: pool, schema});

SingleStore Database Considerations

Connection Management

• For queries: Use either client or pool based on your needs but prefer connection pooling for high-traffic applications