knex: .returning('foo') returns primary key instead of 'foo' column in sqlite3

I have a node app that I switch back and forth between sqlite3 and postgres.

When I my npm script for using sqlite3 I get back a primary key rather than the column I specified. Postgres is returning the correct column.

No matter what I put in for .returning('*'/['uuid']/'name' .....) I seem to be getting the primary key in sqlite3.

This is how the docs describe the intended behavior.

// Returns [ { id: 1, title: 'Slaughterhouse Five' } ]
knex('books')
  .returning(['id','title'])
  .insert({title: 'Slaughterhouse Five'})

// Returns [ { id: 1, title: 'Slaughterhouse Five' } ]
knex('books')
  .returning(['id','title'])
  .insert({title: 'Slaughterhouse Five'})

The Query in Question

add: function (d, results) {
    return db('output')
      .returning('uuid')
      .insert({
        classifiers: JSON.stringify(d.classifiers),
        created: now(),
        datasource: d.datasource,
        files: JSON.stringify(d.files),
        name: d.name,
        results: results,
        uuid: uuid.v4()
      })
      .then((ret) => ret[0])
  },

Screenshot of data screen shot 2016-09-12 at 2 20 08 pm

Config File

var prod = process.env.PROD_CONN_STRING,
  dev = process.env.DEV_CONN_STRING,
  test = process.env.TEST_CONN_STRING;

module.exports = {

  test: {
    client: 'pg',
    connection: test,
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  demo: {
    client: 'sqlite3',
    connection: {
      filename: './tcu.sqlite'
    }
  },

  dev: {
    client: 'pg',
    connection: dev,
    migrations: {
      tableName: 'knex_migrations'
    }
  },


  prod: {
    client: 'pg',
    connection: prod,
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }
};

This might be a bug or perhaps the docs are lacking. Either way I can try to take a look at it if a collaborator can point me in the right direction.

About this issue

  • Original URL
  • State: closed
  • Created 8 years ago
  • Comments: 16 (9 by maintainers)

Commits related to this issue

Most upvoted comments

I have an app using a psql database and some unit tests which use mock-knex and thus set sqlite as the backend. Since my upgrade to knex 0.14.4 the unit tests display this output:

    ✓ obtains a lock and process a book (54ms)
Knex:warning - .returning() is not supported by sqlite3 and will not have any effect.     
Knex:warning - .returning() is not supported by sqlite3 and will not have any effect.     
Knex:warning - .returning() is not supported by sqlite3 and will not have any effect.     

I know that returning() doesn’t work with sqlite (it’s written in the doc) and my tests already take that into account (even if actually it doesn’t matter, since I’m mocking the queries) so it would be nice to have a way to suppress the warning if possible (couldn’t find anything in the docs). thanks!

Aside from the issue at hand I don’t know methods by hand that aren’t supported by specific dialects, so in the spirit of not escalating the issue any further, I will only add a warning for .returning to sqlite.

Knex:warning - .returning() is not supported by sqlite3 and will not have any effect.

#2471

Sorry for the confusion. At the beginning of returning docs, the first line reads “Utilized by PostgreSQL, MSSQL, and Oracle databases, the returning method specifies which column should be returned by the insert and update methods.”

I guess we could be a little clearer in the wording there, but the implication is that this feature isn’t supported by sqlite3.

We could also look into throwing an error to fail when trying to use this feature on an unsupported client. Thoughts?