How do I insert records in a node-pg-migrate migration?

Question

I'm trying to use node-pg-migrate to handle migrations for an ExpressJS app. I can translate most of the SQL dump into pgm.func() type calls, but I can't see any method for handling actual INSERT statements for initial data in my solution's lookup tables.

Answer 1

It is possible using the pgm.sql catch all:

pgm.sql(`INSERT INTO users (username, password, created, forname, surname, department, reviewer, approver, active) VALUES
('rd@example.com', 'salty', '2019-12-31 11:00:00', 'Richard', 'Dyce', 'MDM', 'No', 'No', 'Yes');`)

Note the use of backtick (`) to allow breaking the SQL statement across multiple lines.

Answer 2

You can use raw sql if you needed. Create a migration file with the extension .sql and write usual requests.

This article has a great example.

My example:

-- Up Migration

CREATE TABLE users
(
    id         BIGSERIAL    NOT NULL PRIMARY KEY,
    username   VARCHAR(50) NOT NULL,
    email      VARCHAR(50) NOT NULL,
    password   VARCHAR(50) NOT NULL,
    class_id   INTEGER      NOT NULL,
    created_at DATE         NOT NULL,
    updated_at DATE         NOT NULL
);

CREATE TABLE classes
(
    id          INTEGER NOT NULL PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    health      INTEGER      NOT NULL,
    damage      INTEGER      NOT NULL,
    attack_type VARCHAR(50) NOT NULL,
    ability     VARCHAR(50) NOT NULL,
    created_at  DATE         NOT NULL,
    updated_at  DATE         NOT NULL
);

INSERT INTO classes (id,
                     name,
                     health,
                     damage,
                     attack_type,
                     ability,
                     created_at,
                     updated_at)
VALUES (0,
        'Thief',
        100,
        25,
        'Archery Shot',
        'Run Away',
        NOW(),
        NOW());

-- Down Migration

DROP TABLE users;

DROP TABLE classes;