This is an account of my own experience testing the waters of SQL-speaking objects. SQL-speaking objects are, in my opinion, the antithesis to ORM’s and a more precise way of implementing a domain model.

What’s an SQL-speaking object?

I’ve been on a kick recently about trying to do things in a different way than I’m used to. A while back, I stumbled across the Elegant Objects website. On that website is a list of principles that, when respected, can lead to more explicit object-oriented code. These principles were quite contradictory to what I was used to…so it seemed like a good way to learn a different way to express ideas in code.

One of my favorite articles on that site is related to the use of ORM’s. An alternative to using ORM’s is proposed that’s more respectful of an object and letting it manage itself. Just like most the people in the comment section of that post, my original reaction to such an article was denial: “no way something like this could work or even be scalable!”

I realized such a reaction is a hinderance to growth. I figured I should try it myself and find out how I feel about it.

Maybe it’s not such a bad idea?

I let the contents of this article soak in my brain for some time and realized that it has merits. Having been blinded by the strongly held belief that the “business logic” layer should be separated from the “persistence” really held me back from getting to this conclusion.

Ultimately I realized that an object that relies on its data being somewhere other than immediately-accessible memory doesn’t exactly warrant creating layers for the sake of creating layers.

Think about it. Let’s say we want to implement a new feature in an application and end up identifying the need of two objects: one “plain old object” that has all its data in memory and one that relies on the database for existence. A lot of programmers will see that database-related object and start creating data transfer objects, data access objects, repositories, or whatever the heck due to unfound concerns about how to deal with it. Why do we do this? You wouldn’t go about coding up a special interface for the “plain old object” to efficiently interact with memory using C or Assembly. So why be so weird with database reliant objects?

Fueling the desire to experiment

Recently I’ve been working on Ledgerful, which is an expense tracking mobile app geared towards couples. I started this project back in January. Around this time, I’d started a sort of transformation on how I conceptualize and solve problems in my head before translating into code. I wanted to use the app as a testbed for strengthening my quite weak OOP skills.

This transformation occassionally gave me the urge to refactor large swaths of Ledgerful. I gave in once to this and spent two weeks shifting pieces around. In a nutshell, I removed all the getters and setters in an attempt to have real interactions between the objects in my application.

After the two weeks I spent refactoring, I looked in the rear view mirror and thought to myself: wow! Look how much time I wasted. Even though I quite liked the result, I knew I shouldn’t do that again if I wanted to ever get this project launched. From then on, I decided that if I wanted to experiment, it had to be done on new/small features.

Finally, an opportunity!

Eventually, a small feature had popped up that was small enough in scope that I could experiment with: validating user e-mails during sign up. For this feature, I figured I’d experiment with SQL-speaking objects.

This feature was perfect because it is quite isolated from the rest of the application. Sure it relies on the concept of a user, but it isn’t concerned about anything else such as expenses or trackers – the hotbeds for changes in the app

The Interface

The e-mail validation works as follows:

  1. User signs up and a confirmation e-mail is sent to them.
  2. User clicks this confirmation link in their e-mail and can continue using their account.

Knowing this, I wrote this interface:

public interface UnverifiedUserAuths {

  /**
   * Creates authentication details for a yet-to-be-email-verified user.
   */
  UnverifiedUserAuth create(String hashedPassword, 
                            String name, 
                            String email) throws Exception;

}

public interface UnverifiedUserAuth {

  /**
   * User has been verified by clicking the link in their e-mail.
   */
  void verified();

  /**
   * Returns the id of the unverified user authentication details.
   */
  String id();

}

UnverifiedUserAuths will handle the world of unverified users. It will be treated as an abstraction for a collection of UnverifiedUserAuth. The create method needs to take all the information required to create the user as we will need to be able to create the actual user somewhere down the line.

The Implementation

I use a JDBC wrapper called jcabi for making queries and returning results. I find the API self-explanatory so I won’t go into describing how it works here. I do wish I had gone with jOOQ for type safety.

