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
Primary key. Auto-incrementing integer identifier for the image.
Unique image name. Cannot be null and must be unique across all images.
Timestamp when the image record was created. Automatically set by the database.
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');
};
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;
}
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;
}
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();
}
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');
}
id and name properties
TypeScript Types
interface Image {
id: number;
name: string;
created_at?: Date;
updated_at?: Date;
}