The premise is simple: you want to be able to scroll your page’s content, but you want something to “stick” at the top (or anywhere). Frequently, this will be some sort of header content that you want to always stay at the top, even as the user scrolls, but it could be any sort of content (and stick edges other than the top, and at any offset).

We’ll cover a brief introduction to sticky positioning. We’ll see how it works, and then we’ll look at some common, frustrating ways it can fail. Then we’ll learn exactly how to fix it.

For all the code examples I’ll be using Tailwind, and later, a little React/JSX for looping. I know the Tailwind piece might be controversial to some. But for this post it’ll allow me to show everything in one place, without ever requiring you, dear reader, to toggle between HTML and CSS.

Making Content Stick

Let’s look at the simplest possible example of sticky positioning.

<div class="h-[500px] gap-2 overflow-auto">
  <div class="flex flex-col gap-2 bg-gray-400 h-[300px]">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>

  <div class="sticky top-0 h-[100px] bg-red-300 mt-2 grid place-items-center">
    <span>I'm sticky!</span>
  </div>

  <div class="flex flex-col bg-gray-400 h-[700px] mt-2">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
</div>

Our middle container has sticky top-0 which sets position: sticky and sets the top value to 0. That means we want it to “stick” at the zero position of whatever scroll container is doing the scrolling.

When Things Go Wrong

This may seem like a simple feature, but in practice it frequently goes wrong, and figuring out why can be maddening. Googling “position sticky doesn’t work” will produce a ton of results, the vast majority of which telling you to make sure you don’t have any containers between your sticky element and your scroll container with overflow: hidden; set. This is true: if you do that, sticky positioning won’t work.

But there are many other things which can go wrong. The next most common remedy you’re likely to see is advising that flex children be set to align-self: flex-start, rather than the default of stretch. This is great advice, and relates strongly to what we’ll be covering here. But in so doing we’re going to dig deep into why this is necessary; we’ll even peak briefly at the CSS spec, and when we’re done, you’ll be well equipped to intelligently and efficiently debug position sticky.

Let’s get started. We’ll look at two different ways you can (inadvertantly) break sticky positioning, and how to fix it.

Problem 1: Your Sticky Element is Bigger Than The Scroll Container

The header above says it all.

The sticky element you want to “stick” cannot be larger than the scrolling container in which it’s attempting to stick.

Let’s see an example:

<div class="h-[500px] gap-2 overflow-auto">
  <div class="flex flex-col gap-2 bg-gray-400 h-[400px]">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
  <div class="sticky top-0 h-[600px] bg-red-300 flex flex-col gap-2 flex-1 mt-2">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
  <div class="flex flex-col gap-2 bg-gray-400 h-[400px] mt-2">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
</div>

Here the scroll container is 500px, and the sticky element is 600px.

This is what the code above renders.

It starts well enough, and the top does in fact stick. But eventually, as you scroll far enough, the browser will ensure that the rest of the sticky element displays in its entirety, which will require the top portion of the element, which had previously “stuck” to the top, to scroll away.

This may seem like a silly example. You probably do want all of your content to show. But this problem can show up in subtle, unexpected ways. Maybe your sticky element is a little too long, but your actual content is in a nested element, correctly constrained. If that happens, everything will look perfect, but inexplicably your sticky element will overshoot at the end of the scrolling. If you see that happening, this might be why!

Problem 2: Your Sticky Element Has a Bounding Context That’s Too Small

Let’s take a look at what the CSS spec has to say (in part) on sticky positioning.

For each side of the box [sticky element], if the corresponding inset property
is not auto, and the corresponding border edge of the box would be outside the
corresponding edge of the sticky view rectangle, then the box must be visually shifted (as for relative positioning) to be inward of that sticky view rectangle edge, insofar as it can while its position box remains contained within its containing block.

Emphasis mine, and that emphasized part refers to the element “sticking.” As the sticky element begins to “violate” the sticky constraints you set (i.e. top: 0;), then the browser forcibly shifts it to respect what you set, and “stick” it in place. But notice the very next line makes clear that this only happens while it can be contained within the containing block.

This is the crucial aspect that the entire rest of this post will obsess over. It manifests itself in many ways (frequently being able to be fixed with “start” alignment rather than “stretch” defaults).

Let’s dive in.

Here’s a sticky demo very similar to what we saw before, except I put the sticky element inside of another element (with a red outline). This immediately breaks the stickyness.

<div class="h-[500px] gap-2 overflow-auto p-1">
  <div class="flex flex-col gap-2 bg-gray-400 h-[400px]">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
  <div class="outline-5 h-[200px] outline-red-500">
    <div class="sticky top-0 h-[200px] bg-red-300 flex flex-col gap-2 flex-1 mt-2">
      <span>Top</span>
      <span class="mt-auto">Bottom</span>
    </div>
  </div>
  <div class="flex flex-col gap-2 bg-gray-400 h-[600px] mt-2">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
</div>

The sticky element is about to stick, but, if the browser were to allow it to do so, it would have to “break out of” its parent. Its parent is not sticky, and so it will keep scrolling. But the browser will not let this “breaking out” happen, so the sticking fails.

Let’s make our parent (with the red outline) a little bigger, so this effect will be even clearer.

<div class="h-[500px] gap-2 overflow-auto p-1">
  <div class="flex flex-col gap-2 bg-gray-400 h-[400px]">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
  <div class="outline-5 h-[300px] outline-red-500">
    <div class="sticky top-0 h-[200px] bg-red-300 flex flex-col gap-2 flex-1 mt-2">
      <span>Top</span>
      <span class="mt-auto">Bottom</span>
    </div>
  </div>
  <div class="flex flex-col gap-2 bg-gray-400 h-[600px] mt-2">
    <span>Top</span>
    <span class="mt-auto">Bottom</span>
  </div>
</div>

Now the sticky element does stick, at first. It sticks because there’s some excess space in its parent. The parent does scroll up, and as soon as the bottom of the parent becomes flush, the sticky element stops sticking. Again, this happens because the browser will not allow a sticky element to stick if doing so would break it out of an ancestor element’s bounds.

This too might seem silly; just don’t do that, you might be thinking. Let’s see a more realistic example of this very phenomenon.

Flex (or Grid) Children

Let’s pretend to build a top-level navigation layout for a web app. Don’t focus on the contrived pieces.

We have a main container, which we’ve sized to 500px (in real life it would probably be 100dvh), and then a child, which itself is a grid container with two columns: a navigation pane on the left, and then the main content section to the right. And for reasons that will become clear in a moment, I put a purple outline around the grid child.

We want the main navigation pane frozen in place, while the main content scrolls. To (try to) achieve this, I’ve set the side navigation to be sticky with top: 0.

Naturally, for this layout, you could achieve it more simply in a way that would work. But a more production ready layout for a real application would be much more complex, and would be much more likely to run into the issue we’re about to see. This entire post is about actual production issues I’ve had to debug and fix, and the learnings therefrom.

export const FlexInFlexStickyDemoVersion1 = () => {
  return (
    <div className="flex border-2 rounded-md">
      <div className="h-[500px] flex flex-1 gap-2 overflow-auto p-1">
        <div className="grid grid-rows-1 outline-2 outline-purple-600 grid-cols-[250px_1fr] flex-1">
          {/* Side Navigation Pane */}
          <div className="sticky top-0 flex flex-col gap-8">
            {Array.from({ length: 5 }).map((_, idx) => (
              <span>Side Navigation {idx + 1}</span>
            ))}
          </div>

          {/* Main Content Pane */}
          <div className="flex flex-1 gap-2">
            <div className="flex flex-col flex-1 gap-2">
              {Array.from({ length: 100 }).map((_, idx) => (
                <div className="flex gap-2">
                  <span>Main Content line {idx}</span>
                </div>
              ))}
            </div>
          </div>
        </div>
      </div>
    </div>
  );
};

And when we run this, the sticky positioning does not work at all. Everything scrolls.

The reason is that our grid child is sized to the container, which means our content cannot stick without “breaking out” of its container (the purple grid), and as we saw, the CSS spec does not allow for this.

Why is this happening? Flex children have, by default, their align-self property set to stretch. That means they stretch in the cross axis and fill up their container. The grid’s parent is a flex container in the row direction.

<div className="h-[500px] flex flex-1 gap-2 overflow-auto p-1">

That means the cross direction is vertical. So the grid grows vertically to the 500px height, and calls it a day. And this is why our stickiness is broken.

Once we understand the root cause, the fix is simple:

export const FlexInFlexStickyDemoVersion1 = () => {
  return (
    <div className="flex border-2 rounded-md">
      <div className="h-[500px] flex flex-1 gap-2 overflow-auto p-1">
        <div className="self-start grid grid-rows-1 outline-2 outline-purple-600 grid-cols-[250px_1fr] flex-1">
          {/* Side Navigation Pane */}
          <div className="self-start sticky top-0 flex flex-col gap-8">
            {Array.from({ length: 5 }).map((_, idx) => (
              <span>Side Navigation {idx + 1}</span>
            ))}
          </div>

          {/* Main Content Pane */}
          <div className="flex flex-1 gap-2">
            <div className="flex flex-col flex-1 gap-2">
              {Array.from({ length: 100 }).map((_, idx) => (
                <div className="flex gap-2">
                  <span>Main Content line {idx}</span>
                </div>
              ))}
            </div>
          </div>
        </div>
      </div>
    </div>
  );
};

We’ve added self-start alignment to both the grid container, and also the sticky element. Adding self-start to the grid tells the grid to start at the start of its flex container, and then, rather than stretch to fill its parent, to just flow as big as it needs to. This allows the grid to grow arbitrarily, so the left pane can sticky without needing to break out of its parent (which, as we’ve seen, is not allowed.)

Why did we add self-start to the sticky element? Remember, grid and flex children both have stretch as the default value for align-self. When we told the grid to grow as large as it needs, then leaving the sticky element as it’s default of stretch would cause it to stretch and also grow huge. That violates our original rule #1 above. Remember when we had a sticky element that was 100px larger than its scrolling container? It stuck only until the last 100px of scrolling. Leaving the sticky element as stretch would cause it to grow exactly as large as the content that’s scrolling, which would prevent it from sticking at all.

What if the side nav gets too big?

Let’s make one more tweak, and stick a green outline on our sticky element.

export const FlexInFlexStickyDemoVersion1 = () => {
  return (
    <div className="flex border-2 rounded-md">
      <div className="h-[500px] flex flex-1 gap-2 overflow-auto p-1">
        <div className="self-start grid grid-rows-1 outline-2 outline-purple-600 grid-cols-[250px_1fr] flex-1">
          {/* Side Navigation Pane */}
          <div className="self-start outline-2 outline-green-600 sticky top-0 flex flex-col gap-8">
            {Array.from({ length: 5 }).map((_, idx) => (
              <span>Side Navigation {idx + 1}</span>
            ))}
          </div>

          {/* Main Content Pane */}
          <div className="flex flex-1 gap-2">
            <div className="flex flex-col flex-1 gap-2">
              {Array.from({ length: 100 }).map((_, idx) => (
                <div className="flex gap-2">
                  <span>Main Content line {idx}</span>
                </div>
              ))}
            </div>
          </div>
        </div>
      </div>
    </div>
  );
};

The self-start alignment on the sticky element keeps its content no bigger than needed. This prevents it from stretching to the (new) grid size that is arbitrarily big. But what happens if our sticky content just naturally gets too big to fit within the scroll container?

It sticks, but as the scroll container gets to the very bottom, the browser un-sticks it, so the rest of its content can scroll and be revealed.

This isn’t actually the worst thing in the world. We probably want to give users some way to see the overflowed side navigation content; but we probably want to just cap the height to the main content, and then make that element scrollable.

export const FlexInFlexStickyDemoVersion1 = () => {
  return (
    <div className="flex border-2 rounded-md">
      <div className="h-[500px] flex flex-1 gap-2 overflow-auto p-1">
        <div className="self-start grid grid-rows-1 outline-2 outline-purple-600 grid-cols-[250px_1fr] flex-1">
          {/* Side Navigation Pane */}
          <div className="max-h-[492px] overflow-auto self-start outline-2 outline-green-600 sticky top-0 flex flex-col gap-8">
            {Array.from({ length: 20 }).map((_, idx) => (
              <span>Side Navigation {idx + 1}</span>
            ))}
          </div>

          {/* Main Content Pane */}
          <div className="flex flex-1 gap-2">
            <div className="flex flex-col flex-1 gap-2">
              {Array.from({ length: 100 }).map((_, idx) => (
                <div className="flex gap-2">
                  <span>Main Content line {idx}</span>
                </div>
              ))}
            </div>
          </div>
        </div>
      </div>
    </div>
  );
};

The weird value of 492 is to allow for the 4px top and bottom padding around it (the p-1 class). In real life you’d of course do something more sensible, like define some CSS variables. But for our purposes this shows what we’re interested in. The side pane is now capped at the containers height, and scrolls if needed.

Parting Thoughts

I hope this post has taught you some new things about position sticky which come in handy someday.

In essence, TanStack Start takes TanStack Router, a superb, strongly-typed client-side JavaScript framework, and adds server-side support. This serves two purposes: it gives you a place to execute server-side code, like database access; and it enables server-side rendering, or SSR.

This post is all about one particular, especially powerful feature of TanStack Start: Middleware.

The elevator pitch for Middleware is that it allows you to execute code in conjunction with your server-side operations, executing code on both the client and the server, both before and after your underlying server-side action, and even passing data between the client and server.

This post will be a gentle introduction to Middleware. We’ll build some very rudimentary observability for a toy app. Then, in a future post, we’ll really see what Middleware can do when we use it to achieve single-flight mutations.

Why SSR?

SSR will usually improve LCP (Largest Contentful Paint) render performance compared to a client-rendered SPA. With SPAs, the server usually sends down an empty shell of a page. The browser then parses the script files, and fetches your application components. Those components then render and, usually, request some data. Only then can you render actual content for your user.

These round trips are neither free nor cheap; SSR allows you to send the initial content down directly, via the initial request, which the user can see immediately, without needing those extra round trips. See the post above for some deeper details; this post is all about Middleware.

Prelude: Server Functions

Any full-stack web application will need a place to execute code on the server. It could be for a database query, to update data, or to validate a user against your authentication solution. Server functions are the main mechanism TanStack Start provides for this purpose, and are documented here. The quick introduction is that you can write code like this:

import { createServerFn } from "@tanstack/react-start";

export const getServerTime = createServerFn().handler(async () => {
  await new Promise(resolve => setTimeout(resolve, 1000));
  return new Date().toISOString();
});

Then you can call that function from anywhere (client or server), to get a value computed on the server. If you call it from the server, it will just execute the code. If you call that function from the browser, TanStack will handle making a network request to an internal URL containing that server function.

Getting Started

All of my prior posts on TanStack Start and Router used the same contrived Jira clone, and this one will be no different. The repo is here, but the underlying code is the same. If you want to follow along, you can npm i and then npm run dev and then run the relevant portion of the app at http://localhost:3000/app/epics?page=1.

The epics section of this app uses server functions for all data and updates. We have an overview showing:

• A count of all tasks associated with each individual epic (for those that contain tasks).
• A total count of all epics in the system.
• A pageable list of individual epics which the user can view and edit.

Our Middleware Use Case

We’ll explore middleware by building a rudimentary observability system for our Jira-like app.

What is observability? If you think of basic logging as a caterpillar, observability would be the beautiful butterfly it matures into. Observability is about setting up systems that allow you to holistically observe how your application is behaving. High-level actions are assigned a globally unique trace id, and the pieces of work that action performs are logged against that same trace id. Then your observability system will allow you to intelligently introspect that data, and discover where your problems or weaknesses are.

