Drizzle <> SingleStore

To use Drizzle with a SingleStore database, you should use the mysql2 driver

Drizzle ORM natively supports mysql2 with drizzle-orm/singlestore package.

Step 1 - Install packages

npm i drizzle-orm mysql2
npm i -D drizzle-kit

yarn add drizzle-orm mysql2
yarn add -D drizzle-kit

pnpm add drizzle-orm mysql2
pnpm add -D drizzle-kit

bun add drizzle-orm mysql2
bun add -D drizzle-kit

Step 2 - Initialize the driver and make a query

import { drizzle } from "drizzle-orm/singlestore";

const db = drizzle(process.env.DATABASE_URL);

const response = await db.select().from(...)

import { drizzle } from "drizzle-orm/singlestore";

// You can specify any property from the mysql2 connection options
const db = drizzle({ connection:{ uri: process.env.DATABASE_URL }});

const response = await db.select().from(...)

If you need to provide your existing driver:

import { drizzle } from "drizzle-orm/singlestore";
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "database",
  ...
});

const db = drizzle({ client: connection });

import { drizzle } from "drizzle-orm/singlestore";
import mysql from "mysql2/promise";

const poolConnection = mysql.createPool({
  host: "host",
  user: "user",
  database: "database",
  ...
});

const db = drizzle({ client: poolConnection });

Limitations

Currently, the SingleStore dialect has a set of limitations and features that do not work on the SingleStore database side:

• SingleStore’s serial column type only ensures the uniqueness of column values.
• ORDER BY and LIMIT cannot be chained together.
• Foreign keys are not supported (check).
• INTERSECT ALL and EXCEPT ALL operations are not supported by SingleStore.
• Nested transactions are not supported by SingleStore.
• SingleStore only supports one isolationLevel.
• The FSP option in DATE, TIMESTAMP, and DATETIME is not supported.
• The relational API is not supported and will be implemented once the SingleStore team develops all the necessary APIs for it.
• There may be more limitations because SingleStore is not 100% compatible with MySQL.

What’s next?