Database Authentication - Are 2 database trips better than 1?

Question

Scenario

I have an application where some operations require the authentication of an admin.

Example Steps:

1. I need to validate the admin's username & password
2. insert a row into a MySQL table (if credentials are valid).

One Trip Approach

If I use a single Connection to the database, with multiple PrepearedStatements, I'm able to achieve my goals.

However, the abstraction of the application is damaged. Using this approach, in my DAO (database access object) method, I have to accept the arguments for the row insertion + the arguments for the admin validation.

It does save another connection to the database, but on the other side, the DAO that handles insertion shouldn't know or care about user validation.

An Example signature of the method would be this:

insertData(String data,String metadata,String username,String password)

Two Trip Approach

Using this approach, I am using 2 connections to the database. First one validates the users username & password, and if they are valid, opens another connection, and inserts the row into the database.

This approach uses an extra connection, but the DAO method now accepts only arguments that are relevant for the actual data insertion.

An Example signature of the method would be this:

 insertData(String data,String metadata)

Question

So my question is - which way is better? Should I sacrifice abstraction for database trips, or do several database trips for the sake of abstraction?

Answer 1

Password validation using a modern hash function such as bcrypt is (deliberately) slow compared to a database query, assuming the DB and code run in the same data center. Therefore, the number of database queries is not going to be very important.

Choose that solution which is the most simple and obvious to you, as that solution is more likely to be implemented correctly. For code that has to verify permissions, you do not want any bugs!

Introducing a special object that can only be constructed if valid credentials have been provided may be one way to prevent errors, as you can use the type system to enforce successful authentication.

This doesn't mean that you have to open multiple database connections. Instead, you might have a (global) database connection or connection pool that is used by all parts of your code. The connection can be temporarily borrowed by code that needs to make a query.