PlanetScale’s distributed SQL database and Drizzle ORM bring type safety to your MySQL queries, making your database interactions feel more like working with plain JavaScript objects.
Here’s a look at how it works, not just in theory, but in practice.
Let’s say you have a simple users table in PlanetScale:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
With Drizzle, you define this schema in your application code:
import { mysqlTable, int, varchar, timestamp } from 'drizzle-orm/mysql-core';
import { sql } from 'drizzle-orm';
export const users = mysqlTable('users', {
id: int('id').autoincrement().primaryKey(),
username: varchar('username', { length: 255 }).notNull().unique(),
email: varchar('email', { length: 255 }).notNull(),
createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`),
});
Now, when you query this table using Drizzle, you get full TypeScript type safety. For instance, fetching a user by their ID:
import { eq } from 'drizzle-orm';
import { db } from './db'; // Your Drizzle database instance
import { users } from './schema';
async function getUserById(userId: number) {
const user = await db.query.users.findFirst({
where: eq(users.id, userId),
});
// 'user' is now strongly typed as:
// {
// id: number;
// username: string;
// email: string;
// createdAt: Date | null;
// } | undefined
if (user) {
console.log(`Found user: ${user.username} with email ${user.email}`);
} else {
console.log(`User with ID ${userId} not found.`);
}
}
If you try to access a property that doesn’t exist or misspell one, TypeScript will catch it immediately:
// This will cause a TypeScript error:
// console.log(user.user_name);
This type safety extends to inserts and updates as well. Inserting a new user:
async function createUser(username: string, email: string) {
await db.insert(users).values({ username, email });
console.log(`User ${username} created.`);
}
Drizzle infers the shape of the values object from your users schema definition. If you try to omit a notNull field or provide a value of the wrong type, TypeScript will flag it.
The real power comes when you start joining tables. Let’s add a posts table:
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
And its Drizzle schema definition:
import { mysqlTable, int, varchar, text, timestamp, foreignKey } from 'drizzle-orm/mysql-core';
import { sql } from 'drizzle-orm';
import { users } from './schema';
export const posts = mysqlTable('posts', {
id: int('id').autoincrement().primaryKey(),
userId: int('user_id').notNull(),
title: varchar('title', { length: 255 }).notNull(),
content: text('content'),
createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`),
}, (table) => ({
fkUser: foreignKey({
columns: [table.userId],
foreignColumns: [users.id],
name: 'fk_user_id',
}).onDelete('cascade'),
}));
Now, fetching a user and their posts:
import { eq, and } from 'drizzle-orm';
import { db } from './db';
import { users, posts } from './schema';
async function getUserWithPosts(userId: number) {
const userWithPosts = await db.query.users.findFirst({
where: eq(users.id, userId),
with: {
posts: { // Drizzle automatically infers relation based on schema
where: and(
eq(posts.title, 'Important Post'), // Type-safe column access
posts.createdAt.gt(new Date('2023-01-01')) // Type-safe comparison
),
orderBy: posts.createdAt,
},
},
});
// 'userWithPosts' type:
// ({
// id: number;
// username: string;
// email: string;
// createdAt: Date | null;
// posts: {
// id: number;
// userId: number;
// title: string;
// content: string | null;
// createdAt: Date | null;
// }[];
// }) | undefined
if (userWithPosts) {
console.log(`User: ${userWithPosts.username}`);
userWithPosts.posts.forEach(post => {
console.log(`- Post: ${post.title}`);
});
}
}
Drizzle’s with clause allows defining relationships directly in your query. It intelligently infers the join conditions from your schema’s foreign key definitions. This means you don’t have to manually specify join, on, or select clauses for the related tables if you’ve defined them correctly in your schema.
The underlying Drizzle query object, when defined with users and posts schemas, understands the users.posts relationship. This isn’t just syntactic sugar; Drizzle translates these structured queries into efficient SQL. For the getUserWithPosts example above, Drizzle generates SQL similar to this:
SELECT
users.id, users.username, users.email, users.created_at,
posts.id AS posts_id, posts.user_id AS posts_user_id, posts.title AS posts_title, posts.content AS posts_content, posts.created_at AS posts_created_at
FROM users
LEFT JOIN posts ON users.id = posts.user_id AND posts.title = 'Important Post' AND posts.created_at > '2023-01-01'
WHERE users.id = <userId>
ORDER BY posts.created_at
This generated SQL is then executed against your PlanetScale database. The results are mapped back into the precisely typed JavaScript objects that TypeScript understands.
The most surprising thing is how seamlessly Drizzle handles complex queries and relationships without requiring explicit SQL, yet still generates performant SQL. It’s not just about avoiding SQL string concatenation; it’s about building a domain-specific language within TypeScript that maps directly to relational concepts and then to SQL. The with syntax for eager loading is particularly powerful, abstracting away the common pattern of fetching a parent entity and its related children in a single, type-safe operation. You get the expressiveness of ORM-like queries with the performance characteristics of hand-tuned SQL, all while maintaining full type safety throughout your application.
You’ll next want to explore Drizzle’s schema migration capabilities to manage your PlanetScale database schema changes programmatically.