Insert into database

Job Writing
1

Hello,

I want to perform an ‘insert into’ into a specific table in my database without using upsert unless upsert can do the job I want. The reason for this, is that I want to check a specific value coming from a Kobotoolbox form and only insert if the if statement is true. Here is an example of code I want to type:

if(changedInfo === ‘yes’){
INSERT INTO personalcontactdetails AS a (job, school ) VALUES (‘engineer’,‘Brentford High’) WHERE a.id = id
}

Thanks

Is this possible? and the database is a postgres one

2

@Mamadou has better knowledge of most of the database adaptors (language-postgresql, language-mysql, language-mssql, language-mongodb) so I’ll defer to him, but my sense is that you’ll need to wrap that if/else bit in an operation. depending on the adaptor, you’ll have access to one or both of the generic “do-some-custom-stuff” operations: fn(state => state) or alterState(state => state).

Note that if you’re writing custom code inside one of these operations, you’ll likely need to call insert or upsert or sql with (state) because they all return functions that take state. With alterState(...) and insert(...) (from language-postgres) you could run:

alterState(state => {

  if (state.data.changedInfo === 'yes') {
    return insert('personalcontactdetails', { job: 'engineer', school: 'Brentford High' })(state)
  } else {
    console.log('Not gonna do anything!')
    return state;
  }

});

If you wanted to upsert, you’d need to specify the unique key. So instead of that return insert(...)(state) bit you’d write this (assuming that the id you mentioned in the post is a column in your personalcontactdetails table that has a unique constraint):

return upsert(
  'personalcontactdetails', // the table
  'id', // the column or unique constraint
  { // the data...
    job: 'engineer',
    school: 'Brentford High',
    id: state.data.someId // where do you get your id from in Kobo?
  }
)(state)
3

Hey @brendonGS,

Taylor’s suggestions would be perfect and will allow you to make any data cleaning/transformation you are willing to do before inserting/upserting your data.

In anoher note, if there is no need for steps before inserting, we now have one new helper function in postgresql and mssql which is upsertIf. It works same as upsert but also add a condition to be checked before executing.

It works as this

upsertIf(
    yourLogical, 
    'users', // the DB table
    'id',
    { name: 'Elodie', id: 7 },
)

yourLogical here is a statement that can return true or false. In your case it could be

upsertIf(
    PathToCHANGEdInfo === ‘yes’), 
    'yourTable', // the DB table
    'YourUUID',
    { YourMapping },
)
1 Like
4

Thank you @taylordowns2000 and @Mamadou. That was exactly what I was looking for.

1 Like