To store unverified user details, I created a table in Postgres called unverified_user_auths with the following structure:

Column Type
id VARCHAR(36) NOT NULL
hashed_password VARCHAR(255) NOT NULL
name VARCHAR(255) NOT NULL
email VARCHAR(255) NOT NULL

There will also be a user_auths table that mirrors this same structure. Verified user authentication information will be copied to that table.

Set up the base

First, let’s create the classes for our SQL-speaking objects:

/**
 * An implementation of {@link UnverifiedUserAuths} that's backed by Postgresql.
 */
public final class PgUnverifiedUserAuths implements UnverifiedUserAuths {

  /**
   * The database connection.
   */
  private final Datasource datasource;

  public PgUnverifiedUserAuths(Datasource datasource) {
    this.datasource = datasource;
  }
}

(Note: if you’re using Spring, you can mark this with @Component annotation and let it serve as a bean)

public final class PgUnverifiedUserAuth implements UnverifiedUserAuth {

  private final Datasource datasource;

  /**
   * The id of this unverified user.
   */
  private final String id;

  public PgUnverifiedUserAuths(Datasource datasource, String id) {
    this.datasource = datasource;
    this.id = id;
  }
}

Create

Since UnverifiedUserAuths is acting as our collection of all the unverified user auth information, we’ll be adding the create behavior there:

@Override
public UnverifiedUserAuth create(String hashedPassword, String name, String email) throws Exception {
  final var id = UUID.randomUUID().toString();

  new JdbcSession(dataSource)
      .sql("""
          insert into unverified_user_auths
          (id, hashed_password, name, email)
          values (?, ?, ?, ?)
          """)
      .set(id)
      .set(hashedPassword)
      .set(name)
      .set(email)
      .update(new SingleOutcome<>(String.class));

  return new PgUnverifiedUserAuth(datasource, id);
}

Verification

Now in our PgUnverifiedUserAuth class, we can write our verified method:

@Override
public void verified() {
  try {
    new JdbcSession(dataSource)
        .sql("""
            insert into user_auths (id, hashed_password, email, name)
            select id, hashed_password, email, name
            from unverified_user_auths
            where id = ?
            """)
        .set(id)
        .update(new SingleOutcome<>(String.class));
  } catch (SQLException exception) {
    throw new IllegalStateException(
        "Error finding unverified user auth of id " + id,
        exception
    );
  }
}

Using our SQL-speaking objects

Now whenever a user signs up, we can instantiate the universe of UnverifiedUserAuths by creating a PgUnverifiedAuths object and letting it create things.

@PostMapping("/signup")
public void userSignedUp(@RequestBody SignupData signupData) {
  final var unverifiedAuth = new PgUnverifiedUserAuths(datasource)
    .create(
      hashPassword(signupData.password()), // In my implementation I have a HashedPassword class
      signupData.email(),                  // that can only have objects created through an UnhashedPassword
      signupData.name()
    );
  
  // ... code to send email to verify user ...
}

To verify a user once they’ve clicked on the link in their email:

@PostMapping("/verify")
public void verified(@RequestParam String id) {
  new PgUnverifiedUserAuth(datasource, id).verified();
}

My thoughts

I simply adore the transparency of the code. There’s no hidden magic here. I can trace behavior exactly to the resulting query. When using ORM’s, the queries are generated for you are inefficient and may hit issues due to caching or weird edge case behavior. Having the SQL queries right in your face, you’re able to see everything.

Some people may look at this and think to themselves: my gosh look at all that boilerplate! I don’t find this a negative. The explicitness is rather refreshing. I find that even with an ORM, I’m already thinking about the database in the back of my head when writing business logic.

Having only done this for small things around my application, I don’t have a list of potential issues this will cause. Once the codebase has grown and I’ve incorporated this in other areas, I’ll have a more substantial understanding of dangers and pitfalls to look out for and can write up a post on that.