Royall Spence's Blog

Separating Database Interactions Into Distinct Objects

The biggest obstacle to application maintenance and testing is an unprincipled mixture of calculation and side effects. Any change in state or interaction without an outside entity is a side effect. A common form of this mixture of behaviors is receiving items from a user to insert into a database. Many frameworks automatically set up Active Record classes that combine data representation with a full set of database behaviors via inheritance. In my experience, this feels messy because it’s never clear exactly where the boundary between object logic and database interaction lies.

One way accomplish separation of side effects is to place a side effect behavior into its own object which is then passed into the data providing object. Suppose we have a Book with a title, author, and isbn. How will we insert a new Book into the database without mixing the database interaction with the main Book class? First, we’ll need the ability to pass in an external operation to our Book:

interface BookCommonInterface {
    public function withIsbn(string $isbn);
    public function withTitle(string $title);
    public function withAuthor(string $author);

interface BookInterface extends BookCommonInterface {
    public function operate(BookOperationInterface $operation): self;

interface BookOperationInterface extends BookCommonInterface {
    public function execute();

With these interfaces, we can build database operation objects and send them to the Book for execution. These with methods are known as functional updates. They serve the same role as set methods, but do so by returning an updated copy rather than mutating the object in place. Here’s an immutable example:

class Insert implements BookOperationInterface {
    private $isbn;
    private $title;
    private $author;
    private $db;
    public function __construct(PDO $db, string $isbn = null, string $title = null, string $author = null) {
        $this->db = $db;
        $this->isbn = $isbn;
        $this->title = $title;
        $this->author = $author;
    public function withIsbn(string $isbn) {
        return new Insert($this->db, $isbn, $this->title, $this->author);
    public function withTitle(string $title) {
        return new Insert($this->db, $this->isbn, $title, $this->author);
    public function withAuthor(string $author) {
        return new Insert($this->db, $this->isbn, $this->title, $author);
    public function execute() {
            ->prepare('INSERT INTO books(title, author, isbn) VALUES(:title, :author, :isbn)')
            ->execute(['isbn' => $this->isbn, ':title' => $this->title, ':author' => $this->author]);

class Book implements BookInterface {
    // suppose the constructor and update methods are already written
    public function operate(BookOperationInterface $operation) {

With these objects available to us, it’s easy to insert a new Book with the results of a form submission. Supposing we already have a source of input and database connection arranged, it looks like this:

(new Book('1234-56789', 'Some Book', 'Jane Smith')->operate(new Book\Operation\Insert($db));

This serves as a powerful alternative to the ubiquitous and often confusing Active Record pattern. More than that, it can be applied to any write-based operation; SOAP clients, loggers, or anything else can have its interactions handled this way. A codebase built like this is modular, easy to read, and easy to modify. Try it!