The Data-Access-Pattern first approach with Drizzle

Bringing database and backend work into a single TS file is just as beneficial as developing a full-stack application in one language.

Overview

When you’re diving into the world of building a backend system from scratch, it’s like starting with a blank canvas. But here’s the thing: your canvas is filled with data, and how you handle it can make or break your project. You might have heard the phrase ‘data is king,’ and it couldn’t be more true in the world of backend development. It’s something most of us understand on a gut level, but often, we don’t have the perfect tools to bring this understanding to life. In this post, we’re going to talk about why taking a data-first approach is the secret sauce for successful backend projects and how you can make it work for you, even if you’re just starting out

Recently I’ve asked on X:

When you start working on a backend, do you prefer business logic first or database modelling first?

And I was glad to see that almost all responses are about data modelling first because it’s the right answer, in my opinion. I asked this question because I’m using a pattern that has helped me greatly. It’s all about schema-first data modelling, and there are some TS tools to help you with that.

Drizzle for data modelling

If you choose to do database modelling first because you care about your models’ performance, and I’ve also seen some comments like, ‘backend may change, but data and the database will be there for a long time,’ then I personally prefer to prioritise thinking about data access patterns initially. This is the right approach for you to take these steps:

1. Define what data you will store and how you will access it most of the time;
2. Define the exact schema, data types, properties, etc.;
3. Implement proper indexing and constraint strategies;
4. Attempt to write queries right away to see if it’s feasible, how difficult it might be, etc.

Using Drizzle makes the process much easier, and this is what we’re implementing on all projects. It enables us to iterate rapidly and minimise mistakes. Additionally, we already have a schema defined in TypeScript, and some queries are already prepared in the logic. It appears that we’ve accomplished several tasks in one go without any trade-offs. You can simply ‘push’ your schema and start testing immediately.

So, how do I plan the backend? Let’s assume that we already have all the UI designs and requirements in place, we’ve already thought about the infrastructure architecture, and we need an SQL database for our main backend features. Here are the steps I would take:

1. Create a *.ts file and start modelling your schema with Drizzle. Drizzle aims to closely resemble DDL SQL statements, allowing you to define data types, constraints, properties, and more.

2. If it’s important for your app, you can easily plan indexing and constraint strategies.

3. Define relationships (in any way you prefer, whether real constraints, using `relations()`, or comments).

4. In the same file, you can create a mock connection to a database and visualise how your queries, based on the data access patterns you need, will look. This can help you understand if the current schema is suitable. If it’s not, you can make changes in the same file, and types will be inferred automatically, eliminating the need to wait.

With all of that, you can perform rapid data modelling in one place, eliminating the need for multiple tools, notes, and whiteboards.

Additionally, you don’t have to learn a custom schema syntax, which can sometimes be challenging. You have control over the precise data types, relationships, and constraints, just as you would with raw DDL statements in SQL. Plus, there’s no need for a generation step each time you make a change, which simplifies the process.

Example

Let’s say we have a task to build a backend API for managing a simple feed solution, which includes users, posts, and replies to these posts.

First, you’ll need to create a .ts file, document all the access patterns you would need, create mock database functions, model a schema that you think will fit, and finally, try to write a query that you believe will be the most common.

import { drizzle } from 'drizzle-orm/node-postgres';
import { index, integer, pgTable, text } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
 id: integer('id').primaryKey(),
 name: text('name').notNull(),
});

const posts = pgTable('post', {
 id: integer('id').primaryKey(),
 content: text('name').notNull(),
 ownerId: integer('owner_id').references(() => users.id),
});

// Let's mock the database
const db = drizzle({} as any);

// We need to get all posts with a post creator
function getPostsWithOwner(userId: number) {
 
}

// We need to open specific post with all replies
function getPostWithReplies(postId: number) {
 
}

You can already prototype queries that you think will be used to check which index/constraint strategy to choose.

import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import { index, integer, pgTable, text } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
 id: integer('id').primaryKey(),
 name: text('name').notNull(),
});

const posts = pgTable('post', {
 id: integer('id').primaryKey(),
 content: text('name').notNull(),
 ownerId: integer('owner_id').references(() => users.id),
}, (t) => ({
 // we will filter by ownerId, let's index it
 ownerSearch: index('owner_search_idx').on(t.ownerId),
}));

// Let's mock the database
const db = drizzle({} as any);

function getPostsWithOwner(userId: number) {
 db.select().from(posts)
      .leftJoin(users, eq(users.id, posts.ownerId))
      .where(eq(posts.ownerId, userId));
}

function getPostWithReplies(postId: number) {
 db.select().from(posts)
        // Post replies is still not defined 
        .leftJoin(postReplies, eq(posts.id, postReplies.parentPost))
        .where(eq(posts.id, postId));
}

We just added an index for the owner_id column and noticed that the postReplies table is missing. To ensure all our queries work, let’s go ahead and add it.

const postReplies = pgTable('post_replies', {
 id: integer('id').primaryKey(),
 content: text('name').notNull(),
 // We want to have 1 parent post for all replies
 parentPost: integer('parent_post').references(() => posts.id),
});

Now it seems like the basic data access patterns are defined, we’ve played around with the schema, indexes, and queries. Everything was defined in one single file, and all type hints were changed immediately, thanks to TypeScript.

You can now use the drizzle-kit push command and have the entire schema in your database, allowing you to test it with real data.

For more references you can check Drizzle ORM and Drizzle Kit

Summary

While a Drizzle schema may initially seem ‘messy’ and ‘hard to understand,’ and perhaps ‘not as good as a custom DSL,’ once you dive into real data modelling and schema prototyping, you can grasp the true power of having a TypeScript-first, schema-first Drizzle design. This approach not only provides you with the full capabilities of SQL in your backend but also offers a highly productive way to design any backend and database systems.