Insert to Database in Custom Module

Hey all,

I’m creating a custom module to insert records to the database (I’m not using the standard db modules in SC because I have some very specific checks/matches, redundant record deletions etc…) and I was wondering if the following will actually insert. Currently, the module runs successfully, however, it does not insert or update the database.

// Insert new family charge
    await db.raw(`
        INSERT INTO
            charges_family (
                uuid, family_uuid, total, title, reference, description, chargeFor_monthly, due_date, chargeType, basetotal, discounttotal, chargeDate, createdby, created
            )
        VALUES(
            ${uuid}, ${d.family_uuid}, ${d.totals.enrolsgrand}, ${title}, ${reference}, ${description}, ${d.monthlycharge}, ${due_date}, 'tuition', ${d.totals.baseRate}, ${d.totals.disctotal}, ${charge_date}, ${user}, ${created}
        )
    `);

I do have the relevant connection settings:
image

Thanks in advance for any guidance.

Cheers
Michael

You may want to use the module only to perform your special validation and just return a simple Boolean on whether or not the validation passed or not. Then based on the result, perform the insert or not using standard actions.

Hey @mebeingken,

What I have set up is monthly tuition charges. These are broken down per student per week because:

  1. Multi-enrolment discounts.
  2. Multi-student discounts.
  3. Holding fee discounts.

When changing or adding enrolments to the family, all current and future tuition fees are automatically recalculated. Those weekly enrolments charges are checked to see if they are still valid according to the calculations (another module), then inserted, updated or deleted accordingly. The parent “family charge” is also updated or inserted.

I have done some searches regarding inserts and updates with Knex, and I have landed on the following. Whether this is completely correct or not, I’m not 100% sure, however, it was suggested in a Knex git issues thread, and it works! :smiley:

// Insert new family charge
await db('charges_family')
        .insert(db.raw(`
        (
            uuid, family_uuid, total, title, reference, description, chargeFor_monthly, dueDate, chargeType, basetotal, discounttotal, chargeDate, createdby, created
        )
        VALUES(
            '${uuid}', '${d.family_uuid}', ${d.totals.enrolsgrand}, '${title}', ${'\'' + reference + '\'' || null}, ${'\'' + description + '\'' || null}, '${d.monthlycharge}', '${due_date}', 'tuition', ${d.totals.baseRate}, ${d.totals.disctotal}, '${charge_date}', '${user}', ${timestamp}
        )
`));

Cheers
Michael