TypeScript’s type system can absolutely prevent SQL injection, and it does so by ensuring that the structure and types of your data going into SQL queries match what the query expects, not by magically understanding SQL syntax.
Let’s see it in action. Imagine you have a user table with id (number) and username (string).
// Database schema definition (simplified)
type User = {
id: number;
username: string;
};
// A function that fetches a user by ID
async function getUserById(userId: number): Promise<User | undefined> {
// This is where the magic happens (or doesn't, if done wrong)
// In a real scenario, this would interact with a database driver
console.log(`Executing: SELECT * FROM users WHERE id = ${userId}`);
// Simulate database lookup
const mockUsers: User[] = [
{ id: 1, username: "alice" },
{ id: 2, username: "bob" },
];
return mockUsers.find(user => user.id === userId);
}
// --- Usage ---
async function main() {
const userIdToFetch = 1;
const user = await getUserById(userIdToFetch);
console.log("Found user:", user);
// What happens if we pass a string that looks like SQL?
// In a naive, unsafe implementation, this would be a disaster.
// BUT, because getUserById expects a `number`, TypeScript catches this *before* runtime.
// @ts-expect-error: Argument of type 'string' is not assignable to parameter of type 'number'.
const maliciousUserId = "1 OR 1=1";
// const userMalicious = await getUserById(maliciousUserId); // This line would fail to compile!
}
main();
The core idea is that instead of building SQL strings dynamically with user input, you’re passing typed values to a query execution layer that knows how to safely parameterize them. The type safety comes from ensuring that the variable you pass to the query function matches the expected type for that part of the query.
Let’s break down the mental model. At its heart, preventing SQL injection is about separating code (your SQL query structure) from data (the values you’re filtering or inserting). Traditional injection happens when data is misinterpreted as code. Type-safe SQL, when implemented correctly, enforces this separation at the language level.
Consider a library like pg (for PostgreSQL) or mysql2. They provide methods for executing queries. The naive approach is string concatenation:
// UNSAFE EXAMPLE
const userId = req.query.id; // Imagine this comes from a user's request
const query = `SELECT * FROM users WHERE id = ${userId}`; // BAD!
This is a direct invitation for injection. If userId is 1 OR 1=1, the query becomes SELECT * FROM users WHERE id = 1 OR 1=1, which bypasses the intended filter.
The safe way, which TypeScript encourages, is using parameterized queries. The database driver handles escaping and quoting:
// SAFE EXAMPLE with pg library
import { Pool } from 'pg';
const pool = new Pool({ /* connection config */ });
async function getUserByIdSafe(userId: number) {
const queryText = 'SELECT * FROM users WHERE id = $1'; // $1 is a placeholder
const values = [userId]; // The actual data
try {
const res = await pool.query(queryText, values);
return res.rows[0];
} catch (err) {
console.error('Database query error:', err);
throw err;
}
}
Here, userId is a number. If you try to pass a string like "1 OR 1=1" to getUserByIdSafe, TypeScript will flag it as an error before your code even runs, because the function signature (userId: number) explicitly states it expects a number. The database driver then receives the number 1 and the string "1 OR 1=1" separately. It knows that $1 should be treated as a value to be safely inserted into the id column, not as executable SQL. It will correctly search for a user with id exactly equal to the string "1 OR 1=1", which likely won’t exist, or it might even throw an error if the database schema for id is strictly numeric, but it won’t execute arbitrary SQL.
The ultimate goal is to have a system where your query builder or ORM understands your database schema and your TypeScript types. Libraries like Prisma or Drizzle ORM excel at this. They generate TypeScript types directly from your database schema. When you write a query, they validate that the types you’re using match the schema.
For example, with Prisma:
// schema.prisma
model User {
id Int @id @default(autoincrement())
username String
}
Prisma generates a PrismaClient with types derived from this schema.
// generated by Prisma
// import { PrismaClient } from '@prisma/client';
// const prisma = new PrismaClient();
async function getUserByIdPrisma(userId: number) {
// The Prisma client knows 'id' is an Int and 'username' is a String
// If you tried to filter by username using a non-string, TS would complain.
// If you tried to pass a string to the 'id' filter, TS would complain.
const user = await prisma.user.findUnique({
where: {
id: userId, // TypeScript knows `id` expects a number
},
});
return user;
}
// If you tried this:
// const maliciousId = "1 OR 1=1";
// await prisma.user.findUnique({ where: { id: maliciousId } });
// TypeScript would give you an error: Argument of type 'string' is not assignable to parameter of type 'number'.
The truly surprising thing, and what most people don’t grasp, is that the "type-safe SQL" isn’t about the TypeScript compiler understanding SQL syntax itself. It’s about TypeScript enforcing that the values you pass to your database driver’s parameterized query functions have the correct JavaScript/TypeScript type that the driver expects for that parameter. The driver then takes that correctly typed value and handles the low-level escaping or parameter binding required by the specific SQL dialect. Your TypeScript code guarantees you’re sending a number where a number is expected, a string where a string is expected, etc., preventing the database from misinterpreting your data as SQL commands.
The next hurdle you’ll encounter is understanding how to handle complex query conditions and joins while maintaining this compile-time safety, especially when your database schema evolves.