Skip to main content

​
Overview

The items table stores the main item data for the Cat Data API. Each item has a name, optional description, and references an image through a foreign key relationship.

​
Schema

​
id
integer
required
Primary key. Auto-incrementing integer identifier for the item.
​
name
string
required
Unique item name. Cannot be null and must be unique across all items.
​
description
text
Optional text description of the item. Can be null.
​
image_id
integer
Foreign key reference to the images table. Links this item to an image. Can be null.
​
created_at
timestamp
required
Timestamp when the item record was created. Automatically set by the database.
​
updated_at
timestamp
required
Timestamp when the item record was last updated. Automatically updated by the database.

​
Constraints

  • Primary Key: id
  • Unique: name - Each item must have a unique name
  • Not Null: name - Item name is required
  • Foreign Key: image_id references images(id)

​
Relationships

​
Images Relationship

  • Many-to-One: Multiple items can reference the same image
  • Foreign Key: image_id → images.id
  • Cascade Delete: When an image is deleted, all items referencing that image are automatically deleted

​
Tags Relationship

  • Many-to-Many: Items can have multiple tags through the items_to_tags junction table
  • Junction Table: items_to_tags links items and tags
  • Cascade Delete: When an item is deleted, all associations in items_to_tags are automatically removed

​
Delete Behavior

The CASCADE delete behavior ensures referential integrity:
  • If an image is deleted → all items with that image_id are automatically deleted
  • If an item is deleted → all tag associations in items_to_tags are automatically deleted

​
Migration

The items table is created using Knex.js migration: 
/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function(knex) {
  return knex.schema.createTable('items', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable().unique();
    table.text('description');
    table.integer('image_id').unsigned().references('id').inTable('images').onDelete('CASCADE');
    table.timestamps(true, true);
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function(knex) {
  return knex.schema.dropTableIfExists('items');
};
Migration File: 20250717082805_create_items_table.js

​
TypeScript Types

interface Item {
  id: number;
  name: string;
  description?: string | null;
  image_id?: number | null;
  created_at?: Date;
  updated_at?: Date;
}

interface ItemWithImage extends Item {
  image?: {
    id: number;
    name: string;
  };
}

interface ItemWithTags extends Item {
  tags?: Array<{
    id: number;
    name: string;
  }>;
}

​
Example Queries

​
Get Item with Image

const item = await db('items')
  .select('items.*', 'images.name as image_name')
  .leftJoin('images', 'items.image_id', 'images.id')
  .where('items.id', itemId)
  .first();

​
Get Item with Tags

const itemWithTags = await db('items')
  .select('items.*', db.raw('json_agg(tags.*) as tags'))
  .leftJoin('items_to_tags', 'items.id', 'items_to_tags.item_id')
  .leftJoin('tags', 'items_to_tags.tag_id', 'tags.id')
  .where('items.id', itemId)
  .groupBy('items.id')
  .first();

Build docs developers (and LLMs) love

Get started for freeTalk to us