insertQuery Function Parameters

import {insertQuery} from "agile-query"

1. table (string)

   • Description: The name of the table into which you want to insert data.
   • Required: Yes
   • Example: 'users'

2. insertData (Record<string, string | number> | Record<string, string | number>[])

   • Description: The data to be inserted. This can either be a single object (for inserting a single row) or an array of objects (for inserting multiple rows). Each object represents a column-value pair for the insert.
   • Required: Yes
   • Example:

     • Single row:

       Copy

       insertData: { name: 'John', age: 30 }
       

     • Multiple rows:

       Copy

       insertData: [
           { name: 'Alice', age: 25 },
           { name: 'Bob', age: 28 }
       ]
       

3. dateFields (Array<string>, optional)

   • Description: An optional array of column names that should have a default value of CURRENT_TIMESTAMP when inserting data. These columns will be set to the current timestamp automatically.

   • Required: No

   • Example:

     Copy

     dateFields: ['created_at', 'updated_at']
     

4. uniqueColumn (string | null, optional)

   • Description: A column name to ensure that duplicate rows are avoided. When this parameter is provided, the query will use INSERT IGNORE, which prevents inserting duplicate rows based on this unique column.

   • Required: No

   • Example:

     Copy

     uniqueColumn: 'email'
     

5. onDuplicateUpdateFields (string[], optional)

   • Description: A list of columns that should be updated if a duplicate row is found (based on the unique column). This is used with the ON DUPLICATE KEY UPDATE feature to specify which columns to update in case of duplication.

   • Required: No

   • Example:

     Copy

     onDuplicateUpdateFields: ['name', 'age']
     

Example of Full insertQuery Call with Parameter Descriptions

const query = insertQuery({
    table: 'users',                                 // Name of the table to insert data into
    insertData: [                                   // Data to insert (array of rows)
        { name: 'John', age: 30 },
        { name: 'Alice', age: 25 }
    ],
    dateFields: ['created_at', 'updated_at'],       // Add timestamp columns
    uniqueColumn: 'email',                          // Ensure no duplicate email entries
    onDuplicateUpdateFields: ['name', 'age']        // Update name and age if duplicate email found
});

console.log(query);
// Output: INSERT IGNORE INTO users (name, age, created_at, updated_at)
//         VALUES ('John', 30, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
//                ('Alice', 25, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
//         ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

Parameter Descriptions

1. table (string)

   • Description: The name of the table to insert data into.
   • Example: 'users'

2. insertData (Record<string, string | number> | Record<string, string | number>[])

   • Description: The data to insert. It can be either a single object for inserting a single row or an array of objects for inserting multiple rows.
   • Example:

     • Single row:

       Copy

       insertData: { name: 'John', age: 30 }
       

     • Multiple rows:

       Copy

       insertData: [
           { name: 'Alice', age: 25 },
           { name: 'Bob', age: 28 }
       ]
       

3. dateFields (Array<string>, optional)

   • Description: An array of column names that should be populated with the current timestamp (CURRENT_TIMESTAMP).
   • Example: ['created_at', 'updated_at']

4. uniqueColumn (string | null, optional)

   • Description: A column that ensures unique rows (usually the unique or primary key). If specified, the query will use INSERT IGNORE to avoid inserting duplicate rows based on this column.
   • Example: 'email'

5. onDuplicateUpdateFields (string[], optional)

   • Description: A list of columns that will be updated if a row already exists with the same value for the uniqueColumn. This uses the ON DUPLICATE KEY UPDATE feature in SQL.
   • Example: ['name', 'age']

Error Handling

• Empty insertData: If no data is provided in insertData, an error will be thrown.

  • Example:

    Copy

    insertQuery({
        table: 'users',         // Valid table name
        insertData: [],         // Empty data array will trigger an error
    }); // Error: "Insert data array is empty"
    

Example Usages

1. Single Row Insert with Date Fields

const query = insertQuery({
    table: 'users',
    insertData: { id: 1, name: 'John', email: 'john@example.com' },
    dateFields: ['created_at']
});

console.log(query);
// Output: INSERT INTO users (id, name, email, created_at) VALUES (1, 'John', 'john@example.com', CURRENT_TIMESTAMP)

2. Multiple Row Insert with Date Fields

const query = insertQuery({
    table: 'users',
    insertData: [
        { id: 1, name: 'John', email: 'john@example.com' },
        { id: 2, name: 'Jane', email: 'jane@example.com' }
    ],
    dateFields: ['created_at']
});

console.log(query);
// Output: INSERT INTO users (id, name, email, created_at) VALUES (1, 'John', 'john@example.com', CURRENT_TIMESTAMP), (2, 'Jane', 'jane@example.com', CURRENT_TIMESTAMP)

3. Insert with ON DUPLICATE KEY UPDATE

const query = insertQuery({
    table: 'users',
    insertData: { id: 1, name: 'John', email: 'john@example.com' },
    onDuplicateUpdateFields: ['name', 'email']
});

console.log(query);
// Output: INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com') ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email)

4. Insert with INSERT IGNORE to Prevent Duplicates

const query = insertQuery({
    table: 'users',
    insertData: { id: 1, name: 'John', email: 'john@example.com' },
    uniqueColumn: 'id'
});

console.log(query);
// Output: INSERT IGNORE INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')

Notes and Edge Cases

• Empty insertData: If insertData is an empty object or array, an error is thrown (Insert data array is empty).
• Invalid dateFields: If dateFields are provided, but they don't match the columns in insertData, it will result in mismatched columns, which would be invalid SQL. Make sure the fields exist in the table schema.
• Handling Complex Data Types: The function assumes that the data in insertData is serializable into a string format. If there are nested objects or non-primitive types, they might not be correctly formatted for SQL.