Database definition with drizzle | Local-only calories tracker app

The first step is defining the data layer.

The structure of the app is derived from the shape of the data. This step is therefore the most important to get right 👀

We use drizzle to define each table and their relations. Pglite is a local postgres database, therefore we are going to use drizzle-orm/pg-core to define the database schema:

pnpm add drizzle-orm

We want to be as strict as possible with database types. Ideally the database schema should not allow invalid data to be inserted.

In a single drizzle.ts file we define each table as a separate variable using pgTable. Below an example of the food table:

export const foodTable = pgTable(
  "food",
  {
    // 👇 Auto generated primary key
    id: integer().primaryKey().generatedAlwaysAsIdentity(),

    // 👇 Make names unique
    name: varchar({ length: 255 }).notNull().unique(),

    brand: varchar({ length: 255 }),
    calories: integer().notNull(),
    fats: integer().notNull(),

    // 👇 Default values are optional
    fatsSaturated: integer().notNull().default(0),
    salt: integer().notNull().default(0),
    carbohydrates: integer().notNull(),
    fibers: integer().notNull().default(0),
    sugars: integer().notNull().default(0),
    proteins: integer().notNull(),
  },
  (table) => {
    return {
      // 👇 Index columns to speed up queries (search by name)
      nameIdx: index("name_idx").on(table.name),
    };
  }
);

We won't go into the details of SQL tables and how to define them. Relational databases design is out of scope (but make sure to get this step right in your app, it's key 🙌)

Make sure to define relations and use features like pgEnum to make the database schema more strict:

export const mealEnum = pgEnum("meal", [
  "breakfast",
  "lunch",
  "dinner",
  "snacks",
]);

export const servingTable = pgTable("serving", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  quantity: integer().notNull(),

  // 👇 Use enums to make the database schema more strict
  meal: mealEnum().notNull(),

  // 👇 Relations using references
  foodId: integer("food_id")
    .references(() => foodTable.id)
    .notNull(),
  dailyLogDate: date("daily_log_date")
    .references(() => dailyLogTable.date)
    .notNull(),
});

Generate migrations using `drizzle-kit`

We can use drizzle-kit to generate migrations from the database schema:

pnpm add -D drizzle-kit

We define the configuration inside a new drizzle.config.ts file in the root of the project:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./src/drizzle", // 👈 Output directory
  schema: "./src/schema/drizzle.ts", // 👈 Schema file (created above)

  dialect: "postgresql",
});

We also add a generate script to package.json:

package.json

{
  "scripts": {
    "generate": "pnpm drizzle-kit generate"
  }
}

Running pnpm generate will generate the migrations in the src/drizzle directory.

We are going to use the generated .sql files to handle local schema migrations.

drizzle folder generated, containing the migration files as .sql, as well as other metadata.

Generate migrations using drizzle-kit

Generate migrations using `drizzle-kit`