Skip to main content

​
Overview

The images table stores image metadata for the Cat Data API. Each image has a unique name and is referenced by items through a foreign key relationship.

​
Schema

​
id
integer
required
Primary key. Auto-incrementing integer identifier for the image.
​
name
string
required
Unique image name. Cannot be null and must be unique across all images.
​
created_at
timestamp
required
Timestamp when the image record was created. Automatically set by the database.
​
updated_at
timestamp
required
Timestamp when the image record was last updated. Automatically updated by the database.

​
Constraints

  • Primary Key: id
  • Unique: name - Each image must have a unique name
  • Not Null: name - Image name is required

​
Relationships

  • One-to-Many: An image can be referenced by multiple items through the items.image_id foreign key
  • Cascade Delete: When an image is deleted, all associated items are automatically deleted

​
Migration

The images table is created using Knex.js migration: 
/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function(knex) {
  return knex.schema.createTable('images', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable().unique();
    table.timestamps(true, true); // created_at, updated_at
  });
};

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

​
Database Functions

The following TypeScript functions interact with the images table:

​
addImage

Creates a new image record. 
export async function addImage(
  name: string
): Promise<number> {
  const [result] = await db('images')
    .insert({ name })
    .returning('id');

  return result.id ?? result;
}
Parameters:
  • name (string): Unique name for the image
Returns: The ID of the newly created image

​
getImage

Retrieves a single image by ID. 
export async function getImage(
  id: number
): Promise<{ id: number; name: string }> {
  const row = await db('images')
    .select('id', 'name')
    .where({ id })
    .first();

  return row;
}
Parameters:
  • id (number): Image ID to retrieve
Returns: Image object with id and name properties

​
deleteImage

Deletes an image by ID. 
export async function deleteImage(id: number): Promise<void> {
  await db('images').where({ id }).del();
}
Parameters:
  • id (number): Image ID to delete
Returns: void Note: Deleting an image will cascade delete all associated items.

​
getAllImages

Retrieves all images from the database. 
export async function getAllImages(): Promise<{ id: number; name: string }[]> {
  return db('images').select('id', 'name');
}
Returns: Array of image objects with id and name properties

​
TypeScript Types

interface Image {
  id: number;
  name: string;
  created_at?: Date;
  updated_at?: Date;
}