Best Practices for Retrieving Selective Data from a Database in a RESTful API

I am currently working on designing a RESTful API that interacts with an SQL database containing a 'Users' table. The example 'Users' table has fields such as AccountID, Username, Password, Firstname, Lastname, Email, Text, Setting1, Setting2, and Setting3.

In the services layer of my application, I have implemented getters and setters for Email, Text, Setting1, Setting2, and Setting3. Additionally, there are getters for Firstname and Lastname (but no setters).

The challenge I am facing is how to efficiently retrieve data from the database when the server code calling these services requires selective fields to perform various operations, while maintaining seperation of the business/services logic from the SQL statements (which exist in data gateway classes). For instance, one part of the code may need Firstname, Setting1, and Setting2, while another part may only need Lastname and Setting3.

I've considered two potential solutions:

1. Sequential Field Queries: Query each field independently and in sequence. For example, first query for Firstname, then Setting1, and finally Setting2.

2. Retrieve All Fields: Query all fields and retrieve the necessary data, ignoring the unused fields based on the requirements of the client code.

I am seeking guidance on the following:

• Best Practices: How is this problem typically solved in the design of RESTful APIs?

• Pros and Cons: What are the advantages and disadvantages of each method (sequential queries vs. retrieving all fields)?

• Alternative Solutions: Are there alternative approaches or best practices for efficiently handling selective data retrieval in a scenario like this?

For a code example, the services/business logic functions look like this:

  getUserEmail(userId: number): string {
    // Implement logic to retrieve user email using the database gateway
  }

  setUserEmail(userId: number, email: string): void {
    // Implement logic to update user email using the database gateway
  }

  // Implement similar functions for other fields

  loginUser(username: string, password: string): boolean {
    // Implement logic to check if the provided username and password match a user, using the database gateway
  }

  getUserFirstname(userId: number): string {
    // Implement logic to retrieve user's firstname using the database gateway
  }

  getUserLastname(userId: number): string {
    // Implement logic to retrieve user's lastname using the database gateway
  }

The database gateway class looks like this:

interface UserTableGateway {
  // CRUD Operations
  createUser(user: User): void;
  readUser(userId: number): User | null;
  updateUser(user: User): void;
  deleteUser(userId: number): void;

  // Additional Query Operations
  getUserByEmail(email: string): User | null;
  getUserByUsername(username: string): User | null;
}

I appreciate any insights, experiences, or recommendations that you can provide. Thank you!