如何在Knex迁移中创建PostgreSQL整数数组列及Deal表结构

Hey there! Let's walk through exactly how to set up your Deal table with an integer array column using Knex for PostgreSQL. I'll break this down step by step so it's easy to follow.

Step 1: Generate a Knex Migration File #

First, use Knex's CLI to create a new migration file. This will give you the skeleton for your table creation script:

knex migrate:make create_deal_table

This creates a timestamped file in your migrations directory (like 20240520123456_create_deal_table.js).

Step 2: Write the Migration Script #

Open the generated file and replace the default code with this:

exports.up = function(knex) {
  return knex.schema.createTable('deal', (table) => {
    // Auto-incrementing primary key
    table.increments('id').primary();
    
    // PostgreSQL integer array column (for linking to other table IDs)
    table.specificType('deal', 'integer[]')
      .defaultTo('{}') // Optional: Set default to empty array instead of NULL
      .comment('Array of IDs from related table'); // Optional: Add a comment for clarity
    
    // Optional: Add created/updated timestamps (auto-managed by Knex)
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  // Rollback: Drop the table if it exists
  return knex.schema.dropTableIfExists('deal');
};

Key Details Explained #

• table.increments('id'): Creates an auto-incrementing integer primary key. Knex automatically adapts this to PostgreSQL's serial or identity type (depending on your database version), so you don't have to worry about low-level syntax.
• table.specificType('deal', 'integer[]'): This is the critical part for the array column. Since Knex's cross-database abstract layer can be inconsistent with array types, using specificType directly tells PostgreSQL we want an integer[] (integer array) column. This ensures the type is exactly what you need for linking to other table IDs.
• .defaultTo('{}'): Optional but useful—sets the default value to an empty array instead of NULL, which avoids unexpected null errors when inserting records without specifying the deal column.

Step 3: Run the Migration #

Execute the migration to create the table in your PostgreSQL database:

knex migrate:latest

Bonus: Working with the Array Column #

Once the table is created, here are some common operations you might need:

Insert Data with an Array #

// Insert a record linking to IDs 101, 102, and 103 from another table
await knex('deal').insert({
  deal: [101, 102, 103]
});

Query Records with Specific IDs in the Array #

// Find all deals where the array contains ID 102
const matchingDeals = await knex('deal')
  .whereRaw('? = ANY(deal)', [102]);

// Find deals where the array contains ALL of the specified IDs (101 and 102)
const inclusiveDeals = await knex('deal')
  .whereRaw('deal @> ARRAY[?, ?]::integer[]', [101, 102]);

内容的提问来源于stack exchange，提问作者Sudhir Roy