INSERT

INSERT statement is used to insert a single record or multiple records into a table.
More about INSERT statement can be at:
PostgreSQL - https://www.postgresql.org/docs/11/sql-insert.html
MySQL - https://dev.mysql.com/doc/refman/8.0/en/insert.html
MariaDB - https://mariadb.com/kb/en/library/update/

Following clauses are supported:

• INSERT(columns...) - list of columns for insert
• VALUES(values...) - list of values
• MODEL(model) - list of values for columns will be extracted from model object
• MODELS([]model) - list of values for columns will be extracted from list of model objects
• QUERY(select) - select statement that supplies the rows to be inserted.
• ON CONFLICT - specifies an alternative action to raising a unique violation or exclusion constraint violation error (PostgreSQL only).
• ON DUPLICATE KEY UPDATE - enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY(MySQL and MariaDB).
• RETURNING(output_expression...) - An expressions to be computed and returned by the INSERT statement after each row is inserted. The expressions can use any column names of the table. Use TableName.AllColumns to return all columns. (PostgreSQL only)

This list might be extended with feature Jet releases.

Example

Insert row by row

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
    VALUES(101, "http://www.google.com", "Google", DEFAULT).
    VALUES(102, "http://www.yahoo.com", "Yahoo", nil)

Debug SQL of above insert statement:

INSERT INTO test_sample.link (id, url, name, description) VALUES
     (100, 'http://www.postgresqltutorial.com', 'PostgreSQL Tutorial', DEFAULT),
     (101, 'http://www.google.com', 'Google', DEFAULT),
     (102, 'http://www.yahoo.com', 'Yahoo', NULL)

There is also type-safe insert notation using model types(recommended):

tutorial := model.Link{
    ID:   100,
    URL:  "http://www.postgresqltutorial.com",
    Name: "PostgreSQL Tutorial",
}

google := model.Link{
    ID:   101,
    URL:  "http://www.google.com",
    Name: "Google",
}

yahoo := model.Link{
    ID:   102,
    URL:  "http://www.yahoo.com",
    Name: "Yahoo",
}

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    MODEL(turorial).
    MODEL(google).
    MODEL(yahoo)

Or event shorter if model data is in the slice:

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    MODELS([]model.Link{turorial, google, yahoo})

Link.ID, Link.URL, Link.Name, Link.Description - is the same as Link.AllColumns so above statement can be simplified to:

insertStmt := Link.INSERT(Link.AllColumns).
    MODELS([]model.Link{turorial, google, yahoo})

Link.ID is a primary key autoincrement column so it can be omitted in INSERT statement.
Link.MutableColumns - is shorthand notation for list of all columns minus primary key columns.

insertStmt := Link.INSERT(Link.MutableColumns).
    MODELS([]model.Link{turorial, google, yahoo})

Inserts using VALUES, MODEL and MODELS can appear as the part of the same insert statement.

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description, Link.Description).
    VALUES(101, "http://www.google.com", "Google", DEFAULT, DEFAULT).
    MODEL(turorial).
    MODELS([]model.Link{yahoo})

Insert using query

// duplicate first 10 entries
insertStmt := Link.
    INSERT(Link.URL, Link.Name).
    QUERY(
        SELECT(Link.URL, Link.Name).
            FROM(Link).
            WHERE(Link.ID.GT(Int(0)).AND(Link.ID.LT_EQ(10))),
    )

[PostgreSQL] Insert with ON CONFLICT update

Employee.INSERT(Employee.AllColumns).
MODEL(employee).
ON_CONFLICT(Employee.EmployeeID).DO_NOTHING()

Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
ON_CONFLICT(Link.ID).DO_UPDATE(
    SET(
        Link.ID.SET(Link.EXCLUDED.ID),
        Link.URL.SET(String("http://www.postgresqltutorial2.com")),
    ),
)

Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
ON_CONFLICT(Link.ID).
    WHERE(Link.ID.MUL(Int(2)).GT(Int(10))).
    DO_UPDATE(
        SET(
            Link.ID.SET(
                IntExp(SELECT(MAXi(Link.ID).ADD(Int(1))).
                    FROM(Link)),
            ),
            ColumnList{Link.Name, Link.Description}.SET(ROW(Link.EXCLUDED.Name, String("new description"))),
        ).WHERE(Link.Description.IS_NOT_NULL()),
    )

[MySQL] Insert with ON DUPLICATE KEY UPDATE

Link.INSERT().
VALUES(randId, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
ON_DUPLICATE_KEY_UPDATE(
    Link.ID.SET(Link.ID.ADD(Int(11))),
    Link.Name.SET(String("PostgreSQL Tutorial 2")),
)

Execute statement

To execute insert statement and get sql.Result:

res, err := insertStmt.Exec(db)

To execute insert statement and return records inserted, insert statement has to have RETURNING clause:

insertStmt := Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
    VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
    VALUES(101, "http://www.google.com", "Google", DEFAULT).
    RETURNING(Link.ID, Link.URL, Link.Name, Link.Description)  // or RETURNING(Link.AllColumns)
    
dest := []model.Link{}

err := insertStmt.Query(db, &dest)

Use ExecContext and QueryContext to provide context object to execution.

SQL table used for the example:

CREATE TABLE IF NOT EXISTS link (
    id serial PRIMARY KEY,
    url VARCHAR (255) NOT NULL,
    name VARCHAR (255) NOT NULL,
    description VARCHAR (255)
);