What’s the one thing that sends a chill down every engineer’s spine? Hearing the word “migration.”
.png?width=736&disable=upscale&auto=webp)
Whether it’s switching cloud providers, changing a major dependency or replacing your database, migrations are notoriously complex — and often riddled with edge cases, regressions and rewriting code that used to “just work.”
But that doesn’t always have to be the case.
One of our customers recently switched their database to SingleStore. At the core of their backend was a TypeScript stack powered by Drizzle ORM, and their immediate concern was familiar: “Do we have to rewrite all of our database code?” No, you don’t.
Thanks to their use of Drizzle — and our official SingleStore Drizzle connector — the customer was able to migrate databases with zero changes to their application logic. This is exactly the kind of experience we set out to enable when we partnered with the Drizzle team last year.
In this post, we’ll walk you through how they made the move, why their use of Drizzle made all the difference and how you can avoid the usual pitfalls of a database migration.
Note: SingleStore is supported in drizzle-orm starting from version v0.44.2, and in drizzle-kit from version v0.31.0 onward.
The customer’s code was already ORM-ready
Their backend application is centered around dynamic, user-generated reports, which could contain multiple dimensions, measures and filters. Built entirely in TypeScript using Drizzle ORM, their system allowed users to define analytical views on their data by composing different building blocks — all stored in a relational database.
To power this, the team used a relational schema with clear entity relationships: reports were linked to dimensions and measures through many-to-many tables (report_dimension, report_measure), and filters allowed fine-grained control over what data to include in each report.
With this setup, their app could run queries like:
1db.query.report.findFirst({2 with: {3 reportDimensions: { with: { dimension: true } },4 reportMeasures: { with: { measure: true } },5 reportFilters: { with: { dimension: true } },6 },7 where: and(eq(report.id, reportId), eq(report.userId, userId)),8});
This single statement would retrieve the full report context, including all its linked dimensions, measures and filters — with nested relationships resolved and type safety guaranteed by Drizzle’s typing system.
Minimal migration, maximum payoff
When the customer moved from GCP’s MySQL-compatible service to SingleStore Helios®, they braced for an avalanche of changes to their application logic. But the reality? Swapping the database turned out to be surprisingly straightforward.
Here’s what they actually had to change:
- Update the database URL in .env
1// .env23// from something like:4DATABASE_URL="mysql://user:pass@host:3306/db"56// to this:7DATABASE_URL="singlestore://user:pass@host:port/db_name"
You can get your SingleStore database URL here.
- Switch the dialect in drizzle.config.ts
1export default defineConfig({2 schema: './schema.ts',3 dialect: "singlestore",4 // ...5});
- Import the SingleStore driver
1// app.ts 2 3// instead of:4 5 import { drizzle } from "drizzle-orm/mysql2"6 7// use:8 9 import { drizzle } from "drizzle-orm/singlestore"
- Replace legacy imports with SingleStore-specific ones
1// schema.ts2 3// instead of:4 5import { int, varchar, ... } from "drizzle-orm/mysql-core"6 7// use:8 9import { int, varchar, ... } from "drizzle-orm/singlestore-core"
- Update all table definitions to use singlestoreTable
Minimal change, but crucial:
1// schema.ts2 3// Before4 5const report = mysqlTable("report", { ... });6 7// After8 9const report = singlestoreTable("report", { ... });
That’s it. The rest — query generation, migrations and compatibility — was handled by Drizzle under the hood. No manual rewrites, no SQL diffing, no brittle migration scripts. It just worked.
But what if you have a very complex query that can’t (yet) be written using Drizzle’s abstractions? That’s where Drizzle’s SQL magic operator comes in — and we’ll walk you through a great example next.
Heads-up: A note on auto-increment columns
While Drizzle smooths over most migration details, if you’re migrating from MySQL to SingleStore, there are still a few differences between the two dialects worth noting — especially around column types.
For example, in SingleStore, adding the AUTO_INCREMENT constraint to a primary key will automatically promote the column to BIGINT, even if you explicitly define it as INT. This is expected behavior due to how SingleStore encodes internal aggregator IDs.
So a table defined like this…
1CREATE TABLE new_table (2 pkey INT PRIMARY KEY AUTO_INCREMENT,3 ...4);
…will actually treat pkey as a BIGINT behind the scenes.
You can read more in this article from SingleStore Support.
Another small difference: if you’re using the SERIAL type, MySQL guarantees both uniqueness and sequentiality, while SingleStore guarantees only uniqueness. So for full compatibility and clarity, we recommend explicitly defining columns like:
1<col_name> BIGINT PRIMARY KEY AUTO_INCREMENT
This ensures behavior is consistent and avoids surprises after switching.
The magic sql operator
Like any piece of software, the SingleStore Drizzle connector is constantly evolving. While we’ve built strong compatibility for most use cases, there are a few edge cases — specific query patterns or SQL syntax — that aren’t yet fully supported out of the box. That’s where Drizzle’s magic sql operator comes in. Drizzle’s sql operator lets you write raw SQL queries while still integrating with your defined schema and keeping things type-safe.
Here’s a simple example of how you can use it:
1import { sql } from 'drizzle-orm';2import { db } from './db';3import * as schema from './db/schema';4 5const productId = 42;6 7const result = await db.execute(sql`8 SELECT ${schema.products.name}, ${schema.products.price}9 FROM ${schema.products}10 WHERE ${schema.products.id} = ${productId}11`);
In this example:
${schema.products.name}safely references the name column from your schema.${productId}is a parameterized value — Drizzle will handle escaping and typing automatically.- You retain full control over the SQL syntax, while still leveraging the type-safe schema definitions.
It’s a great way to stay flexible without abandoning structure. So if you hit a wall with Drizzle’s abstractions, don’t worry — the sql operator has your back.
Why use an ORM?
If your codebase is built with plain TypeScript and raw SQL, we strongly recommend adopting an ORM like Drizzle. It gives you something raw SQL can’t: a single, type-safe, developer-friendly layer that manages your database logic for you.
- Schema-as-code. Define your tables right in TypeScript and get instant type safety, autocomplete and helpful hints in your IDE.
- Type-safe queries. Drizzle understands the shape of your data at development time, so you don’t need to guess or use extra tools like Zod to validate results.
- Simple, reliable migrations. Every change to your schema is turned into a migration file automatically. If something breaks, rolling back is easy.
- Works across databases. Drizzle keeps things abstract and consistent no matter which database you’re using. That makes it easier to switch databases or support more than one.
Final remarks
Migrations usually feel like a risky and painful process. But with thoughtful tooling like Drizzle they don’t have to be.
You’re not just getting a smoother developer experience — you’re building a stack that’s resilient to change. Database switches become less scary. Maintenance becomes less expensive. And developer speed stays high.
Make migrations effortless — and your apps faster.
By leveraging Drizzle ORM integration, getting SingleStore’s speed and scalability isn’t a rewrite; it’s just a config change. Whether you’re building something new or modernizing your stack, SingleStore gives your TypeScript app a database that’s built for both OLTP and OLAP workloads.
Want to learn more? Join us for our upcoming webinar, Building Modern Apps with Drizzle ORM and SingleStore on July 23, where we’ll dive deeper into how these tools work together and answer your questions live.
Start free with SingleStore and see how easy database migrations can be.
