The Best Approach for Storing Data in a Database

When it comes to storing data in a database, there are different approaches you can take. In this article, we'll explore several of these approaches and highlight the pros and cons of each one.

Approach 1

The first approach involves defining each column in the insert statement. Here's an example of what this approach might look like in code:

async function storeUser({ firstName, lastName }) {
  try {
    const [id] = await knex('users')
      .insert({
        first_name: firstName,
        last_name: lastName
      });

    return id;
  } catch (error) {
    throw error;
  }
}

While this approach works fine when you're dealing with a small number of columns, it quickly becomes unwieldy when the number of columns grows.

Approach 2

The second approach involves passing in the entire payload as an object. Here's an example:

Copy code
async function storeUser(payload) {
  try {
    const [id] = await knex('users')
      .insert(payload);

    return id;
  } catch (error) {
    throw error;
  }
}

This approach is more versatile, since it can handle any number of columns. However, it's also more vulnerable to errors, since any extra or misspelled columns in the payload will cause an error.

Approach 3

The third approach is similar to the second one, but with an added twist. Instead of passing in the entire payload, you only pass in the fields that you want to store, and the function filters out any extraneous columns. Here's an example:

const _pickBy = require('lodash/pickBy');

async function storeUser(payload) {
  const fillables = ['first_name', 'last_name'];

  try {
    const [id] = await knex('users')
      .insert(_pickBy(payload, (val, key) => fillables.includes(key)));

    return id;
  } catch (error) {
    throw error;
  }
}

This approach is less error-prone than the second one, since it filters out any unregistered properties. However, it only accepts an object, which can be limiting.

The Best Approach

So, which approach is the best? After considering the pros and cons of each one, we recommend a fourth approach:

const _castArray = require('lodash/castArray');
const _pickBy = require('lodash/pickBy');
const _isNil = require('lodash/isNil');

async function storeUser(payload) {
  const fillables = new Set(['first_name', 'last_name']);

  try {
    const data = _castArray(payload)
      .map(row => _pickBy(row, (val, key) => ! _isNil(val) && fillables.has(key)));

    const [id] = await knex('users').insert(data);

    return id;
  } catch (error) {
    throw error;
  }
}

This approach takes advantage of ES6's Set to ensure faster lookups. It can accept both an object and an array, and it omits any unregistered properties in the payload. It also uses several helpful functions from the Lodash library.