SingleStore Drizzle ORM Driver

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

Prerequisites

• Install npm.

• Install the mysql2 package.

• Install Drizzle Kit to manage database migrations using Drizzle.

• (Optional) Install dotenv package to manage environment variables.

• (Optional) Install tsx to run TypeScript.

To install using npm:

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

Connect to SingleStore using Drizzle ORM

To connect your Drizzle ORM application (built with Node.js) to SingleStore, initialize the Drizzle client from the SingleStore driver and pass the connection configuration to a mysql2 connection.

Required connection configurations:

• <hostname>: Hostname or IP address of the SingleStore deployment. Refer to SingleStore Endpoints for more information.

• <port>: Port of the SingleStore deployment.

• <username>: Name of the SingleStore database user with which to access the database.

• <password>: Password for the SingleStore database user.

Example

The following example connects to a SingleStore database named dbTest, creates a table named Stock, and inserts data into the table.

1. Install the required packages.

   Shell

   Copy

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

2. Create a new project folder with the following structure:

   Copy

   Drizzle
   ├── .env
   ├── app.ts
   ├── drizzle.config.ts
   └── db
       └── schema.ts

3. Assign the connection string to the DATABASE_URL variable in the .env file.

   Note: Update the connection string with the connection configuration of your SingleStore deployment.

   Copy

   DATABASE_URL="mysql://s2user:pa55w04d@svchost:3306/dbTest"

4. Define the schema of the Stock table. Add the following code to db/schema.ts file:

   Typescript

   Copy

   import { int, bigint, text, singlestoreTable } from "drizzle-orm/singlestore-core";
   
   export const stock = singlestoreTable("Stock", {
     id: bigint("ID", { mode: "bigint" }).primaryKey().autoincrement(),
     code: text("Code"),
     quantity: int("Quantity"),
   });

5. Add the following configuration to the drizzle.config.ts file:

   Typescript

   Copy

   import { defineConfig } from "drizzle-kit";
   import 'dotenv/config';
   
   export default defineConfig({
     out: './drizzle',
     dialect: 'singlestore',
     schema: './db/schema.ts',
     dbCredentials: {
       url: process.env.DATABASE_URL!}
   })

6. Generate the Drizzle migrations.

   Note: To directly apply the schema changes without generating the migrations, run the npx drizzle-kit push command, and skip to step 8.

   Shell

   Copy

   npx drizzle-kit generate

   Reading config file '/Drizzle/drizzle.config.ts'
   Reading schema files:
   /Drizzle/db/schema.ts
   1 tables
   Stock 3 columns 0 indexes 0 fks
   [✓] Your SQL migration file ➜ drizzle/0000_deep_the_call.sql

   This command creates a migration file (SQL file) that contains the SQL statements required to create the Stock table. For example:

   Shell

   Copy

   cat drizzle/0000_deep_the_call.sql

   CREATE TABLE `Stock` (
   	`ID` bigint AUTO_INCREMENT NOT NULL,
   	`Code` text,
   	`Quantity` int,
   	CONSTRAINT `Stock_ID` PRIMARY KEY(`ID`)
   );

7. Apply this migration to the target SingleStore database.

   Shell

   Copy

   npx drizzle-kit migrate

   migrations applied successfully!

   To verify that the migration is successfully applied, log in to your SingleStore deployment, and run the following commands:

   SQL

   Copy

   USE dbTest;
   DESC Stock;

   +----------+------------+------+------+---------+----------------+
   | Field    | Type       | Null | Key  | Default | Extra          |
   +----------+------------+------+------+---------+----------------+
   | ID       | bigint(20) | NO   | PRI  | NULL    | auto_increment |
   | Code     | text       | YES  |      | NULL    |                |
   | Quantity | int(11)    | YES  |      | NULL    |                |
   +----------+------------+------+------+---------+----------------+

8. To insert data into the Stock table, update app.ts to the following:

   Typescript

   Copy

   import { stock } from "./db/schema.ts"
   import 'dotenv/config';
   import { drizzle } from "drizzle-orm/singlestore";
   
   const db = drizzle(process.env.DATABASE_URL);
   
   async function main() {
       const newStock = [
         { code: 'xvf1', quantity: 40 },
         { code: 'gwl2', quantity: 15 },
         { code: 'cdq3', quantity: 25 }]
       
       await db.insert(stock).values(newStock);
       const rows = await db.select().from(stock);
       console.log(rows);
   }
   main();

9. Run the app.ts file.

   Shell

   Copy

   npx tsx app.ts

   [
     { id: 3n, code: 'cdq3', quantity: 25 },
     { id: 1n, code: 'xvf1', quantity: 40 },
     { id: 2n, code: 'gwl2', quantity: 15 }
   ]

   Run the following command to verify that the rows are inserted:

   SQL

   Copy

   SELECT * FROM Stock;

   +----+------+----------+
   | ID | Code | Quantity |
   +----+------+----------+
   |  3 | cdq3 |       25 |
   |  1 | xvf1 |       40 |
   |  2 | gwl2 |       15 |
   +----+------+----------+