I’m no observability expert, so if you’d like to learn more, Charity Majors co-authored a superb book on this very topic. She’s the co-founder of Honeycomb IO, a mature observability platform.

We won’t be building a mature observability platform here; we’ll be putting together some rudimentary logging with trace id’s. What we’ll be building is not suitable for use in a production software system, but it will be a great way to explore TanStack Start’s Middleware.

Our First Server Function

This is a post about Middleware, which is applied to server functions. Let’s take a very quick look at a server function

export const getEpicsList = createServerFn({ method: "GET" })
  .inputValidator((page: number) => page)
  .handler(async ({ data }) => {
    const epics = await db
      .select()
      .from(epicsTable)
      .offset((data - 1) * 4)
      .limit(4);
    return epics;
  });

This is a simple server function to query our epics. We configure it to use the GET http verb. We specify and potentially validate our input, and then the handler function runs our actual code, which is just a basic query against our SQLite database. This particular code uses Drizzle for the data access, but you can of course use whatever you want.

Server functions by definition always run on the server, so you can do things like connect to a database, access secrets, etc.

Our First Middleware

Let’s add some empty middleware so we can see what it looks like.

import { createMiddleware } from "@tanstack/react-start";

export const middlewareDemo = createMiddleware({ type: "function" })
  .client(async ({ next, context }) => {
    console.log("client before");

    const result = await next({
      sendContext: {
        hello: "world",
      },
    });

    console.log("client after", result.context);

    return result;
  })
  .server(async ({ next, context }) => {
    console.log("server before", context);

    await new Promise(resolve => setTimeout(resolve, 1000));

    const result = await next({
      sendContext: {
        value: 12,
      },
    });

    console.log("server after", context);

    return result;
  });

Let’s step through it.

export const middlewareDemo = createMiddleware({ type: "function" });

This declares the middleware. type: "function" means that this middleware is intended to run against server “functions” – there’s also “request” middleware, which can run against either server functions, or server routes (server routes are what other frameworks sometimes call “API routes”). But “function” middleware has some additional powers, which is why we’re using them here.

