# Announcing MCP Authentication: secure your MCP servers with PropelAuth

If you're a developer who's already comfortable with SQL, you know how frustrating it can be to switch to an ORM that requires you to learn a whole new query language.

And if you’ve never heard of an ORM before, it’s basically a library on top of a database. Typically, it handles both connecting to the database **and** all your interactions with the database.

The problem is that most ORMs provide layers of abstraction between you and the SQL you are trying to write. This can be a polarizing topic, but with some ORMs, I find myself wanting to just write SQL directly instead of spending time understanding that libraries specific syntax. Take this for example:

```js
db.emailTable.findMany({
  where: {
    email: {
      endsWith: 'customer.com',
    },
  },
})
```

Reading that block of code isn’t the problem - it’s pretty clear what it does. Writing it, however, is another story. If we wanted to instead write this in SQL, we’d get something that looks like this:

```sql
SELECT *
  FROM emails
  WHERE email like '%customer.com'
```

and trying to go from that query to the code snippet above can be challenging.

At [PropelAuth](/content/?ref=propelauth.mymidnight.blog), we build a good amount of example applications in different languages and frameworks, and ramping up on ORMs can be a pain when all we want to do is write SQL.

## Enter Drizzle

That's where [Drizzle](https://github.com/drizzle-team/drizzle-orm?ref=propelauth.mymidnight.blog) comes in - their philosophy is simple: "if you know SQL, you know Drizzle ORM." That same query above, in Drizzle, looks like this:

```jsx
db.select()
  .from(emailTable)
  .where(like(emailTable.email, "%customer.com"))
  .all()
```

and that’s really exciting! You get both a syntax that looks incredibly similar to writing in raw SQL, type safety, and autocomplete to guide you through the queries.

But before we get ahead of ourselves, let’s look at a few more examples - including how we can define our schema in the first place.

## Setting up Drizzle

In your application, you need to install Drizzle. We’re going to use SQLite, but there are drivers for Postgres and MySQL as well.

```bash
npm i drizzle-orm better-sqlite3
npm i -D drizzle-kit
```

### Creating a Schema

Let’s now create our first schema in a new folder, `db/schema.ts`:

```jsx
import {integer, sqliteTable, text} from 'drizzle-orm/sqlite-core';

export const postsTable = sqliteTable('post', {
    id: integer('id').primaryKey(),
    username: text('username').notNull(),
    text: text('text').notNull(),
    createdAt: integer('created_at', {mode: 'timestamp'}).notNull().defaultNow(),
})
```

This defines a table called `post` which has an ID, username, text, and a timestamp. If you had a separate users column, you could instead use a foreign key:

```jsx
userId: text('user_id').references(() => users.id), // inline foreign key
```

From there, we can tell it where the database is. For SQLite, we can just use a file:

```jsx
import {drizzle} from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

// ...

const sqlite = new Database('sqlite.db');
export const db = drizzle(sqlite);
```

The last thing we need to do is migrate the database so that it matches our schema. We’ve already installed `drizzle-kit` which will generate the migrations for us:

```bash
$ npm exec drizzle-kit generate:sqlite --out migrations --schema db/schema.ts

1 tables
comments 4 columns 0 indexes 0 fks

[✓] Your SQL migration file ➜ migrations/0000_aspiring_whiplash.sql 🚀
```

This just generates the migration **file**, it doesn’t set up the database for us. We can either apply that SQL file ourselves, or we can do it within application (back in the `schema.ts` file):

```jsx
import {migrate} from "drizzle-orm/better-sqlite3/migrator";
// ...
migrate(db, {migrationsFolder: './migrations'});
```

And now, when we run our application, the database will match the schema we defined. Let’s start querying it.

## Fetching Data

We saw one example of fetching data already, but now let’s add a few more options. Here’s an example query we might want to run:

```sql
SELECT *
    FROM post
    ORDER BY created_at DESC
    LIMIT 10
    OFFSET 40
```

and with Drizzle it looks like:

```jsx
export function fetchPosts(pageSize: number, pageNumber: number) {
    return db.select()
             .from(postsTable)
             .orderBy(desc(postsTable.createdAt))
             .limit(pageSize)
             .offset(pageSize * pageNumber)
             .all()
}
```

This is pretty easy to read, but more important than that - it was simple to write. You autocomplete your way down through methods that you already understand (`from`, `orderBy`, `limit`, `offset`) because they are just SQL.

### Small aside: What type to return?

Drizzle provides `InferModel` so you can get explicit types for querying and inserting data.

```tsx
export type Post = InferModel<typeof postsTable>
export type InsertPost = InferModel<typeof postsTable, 'insert'>
```

How are these different? `InsertPost`, for example, doesn’t require you to pass in `created_at` since it has a default set.

We can now go back and update our function with this type:

```tsx
export function fetchPosts(pageSize: number, pageNumber: number): Post[] {
```

## Inserting Data

There’s not much to say here other than, once again, this looks exactly like the SQL query:

```tsx
export function createPost(insertPost: InsertPost): Post {
    return db.insert(postsTable)
             .values(insertPost)
             .returning()
             .get()
}
```

## The real ORM test… Joins

It’s pretty clear that Drizzle has done a great job matching SQL syntax for select/insert, but what about joins? To me, the true test of an ORM is how it handles joins because of how complicated the query can get.

If we rewrite our schema a bit so that the username is on a separate `user` table:

```tsx
export const usersTable = sqliteTable('user', {
    id: integer('id').primaryKey(),
    username: text('username').notNull(),
    // other fields that we don't care about
})

export const postsTable = sqliteTable('post', {
    id: integer('id').primaryKey(),
    userId: text('user_id').references(() => usersTable.id),
    text: text('text').notNull(),
    createdAt: integer('created_at', {mode: 'timestamp'}).notNull().defaultNow(),
})
```

Then we can write a join like this:

```tsx
db.select({
  id: postsTable.id,
  username: usersTable.username,
  text: postsTable.text,
  createdAt: postsTable.text
})
  .from(postsTable)
  .innerJoin(usersTable, eq(postsTable.userId, usersTable.id))
  .all()
```

This is what sold me.

I’ve used ORMs before where reading the documentation about how joins work requires you to read through lengthy descriptions about how relationships between tables work. With Drizzle, this syntax feels natural and is more about supporting my desire to just write SQL safely instead of making me learn something new.

### Summary

Drizzle is a new, exciting Typescript ORM that really nailed their syntax. If you know SQL, you can ramp up and start writing queries incredibly quickly.

Beyond just selects and inserts, Drizzle even nails the syntax for joins and aggregations, which is hard to find in the ORM space.

Have you tried Drizzle yet? Let us know what you think!