null values are inserted as string "null"

[damingo]

A query query like this

await db.execute('INSERT INTO settings(name, value) VALUES($1, $2)', ['test', null])

inserts the string "null" into the value column.

[joehillen]

Is there a workaround for this?

[damingo]

Is there a workaround for this?

A composed query similar to this:

    // Executes a "Insert if not exists"
    // table: Table name
    // columnNames: (Array) The table must have a 'uid' that must not be passed in.
    // fields:
    async insert({table, columnNames, fields}) {
        try {
            // `uid` must be the first column as it is referenced in the insert statement (see below).
            columnNames = ['uid', ...columnNames]
            let columns = []
            let columnReferences = []
            let values = []
            let counter = 1
            // NOTE: A composed query is required as NULL values in the values array are otherwise inserted
            // as string values 'NULL'. (2022-03-02)
            // See: https://github.com/tauri-apps/tauri-plugin-sql/issues/94 columns with NULL values must be excluded.
            columnNames.filter(attr => fields.hasOwnProperty(camelize(attr)) && fields[camelize(attr)] !== null)
                .forEach(attr => {
                    columns.push(attr)
                    columnReferences.push(`$${counter}`)
                    values.push(fields[camelize(attr)])
                    counter += 1
                })
            let query = `INSERT INTO ${table} (${columns.join(', ')})
                         SELECT ${columnReferences.join(', ')}
                         WHERE NOT EXISTS(SELECT 1 FROM ${table} WHERE uid = $1)`
            return await this.db.execute(query, values)
        } catch (error) {
            this.handleError(error)
        }
    }

[olingern]

Possibly related https://github.com/tauri-apps/tauri-plugin-sql/issues/121#issuecomment-1293810650