.client(async ({ next, context }) => {

This allows us to run code on the client. Note the arguments: next is how we tell TanStack to proceed with the rest of the middlewares in our chain, as well as the underlying server function this middleware is attached to. And context holds the mutable “context” of the middleware chain.

console.log("client before");

const result = await next({
  sendContext: {
    hello: "world",
  },
});

console.log("client after", result.context);

We do some logging, then tell TanStack to run the underlying server function (as well as any other middlewares we have in the chain), and then, after everything has run, we log again.

Note the sendContext we pass into the call to next

sendContext: {
  hello: "world",
},

This allows us to pass data from the client, up to the server. Now this hello property will be in the context object on the server.

And of course don’t forget to return the actual result.

return result;

You can return next(), but separating the call to next with the return statement allows you to do additional work after the call chain is finished: modify context, perform logging, etc.

And now we essentially restart the same process on the server.

  .server(async ({ next, context }) => {
    console.log("server before", context);

    await new Promise(resolve => setTimeout(resolve, 1000));

    const result = await next({
      sendContext: {
        value: 12
      }
    });

    console.log("server after", context);

    return result;

We do some logging and inject an artificial delay of one second to simulate work. Then, as before, we call next() which triggers the underlying server function (as well as any other Middleware in the chain), and then return the result.

Note again the sendContext.

const result = await next({
  sendContext: {
    value: 12,
  },
});

This allows us to send data from the server back down to the client.

Let’s Run It

We’ll add this middleware to the server function we just saw.

export const getEpicsList = createServerFn({ method: "GET" })
  .inputValidator((page: number) => page)
  .middleware([middlewareDemo])
  .handler(async ({ data }) => {
    const epics = await db
      .select()
      .from(epicsTable)
      .offset((data - 1) * 4)
      .limit(4);
    return epics;
  });

When we run it, this is what the browser’s console shows:

client before
client after {value: 12}

With a one second delay before the final client log, since that was the time execution was on the server with the delay we saw.

Nothing too shocking. The client logs, then sends execution to the server, and then logs again with whatever context came back from the server. Note we use result.context to get what the server sent back, rather than the context argument that was passed to the client callback. This makes sense: that context was created before the server was ever invoked with the next() call, so there’s no way for it to magically, mutably update based on whatever happens to get returned from the server. So we just read result.context to get what the server sent back.

The Server

Now let’s see what the server console shows.

server before { hello: 'world' }
server after { hello: 'world' }

Nothing too interesting here, either. As we can see, the server’s context argument does in fact contain what was sent to it from the client.

When Client Middleware Runs on the Server

Don’t forget, TanStack Start will server render your initial path by default. So what happens when a server function executes as a part of that process, with Middleware? How can the client middleware possibly run, when there’s no client in existence yet—only a request, currently being executed on the server.

During SSR, client Middleware will run on the server. This makes sense: whatever functionality you’re building will still work, but the client portion of it will run on the server. So be sure not to use any browser-only APIs like localStorage.

Let’s see this in action, but during the SSR run. The prior logs I showed were the result of browsing to a page via navigation. Now I’ll just refresh that page, and show the server logs.

client before
server before { hello: 'world' }
server after { hello: 'world' }
client after { value: 12 }

This is the same as before, but now server, and client logs are together, since this code all runs during the server render phase. The server function is called from the server, while it generates the HTML to send down for the initial render. And as before, there’s a one second delay while the server is working.

Building Real Middleware

Let’s build some actual logging Middleware with an observability flair. If you want to look at real observability solutions, please check out the book I mentioned above, or a real Observability solution like Honeycomb. But our focus will be on TanStack Middleware, not a robust observability solution.

The Client

Let’s start our Middleware with our client section. It will record the local time that this Middleware began. This will allow us to measure the total end-to-end time that our action took, including server latency.

export const loggingMiddleware = (name: string) =>
  createMiddleware({ type: "function" })
    .client(async ({ next, context }) => {
      console.log("middleware for", name, "client", context);

      const clientStart = new Date().toISOString();

Now let’s call the rest of our Middleware chain and our server function.

const result = await next({
  sendContext: {
    clientStart,
  },
});

Once the await next completes, we know that everything has finished on the server, and we’re back on the client. Let’s grab the date and time that everything finished, as well as a logging id that was sent back from the server. With that in hand, we’ll call setClientEnd, which is just a simple server function to update the relevant row in our log table with the clientEnd time.

const clientEnd = new Date().toISOString();
const loggingId = result.context.loggingId;

await setClientEnd({ data: { id: loggingId, clientEnd } });

return result;

For completeness, that server function looks like this:

export const setClientEnd = createServerFn({ method: "POST" })
  .inputValidator((payload: { id: string; clientEnd: string }) => payload)
  .handler(async ({ data }) => {
    await db.update(actionLog).set({ clientEnd: data.clientEnd }).where(eq(actionLog.id, data.id));
  });

The Server

Let’s look at our server handler.

    .server(async ({ next, context }) => {
      const traceId = crypto.randomUUID();

      const start = +new Date();

      const result = await next({
        sendContext: {
          loggingId: "" as string
        }
      });

We start by creating a traceId. This is the single identifier that represents the entirety of the action the user is performing; it’s not a log id. In fact, for real observability systems, there will be many, many log entries against a single traceId, representing all the sub-steps involved in that action.

For now, there’ll just be a single log entry, but in a bit we’ll have some fun and go a little further.

Once we have the traceId, we note the start time, and then we call await next to finish our work on the server. We add a loggingId to the context we’ll be sending back down to the client. It’ll use this to update the log entry with the clientEnd time, so we can see the total end-to-end network time.

const end = +new Date();

const id = await addLog({
  data: { actionName: name, clientStart: context.clientStart, traceId: traceId, duration: end - start },
});
result.sendContext.loggingId = id;

return result;

Next we get the end time after the work has completed. We add a log entry, and then we update the context we’re sending back down to the client (the sendContext object) with the correct loggingId. Recall that the client callback used this to add the clientEnd time.

And then we return the result, which then finishes the processing on the server, and allows control to return to the client.

The addLog function is pretty boring; it just inserts a row in our log table with Drizzle.

export const addLog = createServerFn({ method: "POST" })
  .inputValidator((payload: AddLogPayload) => payload)
  .handler(async ({ data }) => {
    const { actionName, clientStart, traceId, duration } = data;

    const id = crypto.randomUUID();
    await db.insert(actionLog).values({
      id,
      traceId,
      clientStart,
      clientEnd: "",
      actionName,
      actionDuration: duration,
    });

    return id as string;
  });

The value of clientEnd is empty, initially, since the client callback will fill that in.

Let’s run our Middleware. We’ll add it to a serverFn that updates an epic.

export const updateEpic = 
  createServerFn({ method: "POST" })
    .middleware([loggingMiddleware("update epic")])
    .inputValidator((obj: { id: number; name: string }) => obj)
    .handler(async ({ data }) => { await new Promise(resolve => setTimeout(resolve, 1000 * Math.random()));

  await db.update(epicsTable)
    .set({ name: data.name })
    .where(eq(epicsTable.id, data.id));
});

And when this executes, we can see our logs!

The Problem

There’s one small problem: we have a TypeScript error.

Here’s the entire middleware, with the TypeScript error pasted as a comment above the offending line

import { createMiddleware } from "@tanstack/react-start";
import { addLog, setClientEnd } from "./logging";

export const loggingMiddleware = (name: string) =>
  createMiddleware({ type: "function" })
    .client(async ({ next, context }) => {
      console.log("middleware for", name, "client", context);

      const clientStart = new Date().toISOString();

      const result = await next({
        sendContext: {
          clientStart,
        },
      });

      const clientEnd = new Date().toISOString();
      // ERROR: 'result.context' is possibly 'undefined'
      const loggingId = result.context.loggingId;

      await setClientEnd({ data: { id: loggingId, clientEnd } });

      return result;
    })
    .server(async ({ next, context }) => {
      const traceId = crypto.randomUUID();

      const start = +new Date();

      const result = await next({
        sendContext: {
          loggingId: "" as string,
        },
      });

      const end = +new Date();

      const id = await addLog({
        data: { actionName: name, clientStart: context.clientStart, traceId: traceId, duration: end - start },
      });
      result.sendContext.loggingId = id;

      return result;
    });

Why does TypeScript dislike this line?

We call it on the client, after we call await next. Our server does in fact add a loggingId to its sendContext object. And it’s there: the value is logged.

The problem is a technical one. Our server callback can see the things the client callback added to sendContext. But the client callback is not able to “look ahead” and see what the server callback added to its sendContext object. The solution is to split the Middleware up.

Here’s a version 2 of the same Middleware. I’ve added it to a new loggingMiddlewareV2.ts module.

I’ll post the entirety of it below, but it’s the same code as before, except all the stuff in the .client handler after the call to await next has been moved to a second Middleware. This new, second Middleware, which only contains the second half of the .client callback, then takes the other Middleware as its own Middleware input.

Here’s the code:

import { createMiddleware } from "@tanstack/react-start";
import { addLog, setClientEnd } from "./logging";

const loggingMiddlewarePre = (name: string) =>
  createMiddleware({ type: "function" })
    .client(async ({ next, context }) => {
      console.log("middleware for", name, "client", context);

      const clientStart = new Date().toISOString();

      const result = await next({
        sendContext: {
          clientStart,
        },
      });

      return result;
    })
    .server(async ({ next, context }) => {
      const traceId = crypto.randomUUID();

      const start = +new Date();

      const result = await next({
        sendContext: {
          loggingId: "" as string,
        },
      });

      const end = +new Date();

      const id = await addLog({
        data: { actionName: name, clientStart: context.clientStart, traceId: traceId, duration: end - start },
      });
      result.sendContext.loggingId = id;

      return result;
    });

export const loggingMiddleware = (name: string) =>
  createMiddleware({ type: "function" })
    .middleware([loggingMiddlewarePre(name)])
    .client(async ({ next }) => {
      const result = await next();

      const clientEnd = new Date().toISOString();
      const loggingId = result.context.loggingId;

      await setClientEnd({ data: { id: loggingId, clientEnd } });

      return result;
    });

We export that second Middleware. It takes the other one as its own middleware. That runs everything, as before. But now when the .client callback calls await next, it knows what’s in the resulting context object. It knows this because that other Middleware is now input to this Middleware, and the typings can readily be seen.

Going Deeper

We could end the post here. I don’t have anything new to show with respect to TanStack Start. But let’s make our observability system just a little bit more realistic, and in the process see a cool Node feature that’s not talked about enough, and also has the distinction of being the worst named API in software engineering history: asyncLocalStorage.

You’d be forgiven for thinking asyncLocalStorage was some kind of async version of your browser’s localStorage. But no: it’s a way to set and maintain context for the entirety of an async operation in Node.

When Server Functions Call Server Functions

Let’s imagine our updateEpic server function also wants to read the epic it just updated. It does this by calling the getEpic serverFn. So far so good, but if our getEpic serverFn also has logging Middleware configured, we really would want it to use the traceId we already created, rather than create its own.

Think about React context: it allows you to put arbitrary state onto an object that can be read by any component in the tree. Well, Node’s asyncLocalStorage allows this same kind of thing, except instead of being read anywhere inside of a component tree, the state we set can be read anywhere within the current async operation. This is exactly what we need.

Note that TanStack Start did have a getContext / setContext set of api’s in an earlier beta version, which maintained state for the current, entire request, but they were removed. If they wind up being re-added at some point (possibly with a different name) you can just use them.

Let’s start by importing AsyncLocalStorage, and creating an instance.

import { AsyncLocalStorage } from "node:async_hooks";

const asyncLocalStorage = new AsyncLocalStorage();

Now let’s create a function for reading the traceId that some middleware higher up in our callstack might have added

function getExistingTraceId() {
  const store = asyncLocalStorage.getStore() as any;
  return store?.traceId;
}

All that’s left is to read the traceId that was possibly set already, and if none was set, create one. And then, crucially, use asyncLocalStorage to set our traceId for any other Middleware that will be called during our operation.

    .server(async ({ next, context }) => {
      const priorTraceId = getExistingTraceId();
      const traceId = priorTraceId ?? crypto.randomUUID();

      const start = +new Date();

      const result = await asyncLocalStorage.run({ traceId }, async () => {
        return await next({
          sendContext: {
            loggingId: "" as string
          }
        });
      });

The magic line is this:

const result = await asyncLocalStorage.run({ traceId }, async () => {
  return await next({
    sendContext: {
      loggingId: "" as string,
    },
  });
});

Our call to next is wrapped in asyncLocalStorage.run, which means virtually anything that gets called in there can see the traceId we set. There are a few exceptions at the margins, for things like WorkerThreads. But any normal async operations which happen inside of the run callback will see the traceId we set.

The rest of the Middleware is the same, and I’ve saved it in a loggingMiddlewareV3 module. Let’s take it for a spin. First, we’ll add it to our getEpic serverFn.

export const getEpic = createServerFn({ method: "GET" })
  .middleware([loggingMiddlewareV3("get epic")])
  .inputValidator((id: string | number) => Number(id))
  .handler(async ({ data }) => {
    const epic = await db.select().from(epicsTable).where(eq(epicsTable.id, data));
    return epic[0];
  });

Now let’s add it to updateEpic, and update it to also call our getEpic server function.

export const updateEpic = createServerFn({ method: "POST" })
  .middleware([loggingMiddlewareV3("update epic")])
  .inputValidator((obj: { id: number; name: string }) => obj)
  .handler(async ({ data }) => {
    await new Promise(resolve => setTimeout(resolve, 1000 * Math.random()));
    await db.update(epicsTable).set({ name: data.name }).where(eq(epicsTable.id, data.id));

    const updatedEpic = await getEpic({ data: data.id });
    return updatedEpic;
  });

Our server function now updates our epic, and then calls the other serverFn to read the newly updated epic.

Let’s clear our logging table, then give it a run. I’ll edit, and save an individual epic. Opening the log table now shows this:

Note there’s three log entries. In order to edit the epic, the UI first reads it. That’s the first entry. Then the update happens, and then the second read, from the updateEpic serverFn. Crucially, notice how the last two rows, the update and the last read, both share the same traceId!

Our “observability” system is pretty basic right now. The clientStart and clientEnd probably don’t make much sense for these secondary actions that are all fired off from the server, since there’s not really any end-to-end latency. A real observability system would likely have separate, isolated rows just for client-to-server latency measures. But combining everything together made it easier to put something simple together, and showing off TanStack Start Middleware was the goal, not creating a real observability system.

Besides, we’ve now seen all the pieces you’d need if you wanted to actually build this into something more realistic: TanStack’s Middleware gives you everything you need to do anything you can imagine.

Parting Thoughts

We’ve barely scratched the surface of Middleware. Stay tuned for a future post where we’ll push middleware to its limit and achieve single-flight mutations.

for (int i = 0; i < 10; i++) {
}

For most of us, some variation of this code is one of the first things we learned when we were first starting out. For me it was C++, but just about any language has some version of it—even CSS. Yes, CSS has counter variables!

The Basics

CSS Counters are driven by four properties: 

1. counter-reset
2. counter-set
3. counter-increment
4. counter()

Let’s say we wanted a React component that renders a few lines of text, where the number of lines is received as a prop. But we also want to display line numbers next to each line, and we want to use CSS to do so. That last assumption might seem silly, but bear with me; we’ll look at a real-world use case at the end.

Here’s the component

const NumberedSection: FC<{ count: number }> = ({ count }) => {
  return (
    <div>
      {Array.from({ length: count }).map((_, idx) => (
        <span key={idx}>This is line</span>
      ))}
    </div>
  );
};

We’ll use a CSS counter called count-val to manage our line numbers. In CSS, we can reset our counter for each and every counter-container <div>.

.counter-container {
  counter-reset: count-val;
}

And then for each line inside that container, we can increment our counter, and render the current number in a pseudo-element.

.counter-container span::before {
  counter-increment: count-val;
  content: counter(count-val);
  margin-right: 5px;
  font-family: monospace;
}

If we render two of these components like this:

<NumberedSection count={3} />
<hr />
<NumberedSection count={4} />

It will display numbered lines just like we want:

If you wanted to increment by some other value than 1, you can specify whatever counter-increment you want:

counter-increment: count-val 2;

And if you wanted to just set a counter to a specific value, the counter-set property is for you. There’s a few other options that are of course discussed on MDN.

I know this seems silly, and I know this would have been simpler to do in JavaScript. The counter variable is already right there.

A Better Use Case

Let’s get slightly more realistic. What if you have various headings on your page, representing section titles. And, as you might have guessed, you want them numbered.

Let’s start by reseting a CSS counter right at the root of our page

body {
  counter-reset: tile-num;
}

Then we’ll increment and display that counter for each heading that happens to be on our page. And if we want custom formatting on the line numbers, we can list out strings, and CSS will concatenate them.

h2.title::before {
  counter-increment: tile-num;
  content: counter(tile-num) ": ";
}

Now when we have some content:

<h2 className="title">This is a title</h1>
<p>Content content content</p>

<h2 className="title">This is the next title on the page</h1>
<p>Content content content</p>

<h2 className="title">This is a title</h1>
<p>Content content content</p>

We’ll get line numbers next to each heading.

One Last Example

Before going, I’d like to share the use case that led me to discover this feature. So far the examples we’ve seen are either contrived, or better served by just using JavaScript. But what if you don’t have control over the markup that’s generated on your page?

I recently moved my blog’s code syntax highlighting from Prism to Shiki. Everything went well except for one thing: Shiki does not support line numbers. This created a perfect use case for CSS counters.

I used my Shiki configuration to inject a data-linenumbers attribute onto any pre tag containing code I wanted numbered, and then I solved this with a little bit of CSS.

pre[data-linenumbers] code {
  counter-reset: step;
}

pre[data-linenumbers] code .line::before {
  content: counter(step);
  counter-increment: step;
  width: 1rem;
  margin-right: 1rem;
  display: inline-block;
  text-align: right;
  color: rgba(115, 138, 148, 0.4);
}

Just like that, I had numbered lines

Odds & Ends

We’ve covered all you’ll probably ever use of CSS counters, but for completeness let’s look at some tricks it supports.

Formatting the numbers

It turns out you can customize the display of the number from the CSS counter. The counter() function takes an optional second argument, detailed here.

For example, you can display these counter values as uppercase Roman numerals.

counter(tile-num, upper-roman)

Nested Counters

Remember the titles we saw before? What if those containers with the numbered titles could nest within each other.

Take a look at this markup.

<div className="nested">
  <h1 className="title">This is a title</h1>
  <p>Content content content</p>
  <h2 className="title">This is the next title</h2>
  <section>
    Content content content
    <div className="nested">
      <h3 className="title">Nested title</h3>
      <p>Content content content</p>
      <h3 className="title">Nested title</h3>
      <section>
        Content content content
        <div className="nested">
          <h4 className="title">Nested 2nd title</h4>
        </div>
      </section>
    </div>
  </section>
  <h2 className="title">Last title</h1>
  <p>Content content content</p>
</div>

Do you see how those nested containers can … nest within each other? Each new nested container resets its counter. But wouldn’t it be neat if css could take all values from the current elements’ ancestors, and connect them? Like a nested table of contents?

Well it can! Let’s take a look at the css that produced the above.

.nested {
  counter-reset: nested-num;
}
.nested p {
  margin-left: 10px;
}

.nested .title::before {
  counter-increment: nested-num;
  content: counters(nested-num, ".");
  margin-right: 5px;
}

To achieve this we just use the counters function, rather than counter. It takes a second argument that tells CSS how to join the numeric values for all counter instances on the current element. It also supports a third argument (not shown) to allow you to alter the display of these numbers, like we did before with roman numerals.

Concluding Thoughts

CSS counters are a fun feature that can occasionally come in handy. They’re a useful feature to keep in the back of your mind: they might help you out one day.

We have the same books table as before, containing approximately 90 million records.

Editors’ note: Need to bone up on PostgreSQL all around? Our course Complete Intro to SQL & PostgreSQL from Brian Holt will be perfect for you.

Filtering and sorting

Let’s dive right in. Imagine you work for a book distribution company. You’re responsible for publishers and need to query info on them. There are approximately 250,000 different publishers, with a wide variance in the number of books published by each, which we’ll explore.

Let’s start easy. You want to see the top 10 books, sorted alphabetically, for a single publisher.

explain analyze
select *
from books
where publisher = 157595
order by title
limit 10;

This publisher is relatively small, with only 65 books in its catalog. Nonetheless, the query is slow to run, taking almost four seconds.

If you followed the same steps from part 1 to create this same database, note that your ids will be different.

This is hardly surprising; there are a lot of rows in our table, and finding the rows for that publisher takes a while, since Postgres has to scan the entire heap.

So we add an index on, for now, just publisher.

CREATE INDEX idx_publisher ON books(publisher);

We can think of our index in this way. It just helps us identify all the book entries by publisher. To get the rest of the info on the book, we go to the heap.

And now our same query is incredibly fast.

Nothing surprising or interesting.

But now you need to run the same query, but on a different publisher, number 210537. This is the biggest publisher in the entire database, with over 2 million books. Let’s see how our index fares.

explain analyze
select *
from books
where publisher = 210537
order by title
limit 10;

Actually, our index wasn’t used at all. Postgres just scanned the whole table, grabbing our publisher along the way, and then sorted the results to get the top 10. We’ll discuss why a little later, as we did in the prior post, but the short of it is that the random heap accesses from reading so many entries off of an index would be expensive; Postgres decided the scan would be cheaper. These decisions are all about tradeoffs and are governed by statistics and cost estimates.

Previously, we threw the “other” field into the INCLUDE() list, so the engine wouldn’t have to leave the index to get the other field it needed. In this case, we’re selecting everything. I said previously to be diligent in avoiding unnecessary columns in the SELECT clause for just this reason, but here, we assume we actually do need all these columns.

We probably don’t want to dump every single column into the INCLUDE list of the index: we’d basically just be redefining our table into an index.

But why do we need to read so many rows in the first place? We have a limit of 10 on our query. The problem, of course, is that we’re ordering on title. And Postgres needs to see all rows for a publisher (2 million rows in this case) in order to sort them, and grab the first 10.

What if we built an index on publisher, and then title?

CREATE INDEX idx_publisher_title ON books(publisher, title);

That would look like this:

If Postgres were to search for a specific publisher, it could just seek down to the start of that publisher’s books, and then read however many needed, right off the leaf nodes, couldn’t it? There could be 2 million book entries in the leaf nodes, but Postgres could just read the first 10, and be guaranteed that they’re the first 10, since that’s how the index is ordered.

Let’s try it.

We got the top 10 books, sorted, from a list of over two million in less than a fourth of a millisecond. Amazing!

More publishers!

Now your boss comes and tells you to query the top 10 books, sorted alphabetically, as before, but over either publisher, combined. To be clear, the requirement is to take all books both publishers have published, combine them, then get the first ten, alphabetically.

Easy, you say assuredly, fresh off the high of seeing Postgres grab you that same data for your enormous publisher in under a millisecond.

You can put both publisher ids into an IN clause. Then, Postgres can search for each, one at a time, save the starting points of both, and then start reading forward on both, and sort of merge them together, taking the smaller title from either, until you have 10 books total.

Let’s try it!

explain analyze
select *
from books
where publisher in (157595, 210537)
order by title
limit 10;

Which produces this

[Sad Trombone]

Let’s re-read my completely made-up, assumed chain of events Postgres would take, from above.

Postgres can search for each, one at a time, save the starting points of both, and then start reading forward on both, and sort of merge them together, taking the smaller title from either, until you have 10 books total.

It reads like the Charlie meme from Always Sunny in Philadelphia.

If your description of what the database will do sounds like something that would fit with this meme, you’re probably overthinking things.

Postgres operates on very simple operations that it chains together. Index Scan, Gather Merge, Sort, Sequential Scan, etc.

Searching multiple publishers

To be crystal clear, Postgres absolutely can search multiple keys from an index. Here’s the execution plan for the identical query from a moment ago, but with two small publishers for the publisher ids, which each have just a few hundred books

It did indeed do an index scan, on that same index. It just matched two values at once.

Rather than taking one path down the B Tree, it takes multiple paths down the B Tree, based on the multiple key value matches.

Index Cond: (publisher = ANY ('{157595,141129}'::integer[]))

That gives us all rows for either publisher. Then it needs to sort them, which it does next, followed by the limit.

Why does it need to sort them? When we have a single publisher, we know all values under that publisher are ordered.

Look at the index.

Imagine we searched for publisher 8. Postgres can go directly to the beginning of that publisher, and just read:

"Animal Farm"
"Of Mice and Men"

Look what happens when we search for two publishers, 8 and also, now, 21.

We can’t just start reading for those matched records. That would give us

"Animal Farm"
"Of Mice and Men"
"Lord of The Flies"
"The Catcher in The Rye"

The books under each publisher are ordered, but the overall list of matches is not. And again, Postgres operates on simple operations. Elaborate meta descriptions like “well it’ll just merge the matches from each publisher taking the less of the next entry from either until the limit is satisfied” won’t show up in your execution plan, at least not directly.

Why did the publisher id change the plan?

Before we make this query fast, let’s briefly consider why our query’s plan changed so radically between searching for two small publishers compared to an enormous publisher and a small one.

As we discussed in part 1, Postgres tracks and uses statistics about your data in order to craft the best execution plan it can. Here, when you searched for the large publisher, it realized that query would yield an enormous number of rows. That led it to decide that simply scanning through the heap directly would be faster than the large number of random i/o that would be incurred from following so many matches in the index’s leaf nodes, over to the corresponding locations on the heap. Random i/o is bad, and Postgres will usually try to avoid it.

Crafting a better query

You can absolutely have Postgres find the top 10 books in both publishers, and then put them together, sorted, and take the first 10 from there. You just have to be explicit about it.

explain analyze
with pub1 as (
    select * from books
    where publisher = 157595
    order by title limit 10
), pub2 as (
    select * from books
    where publisher = 210537
    order by title limit 10
)
select * from pub1
union all
select * from pub2
order by title
limit 10;

The syntax below is called a common table expression, or a CTE. It’s basically a query that we define, and then query against later.

with pub1 as (
    select * from books
    where publisher = 157595
    order by title limit 10
)

Let’s run it!

The execution plan is beautiful

It’s fast! As you can see, it runs in less than a fifth of a millisecond (0.186ms — but who’s counting)?

Always read these from the bottom:

It’s the same exact index scan from before, but on a single publisher, with a limit of 10, run twice. Postgres can seek to the right publisher, and just read 10 for the first publisher, and then repeat for the second publisher. Then it puts those lists together.

Remember the silly, contrived Postgres operation I made up before?

… and then start reading forward on both, and sort of merge them together, taking the smaller title from either, until you have 10 books total.

You’re not going to believe this, but that’s exactly what the Merge Append on line 2 does

->  Merge Append  (cost=1.40..74.28 rows=20 width=111) (actual time=0.086..0.115 rows=10 loops=1)

You can achieve amazing things with modern databases if you know how to structure your queries just right.

How does this scale?

You won’t want to write queries like this manually. Presumably, you’d have application code taking a list of publisher ids, and constructing something like this. How will it perform as you add more and more publishers?

I’ve explored this very idea on larger production sets of data (much larger than what we’re using here). I found that, somewhere around a thousand ids, the performance does break down. But not because there’s too much data to work with. The execution of those queries, with even a thousand ids, took only a few hundred ms. But the Planning Time started to take many, many seconds. It turns out having Postgres parse through a thousand CTEs, and put a plan together takes time.

Version 2

We’re onto something, for sure. But can we take a list of ids, and force them into individual queries that match on that specific id, with a limit, and then select from the overall bucket of results? Exactly like before, but without having to manually cobble together a CTE for each id?

When there’s a will, there’s a way.

explain analyze
with ids as (
    select * from (
      values (157595), (210537)
    ) t(id)
), results as (
    select bookInfo.*
    from ids
    cross join lateral (
      select *
      from books
      where publisher = ids.id
      order by title
      limit 10
    ) bookInfo
)
select *
from results
order by title
limit 10;

Let’s walk through this.

Our ids CTE:

with ids as (
    select * from (
      values (157595), (210537)
    ) t(id)
)

This defines a pseudo-table that has one column, with two rows. The rows have values of our publisher ids for the sole column: 157595 and 210537.

values (157595), (210537)

But if it’s a table, how do we query against the column? It needs to have a name. That’s what this syntax is.

t(id)

We gave that column a name of id.

The results CTE is where the real work happens.

results as (
    select bookInfo.*
    from ids
    cross join lateral (
      select *
      from books
      where publisher = ids.id
      order by title
      limit 10
    ) bookInfo
)

We query against our ids table, and then use the ugly cross join lateral expression as a neat trick to run our normal books query, but with access to the publisher value in the ids CTE. The value in the ids CTE is the publisher id. So we’ve achieved what we want: we’re conceptually looping through those ids, and then running our fast query on each.

The term lateral is the key. Think of (American) football, where a lateral is a sideways pass. Here, the lateral keyword allows us to “laterally” reference the ids.id value from the expression right beside it; the ids CTE laterals each id over to the results CTE.

That coaxes Postgres to run its normal index scan, followed by a read of the next 10 rows. That happens once for each id. That whole meta-list will then contain (up to) 10 rows for each publisher, and then this…

select *
from results
order by title
limit 10;

… re-sorts, and takes the first 10.

In my own experience, this scales fabulously. Even with a few thousand ids I couldn’t get this basic setup to take longer than half a second, even on a much larger table than we’ve been looking at here.

Let’s run it!

Let’s see what this version of our query looks like

Still a small fraction of a millisecond, but ever so slightly slower; this now runs in 0.207ms. And the execution plan is a bit longer and more complex.

->  Nested Loop  (cost=0.69..81.19 rows=20 width=111) (actual time=0.042..0.087 rows=20 loops=1)

A nested loop join is a pretty simple (and usually pretty slow) join algorithm. It just takes each value in the one list, and then applies it to each value in the second list. In this case, though, it’s taking values from a static list and applying them against an incredibly fast query.

The left side of the join is each id from that static table we built

->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)

The right side is our normal (fast) query that we’ve seen a few times now.

->  Limit  (cost=0.69..40.48 rows=10 width=111) (actual time=0.024..0.037 rows=10 loops=2)
      ->  Index Scan using idx_publisher_title on books  (cost=0.69..2288.59 rows=575 width=111) (actual time=0.023..0.034 rows=10 loops=2)
         Index Cond: (publisher = "*VALUES*".column1)

However, our nice Merge Append is gone, replaced with a normal sort. The reason is that we replaced discrete CTEs, each of which produced separate, identically sorted outputs, which the planner could identify, and apply a Merge Append to. Merge Append works on multiple, independently sorted streams of data. Instead, this is just a regular join, which produces one stream of data, and therefore needs to be sorted.

But this is no tragedy. The query runs in a tiny fraction of a millisecond, and will not suffer planning time degradation like the previous CTE version would, as we add more and more publisher ids. Plus, the sort is over just N*10 records, where N is the number of publishers. It would take a catastrophically large N to wind up with enough rows where Postgres would struggle to sort them quickly, especially since the limit of 10 would allow it to do an efficient top-N heapsort, like we saw in part 1.

Stepping back

The hardest part of writing this post is knowing when to stop. I could easily write as much content again: we haven’t even gotten into joins, and how indexes can help there, or even materialized views. This is an endless topic, and one that I enjoy, but we’ll stop here for now.

The one theme throughout can be summed up as: understand how your data is stored, and craft your queries to make the best use possible of this knowledge. If you’re not sure exactly how to craft your queries to do this, then use your knowledge of how indexes work, and what you want your queries to accomplish to ask an extremely specific question to your favorite AI model. It’s very likely to at least get you closer to your answer. Oftentimes knowing what to ask is half the battle.

And of course, if your data is not stored as you need, then change how your data is stored. Indexes are the most common way, which we’ve discussed here. Materialized views would be the next power tool to consider when needed. But that’s a topic for another day.

Parting thoughts

Hopefully, these posts have taught you a few things about querying, query tuning, and crafting the right index for the right situation. These are skills that can have a huge payoff in achieving palpable performance gains that your users will notice.

Editor’s note: our The Complete Course for Building Backend Web Apps with Go includes setting up a PostgreSQL database and running it in Docker, all from scratch.

This will set us up nicely for part 2, where we’ll explore some interesting, counterintuitive ways to press indexes into service to achieve great querying performance over large amounts of data.

There are other types of database indexes beside B Tree, but B Tree indexes are the most common, which is why they’ll be the exclusive focus of this post.

Everything in these posts will use Postgres, but everything is directly applicable to other relational databases (like MySQL). All the queries I’ll be running are against a simple books database which I scaffolded, and had Cursor populate with about 90 million records. The schema for the database, as well as the code to fill it are in this repo. If you’d like to follow along on your own: sql/db_create.sql has the DDL, and npx tsx insert-data/fill-database.ts will run the code to fill it.

We’ll be looking at some B Tree visualizations as we go. Those were put together with a web app I had Cursor help me build.

Editors’ note: Need to bone up on PostgreSQL all around? Our course Complete Intro to SQL & PostgreSQL from Brian Holt will be perfect for you.

Setting some baselines

Just for fun, let’s take a look at the first 10 rows in the books table. Don’t look too close, again, this was all algorithmically generated by AI. The special characters at the beginning were my crude way of forcing the (extremely repetitive) titles to spread out.

That’s the last time we’ll be looking at actual data. From here forward we’ll look at queries, the execution plans they generate, and we’ll talk about how indexes might, or might not be able to help. Rather than the psql terminal utility I’ll be running everything through DataGrip, which is an IDE for databases. The output is identical, except with nicely numbered lines which will make things easier to talk about as we go.

Let’s get started. Let’s see what the prior query looks like by putting explain analyze before it. This tells Postgres to execute the query, and return to us the execution plan it used, as well as its performance.

explain analyze
select * from books limit 10;

We asked for 10 rows. The database did a sequential scan on our books table, but with a limit of 10. This couldn’t be a simpler query, and it returned in less than one twentieth of one millisecond. This is hardly surprising (or interesting). Postgres reached in and grabbed the first ten rows.

Let’s grab the first 10 books, but this time sorted alphabetically.

Catastrophically, this took 20 seconds. With 90 million rows in this table, Postgres now has to (kind of) sort the entire table, in order to know what the first 10 books are.

I say kind of since it doesn’t really have to sort the entire table; it just has to scan the entire table and keep track of the 10 rows with the lowest titles. That’s what the top-N heapsort is doing.

And it’s doing that in parallel. We can see two child workers getting spawned (in addition to the main worker already running our query) to each scan about a third of the table. Then the Gather Merge pulls from each worker until it has the top 10. In this case it only needed to pull the top 7 rows from each worker to get its 10; this is reflected in lines 3-9 of the execution plan.

Line 5 makes this especially clear

->  Sort  (cost=2839564.34..2934237.14 rows=37869122 width=112) (actual time=20080.358..20080.359 rows=7 loops=3)

Notice the loops=3, and the rows=37 million. Each worker is scanning its share of the table, and keeping the top 7 it sees.

These 3 groups of 7 are then gathered and merged together in the Gather Merge on line 2

->  Gather Merge  (cost=2840564.36..11677309.78 rows=75738244 width=112) (actual time=20093.790..20096.619 rows=10 loops=1)

Rather than just slapping an index in, and magically watching the time drop down, let’s take a quick detour and make sure we really understand how indexes work. Failing to do this can result in frustration when your database winds up not picking the index you want it to, for reasons that a good understanding could make clear.

Indexes

The best way to think about a database index is in terms of an index in a book. These list all the major terms in the book, as well as all the pages that the term appears on. Imagine you have a 1,000 page book on the American Civil War, and wanted to know what pages Philip Sheridan are mentioned on. It would be excruciatingly slow to just look through all 1,000 pages, searching for those words. But if there’s a 30 or so page index, your task is considerably simpler.

Before we go further, let’s look at a very basic index over a numeric id column

This is a B Tree, which is how (most) indexes in a database are stored.

Start at the very, very bottom. Those blue “leaf” nodes contain the actual data in your index. These are the actual id values. This is a direct analogue to a book’s index.

So what are the gold boxes above them? These help you find where the leaf node is, with the value you’re looking for.

Let’s go to the very top, to the root node of our B Tree. Each of these internal nodes will have N key values, and N+1 pointers. If the value you’re looking for is strictly less than the first value, go down that first, left-most arrow and continue your search. If the value you’re looking for is greater than or equal to that first key, but strictly less than the next key, take the second arrow. And so on. In real life the number of keys in each of these nodes will be determined by how many of them can fit into a single page on disk (and will usually be much more than 3).

So with this B Tree, if we want to find id = 33, we start at the root. The id 33 is not < 19, so we don’t take the first arrow. But 33 is >=19 and <37, so we take the middle arrow.

Now we repeat. The id 33 is not < 25, so we don’t take the left most path. The id 33 is not >= 25 AND < 31, so we don’t take the middle path. But 33 is greater than 31 (it better be, this is the last path remaining), so we take the right most path. And that takes us to the leaf node with our key value.

Notice also that these leaf nodes have pointers forward and backward. This allows us to not only find a specific key value, but also a range of values. If we wanted all ids > 33, we could do as we did, and just keep reading.

But — now what? What if we ran a query of SELECT * FROM books WHERE id = 33 – we’ve arrived at a leaf node in our index with … our key. How do we get all the data associated with that key? In other words the actual row in the database for that value?

The thing I’ve left off so far is that leaf nodes also contain pointers to the actual table where that value in question is.

Returning briefly to our analogy with a book’s index, those heap pointers correspond to the page number beside each index entry, telling you where to go in the book to see the actual content.

So the full story to find a single row in our database by an id value, via an index, would actually look more like this:

We’ll talk later about these heap reads, or lack thereof when we get into covering indexes and the Index Only Scan operation.

Bear with me a little longer. Before we look at what an index on title would look like, and create one in our database to run our query against, let’s take a slightly deeper look at B Trees. Internalizing how they work can be incredibly valuable.

B Trees run in O(LogN) time

You may have been taught a fun little math fact in school, that if you were to be given a penny on January 1st, then have your penny doubled on January 2nd, then have that new amount (2 cents) doubled on January 3rd, etc, you’d have about $10 million dollars before February. That’s the power of exponential operations. Anytime you’re repeatedly multiplying a value by some constant (which is all doubling is, for constant 2), it will become enormous, fast.

Now think of a more depressing, reverse scenario. If someone gave you $10 million on January 1st, but with the condition that your remaining money would be halved each day, you’d have a lowly cent remaining on Feb 1st. This is a logarithm; it’s the inverse of exponentiation. Rather than multiplying a value by some constant, we divide it by some constant. No matter how enormous, it will become small, fast.

This is exactly how B Trees work. In our example B Tree above, there were 9 leaf pages. Our internal nodes had up to 3 pointers. Notice that we were able to find out the exact leaf node we wanted by following only 2 of those gold nodes’ arrows (which is also the depth of the tree).

9 divided by 3 is 3

3 divided by 3 is 1

Or, more succinctly, Log₃9 = 2

Which reads as

Logarithm base 3 of 9 is 2

But these small values don’t really do this concept justice. Imagine if you had an index with whose leaves spanned 4 billion pages, and your index nodes had only 2 pointers, each (both of these assumptions are unrealistic). You’d still need only 32 page reads to find any specific value.

2³² = ~4 billion

and also

Log₂(~4 billion) = 32.

They’re literally inverse operations of each other.

How deep are real indexes?

Before we move on, let’s briefly look at how deep a real Postgres index is on a somewhat large amount of data. The books table with 90 million entries already has an index defined on the primary key id field, which is a 32 bit integer. Without going into gross detail about what all is stored on a B Tree node (N keys, N+1 offsets to other nodes, some metadata and headers, etc), ChatGPT estimates that Postgres can store between 400-500 key fields on an index on a 32 bit integer.

Let’s check that. There’s a Postgres extension for just this purpose.

CREATE EXTENSION IF NOT EXISTS pageinspect;

and then

SELECT * FROM bt_metap('books_pkey');

which produces

Note the level 3, which is what our index’s depth is. That means it would take just 3 page reads to arrive at the correct B Tree leaf for any value (this excludes reading the root node itself, which is usually just stored in memory).

Checking the math, the Log₄₅₀(90,000,000) comes out to … 2.998

Taking an index for a spin

Let’s run a quick query by id, with the primary key index that already exists, and then look at how we can create one on title, so we can re-run our query to find the first 10 books in order.

explain analyze
select *
from books
where id = 10000;

which produces the following

We’re running an index scan. No surprises there. The Index Cond

  Index Cond: (id = 10000)

is the condition Postgres uses to navigate the internal nodes; those were the gold nodes from the visualization before. In this case, it predictably looks for id = 10000

Re-visiting our titles sort

Let’s take a fresh look at this query.

select *
from books
order by title
limit 10;

This time let’s define an index, like so

CREATE INDEX idx_title ON books(title);

This index would look something like this (conceptually at least).

Now our query runs in less than a fifth of a millisecond.

Notice what’s missing from this execution plan, that was present on the previous query, when we looked for a single index value.

Did you spot it?

It’s the Index Cond. We’re not actually … looking for anything. We just want the first ten rows, sorted by title. The index stores all books, sorted by title. So the engine just hops right down to the start of the index, and simply reads the first ten rows from the leaf nodes (the blue nodes from the diagrams).

More fun with indexes

Let’s go deeper. Before we start, I’ll point out that values for the pages column was filled with random values from 100-700. So there are 600 possible values for pages, each randomly assigned.

Let’s look at a query to read the titles of books with the 3 maximum values for pages. And let’s pull a lot more results this time; we’ll limit it to one hundred thousand entries

explain analyze
select title, pages
from books
where pages > 697
limit 100000;

As before, we see a parallel sequential scan. We read through the table, looking for the first 100,000 rows. Our condition matches very few results, so the database has to discard through over 6 million records before it finds the first 100,000 matching our condition

Rows Removed by Filter: 6627303

The whole operation took 833ms.

Let’s define an index on pages

CREATE INDEX idx_pages ON books(pages);

You might notice that the pages column is by no means unique; but that doesn’t effect anything: the leaf pages can easily contain duplicate entries.

Everything else works the same as it always has: the database can quickly jump down to a specific value. This allows us to query a particular value, or even grab a range of values sorted on the column we just looked up. For example, if we want all books with pages > 500, we just seek to that value, and start reading.

Let’s re-run that query from before

explain analyze
select title, pages
from books
where pages > 697
limit 100000;

There’s a lot going on. Our index is being used, but not like before

->  Bitmap Index Scan on idx_pages  (cost=0.00..4911.16 rows=451013 width=0) (actual time=38.057..38.057 rows=453891 loops=1)

Bitmap scan means that the database is scanning our table, and noting the heap locations with records matching our filter. It literally builds a bitmap of matching locations, hence the name. It then sorts those locations in order of disk access.

It then pulls those locations from the heap. This is the Bitmap Heap Scan on line 5

->  Parallel Bitmap Heap Scan on books  (cost=5023.92..1441887.67 rows=187922 width=73) (actual time=41.383..1339.997 rows=33382 loops=3)

But remember, this is the heap, and it’s not ordered on pages, so those random locations may have other records not matching our filter. This is done in the Index Recheck on line 6

Recheck Cond: (pages > 697)

which removed 1,603,155 results.

Why is Postgres doing this, rather than just walking our index, and following the pointers to the heap, as before?

Postgres keeps track of statistics on which values are contained in its various columns. In this case, it knew that relatively few values would match on this filter, so it chose to use this index.

But that still doesn’t answer why it didn’t use a regular old index scan, following the various pointers to the heap. Here, Postgres decided that, even though the filter would exclude a large percentage of the table, it would need to read a lot of pages from the heap, and following all those random pointers from the index to the heap would be bad. Those pointers point in all manner of random directions, and Random I/O is bad. In fact, Postgres also stores just how closely, or badly those pointers correspond to the underlying order on the heap for that column via something called correlation. So if, somehow, the book entries in the heap just happened to be stored (more or less) in increasing values of pages, there would be a high correlation on the pages column, and this index would be more likely to be used for this query.

For these reasons, Postgres thought it would be better to use the index to only keep track of which heap locations had relevant records, and then fetch those heap locations in heap order, after the Bitmap scan sorted them. This results in neighboring chunks of memory in the heap being pulled together, rather than frequently following those random pointers from the index.

Again, Random I/O tends to be expensive and can hurt query performance. This was not faulty reasoning at all.

But in this case Postgres wagered wrong. Our query now runs slower than the regular table scan from before, on the same query. It now takes 1.38 seconds, instead of 833ms. Adding an index made this query run slower.

Was I forcing the issue with the larger limit of 100,000? Of course. My goal is to show how indexes work, how they can help, and occasionally, how they can lead the query optimizer to make the wrong choice, and hurt performance. But please don’t think an index causing a worse, slower execution plan is an unhinged, unheard of eventuality which I contrived for this post; I’ve seen it happen on very normal queries on production databases.

The road not traveled

Can we force Postgres to do a regular index scan, to see what might have been? It turns out we can; we can (temporarily) turn off bitmap scans, and run the same query.

SET enable_bitmapscan = off;

explain analyze
select title, pages
from books
where pages > 697
limit 100000;

and now our query runs in just 309ms

Clearly Postgres’s statistics led it astray this time. They’re based on heuristics and probabilities, along with estimated costs for things like disk access. It won’t always work perfectly.

When stats get things right

Before we move on, let’s query all the books with an above-average number of pages

explain analyze
select title, pages
from books
where pages > 400
limit 100000;

In this case Postgres was smart enough to not even bother with the index.

Postgres’ statistics told it that this query would match an enormous number of rows, and just walking across the heap would get it the right results more quickly than bothering with the index. And in this case it assumed correctly. The query ran in just 37ms.

Covering Indexes

Let’s go back to this query

explain analyze
select title, pages
from books
where pages > 697
limit 100000;

It took a little over 800ms with no index, and over 1.3 seconds with an index on just pages.

The shortcoming of our index was that it did not include title, which is needed for this query; Postgres had to keep running to the heap to retrieve it. 

Your first instinct might be to just add title to the index.

CREATE INDEX idx_pages_title ON books(pages, title);

Which would look like this:

This would work fine. We’re not needing to filter based on title, only pages. But having those titles in the gold non-leaf nodes wouldn’t hurt one bit. Postgres would just ignore it, find the starting point for all books with > 400 pages, and start reading. There’s be no need for heap access at all, since the titles are right there.

Let’s try it.

Our query now runs in just 32ms! And we have a new operation in our execution plan.

->  Index Only Scan using idx_pages_title on books  (cost=0.69..30393.42 rows=451013 width=73) (actual time=0.243..83.911 rows=453891 loops=1)

Index Only Scan means that only the index is being scanned. Again, there’s no need to look anything up in the heap, since the index has all that we need. That makes this a “covering index” for this query, since the index can “cover” it all.

More or less.

Heap Fetches: 0

That’s Line 4 above, and it is not as redundant as it might seem. Postgres does have to consult something called a visibility table to make sure the values in your index are up to date given how Postgres handles updates through it’s MVCC system. If those values are not up to date, it will have to hit the heap. But unless your data are changing extremely frequently this should not be a large burden.

In this case, it turns out Postgres had to go to the heap zero times.

A variation on the theme

If you’re using Postgres or Microsoft SQL Server you can create an even nicer version of this index. Remember, we’re not actually querying on title here, at all. We just put it in the index so the title values would be in the leaf nodes, so Postgres could read them, without having to visit the heap.

Wouldn’t it be nice it we could only put those titles in the leaf nodes? This would keep our internal nodes smaller, with less content, which, in a real index, would let us cram more key values together, resulting in a smaller, shallower B Tree that would potentially be faster to query.

We do this with the INCLUDE clause when creating our index (in databases that support this feature).

CREATE INDEX idx_pages_include_title ON books(pages) INCLUDE(title);

This tell Postgres to create our index on the pages column, as before, but also include the title field in the leaf nodes. It would look like this, conceptually.

And re-running that same query, we see that it does run a bit faster. From 32ms down to just 21ms.

To be clear, it’s quite fast either way, but a nice 31% speedup isn’t something to turn down if you’re using a database that supports this feature (MySQL does not).

Pay attention to your SELECT clauses

There’s one corollary to the above: don’t request things you don’t need in your queries; don’t default to SELECT *

Requesting only what you need will not only reduce the amount of data that has to travel over the wire, but in extreme cases can mean the difference between an index scan, and an index-only scan. In the above query, if we’d done SELECT * instead of SELECT title, pages, none of the indexes we added would have been able to help; those heap accesses would have continued to hurt us.

Wrapping up

To say that this post is only scratching the surface would be an understatement. The topic of indexing, and query optimization could fill entire books, and of course it has.

Hopefully, this post has you thinking about indexes the right way. Thinking about how indexes are stored on disk, and how they’re read. And never, ever forgetting about the fact that, when scanning an index, you may still need to visit the heap for every matched entry you find, which can get expensive.

Editor’s note: our The Complete Course for Building Backend Web Apps with Go includes setting up a PostgreSQL database and running it in Docker, all from scratch.

The code for everything in this post is on my GitHub repo.

Getting Started

We’ll look at a toy task management app that does minimal work so we can focus on state management. It shows a (static) list of tasks, a button to add a new task, a heading showing the number of tasks, and a component to change the UI view between three options.

Moreover, the same app was written 3 times, once using vanilla React context for state, once using Zustand simply but non-idiomatically, and then a third version using Zustand more properly, so we can see some of the performance benefits it offers.

Each of the three apps is identical, except for the label above the Add New Task button.

Each app is broken down more or less identically as so.

function App() {
  console.log("Rendering App");

  return (
    <div className="m-5 p-5 flex flex-col gap-2">
      <VanillaLabel />
      <AddNewTask />
      <TasksCount />
      <TasksHeader />
      <Filter />
      <TasksBody />
    </div>
  );
}

It’s probably more components than needed, but it’ll help us inspect render performance.

The state we need

Our state payload for this app will include an array of tasks, a method to update the tasks, the current UI view being displayed, a function to update it, and a current filter, with, of course, a method to update it.

Those values can all be declared as various pieces of state, and then passed down the component tree as needed. This is simple and it works, but the excessive amount of prop passing, often referred to as “prop drilling,” can get annoying pretty quickly. There are many ways to avoid this, from state management libraries like Zustand, Redux, and MobX, to the regular old React context.

In this post, we’ll first explore what this looks like using React context, and then we’ll examine how Zustand can simplify things while improving performance in the process.

The Vanilla Version

There’s a very good argument to be made that React’s context feature was not designed to be a state management library, but that hasn’t stopped many devs from trying. To avoid excessive prop drilling while minimizing external dependencies, developers will often store the state required for a specific part of their UI in context and access it lower in the component tree as needed.

Our app has its entire state stored like this, but that’s just a product of how unrealistically small it is.

Let’s get started. First, we have to declare our context

const TasksContext = createContext<TasksState>(null as any);

Then we need a component that renders a Provider for that context, while declaring, and then passing in the actual state

export const TasksProvider = ({ children }: { children: ReactNode }) => {
  console.log("Rendering TasksProvider");

  const [tasks, setTasks] = useState<Task[]>(dummyTasks);
  const [currentView, setCurrentView] = useState<TasksView>("list");
  const [currentFilter, setCurrentFilter] = useState<string>("");

  const value: TasksState = {
    tasks,
    setTasks,
    currentView,
    setCurrentView,
    currentFilter,
    setCurrentFilter,
  };

  return <TasksContext.Provider value={value}>{children}</TasksContext.Provider>;
};

The logging console.log("Rendering TasksProvider"); is present in every component in all versions of this app, so we can inspect re-renders.

Notice how we have to declare each piece of state with useState (or useReducer)

const [tasks, setTasks] = useState<Task[]>(dummyTasks);
const [currentView, setCurrentView] = useState<TasksView>("list");
const [currentFilter, setCurrentFilter] = useState<string>("");

and then splice it together in our big state payload, and then render our context provider

const value: TasksState = {
  tasks,
  setTasks,
  currentView,
  setCurrentView,
  currentFilter,
  setCurrentFilter,
};

return <TasksContext.Provider value={value}>{children}</TasksContext.Provider>;

To get the current context value in a component that wants to use it, we call the useContext hook, and pass in the context object we declared above. To simplify this, it’s not uncommon to build a simple hook for just this purpose.

export const useTasksContext = () => {
  return useContext(TasksContext);
};

Now components can grab whatever slice of state they need.

const { currentView, tasks, currentFilter } = useTasksContext();

What’s the problem?

This code is fine. It’s simple enough. And it works. I’ll be honest, though, as someone who works with code like this a lot, the boilerplate can become annoying pretty quickly. We have to declare each piece of state with the normal React primitives (useState, useReducer), and then also integrate it into our context payload (and typings). It’s not the worst thing to deal with; it’s just annoying.

Another downside of this code is that all consumers of this context will always rerender anytime any part of the context changes, even if that particular component is not using the part of the context that just changed. We can see that with the logging that’s in these components.

For example, changing the current UI view rerenders everything, even though only the task header, and task body read that state

Introducing Zustand

Zustand is a minimal but powerful state management library. To create state, Zustand gives you a create method

import { create } from "zustand";

It’s easier to show this than to describe it.

export const useTasksStore = create<TasksState>(set => ({
  tasks,
  setTasks: (arg: Task[] | ((tasks: Task[]) => Task[])) => {
    set(state => {
      return {
        tasks: typeof arg === "function" ? arg(state.tasks) : arg,
      };
    });
  },
  currentView: "list",
  setCurrentView: (newView: TasksView) => set({ currentView: newView }),
  currentFilter: "",
  setCurrentFilter: (newFilter: string) => set({ currentFilter: newFilter }),
}));

We pass a function to create and return our state. Just like that. Simple and humble. The function we pass also takes an argument, which I’ve called set. The result of the create function, which I’ve named useTasksStore here, will be a React hook that you use to read your state.

Updating state

Updating our state couldn’t be simpler. The set function we see above is how we do that. Notice our updating functions like this:

setCurrentView: (newView: TasksView) => set({ currentView: newView }),

By default set will take what we return, and integrate it into the state that’s already there. So we can return the pieces that have changed, and Zustand will handle the update.

Naturally, there’s an override: if we pass true for the second argument to set, then what we return will overwrite the existing state in its entirety.

clear: () => set({}, true);

The above would wipe our state, and replace it with an empty object; use this cautiously!

Reading our state

To read our state in the components which need it, we call the hook that was returned from create, which would be useTasksStore from above. We could read our state in the same way we read our context above

This is not the best way to use Zustand. Keep reading for a better way to use this API.

const { currentView, tasks, currentFilter } = useTasksStore();

This will work and behave exactly like our context example before.

This means changing the current UI view will again re-render all components that read anything from the Zustand store, whether related to this piece of state, or not.

The Correct Way to Read State

It’s easy to miss in the docs the first time you read them, but when reading from your Zustand store, you shouldn’t do this:

const { yourFields } = useTasksStore();

Zustand is well optimized, and will cause the component with the call to useTasksStore to only re-render when the result of the hook call changes. By default, it returns an object with your entire state. And when you change any piece of your state, the surrounding object will have to be recreated by Zustand, and will no longer match.

Instead, you should pass a selector argument into useTasksStore, in order to select the piece of state you want. The simplest usage would look like this

const currentView = useTasksStore(state => state.currentView);
const tasks = useTasksStore(state => state.tasks);
const currentFilter = useTasksStore(state => state.currentFilter);

Now our call returns only the currentView value in the first line, or our tasks array, or currentFilter in our second and third lines, respectively.

The value returned for currentView will only be different if you’ve changed that state value, and so on with tasks, and currentFilter. That means if none of these values have changed, then this component will not rerender, even if other values in our Zustand store have changed.

If you don’t like having those multiple calls, you’re free to use Zustand’s useShallow helper

import { useShallow } from "zustand/react/shallow";

// ...
const { tasks, setTasks } = useTasksStore(
  useShallow(state => ({
    tasks: state.tasks,
    setTasks: state.setTasks,
  }))
);

The useShallow hook lets us return an object with the state we want, and will trigger a rerender only if a shallow check on the properties in this object change.

If you want to save a few lines of code, you’re also free to return an array with useShallow.

const [tasks, setTasks] = useTasksStore(useShallow(state => [state.tasks, state.setTasks]));

This does the same thing.

The Zustand-optimized version of the app only uses the useTasksStore hook with a selector function, which means we can observe our improved re-rendering.

Changing the current UI view will only rerender the components that use the ui view part of the state.

For a trivial app like this, it probably won’t matter, but for a large app at scale, this can be beneficial, especially for users on slower devices.

Odds & Ends

The full Zustand docs are here. Zustand has a delightfully small surface area, so I’d urge you to just read the docs if you’re curious.

That being said, there are a few features worth noting here.

Async friendly

Zustand doesn’t care where or when the set function is called. You’re free to have async methods in your store, which call set after a fetch.

The docs offer this example:

const useFishStore = create(set => ({
  fishies: {},
  fetch: async pond => {
    const response = await fetch(pond);
    set({ fishies: await response.json() });
  },
}));

Reading state inside your store, but outside of set

We already know that we can call set(oldState => newState), but what if we need (or just want) to read the current state inside one of our actions, unrelated to an update?

It turns out create also has a second argument, get, that you can use for this very purpose

export const useTasksStore = create<TasksState>((set, get) => ({

And now you can do something like this

logOddTasks: () => {
  const oddTasks = get().tasks.filter((_, index) => index % 2 === 0);
  console.log({ oddTasks: oddTasks });
},

The first line grabs a piece of state, completely detached from any updates.

Reading state outside of React components

Zustand gives you back a React hook from create. But what if you want to read your state outside of a React component? Zustand attaches a getState() method directly onto your hook, which you can call anywhere.

useEffect(() => {
  setTimeout(() => {
    console.log("Can't call a hook here");
    const tasks = useTasksStore.getState().tasks;
    console.log({ tasks });
  }, 1000);
}, []);

Pushing further

Zustand also supports manual, fine-grained subscriptions; bindings for vanilla JavaScript, with no React at all; and integrates well with immutable helpers like Immer. It also has some other, more advanced goodies that we won’t try to cover here. Check out the docs if this post has sparked your interest!

Concluding Thoughts

Zustand is a wonderfully simple, frankly fun library to use to manage state management in React. And as an added bonus, it can also improve your render performance.

A quick intro on structural typing

In a nutshell, structural typing means that TypeScript cares only about the structure of your values, not the types they were declared with. That means the following code contains no errors:

class Thing1 {
  name: string = "";
}

class Thing2 {
  name: string = "";
}

let thing1: Thing1 = new Thing1();
let thing2: Thing1 = new Thing2();
let thing3: Thing1 = { name: "" };

Types are essentially contracts, and TypeScript cares only that you satisfy the contract with something that has what the original type specified.

Interestingly, this also means you can supply extraneous, superfluous “stuff” when satisfying types: the following also has no errors.

const val = {
  name: "",
  xyz: 12,
};

let thing4: Thing1 = val;

The Thing1 type only calls for a name property that’s a string. If you also specify other properties, TypeScript is (usually) OK with it. This might seem surprising coming from other languages, but it’s a pragmatic tradeoff given that TypeScript’s primary purpose is to provide some manner of type safety to a completely untyped programming language: JavaScript.

I said usually above because occasionally TypeScript will be a bit stricter about not allowing “extra” values like we saw above. In particular, when assigning an object literal to a variable that’s declared with a type, TypeScript will require a strict matching.

let thing4: Thing1 = val;

const val2: Thing1 = {
  name: "",
  xyz: 12,
  // Error: Object literal may only specify known properties, and 'xyz' does not exist in type 'Thing1'
};

This is called “excess property checking.” It happens when assigning an object literal to a variable with a declared type, like we just saw, and also when passing an object literal to a function parameter that has a declared type.

The satisfies keyword

To provide the most simplistic example of using satisfies, let’s go back to this code

const val3 = {
  name: "",
  xyz: 12,
};

Right now val3 has the inferred type

{
  name: string;
  xyz: number;
}

If we wanted, we could write this code like this:

const val3 = {
  name: "",
  xyz: 12,
  // Error: Object literal may only specify known properties, and 'xyz' does not exist in type 'Thing1'
} satisfies Thing1;

That produced the same error we saw before, and the same error we would have gotten if we had declared val3 as Thing1.

const val3: Thing1 = {
  name: "",
  xyz: 12,
  // Error: Object literal may only specify known properties, and 'xyz' does not exist in type 'Thing1'
};

The satisfies keyword allows you to assert that a certain value “satisfies” a given type, while preventing a wider type from being inferred.

Bear with me.

You’re probably thinking that this is completely pointless, since we can just move Thing1 up into a proper type declaration, and even save a few keystrokes while doing so!

But not all situations lend themselves to this solution.

Let’s take a look at a slightly more complex, more realistic example.

The satisfies Keyword in the Wild

This is a situation I actually ran into. I’ll do my best to simplify it, while keeping the realistic parts.

Imagine we’re writing an inventory management system. We have an inventory item type.

type InventoryItem = {
  sku: string;
  description: string;
  originCode?: string;
};

Maybe we have some external backend systems we need to fetch data from.

type BackendResponse = {
  item_sku: string;
  item_description: string;
  item_metadata: Record<string, string>;
  item_origin_code: string;
};

function getBackendResponse(): BackendResponse[] {
  return [];
}

The getBackendResponse function is hard coded to return an empty array, but just pretend it makes a request and returns actual data. Then pretend we want to take that data and actually insert it. We have a function to do the inserting; we’re only interested in the types though, so we’ll leave the implementation empty

function insertInventoryItems(items: InventoryItem[]) {}

Let’s put things together. Fetch some items from our external system, manipulate them into the proper structure for our own InventoryItem type, and then call our insertInventoryItems function

function main() {
  const backendItems = getBackendResponse();
  insertInventoryItems(
    backendItems.map(item => {
      return {
        sku: item.item_sku,
        description: item.item_description,
        originCodeXXXXX: item.item_origin_code,
      };
    })
  );
}

Unfortunately, this code has no errors, even though we completely fat-fingered the originCode property.

You already know that TypeScript will allow you to provide “extra” properties in places where excess property checking doesn’t exist, but you may be wondering why it’s not an error that we completely left off the real originCode property. The reason is that this is an optional property! That makes it all the more important that we disallow excess cruft.

You might be thinking that we can just restructure our code so that excess property checking is in place, and we certainly could do that

function main() {
  const backendItems = getBackendResponse();
  insertInventoryItems(
    backendItems.map(item => {
      const result: InventoryItem = {
        sku: item.item_sku,
        description: item.item_description,
        originCodeXXXXX: item.item_origin_code,
        // Error: Object literal may only specify known properties, but 'originCodeXXXXX'
        // does not exist in type 'InventoryItem'. Did you mean to write 'originCode'
      };
      return result;
    })
  );
}

This works and produces the error we want to see. But it’s just a byproduct of the (frankly weird) way we chose to write it, and this protection would disappear if anyone were to come along, see this weird, pointless intermediate variable declaration, and “helpfully” refactor the code to just immediately return the object literal like we just had.

The better solution is to use satisfies to prevent the unwanted widening; that’s why it exists!

function main() {
  const backendItems = getBackendResponse();
  insertInventoryItems(
    backendItems.map(item => {
      return {
        sku: item.item_sku,
        description: item.item_description,
        originCodeXXXXX: item.item_origin_code,
        // Error: Object literal may only specify known properties, but 'originCodeXXXXX'
        // does not exist in type 'InventoryItem'. Did you mean to write 'originCode'
      } satisfies InventoryItem;
    })
  );
}

Now we’re back to the more idiomatic code we started with, with the same strict checks we’re looking for.

Before we wrap up, let’s briefly consider this alternative you might be wondering about

function main() {
  const backendItems = getBackendResponse();
  insertInventoryItems(
    backendItems.map(item => {
      return {
        sku: item.item_sku,
        description: item.item_description,
        originCodeXXXXX: item.item_origin_code,
      } as InventoryItem;
    })
  );
}

This produces no errors at all. The as keyword is a typecast. It’s something to avoid; it essentially allows you to “lie” to the type checker and assert that a given expression matches a given type. In this case, the cast pointless because this object already matches the InventoryItem type. It has a sku, and a description. It also has some extra “stuff” but TypeScript doesn’t really mind. It’s the satisfies keyword which additionally forces TypeScript to also not allow a wider type, and therefor start minding about extra properties.

For completeness, this version of the casting code actually does fail

function main3() {
  const backendItems = getBackendResponse();
  insertInventoryItems(
    backendItems.map(item => {
      return {
        sku: item.item_sku,
        descriptionXXX: item.item_description,
        // Error: Conversion of type '{ sku: string; descriptionXXX: string; originCodeXXXXX: string; }' to type
        // 'InventoryItem' may be a mistake because neither type sufficiently overlaps with the other. If this
        // was intentional, convert the expression to 'unknown' first. Property 'description' is missing in type
        // '{ sku: string; descriptionXXX: string; originCodeXXXXX: string; }' but required in type 'InventoryItem'.
        originCodeXXXXX: item.item_origin_code,
      } as InventoryItem;
    })
  );
}

TypeScript will allow you to lie, but only so far. If the cast makes absolutely no sense, TypeScript won’t allow it. As the error indicates, if you, for some reason, actually wanted to go through with this code, you could do:

<code>as unknown as InventoryItem;</code>

since unknown is a “top” type, which means anything can be cast to it, and from it.

Wrapping up

Use satisfies when you want to prevent type widenings, in situations where a top-level variable declaration doesn’t quite fit well.

This is a post I’ve been looking forward to writing for a long time; it’s also a difficult one to write.

The goal (and challenge) will be to show why a server layer on top of a JavaScript router is valuable, and why TanStack Start’s implementation is unique compared to the alternatives (in a good way). From there, showing how TanStack Start actually works will be relatively straightforward. Let’s go!

Please keep in mind that, while this post discusses a lot of generic web performance issues, TanStack Start is still a React-specific meta-framework. It’s not a framework-agnostic tool like Astro. 

Why Server Rendering?

Client-rendered web applications, often called “Single Page Applications” or “SPAs” have been popular for a long time. With this type of app, the server sends down a mostly empty HTML page, possibly with some sort of splash image, loading spinner, or maybe some navigation components. It also includes, very importantly, script tags that load your framework of choice (React, Vue, Svelte, etc) and a bundle of your application code.

These apps were always fun to build, and in spite of the hate they often get, they (usually) worked just fine (any kind of software can be bad). Admittedly, they suffer a big disadvantage: initial render performance. Remember, the initial render of the page was just an empty shell of your app. This displayed while your script files loaded and executed, and once those scripts were run, your application code would most likely need to request data before your actual app could display. Under the covers, your app is doing something like this

The initial render of the page, from the web server, renders only an empty shell of your application. Then some scripts are requested, and then parsed and executed. When those application scripts run, you (likely) send some other requests for data. Once that is done, your page displays.

To put it more succinctly, with client-rendered web apps, when the user first loads your app, they’ll just get a loading spinner. Maybe your company’s logo above it, if they’re lucky.

This is perhaps an overstatement. Users may not even notice the delay caused by these scripts loading (which are likely cached), or hydration, which is probably fast. Depending on the speed of their network, and the type of application, this stuff might not matter much.

Maybe.

But if our tools now make it easy to do better, why not do better?

Server Side Rendering

With SSR, the picture looks more like this

The server sends down the complete, finished page that the user can see immediately. We do still need to load our scripts and hydrate, so our page can be interactive. But that’s usually fast, and the user will still have content to see while that happens.

Our hypothetical user now looks like this, since the server is responding with a full page the user can see.

Streaming

We made one implicit assumption above: that our data was fast. If our data was slow to load, our server would be slow to respond. It’s bad for the user to be stuck looking at a loading spinner, but it’s even worse for the user to be stuck looking at a blank screen while the server churns.

As a solution for this, we can use something called “streaming,” or “out of order streaming” to be more precise. The user still requests all the data, as before, but we tell our server “don’t wait for this/these data, which are slow: render everything else, now, and send that slow data to the browser when it’s ready.”

All modern meta-frameworks support this, and our picture now looks like this

To put a finer point on it, the server does still initiate the request for our slow data immediately, on the server during our initial navigation. It just doesn’t block the initial render, and instead pushes down the data when ready. We’ll look at streaming with Start later in this post.

Why did we ever do client-rendering?

I’m not here to tear down client-rendered apps. They were, and frankly still are an incredible way to ship deeply interactive user experiences with JavaScript frameworks like React and Vue. The fact of the matter is, server rendering a web app built with React was tricky to get right. You not only needed to server render and send down the HTML for the page the user requested, but also send down the data for that page, and hydrate everything just right on the client.

It’s hard to get right. But here’s the thing: getting this right is the one of the primary purposes of this new generation of meta-frameworks. Next, Nuxt, Remix, SvelteKit, and SolidStart are some of the more famous examples of these meta-frameworks. And now TanStack Start.

Why is TanStack Start different?

Why do we need a new meta-framework? There’s many possible answers to that question, but I’ll give mine. Existing meta-frameworks suffer from some variation on the same issue. They’ll provide some mechanism to load data on the server. This mechanism is often called a “loader,” or in the case of Next, it’s just RSCs (React Server Components). In Next’s (older) pages directory, it’s the getServerSideProps function. The specifics don’t matter. What matters is, for each route, whether the initial load of the page, or client-side navigation via links, some server-side code will run, send down the data, and then render the new page.

Need to bone up on React in general? Brian Holt’s Complete Intro to React and Intermediate React will get you there.

An Impedance Mismatch is Born

Notice the two worlds that exist: the server, where data loading code will always run, and the client. It’s the difference and separation between these worlds that can cause issues.

For example, frameworks always provide some mechanism to mutate data, and then re-fetch to show updated state. Imagine your loader for a page loads some tasks, user settings, and announcements. When the user edits a task, and revalidates, these frameworks will almost always re-run the entire loader, and superfluously re-load the user’s announcements and user settings, in addition to tasks, even though tasks are the only thing that changed.

Are there fixes? Of course. Many frameworks will allow you to create extra loaders to spread the data loading across, and revalidate only some of them. Other frameworks encourage you to cache your data. These solutions all work, but come with their own tradeoffs. And remember, they’re solutions to a problem that meta-frameworks created, by having server-side loading code for every path in your app.

Or what about a loader that loads 5 different pieces of data? After the page loads, the user starts browsing around, occasionally coming back to that first page. These frameworks will usually cache that previously-displayed page, for a time. Or not. But it’s all or none. When the loader re-runs, all 5 pieces of data will re-fire, even if 4 of them can be cached safely.

You might think using a component-level data loading solution like react-query can help. react-query is great, but it doesn’t eliminate these problems. If you have two different pages that each have 5 data sources, of which 4 are shared in common, browsing from the first page to the second will cause the second page to re-request all 5 pieces of data, even though 4 of them are already present in client-side state from the first page. The server is unaware of what happens to exist on the client. The server is not keeping track of what state you have in your browser; in fact the “server” might just be a Lambda function that spins up, satisfies your request, and then dies off.

In the picture, we can see a loader from the server sending down data for queryB, which we already have in our TanStack cache.

Where to, from here?

The root problem is that these meta-frameworks inevitably have server-only code running on each path, integrating with long-running client-side state. This leads to conflicts and inefficiencies which need to be managed. There’s ways of handling these things, which I touched on above. But it’s not a completely clean fit.

How much does it matter?

Let’s be clear right away: if this situation is killing performance of your site, you have bigger problems. If these extra calls are putting undue strain on your services, you have bigger problems.

That said, one of the first rules of distributed systems is to never trust your network. The more of these calls we’re firing off, the better the chances that some of them might randomly be slow for some reason beyond our control. Or fail.

We typically tolerate requesting more than we need in these scenarios because it’s hard to avoid with our current tooling. But I’m here to show you some new, better tooling that side-steps these issues altogether.

Isomorphic Loaders

In TanStack, we do have loaders. These are defined by TanStack Router. I wrote a three-part series on Router here. If you haven’t read that, and aren’t familiar with Router, give it a quick look.

Start takes what we already have with Router, and adds server handling to it. On the initial load, your loader will run on the server, load your data, and send it down. On all subsequent client-side navigations, your loader will run on the client, like it already does. That means all subsequent invocations of your loader will run on the client, and have access to any client-side state, cache, etc. If you like react-query, you’ll be happy to know that’s integrated too. Your react-query client can run on the server, to load, and send data down on the initial page load. On subsequent navigations, these loaders will run on the client, which means your react-query queryClient will have full access to the usual client-side cache react-query always uses. That means it will know what does, and does not need to be loaded.

It’s honestly such a refreshing, simple, and most importantly, effective pattern that it’s hard not being annoyed none of the other frameworks thought of it first. Admittedly, SvelteKit does have universal loaders which are isomorphic in the same way, but without a component-level query library like react-query integrated with the server.

TanStack Start

Enough setup, let’s look at some code. TanStack Start is still in beta, so some of the setup is still a bit manual, for now.

The repo for this post is here.

If you’d like to set something up yourself, check out the getting started guide. If you’d like to use react-query, be sure to add the library for that. You can see an example here. Depending on when you read this, there might be a CLI to do all of this for you.

This post will continue to use the same code I used in my prior posts on TanStack Router. I set up a new Start project, copied over all the route code, and tweaked a few import paths since the default Start project has a slightly different folder structure. I also removed all of the artificial delays, unless otherwise noted. I want our data to be fast by default, and slow in a few places where we’ll use streaming to manage the slowness.

We’re not building anything new, here. We’re taking existing code, and moving the data loading up to the server in order to get it requested sooner, and improve our page load times. This means everything we already know and love about TanStack Router is still 100% valid.

Start does not replace Router; Start improves Router.

Loading Data

All of the routes and loaders we set up with Router are still valid. Start sits on top of Router and adds server processing. Our loaders will execute on the server for the first load of the page, and then on the client as the user browses. But there’s a small problem. While the server environment these loaders will execute in does indeed have a fetch function, there are differences between client-side fetch, and server-side fetch—for example, cookies, and fetching to relative paths.

To solve this, Start lets you define a server function. Server functions can be called from the client, or from the server; but the server function itself always executes on the server. You can define a server function in the same file as your route, or in a separate file; if you do the former, TanStack will do the work of ensuring that server-only code does not ever exist in your client bundle.

Let’s define a server function to load our tasks, and then call it from the tasks loader.

import { getCookie } from "vinxi/http";
import { createServerFn } from "@tanstack/start";
import { Task } from "../../types";

export const getTasksList = createServerFn({ method: "GET" }).handler(async () => {
  const result = getCookie("user");

  return fetch(`http://localhost:3000/api/tasks`, { method: "GET", headers: { Cookie: "user=" + result } })
    .then(resp => resp.json())
    .then(res => res as Task[]);
});

We have access to a getCookie utility from the vinxi library on which Start is built. Server functions actually provide a lot more functionality than this simple example shows. Be sure to check out the docs to learn more.

If you’re curious about this fetch call:

fetch(`http://localhost:3000/api/tasks`, { method: "GET", headers: { Cookie: "user=" + result } });

That’s how I’m loading data for this project, on the server. I have a separate project running a set of Express endpoints querying a simple SQLite database. You can fetch your data however you need from within these server functions, be it via an ORM like Drizzle, an external service endpoint like I have here, or you could connect right to a database and query what you need. But that latter option should probably be discouraged for production applications.

Now we can call our server function from our loader.

loader: async ({ context }) => {
    const now = +new Date();
    console.log(`/tasks/index path loader. Loading tasks at + ${now - context.timestarted}ms since start`);
    const tasks = await getTasksList();
    return { tasks };
  },

That’s all there is to it. It’s almost anti-climactic. The page loads, as it did in the last post. Except now it server renders. You can shut JavaScript off, and the page will still load and display (and hyperlinks will still work).

Streaming

Let’s make the individual task loading purposefully slow (we’ll just keep the delay that was already in there), so we can see how to stream it in. Here’s our server function to load a single task.

export const getTask = createServerFn({ method: "GET" })
  .validator((id: string) => id)
  .handler(async ({ data }) => {
    return fetch(`http://localhost:3000/api/tasks/${data}`, { method: "GET" })
      .then(resp => resp.json())
      .then(res => res as Task);
  });

Note the validator function, which is how we strongly type our server function (and validate the inputs). But otherwise it’s more of the same.

Now let’s call it in our loader, and see about enabling streaming

Here’s our loader:

loader: async ({ params, context }) => {
    const { taskId } = params;

    const now = +new Date();
    console.log(`/tasks/${taskId} path loader. Loading at + ${now - context.timestarted}ms since start`);
    const task = getTask({ data: taskId });

    return { task };
  },

Did you catch it? We called getTask without awaiting it. That means task is a promise, which Start and Router allow us to return from our loader (you could name it taskPromise if you like that specificity in naming).

But how do we consume this promise, show loading state, and await the real value? There are two ways. TanStack Router defines an Await component for this. But if you’re using React 19, you can use the new use psuedo-hook.

import { use } from "react";

function TaskView() {
  const { task: taskPromise } = Route.useLoaderData();
  const { isFetching } = Route.useMatch();

  const task = use(taskPromise);

  return (
    <div>
      <Link to="/app/tasks">Back to tasks list</Link>
      <div className="flex flex-col gap-2">
        <div>
          Task {task.id} {isFetching ? "Loading ..." : null}
        </div>
        <h1>{task.title}</h1>
        <Link 
          params={{ taskId: task.id }}
          to="/app/tasks/$taskId/edit"
        >
          Edit
        </Link>
        <div />
      </div>
    </div>
  );
}

The use hook will cause the component to suspend, and show the nearest Suspense boundary in the tree. Fortunately, the pendingComponent you set up in Router also doubles as a Suspense boundary. TanStack is impressively well integrated with modern React features.

Now when we load an individual task’s page, we’ll first see the overview data which loaded quickly, and server rendered, above the Suspense boundary for the task data we’re streaming

When the task comes in, the promise will resolve, the server will push the data down, and our use call will provide data for our component.

React Query

As before, let’s integrate react-query. And, as before, there’s not much to do. Since we added the @tanstack/react-router-with-query package when we got started, our queryClient will be available on the server, and will sync up with the queryClient on the client, and put data (or in-flight streamed promises) into cache.

Let’s start with our main epics page. Our loader looked like this before:

async loader({ context, deps }) {
    const queryClient = context.queryClient;

    queryClient.ensureQueryData(
      epicsQueryOptions(context.timestarted, deps.page)
    );
    queryClient.ensureQueryData(
      epicsCountQueryOptions(context.timestarted)
    );
  }

That would kick off the requests on the server, but let the page render, and then suspend in the component that called useSuspenseQuery—what we’ve been calling streaming.

Let’s change it to actually load our data in our loader, and server render the page instead. The change couldn’t be simpler.

async loader({ context, deps }) {
  const queryClient = context.queryClient;

  await Promise.allSettled([
    queryClient.ensureQueryData(
      epicsQueryOptions(context.timestarted, deps.page)
    ),
    queryClient.ensureQueryData(
      epicsCountQueryOptions(context.timestarted)
    ),
  ]);
},

Note we’re awaiting a Promise.allSettled call here so the queries can run together. Make sure you don’t sequentially await each individual call, as that would create a waterfall, or use Promise.all, as that will quit immediately if any of the promises error out.

Streaming with react-query

As I implied above, to stream data with react-query, do the exact same thing, but don’t await the promise. Let’s do that on the page for viewing an individual epic.

loader: ({ context, params }) => {
  const { queryClient, timestarted } = context;

  queryClient.ensureQueryData(
    epicQueryOptions(timestarted, params.epicId)
  );
},

Now if this page is loaded initially, the query for this data will start on the server and stream to the client. If the data are pending, our suspense boundary will show, triggered automatically by react-query’s useSuspenseBoundary hook.

If the user browses to this page from a different page, the loader will instead run on the client, but still fetch those same data from the same server function, and trigger the same suspense boundary.

Parting Thoughts

I hope this post was useful to you. It wasn’t a deep dive into TanStack Start — the docs are a better venue for that. Instead, I hope I was able to show why server rendering can offer almost any web app a performance boost, and why TanStack Start is a superb tool for doing so. Not only does it simplify a great deal of things by running loaders isomorphically, but it even integrates wonderfully with react-query.

The react-query integration is especially exciting to me. It delivers component-level data fetching while still allowing for server fetching, and streaming—all without sacrificing one bit of convenience.

This post will introduce the platform, show how to deploy web apps, stand up databases, and some other fun things. If you leave here wanting to learn more, the docs are here and are outstanding.

What is Fly?

Where platforms like Vercel and Netlify run your app on serverless functions which spin up and die off as needed (typically running on AWS Lambda), Fly runs your machines on actual VM’s, running in their infrastructure. These VMs can be configured to scale up as your app’s traffic grows, just like with serverless functions. But as the continuously run, there is no cold start issues. That said, if you’re on a budget, or your app isn’t that important (or both) you can also configure Fly to scale your app down to zero machines when traffic dies. You’ll be billed essentially nothing during those periods of inactivity, though your users will see a cold start time if they’re the first to hit your app during an inactive period.

To be perfectly frank, the cold start problem has been historically exaggerated, so please don’t pick a platform just to avoid cold starts.

Why VMs?

You might be wondering why, if cold starts aren’t a big deal in practice, one should care about Fly using VMs instead of cloud functions. For me there’s two reasons: the ability to execute long-running processes, and the ability to run anything that will run in a Docker image. Let’s dive into both.

The ability to handle long-running processes greatly expands the range of apps Fly can run. They have turn-key solutions for Phoenix LiveView, Laravel, Django, Postgres, and lots more. Anything you ship on Fly will be via a Dockerfile (don’t worry, they’ll help you generate them). That means anything you can put into a Dockerfile, can be run by Fly. If there’s a niche database you’ve been wanting to try (Neo4J, CouchDB, etc), just stand one up via a Dockerfile (and both of those DBs have official images), and you’re good to go. New databases, new languages, new anything: if there’s something you’ve been wanting to try, you can run it on Fly if you can containerize it; and anything can be containerized.

But… I don’t know Docker

Don’t worry, Fly will, as you’re about to see, help you scaffold a Dockerfile from any common app framework. We’ll take a quick look at what’s generated, and explain the high points.

That said, Docker is one of the most valuable tools for a new engineer to get familiar with, so if Fly motivates you to learn more, so much the better!

If you’d like to go deeper on Docker, our course Complete Intro to Containers from Brian Holt is fantastic.

Let’s launch an app!

Let’s ship something. We’ll create a brand new Next.js app, using the standard scaffolding here.

We’ll create an app, run npm i and then npm run dev and verify that it works.

Now let’s deploy it to Fly. If you haven’t already, install the Fly CLI, and sign up for an account. Instructions can be found in the first few steps of the quick start guide.

To deploy an app on Fly, you need to containerize your app. We could manually piece together a valid Dockerfile that would run our Next app, and then run fly deploy. But that’s a tedious process. Thankfully Fly has made life easier for us. Instead, we can just run fly launch from our app’s root directory.

Fly easily detected Next.js, and then made some best guesses as to deployment settings. It opted for the third cheapest deployment option. Here’s Fly’s full pricing information. Fly let’s us accept these defaults, or tweak them. Let’s hit yes to tweak. We should be taken to the fly.io site, where our app is in the process of being set up.

For fun, let’s switch to the cheapest option, and change the region to Virginia (what AWS would call us-east-1).

Hit confirm, and return to your command line. It should finish setting everything up, which should look like this, in part.

If we head over to our Fly dashboard, we should see something like this:

We can then click that app and see the app’s details

And lastly, we can go to the URL listed, and see the app actually running!

Looking closer

There’s a number of files that Fly created for us. The two most important are the Dockerfile, and fly.toml. Let’s take a look at each. We’ll start with the Dockerfile.

# syntax = docker/dockerfile:1

# Adjust NODE_VERSION as desired
ARG NODE_VERSION=20.18.1
FROM node:${NODE_VERSION}-slim as base

LABEL fly_launch_runtime="Next.js"

# Next.js app lives here
WORKDIR /app

# Set production environment
ENV NODE_ENV="production"

# Throw-away build stage to reduce size of final image
FROM base as build

# Install packages needed to build node modules
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y build-essential node-gyp pkg-config python-is-python3

# Install node modules
COPY package-lock.json package.json ./
RUN npm ci --include=dev

# Copy application code
COPY . .

# Build application
RUN npm run build

# Remove development dependencies
RUN npm prune --omit=dev


# Final stage for app image
FROM base

# Copy built application
COPY --from=build /app /app

# Start the server by default, this can be overwritten at runtime
EXPOSE 3000
CMD [ "npm", "run", "start" ]

A Quick Detour to Understand Docker

Docker is a book unto its own, but as an extremely quick intro: Docker allows us to package our app into an “image.” Containers allow you to start with an entire operating system (almost always a minimal Linux distro), and allow you to do whatever you want with it. Docker then packages whatever you create, and allows it to be run. The Docker image is completely self-contained. You choose the whatever goes into it, from the base operating system, down to whatever you install into the image. Again, they’re self-contained.

Now let’s take a quick tour of the important pieces of our Dockerfile.

After some comments and labels, we find what will always be present at the top of a Dockerfile: the FROM command.

FROM node:${NODE_VERSION}-slim as base

This tells us the base of the image. We could start with any random Linux distro, and then install Node and npm, but unsurprisingly there’s already an officially maintained Node image: there will almost always be officially maintained Docker images for almost any technology. In fact, there’s many different Node images to choose from, many with different underlying base Linux distro’s.

There’s a LABEL that’s added, likely for use with Fly. Then we set the working directory in our image.

WORKDIR /app

We copy the package.json and lockfiles.

# Install node modules
COPY package-lock.json package.json ./

Then run npm i (but in our Docker image):

RUN npm ci --include=dev

Then we copy the rest of the application code:

# Copy application code
COPY . .

Hopefully you get the point. We won’t go over every line, here. But hopefully the general idea is clear enough, and hopefully you’d feel comfortable tweaking this if you wanted to. Two last points though. See this part:

# Install packages needed to build node modules
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y build-essential node-gyp pkg-config python-is-python3

That tells the Linux package manager to install some things Fly thinks Next might need, but in actuality probably doesn’t. Don’t be surprised if these lines are absent when you read this, and try for yourself.

Lastly, if you were wondering why the package.json and lockfiles were copied, followed by npm install and then followed by copying the rest of the application code, the reason is (Docker) performance. Briefly, each line in the Dockerfile creates a “layer.” These layers can be cached and re-used if nothing has changed. If anything has changed, that invalidates the cache for that layer, and also all layers after it. So you’ll want to push your likely-to-change work as low as possible. Your application code will almost always change between deployments; the dependencies in your package.json will change much less frequently. So we do that install first, by itself, so it will be more likely to be cached, and speed up our builds.

I tried my best to provide the absolute minimal amount of a Docker intro to make this post make sense, without being overhwelming. I hope I’ve succeeded. If you’d like to learn more, there’s tons of books and YouTube videos, and even an entire course here on Frontend Masters.

Fly.toml

Now let’s take a peek at the fly.toml file.

# fly.toml app configuration file generated for next-fly-test on 2024-11-28T19:04:19-06:00
#
# See https://fly.io/docs/reference/configuration/ for information about how to use this file.
#

app = 'next-fly-test'
primary_region = 'iad'

[build]

[http_service]
  internal_port = 3000
  force_https = true
  auto_stop_machines = 'stop'
  auto_start_machines = true
  min_machines_running = 0
  processes = ['app']

[[vm]]
  size = 'shared-cpu-1x'

This is basically the config file for the Fly app. The options for this file are almost endless, and are documented here. The three most important lines are the next three.

auto_stop_machines = 'stop'

This tells Fly to automatically kill machines when they’re not needed, when traffic is low on our app.

auto_start_machines = true

The line above tells Fly to automatically spin up new machines when it detects it needs to do so, given your traffic. Lastly, this line

min_machines_running = 0

That line allows us to tell Fly to always keep a minimum number of machines running, no matter how minimal your current traffic is. Setting it to zero allows for no machines to be running, which means your next visitor will see a slow response as the first machine spins up.

You may have noticed above that Fly spun up two machines initially, even though there was no traffic at all. It does this by default to give your app a higher availability, that is, in case anything happens to the one machine, the other will (hopefully) still be up and running. If you don’t want or need this, you can prevent it by passing --ha=false when you run fly launch or fly deploy (or you can just kill one of the machines in the dashboard – Fly will not re-create it on subsequent deploys).

Machines won’t bill you if they’re not running

When a machine is not running, you’ll be billed essentially zero for it. You’ll just pay $0.15 per GB, per month, per machine (machines will usually have only one GB).

Adding a database

You can launch a Fly app anytime with just a Dockerfile. You could absolutely find an official Postgres Docker image and deploy from that. But it turns out Fly has this built in. Let’s run fly postgres create in a terminal, and see what happens

It’ll ask you for a name and a region, and then how serious of a Postgres setup you want. Once it’s done, it’ll show you something like this.

The connection string listed at the bottom can be used to connect to your db from within another Fly app (which you own). But to run database creation and migration scripts, and for local development you’ll need to connect to this db on your local machine. To do that, you can run this:

fly proxy 5432 -a <your app name>

Now you can connect via the same connection string on your local machine, but on localhost:5432 instead of flycast:5432.

Making your database publicly available

It’s not ideal, but if you want to make your Fly pg box publicly available, you can. You basically have to add a dedicated ipv4 address to it (at a cost of $2 per month), and then tweak your config.

Consider using a dedicated host for serious applications.

Fly’s built-in Postgres support is superb, but there’s some things you’ll have to manage yourself. If that’s not for you, Supabase is a fully managed pg host, and it’s also superb. Fly even has a service for creating Supabase db’s on Fly infra, for extra low latency. It’s currently only in public alpha, but it might be worth keeping an eye on.

Interlude

If you just want a nice place to deploy your apps, what we’ve covered will suffice for the vast majority of use cases. I could stop this post here, but I’d be remiss if I didn’t show some of the cooler things you can do with Fly. Please don’t let what follows be indicative of the complexity you’ll normally deal with. We’ll be putting together a cron job for running Postgres backups. In practice, you’ll just use a mature DB provider like Supabase or PlanetScale, which will handle things like this for you.

But sometimes it’s fun to tinker, especially for side projects. So let’s kick the tires a bit and see what we can come up with.

Having Fun

One of Fly’s greatest strengths is its flexibility. You give it a Dockerfile, and it’ll run it. To drive that point home, let’s conclude this post with a fun example.

As much as I love Fly, it makes me a little uneasy that my database is running isolated in some VM under my account. Accidents happen, and I’d want automatic backups. Why don’t we build a Docker image to do just that?

I’ll want to run a script, written in TypeScript, preferably without hating my life: Bun is ideal for this. I’ll also need to run the actual pg_dump command. So what should I build my Dockerfile from: the bun image, which would lack to pg utilities, or the pg base, which wouldn’t have bun installed. I could do either, and use the Linux package manager to install what I need. But really, there’s a simpler way: use a multi-stage Docker build. Let’s see the whole Dockerfile

FROM oven/bun:latest AS BUILDER

WORKDIR /app

COPY . .

RUN ["bun", "install"]
RUN ["bun", "build", "index.ts", "--compile", "--outfile", "run-pg_dump"]

FROM postgres:16.4

WORKDIR /app
COPY --from=BUILDER /app/run-pg_dump .
COPY --from=BUILDER /app/run-backup.sh .

RUN chmod +x ./run-backup.sh

CMD ["./run-backup.sh"]

We start with a Bun image. We run a bun install to tell Bun to install what we need: aws sdk’s and such. Then we tell Bun to compile our script into a standalone executable: yes, Bun can do that, and yes: it’s that easy.

FROM postgres:16.4

Tells Docker to start a new stage, from a new (Postgres) base.

WORKDIR /app
COPY --from=BUILDER /app/run-pg_dump .
COPY --from=BUILDER /app/run-backup.sh .

RUN chmod +x ./run-backup.sh

CMD ["./run-backup.sh"]

This drops into the /app folder from the prior step, and copies over the run-pg_dump file, which Bun compiled for us, and also copies over run-backup.sh. This is a shell script I wrote. It runs pg_dump a few times, to generate the files the Bun script (run-pg_dump) is expecting, and then calls it. Here’s what that file looks like:

<strong>#!/bin/sh</strong>

PG_URI_CLEANED=$(echo ${PG_URI} | sed -e 's/^"//' -e 's/"$//')

pg_dump ${PG_URI_CLEANED} -Fc > ./backup.dump

pg_dump ${PG_URI_CLEANED} -f ./backup.sql

./run-pg_dump

This unhinged line:

PG_URI_CLEANED=$(echo ${PG_URI} | sed -e 's/^"//' -e 's/"$//')

is something ChatGPT helped me write, to strip the double quotes from my connection string environment variable.

Lastly, if you’re curious about the index.ts file Bun compiled into a standalone executable, this is it:

import fs from "fs";
import path from "path";

import { S3Client, PutObjectCommand } from "@aws-sdk/client-s3";

const numToDisplay = (num: number) => num.toString().padStart(2, "0");

const today = new Date();
const date = `${today.getFullYear()}/${numToDisplay(today.getMonth() + 1)}/${numToDisplay(today.getDate())}`;
const time = `${today.getHours()}-${numToDisplay(today.getMinutes())}-${numToDisplay(today.getSeconds())}`;
const filename = `${date}/${time}`;

const REGION = "us-east-1";
const dumpParams = {
  Bucket: "my-library-backups",
  Key: `${filename}.dump`,
  Body: fs.readFileSync(path.resolve(__dirname, "backup.dump")),
};
const sqlParams = {
  Bucket: "my-library-backups",
  Key: `${filename}.sql`,
  Body: fs.readFileSync(path.resolve(__dirname, "backup.sql")),
};

const s3 = new S3Client({
  region: REGION,
  credentials: {
    accessKeyId: process.env.AWS_ID!,
    secretAccessKey: process.env.AWS_SECRET!,
  },
});

s3.send(new PutObjectCommand(sqlParams))
  .then(() => {
    console.log("SQL Backup Uploaded!");
  })
  .catch(err => {
    console.log("Error: ", err);
  });

s3.send(new PutObjectCommand(dumpParams))
  .then(() => {
    console.log("Dump Backup Uploaded!");
  })
  .catch(err => {
    console.log("Error: ", err);
  });

I’m sure someone who’s actually good with Docker could come up with something better, but this works well enough.

To see this whole thing all together, in one place, you can see it in my GitHub.

Scheduling a custom job

We have a working, valid Docker image. How do we tell Fly to run it on an interval? Fly has a command just for that: fly machine run. In fact, it can take a schedule argument, to have Fly run it on an interval. Unfortunately, the options are horribly limited: only hourly, daily, and monthly. But, as a workaround you can run this command at different times: this will set up executions at whatever interval you selected, scheduled off of when you ran the command.

fly machine run . --schedule=daily

If you ran that command at noon, that will schedule a daily task that runs at noon every day. If you run that command again at 5pm, it will schedule a second task to run daily, at 5pm (without interfering with the first). Each job will have a dedicated machine, but will be idle when not running, which means it will cost you almost nothing; you’ll pay the normal $0.15 per month, per GB on the machine.

I hate this limitation in scheduling machines. In theory there’s a true cron job template here, but it’s not the simplest thing to look through.

Odds and ends

That was a lot. Let’s lighten things up a bit with some happy odds and ends, before we wrap up.

Custom domains

Fly makes it easy to add a custom domain to your app. You’ll just need to add the right records. Full instructions are here.

Secrets

You’ll probably have some secrets you want run in your app, in production. If you’re thinking you could just bundle a .env.prod file into your Docker image, yes, you could. But that’s considered a bad idea. Instead, leverage Fly’s secret management.

Learning More

This post started brushing up against some full-stack topics. If this sparked your interest, be sure to check out the entire course on full-stack engineering here on Frontend Masters.

Wrapping Up

The truth is we’ve truly, barely scratched the surface of Fly. For simple side projects what we’ve covered here is probably more than you’d need. But Fly also has power tools available for advanced use cases. The sky’s the limit!

Fly.io is a wonderful platform. It’s fun to work with, will scale to your application’s changing load, and is incredibly flexible. I urge you to give it a try for your next project.

I wrote about Drizzle previously. That post focused exclusively on the typed SQL API. This post will look at another drizzle feature: database migrations. Not only will Drizzle allow you to query your database via a strongly typed API, but it will also keep your object model and database in sync. Let’s get started!

Our Database

Drizzle supports Postgres, MySQL, and SQLite. For this post we’ll be using Postgres, but the idea is the same for all of them. If you’d like to follow along at home, I urge you to use Docker to spin up a Postgres database (or MySQL, if that’s your preference). If you’re completely new to Docker, it’s not terribly hard to get it installed. Once you have it installed, run this:

docker container run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -p 5432:5432 postgres:17.2-alpine3.20

That should get you a Postgres instance up and running that you can connect to on localhost, with a username and password of docker / docker. When you stop that process, your database will vanish into the ether. Restarting that same process will create a brand new Postgres instance with a completely clean slate, making this especially convenient for the type of testing we’re about to do: database migrations.

Incidentally, if you’d like to run a database that actually persists its data on your machine, you can mount a volume.

docker container run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -p 5432:5432 -v /Users/arackis/Documents/pg-data:/var/lib/postgresql/data postgres:17.2-alpine3.20

That does the same thing, while telling Docker to alias the directory in its image of /var/lib/postgresql/data (where Postgres stores its data) onto the directory on your laptop at /Users/arackis/Documents/pg-data. Adjust the latter path as desired. (The other path isn’t up for debate, as that’s what Postgres uses.)

Setting Up

We’ll get an empty app up (npm init is all we need), and then install a few things.

npm i drizzle-orm drizzle-kit pg

The drizzle-orm package is the main ORM that handles querying your database. The drizzle-kit package is what handles database migrations, which will be particularly relevant for this post. Lastly, the pg package is the Node Postgres drivers.

Configuring Drizzle

Let’s start by adding a drizzle.config.ts to the root of our project.

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  out: "./drizzle-schema",
  dbCredentials: {
    database: "jira",
    host: "localhost",
    port: 5432,
    user: "docker",
    password: "docker",
    ssl: false,
  },
});

We tell Drizzle what kind of database we’re using (Postgres), where to put the generated schema code (the drizzle-schema folder), and then the database connection info.

Wanna see more real-world use cases of Drizzle in action? Check out Scott Moss’ course Intermediate Next.js which uses it and gets into it when the project gets into data fetching needs.

The Database First Approach

Say we already have a database and want to generate a Drizzle schema from it. (If you want to go in the opposite direction, stay tuned.)

To create our initial database, I’ve put together a script, which I’ll put here in its entirety.

CREATE DATABASE jira;

\c jira

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    name VARCHAR(250),
    avatar VARCHAR(500)
);

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(250),
    epic_id INT,
    user_id INT
);

CREATE TABLE epics (
    id SERIAL PRIMARY KEY,
    name VARCHAR(250),
    description TEXT,
    due DATE
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(250)
);

CREATE TABLE tasks_tags (
    id SERIAL PRIMARY KEY,
    task INT,
    tag INT
);

ALTER TABLE tasks
    ADD CONSTRAINT fk_task_user
    FOREIGN KEY (user_id)
    REFERENCES users (id);

ALTER TABLE tasks
    ADD CONSTRAINT fk_task_epic
    FOREIGN KEY (epic_id)
    REFERENCES epics (id);

ALTER TABLE tasks_tags
    ADD CONSTRAINT fk_tasks_tags_tag
    FOREIGN KEY (tag)
    REFERENCES tags (id);

ALTER TABLE tasks_tags
    ADD CONSTRAINT fk_tasks_tags_task
    FOREIGN KEY (task)
    REFERENCES tasks (id);

This will construct a basic database for an hypothetical Jira clone. We have tables for users, epics, tasks and tags, along with various foreign keys connecting them. Assuming you have psql installed (can be installed via libpq), you can execute that script from the command line like this:

PGPASSWORD=docker psql -h localhost -p 5432 -U docker -f database-creation-script.sql

Now run this command:

npx drizzle-kit pull

This tells Drizzle to look at our database and generate a schema from it.

Files generated

Inside the drizzle-schema folder there’s now a schema.ts file with our Drizzle schema. Here’s a small sample of it.

import { pgTable, serial, varchar, foreignKey, integer } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const users = pgTable("users", {
  id: serial().primaryKey().notNull(),
  username: varchar({ length: 50 }),
  name: varchar({ length: 250 }),
  avatar: varchar({ length: 500 }),
});

export const tasks = pgTable(
  "tasks",
  {
    id: serial().primaryKey().notNull(),
    name: varchar({ length: 250 }),
    epicId: integer("epic_id"),
    userId: integer("user_id"),
  },
  table => {
    return {
      fkTaskUser: foreignKey({
        columns: [table.userId],
        foreignColumns: [users.id],
        name: "fk_task_user",
      }),
      fkTaskEpic: foreignKey({
        columns: [table.epicId],
        foreignColumns: [epics.id],
        name: "fk_task_epic",
      }),
    };
  }
);

The users entity is a table with some columns. The tasks entity is a bit more interesting. It’s also a table with some columns, but we can also see some foreign keys being defined.

In Postgres, foreign keys merely create a constraint that’s checked on inserts and updates to verify that a valid value is set, corresponding to a row in the target table. But it has no effect on application code, so you might wonder why Drizzle saw fit to bother creating it. Essentially, Drizzle will allow us to subsequently modify our schema in code, and generate an SQL file that will make equivalent changes in the database. For this to work, Drizzle needs to be aware of things like foreign keys, indexes, etc, so the schema in code, and the database are always truly in sync, and Drizzle knows what’s missing, and needs to be created.

Relations

The other file Drizzle created is relations.ts. Here’s a bit of it:

import { relations } from "drizzle-orm/relations";

export const tasksRelations = relations(tasks, ({ one, many }) => ({
  user: one(users, {
    fields: [tasks.userId],
    references: [users.id],
  }),
  epic: one(epics, {
    fields: [tasks.epicId],
    references: [epics.id],
  }),
  tasksTags: many(tasksTags),
}));

export const usersRelations = relations(users, ({ many }) => ({
  tasks: many(tasks),
}));

This defines the relationships between tables (and is closely related to foreign keys). If you choose to use the Drizzle query API (the one that’s not SQL with types), Drizzle is capable of understanding that some tables have foreign keys into other tables, and allows you to pull down objects, with related objects in one fell swoop. For example, the tasks table has a user_id column in it, representing the user it’s assigned to. With the relationship set up, we can write queries like this:

const tasks = await db.query.tasks.findMany({
  with: {
    user: true,
  },
});

This will pull down all tasks, along with the user each is assigned to.

Making Changes (Migrations)

With the code generation above, we’d now be capable of using Drizzle. But that’s not what this post is about. See my last post on Drizzle, or even just the Drizzle docs for guides on using it. This post is all about database migrations. So far, we took an existing database, and scaffolded a valid Drizzle schema. Now let’s run a script to add some things to the database, and see about updating our Drizzle schema.

We’ll add a new column to tasks called importance, and we’ll also add an index on the tasks table, on the epic_id column. This is unrelated to the foreign key we already have on this column. This is a traditional database index that would assist us in querying the tasks table on the epic_id column.

Here’s the SQL script we’ll run:

CREATE INDEX idx_tasks_epic ON tasks (epic_id);

ALTER TABLE tasks
    ADD COLUMN importance INT;

After running that script on our database, we’ll now run:

npx drizzle-kit pull

Our terminal should look like this:

We can now see our schema updates in the git diffs:

Note the new columns being added, and the new index being created. Again, the index will not affect our application code; it will make our Drizzle schema a faithful representation of our database, so we can make changes on either side, and generate updates to the other. To that end, let’s see about updating our code, and generating SQL to match those changes.

The Code First Approach

Let’s go the other way. Let’s start with a Drizzle schema, and generate an SQL script from it. In order to get a Drizzle schema, let’s just cheat and grab the schema.ts and relations.ts files Drizzle created above. We’ll paste them into the drizzle-schema folder, and remove anything else Drizzle created: any snapshots, and anything in the meta folder Drizzle uses to track our history.

Next, since we want Drizzle to read our schema files, rather than just generate them, we need to tell Drizzle where they are. We’ll go back into our drizzle.config.ts file, and add this line:

schema: ["./drizzle-schema/schema.ts", "./drizzle-schema/relations.ts"],

Now run:

npx drizzle-kit generate

Voila! We have database assets being created.

The resulting sql file is huge. Mine is named 0000_quick_wild_pack.sql (Drizzle will add these silly names to make the files stand out) and looks like this, in part.

CREATE TABLE IF NOT EXISTS "epics" (
	"id" serial PRIMARY KEY NOT NULL,
	"name" varchar(250),
	"description" text,
	"due" date
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "tags" (
	"id" serial PRIMARY KEY NOT NULL,
	"name" varchar(250)
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "tasks" (
	"id" serial PRIMARY KEY NOT NULL,
	"name" varchar(250),
	"epic_id" integer,
	"user_id" integer
);

Making a schema change

Now let’s make some changes to our schema. Let’s add that same importance column to our tasks table, add that same index on epicId, and then, for fun, let’s tell Drizzle that our foreign key on userId should have an ON DELETE CASCADE rule, meaning that if we delete a user, the database will automatically delete all tasks assigned to that user. This would probably be an awful rule to add to a real issue tracking software, but it’ll help us see Drizzle in action.

Here are the changes:

And now we’ll run npx drizzle-kit generate and you should see:

As before, Drizzle generated a new sql file, this time called 0001_curved_warhawk.sql which looks like this:

ALTER TABLE "tasks" DROP CONSTRAINT "fk_task_user";
--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "importance" integer;--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "tasks" ADD CONSTRAINT "fk_task_user" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "idx_tasks_epicId" ON "tasks" USING btree ("epic_id");

It added a column, overwrote the foreign key constraint we already had to add our CASCADE rule, and created our index on epic_id.

Mixing & Matching Approaches

Make no mistake, you do not have to go all in on code-first, or database-first. You can mix and match approaches. You can scaffold a Drizzle schema from a pre-existing database using drizzle-kit pull, and then make changes to the code, and generate sql files to patch your database with the changes using drizzle-kit generate. Try it and see!

Going Further

Believe it or not, we’re only scratching the surface of what drizzle-kit can do. If you like what you’ve seen so far, be sure to check out the docs.

Concluding Thoughts

Drizzle is an incredibly exciting ORM. Not only does it manage to add an impressive layer of static typing on top of SQL, allowing you to enjoy the power and flexibility of SQL with the type safety you already expect from TypeScript. But it also provides an impressive suite of commands for syncing your changing database with your ORM schema.