Insert into relationship table using id created at user registration

Question

I have two tables as seen below

The first table is for users and is populated via a registration form on the client side. When a new user is created, I need the second 'quotas' table to be populated with date, amount, and linked with the user id. The 'user_id' is used to pull the quotas information in a GET and display client side. I am having issues using the 'id' to populate the second table at the time of creation. I am using knex to make all queries. Would I be using join to link them in knex?

server

hydrateRouter   // get all users
    .route('/api/user')
    .get((req, res) => {
        knexInstance
            .select('*')
            .from('hydrate_users')
            .then(results => {
                res.json(results)
            })
    })
    .post(jsonParser, (req, res, next) => {  // register new users
        const { username, glasses } = req.body;
        const password = bcrypt.hashSync(req.body.password, 8);
        const newUser = { username, password, glasses };
        knexInstance
            .insert(newUser)
            .into('hydrate_users')
            .then(user => {
                res.status(201).json(user);
            })
            .catch(next);
    })

client

export default class Register extends React.Component {
  constructor(props) {
    super(props);
    this.state = {
      username: '',
      password: '',
      glasses: 0
    }
  }

  handleSubmit(event) {
    event.preventDefault();
    fetch('http://localhost:8000/api/user', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json'
      },
      body: JSON.stringify(this.state) 
    })
    .then(response => response.json())
    .then(responseJSON => {
      this.props.history.push('/login');
    })
  }

server side route for displaying the water amount

hydrateRouter
    .route('/api/user/waterconsumed/:user_id')  // display water consumed/day
    .all(requireAuth)
    .get((req, res, next) => {
        const {user_id} = req.params;
        knexInstance
            .from('hydrate_quotas')
            .select('amount')
            .where('user_id', user_id)
            .first()
            .then(water => {
                res.json(water)
            })
            .catch(next)
    })

Thank you!

Answer 1

Getting the id of an inserted row

So this is a common pattern in relational databases, where you can't create the egg until you have the unique id of the chicken that lays it! Clearly, the database needs to tell you how it wants to refer to the chicken.

In Postgres, you can simply use Knex's .returning function to make it explicit that you want the new row's id column returned to you after a successful insert. That'll make the first part of your query look like this:

knexInstance
  .insert(newUser)
  .into('users')
  .returning('id')

Note: not all databases support this in the same way. In particular, if you happen to be developing locally using SQLite, it will return the number of rows affected by the query, not the id, since SQLite doesn't support SQL's RETURNING. Best is just to develop locally using Postgres to avoid nasty surprises.

Ok, so we know which chicken we're after. Now we need to make sure we've waited for the right id, then go ahead and use it:

.then(([ userId ]) => knexInstance
  .insert({ user_id: userId,
            date: knex.fn.now(),
            amount: userConstants.INITIAL_QUOTA_AMOUNT })
  .into('quotas')
)

Or however you choose to populate that second table.

Note: DATE is a SQL keyword. For that reason, it doesn't make a great column name. How about created or updated instead?

Responding with sensible data

So that's basic "I have the ID, let's insert to another table" strategy. However, you actually want to be able to respond with the user that was created... this seems like sensible API behaviour for a 201 response.

What you don't want to do is respond with the entire user record from the database, which will expose the password hash (as you're doing in your first code block from your question). Ideally, you'd probably like to respond with some UI-friendly combination of both tables.

Luckily, .returning also accepts an array argument. This allows us to pass a list of columns we'd like to respond with, reducing the risk of accidentally exposing something to the API surface that we'd rather not transmit.

const userColumns = [ 'id', 'username', 'glasses' ]
const quotaColumns = [ 'amount' ]

knexInstance
  .insert(newUser)
  .into('users')
  .returning(userColumns)
  .then(([ user]) => knexInstance
    .insert({
      user_id: user.id,
      date: knex.fn.now(),
      amount: userConstants.INITIAL_QUOTA_AMOUNT
    })
    .into('quotas')
    .returning(quotaColumns)
    .then(([ quota ]) => res.status(201)
      .json({
        ...user,
        ...quota
      })
    )
  )

Async/await for readability

These days, I'd probably avoid a promise chain like that in favour of the syntactic sugar that await provides us.

try {
  const [ user ] = await knexInstance
    .insert(newUser)
    .into('users')
    .returning(userColumns)
  const [ quota ] = await knexInstance
    .insert({
      user_id: userId,
      date: knex.fn.now(),
      amount: userConstants.INITIAL_QUOTA_AMOUNT
    })
    .into('quotas')
    .returning(quotaColumns)

  res
    .status(201)
    .json({
      ...user,
      ...quota
    })
} catch (e) {
  next(Error("Something went wrong while inserting a user!"))
}

A note on transactions

There are a few assumptions here, but one big one: we assume that both inserts will be successful. Sure, we provide some error handling, but there's still the possibility that the first insert will succeed, and the second fail or time out for some reason.

Typically, we'd do multiple insertions in a transaction block. Here's how Knex handles this:

try {
  const userResponse = await knexInstance.transaction(async tx => {  
    const [ user ] = await tx.insert(...)
    const [ quota ] = await tx.insert(...)
    return {
      ...user,
      ...quota
    }
  })

  res
    .status(201)
    .json(userResponse)
} catch (e) {
  next(Error('...'))
}

This is good general practice for multiple inserts that depend on each other, since it sets up an "all or nothing" approach: if something fails, the database will go back to its previous state.