Checkout

Migrations

As discussed in class, typically you aren't going to have a full understanding of the schema prior to deployment. As a result, it will need to be altered regularly as you build features. In fact, attempting to fully design a schema before building features that use it is a great way to over-complicate the database.

These revisions are typically known as migrations, and different ORMs handle them in different ways. The way Prisma handles it is to use a schema file to dictate the structure of the database.

I created an example table to demonstrate how this works. After adding the model, I ran this to apply the change to the database:

$ npx prisma migrate dev --name example
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "verceldb", schema "public" at "ep-still-credit-a4o5lgah.us-east-1.aws.neon.tech:5432"

Applying migration `20240327142754_example`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20240327142754_example/
    └─ migration.sql

Your database is now in sync with your schema.

Running generate... (Use --skip-generate to skip the generators)

added 1 package, and audited 211 packages in 2s

49 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

✔ Generated Prisma Client (v5.11.0) to ./node_modules/@prisma/client in 32ms
      

As you can see, the command also generated this file, which specifies the actual sql that needs to be run to align the database with the schema file.

Once you have your database set up, you should run npx prisma migrate dev yourself after pulling these changes down, what you'll find is that the database you created in the last step now also contains a table called Example. It accomplishes this by identifying any migration files not yet applied and running the sql in them. At this point, you can even run your local dev server and build up your own list of examples.

In a production grade application, the migration would be run against production as part of the deployment process. This assignment is already complicated enough, though, so I'll accomplish this by running migrations manually after merging your changes.

Working With Objects

The reason ORMs get their name is because they take information in the database and map them to objects. Instead of writing a select query and manipulating the results, you are able to call a function that constructs the query and maps it to an object. You can see that in the example here, here's the relevant line:

const records = await this.client.Example.findMany();

Underneath, this is generating and executing a SELECT query. It's then taking the results and mapping it to an array of objects that looks like this:

[{
  id: 1,
  value: "one",
}]
      

In practice, it doesn't mean you can forget about the database, but it can simplify basic use cases. If you start getting into complex queries, you'll find yourself fighting with the tool, but it's extremely helpful 99% of the time.

Testable Design

Let's begin by looking at the logic to list examples:

export async function example_list(table) {
    return await table.all();
}
      

Notice how it's completely unaware of the fact that it's interacting with Prisma or the database. You get a clue to that purpose with the variable table, but that's just a name to make how it's used clearer.

Because of this inversion of dependencies, it's possible to pass any object into this function that implements all. Most importantly, look at one of the tests for this function:

import { Examples } from "./database/memory";

test("one example", async () => {
    const value = "value";
    expect(await example_list(new Examples([value]))).toContainEqual(value);
});

      

Notice that this test uses an in-memory database. This isn't anything fancy, all we're really doing is maintaining a list inside of a class that can stand in for the database. Here's the code for that class, which is what we'll be using when running our tests.

export class Examples extends MemoryTable {
    async all() {
        return this.records;
    }

    async add(value) {
        this.records.push(value);
    }
}
      

When running with the actual site, on the other hand, you would use the Prisma implementation. Notice how it implements all of the same methods, and neatly hides the implementation details of interacting with that database behind this abstraction.

export class Examples extends PrismaTable {
    async all() {
        const records = await this.client.Example.findMany();
        return records.map((record) => record.value);
    }

    async add(value) {
        await this.client.Example.create({
            data: { value },
        });
    }
}
      

Now, when building a list of examples to display, this class can be used instead. The all method in both cases returns a list of strings. When interacting with examples when loading the page, they would be loaded like this:

import { example_list } from "$lib/server/example_list";
import { Examples } from "$lib/server/database/prisma";

export async function load() {
    return {
        values: await example_list(new Examples()),
    };
}
      

When building the code to allow checkout, you will need to build classes that also extend MemoryTable and PrismaTable. You should then build functions in src/lib/server that have unit tests attached to them using the MemoryTable implementation. All the code included in +page.server.js should accomplish is connecting the DatabaseTable implementation to a function call and defining the returned data structure.