Meta work is more interesting than work

The puzzles we create for ourselves...

Posted by Jake Corn on November 25 2019

(I stole the title from OTI Dave Thomas from something he said on Software Engineering Radio, he's a giant in the industry that speaks like no other)

Here is a short list of just the javascript orms I could find that support raw queries

knex.js
typeorm
sequelize
pg
mysql
mssql
bookshelf.js
objection js (via knex)
waterline js

When choosing one of these a minimum requirement is that we need to be able to query our datastore (for our purposes, an sql one).

Many of these support a notion of entity mapping. (a class, object, function that relates to a table and the operations you can perform against it)

In many cases this protects you from needing to litter your code with SQL. ewwww!

getBooks() {
  return Book.find({})
}

yay, no sql mentioned...

what about a more complex find operation?

getBooks({ yearPublished, author }) {
  return Book.find({
    where: {
      yearPublished: GreaterThan(yearPublished),
      author,
    },
  })
}

okay, that still mentions no sql. What if we add some joins?

getBooks({ yearPublished, author }): Promise<Book[]> {
  return Book.find({
    where: {
      yearPublished: GreaterThan(yearPublished),
      'Books.Author': author
    },
    join: {
      table: 'MagazineArticles',
      on: 'MagazineArticles.Author = Books.Author'
    }
  })
}

that's starting to leak sql. Moreover the base Object we queried from is now intermingled with the MagazineArticle type. also, I needed to qualify author after joining to the MagazineArticle set.

If we had a typescript definition for Book it might look like this.

class Book {
  title: string
  author: string
  yearPublished: number
  isbn: string
  createdDate: Date
  lastModifiedDate: Date
}

But for my new query I need a new type to avoid mixing the types.

class BookWithMagazineArticle {
  //...
}

Now I could cast my query results into a variable of this type

async getBooks({ yearPublished, author }) {
  const book: BookWithMagazineArticle = await BookWithMagazineArticle.find({
    where: {
      yearPublished: GreaterThan(yearPublished),
      'Books.Author': author
    },
    join: {
      table: 'MagazineArticles',
      on: 'MagazineArticles.Author = Books.Author'
    },
  })
  return book
}

then I could dig into my new lovely property bag with plenty of editor help, now this is programming!

async getBooks({ yearPublished, author }) {
  const book: BookWithMagazineArticle = await BookWithMagazineArticle.find({
    where: {
      yearPublished: GreaterThan(yearPublished),
      'Books.Author': author
    },
    join: {
      table: 'MagazineArticles',
      on: 'MagazineArticles.Author = Books.Author'
    },
  })
  book.author = titleCase(book.author)
  return book
}

I typed '.' and the editor knew what i was doing.. THIS is correct code.

I've received a new requirement not to send back "author" for the book.

I change my query to include only the other fields.

async getBooks({ yearPublished, author }) {
  const book: BookWithMagazineArticle = await BookWithMagazineArticle.find({
    where: {
      yearPublished: GreaterThan(yearPublished),
      'Books.Author': author
    },
    join: {
      table: 'MagazineArticles',
      on: 'MagazineArticles.Author = Books.Author'
    },
    select: [/* list including all except author */]
  })

  book.author = titleCase(book.author)
  return book
}

and my BookWithMagazineArticle will come back as

BookWithMagazineArticle {
  title: 'Moby Dick',
  author: 'Undefined',
  ...
}

Perfect... erg...

The query did not return the book object the way I expected it to and accidentally sent back an "Undefined".

I need a new Entity type

class SlimBookWithMagazines {
  ...
}

But why do I "need" this? Is it because I needed a class for my users so I could send them the data they need?

no

Was I unable to query to the database before I had these classes and repositories.

no

instead of having x number of things I could have had one thing

getBooks({ yearPublished, author }) {
  return query(`
    select
      books.title,
      books.isbn,
      magazineArticles.*
    from
      Books books
    inner join MagazineArticles magazineArticles
      on magazineArticles.author = books.Author
    where
      books.Author = :author
      books.yearPublished = :yearPublished
  `, { yearPublished, author })
}

At the start I had all that was needed to get the data. I also had all that was needed to be flexible.

Often it is better to let functions be functions and data be data and to let work get done.

-- jake