Eloquent upsert() requires a UNIQUE index on MySQL

Posted on: 2021-10-29 16:19:48

tl;dr - upsert() acts different based on your DB. MySQL requires that a UNIQUE INDEX exist for the combination of column you are wanting upsert to work on.

Where I can, I've been using upsert() as a way to make many queries into (usually) a single query.

Recently, I had a situation where a function in our code base was essentially doing this:

foreach ($anArray as $newModels) {

This operation was wrapped in a DB::beginTransaction() / DB::commit().

When I finally got around to re-writing it, it was much cleaner and of course looked something like this:

   ['key_one', 'key_two', 'key_three'].
   ['col1', 'col2']

I ran the test suite (it passed), pushed and went to sleep.

When I woke up the next morning, I saw a number of errors and a large number of records being generated. Obviously, something was not right in the world of upsert.

After digging into things and exploring how MySQL "compiles" upserts, it turns out that this:

   ['key_one', 'key_two', 'key_three'].
   ['col1', 'col2']

Also could be written like this:

   ['col1', 'col2']

That second set of parameters is never used in the function:

public function compileUpsert(Builder $query, array $values, array $uniqueBy, array $update)
    $sql = $this->compileInsert($query, $values).' on duplicate key update ';

    $columns = collect($update)->map(function ($value, $key) {
        return is_numeric($key)
            ? $this->wrap($value).' = values('.$this->wrap($value).')'
            : $this->wrap($key).' = '.$this->parameter($value);
    })->implode(', ');

    return $sql.$columns;

$uniqueBy isn't used. It is in the SQLite version, but not here.

After cleaning up the DB and adding an index, the upsert will work as expected.

Continue reading...

Notes on writing reproducable Laravel tests

Posted on: 2021-09-22 11:46:22

Just writing some notes down on things that have made my life easier:

Testing timestamps

This has kicked my butt so many times. Started using Carbon::setTestNow(now()) and Carbon::now() to test timestamps coming back from API calls. You may ask: why do you care? This is beacuse I want to know the format of what comes back. I suppose I could just write a custom assertion that lets me know if the timestamp is within, say, the last second or something like that and matches a valid format. This might work. But I like being precise.

Fully test the "shape" of responses

One of the big things that I've come to rely on tests for is to make sure that I don't accidentally break stuff when refactoring or making changes.

All of our new code is testing such that the entire structure of the response is tested to make sure it's good. We also then test some of the data points to make sure things are where they should be.

$this->actingAs($user, 'api')
        'data' => [
    ->assertJsonPath('', $model->id)

A couple of times I refactored something and ended up accidentally forgetting (or renaming a value) because a test didn't cover it. APIs are contracts. They should always return the expected data.

Testing the DB, too.

I've also gotten into a habit of testing the DB after certain API calls, too. This is helpful for testing both what should be there and also for what shouldn't be there. Yes, the tests are longer. This isn't as necessary as much anymore as we've started pulling stuff out of controllers, but on older code this can be valuable, too.

Testing with no internet connection

Turns out we've accidentally had tests that reached out to production systems to pull data. Testing while offline finds these tests and allows you refactor/abstract them.

Knowing when to use fake/random values

This is actually a big one for me. For several weeks I fought with tests that would randomly fail.

Turns out the problem was that the models being tested had a flag on their factory that was choosing a random value. This random value determined if the user had an active subscription.

Now, we assume free and require paid using a factory state: User::factory()->paid()->create(). This ensures that the tests are clear and we've stopped running into this problem. However, here are some fields you likely shouldn't be using random data for:

  • status (paid_status, post status)
  • state (sending, processing, etc.)
  • type (public, private, shared posts, etc.)
  • flags (reported, archived, hidden)
  • tags (any tags, really)
Continue reading...

Modifying Eloquent's relations upon loading

Posted on: 2021-09-01 14:06:48

In our application's backend, we have a model that is very large to handle our Training programs. Part of the reason it's so large is because there are a lot of layers to give flexibility to training programmers. For a normal user who is doing a workout, their session looks like this:

              /     \                                                   
,----------------.  ,------.      
|Training Program|  |Groups|      
|----------------|  |------|      
`----------------'  `------'      

One of the things we really have been wanting to implement is the ability to "swap" a Movement for another. If you've ever done a work out, especially an advanced one, a good trainer will normally start with a base workout and then will scale it to an individual. If you've ever done Crossfit, you've done this.

In the end, this is essentially what we are wanting to accomplish:

$swap = [ 
   'original_movement' => 'push-up',
   'new_movement' => 'knee push-up'

// true, this is the old one.
$workout->groups[0]->movements[0]->movement === 'push-up';
$workout->groups[0]->originalMovements[0]->movement === 'push-up';


// now is the new one but original is unchanged.
$workout->groups[0]->movements[0]->movement === 'knee push-up'; 
$workout->groups[0]->originalMovements[0]->movement === 'push-up';

We have a lot of code that leverages Eloquent's relations to load and build responses for our API. However, the API isn't the only thing that will need this information. Ultimately, we cannot simply depend on the API/resources to format a response for a user, we need to be able to ensure that for a given point in time, a user's workout representation can be easily generated.

So updating our resource models to somehow transform the data is out.

When thinking about it from a DX perspective first, this is how I originally want to be able to do things:

  • When loading a user's training Session, I want their swaps to automatically apply.
  • When loading a Workout (outside of a session), swaps should be able to be selectively applied (e.g. $workout->setSwaps(...)).
  • A minimum amount of changes should be done to the rest of the API except for adding the necessary attributes to the respective resources.

A trait and an override gets us most of the way

What I ended up doing was creating a trait AppliesMovementSwaps that exposes 3 different implementation points:

  • Setting $inheritSwapsRelation denotes which relation may contain swap information. This allows setSwaps() on a model to talk to a relation to grab their swap information.
  • Setting $appliesSwapsRelation denotes a "child" relation which may want to take action when adding swaps from "above".
  • Classes can implement applySwaps() to take action when swaps are added. More on this in a moment.

So now the classes look like this (they all use AppliesMovementSwaps).

|Session                          | 
|$appliesSwapsRelation = 'workout'| 
|Workout                           |
|$appliesSwapsRelation = 'groups'  |
|$inheritsSwapsRelation = 'session'|
|Groups                            |
|$inheritsSwapsRelation = 'workout'|


  • The Groups model (where the swaps are applied) had the relation that needed to be modified (exercises) duplicated so that the original is always available (e.g. originalExercises).
  • A pointer to the originalExercises relation was created.

This was done so that one relation always points to what was programmed for the workout. exercises is what the user sees and that's the the relation that gets modified.

Finally, (and most importantly) I overrode setRelation on the Group model so that I could apply the transformation and "swap" out the records. This allows the relation to be modified and continue to work as a regular relation even though we are doing things with it.

public function setRelation($relation, $value)
    if ($relation === 'movements' && count($this->getSwaps()) > 0) {
        $value = $this->movementsWithSwaps($value);

    return parent::setRelation($relation, $value);

This all should work in theory. In practice, relations make things a little bit harder.

Modifying relations with setRelation

The above mostly works. But it doesn't in some instances. Here is an example:

// Load workout, apply swaps.
$workout->groups[0]->movements[0]->movement === 'knee push-ups';

// This doesn't.
$workout->groups[0]->movements[0]->movement === 'push-ups';

// Wait, this works?
$workout->groups[0]->movements[0]->movement === 'knee push-ups';

... what is going on?

Eloquent + setRelation

When Eloquent is accessing a relation:

                     ^^^^^^^^^ This is a relation.
          ^^^^^^ This is too, but not relevant.

It ends up calling a small shrub of functions:


It is here within getRelationshipFromMethod() where the issue is manifested:

protected function getRelationshipFromMethod($method)
    $relation = $this->$method();

    // <snip>...

    return tap($relation->getResults(), function ($results) use ($method) {
        $this->setRelation($method, $results);

Huh. So the first time you load a relation, it gets tap()'d and during the tap setRelation gets called. This means that the first access doesn't see the modified values that we do in setRelation only on second access does it pull from the relation value stored on the model. Shucks.

A hack to make it work

To make it work, we ended up hacking applySwaps() to force the reload of those certain relations when swaps were being applied. This means the relation value is primed for any access.

So far, this works really well. It feels hackish, but all of the tests (nearly 2 dozen) are passing so... yay?


Here's things that could make this easier:

  • Add the ability to transform() a relation before it gets loaded. This would remove the need to override setRelation. This seems clean and could be easy to do.
  • Add support for a get{Relation}Relation() method? I looked at Laravel Relationship Events but it didn't support the transformation.
  • Rewrite the models to simply support a getter. This is probably the "right" way but would likely have required a lot more time since right now everything is handled using relations.
Continue reading...

Databases inspection while debugging tests

Posted on: 2021-05-04 13:21:39

While debugging, I use a local MySQL database. Sometimes, I want to inspect the contents of the DB while the tests are running to I can check out the state of things.

This is how I do it.

Add a breakpoint to your code, and then open up your query environment of choice:

set tx_isolation="READ-UNCOMMITTED";

Now run whatever queries you want to inspect the DB.

The tx_isolation change will only impact the current session.

Continue reading...

Don't put services in a Laravel Console command class constructor

Posted on: 2021-04-20 22:33:15

It will attempt to resolve the component, and that might not take place when you want it to.

You know, for instance, before your cached config gets cleared and composer dumpautoload tries to run and barfs.

Continue reading...

404 when deleting a model through Livewire

Posted on: 2021-04-06 15:35:43

LiveWire has become a new favorite thing of mine the last few weeks. It scratches the itch that I've had for a long time now: I want some flexibility in the UI to do some very simple AJAX and I want to stay in Blade templates.

Anyway, I had an interesting issue which looked like this:

  1. Open up a page with a Livewire form on it.
  2. Submit the form, causing a new model to be created.
  3. This model now has a delete button: so click on it.
  4. Livewire shows a 404.

After looking at the HTML coming through the wire, I noticed that the relations Livewire was sending, there was something not right.

Livewire sends some meta-data back to the browser related to your component. It does this, probably, so that it can just rehydrate the models on the component without the original context of the request which was originally used to generate the component.

At any rate, the model's ID that I was generating was 0. Something about the model wasn't serializing properly.

Turns out that I hadn't actually set $incrementing = false nor $primaryKey on this model. It... wasn't a standard incrementing model. It's primary key was based on another table and it was only a 0-or-1 to 1 mapping anyway.

Moral of the story: make sure you test the serializability of your models!

Continue reading...

Nerdy Movies I grew up watching

Posted on: 2021-02-06 15:49:27

I was thinking a few nights ago while listening to the Hacker's soundtrack that there were a ton of movies I watched growing while either growing up or while I was in college. Some of these I watched a lot...

Anyway, I wanted to record this here for posterity... don't want to forget them!

  1. War Games (1983) - This is the move that started it all for me. I remember watching this a lot and thinking how cool it was that the protagonist had a computer in his room. This movie has it all: suspense, nerdery, hacking skills, a little romance, and a happy ending. I did a little war-dialing when I was a kid and this movie was what I felt like I was doing. (Rather than making my mom upset because I was calling numbers I shouldn't have.)

  2. Sneakers (1992) - The original hacking movie. It has everything: social engineering, computer hacking, code-breaking... you name it! And it has Robert Redford. What's not to like? My favorite scene is the scene where he bypasses the temperature sensor by walking really slowly.

  3. The Net (1995) - Sandra Bullock was a big teenage heart-throb. Who doesn't like nerdy girls? She is an amazing thriller actress. Speed only came out the year before. This is a staple nerd-flick if there ever was one.

  4. Hackers (1995) - Ah. This is "the" movie that I can remember watching over and over. The thrill of the hack, the "sticking it to" the big guys. Having a commanding knowledge of technology in this age made one feel like a deity—and these guys made it look cool, too.

  5. Mission Impossible (1996) - Not so much a hacker movie but there was a pretty good component to it. The feels when they were on the train and their upload failed.

  6. Pirates of Silicon Vally (1999) - Noah Wyle played Steve Jobs in this made-for-TV docu-drama exploring Apple and Microsoft in the build-up to the 21st centry. I don't remember much about it, other than I watched it a bunch.

  7. Antitrust (2001) - Another thriller. This movie came out while I was in high-school. I really idolized the way this story was played out as I felt like I connected with the main character.

  8. Operation Takedown (2000) - This one was about Kevin Mitnick. We studied his case some in college and I remember watching this once or twice. No matter how careful you are it seems that your sins will always find you out.

  9. Revolution OS (2001) - A documentary about Linux. Nothing special, but I watched it over and over again in college... on my Slackware machine.

(Dis)honorable Mentions

  • Swordfish (2001) - I think I watched more clips of this than ever watching the movie, but it was pretty talked about in college. A brief search should find out why that was the case.

  • Live Free or Die Hard (2007) - Another hacker movie... with violence. Not a bad movie but I don't remember liking this one much at all.

Did you have a movie that was formative in your life?

Continue reading...

Carbon today() vs. now()

Posted on: 2021-01-13 08:43:55

Beware of Carbon::today() vs. Carbon::now(). They are not synonyms. The former is equiv. to Carbon::now()->startOfDay().

This is important, especially in test environments. If you're thinking that Carbon::today() is actually today... well I suppose it is, but in what timezone?

I had a test that was doing something calculated off of Carbon::today()->subDays(1) and that is not equal to Carbon::now()->subDays(1).

Continue reading...

Notes on using SQLite with Laravel from a MySQL user

Posted on: 2020-12-04 22:44:32

Come with me on a journey to discover how easy (or not) it is to get a large Laravel app working with SQLite.

Continue reading...

Using a CSS Inliner with Laravel

Posted on: 2020-11-29 09:23:15

Here are some notes from my use of the Laravel CSS Inliner plugin. Mainly stemming from problems we had with the responsive CSS getting blown away by the inliner.

Continue reading...