Laravel Herd Pro MySQL and WordPress Database Connection Error

If you are using Laravel Herd Pro with a MySQL database, you may run into the “Error establishing a database connection” error.

To fix this, change your database host settings from define('DB_HOST', 'localhost'); to define('DB_HOST', '127.0.0.1'); and that should do the trick.

This can also fix errors from other software that attempts to connect using a socket (e.g., “Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’”).

Migrating from Homebrew MySQL to Laravel Herd Pro Services

Laravel Herd is an amazing application designed to make it as easy as possible to get started with PHP development.

I have been using Homebrew to run MySQL, Redis, Meilisearch, and more, but since we recently got Herd Pro, I figured it made sense to consolidate.

Here are a few other articles on how to migrate databases to Herd Pro:

My method is a combination of the two: copy the data files from the Homebrew MySQL to Herd Pro to save the time that it would take to dump and import.

Note: this only works if your homebrew MySQL and Herd Pro MySQL are on the same minor version (8.0.1 to 8.0.3 would work; 8.0.x to 8.4.x would not).

  1. Stop the homebrew MySQL service, if you haven’t already: brew services stop mysql (or maybe [email protected] if you’ve updated in the past few months)
  2. Stop the Herd MySQL service, if you haven’t already, using the Herd services UI
  3. Find the Herd data directory: right-click on the MySQL service and choose “Open data directory”
  4. Copy or move the files to retain a backup
  5. Find the homebrew data directory: in a terminal, run open $(brew --prefix)/var/mysql to open the directory in Finder
  6. Copy the files to the Herd data directory
  7. Restart Herd
  8. After you’ve confirmed everything is fine, maybe delete the homebrew mysql data directory and brew uninstall [email protected]

Proxying Signed AWS S3 URLs using CloudFlare Workers

A common use case for S3 is hosting content that should not be available to the public, but needs to be made available to specific user(s) or for a specific length of time. A great example of this is granting access to digital files after a purchase or subscription payment.

In this case, I needed the domain to be a first-party subdomain, rather than a default Amazon AWS domain, due to same-origin policy requirements.

Hat-tip to Fershad Irani for an initial version, which I modified to suit my needs.

Set up the AWS Bucket

  1. Create a bucket
  2. Prevent all public access to objects in the bucket
  3. Upload files

Configure a Cloudflare Worker

  1. Go to CloudFlare > Workers & Pages > Overview and create a new application
  2. Add the worker code below, modifying line 8 to use your bucket name
  3. Publish the worker
  4. If you already added the subdomain under the DNS tab pointing to anywhere, delete that before proceeding
  5. View the worker and go to the Triggers tab
  6. Under Custom Domains, add a custom domain (documentation) and enter your custom subdomain
  7. Under Routes, add a route for your custom subdomain

My Favorite VS Code Extensions

In a given day, I tend to work primarily on Laravel apps, some using Livewire and some with Inertia.js and a Vue.js frontend, as well as a smattering of WordPress sites and/or custom plugins.

I’ve tried PhpStorm and didn’t care for it, so like what feels like 90% of the rest of the industry, I use VS Code as my primary editor.

Here’s a list of the extensions I use on a daily basis:

General

Sublime Text Keymap and Settings Importer: I used Sublime Text for a year or so and built up muscle memory for the keyboard shortcuts, so these make a lot more intuitive sense to me than the standard VS Code keyboard shortcuts.

Markdown Preview Mermaid Support: I like to document solution architecture using mermaid diagrams, and this is a great extension to preview these in VS code.

Path Intellisense provides autocompletion when typing relative file paths in a project.

EditorConfig for VS Code configures some editor settings for different projects based on the configuration stored in the project.

Prettier – Code formatter is useful for automatically formatting code files.

TODO Highlight v2 provides visual feedback for TODO/FIXME/etc. comments in code.

Encode Decode is extremely useful when dealing with encoded strings. I use it fairly frequently to decode base64-encoded strings.

Remote – SSH / Remote – SSH: Editing Configuration Files / Remote Explorer make it really easy to “cowboy code” on a server 😬 and are useful for occasional debugging in production.

Live Share is amazing for pair-programming: it allows you to open the same codebase your colleague is working on and work with it on your machine just as if it were a local project.

Partial Diff is useful when comparing two files to find minor differences (e.g., when debugging API requests or responses).

PHP

Composer Intelephense shows you the actually-installed version of each package in your composer.json file, and gives you a quick link to the packagist.org page for each.

PHP DocBlocker reduces some of the boilerplate necessary when writing docblocks.

PHP Intelephense in my opinion is significantly better than the built-in PHP language support, providing autocompletion for functions, methods, variables, etc., project-wide parameter hints, and even some static analysis features.

PHPUnit Test Workbench is a very useful wrapper for phpunit; you can run a single test, a single file, or the entire test suite, and it shows a list of all of the tests in the sidebar. I use it frequently to run my entire test suite to see a quick list of which job(s) failed. (I used to use PHPUnit Test Explorer along with Test Explorer UI and Test Adapter Converter)

phpstan runs static analysis on files as I save them, showing my errors as I write code.

PHP Debug might just be the extension I interact with the most; it lets me set breakpoints, step through, and inspect code as it runs. I can’t imagine trying to program without it.

Laravel

I’m eagerly awaiting the release of the first-party Laravel extension, as I expect it to replace several of the following plugins; but for now, I use these:

DotEnv provides syntax highlighting for .env files used for Laravel configuration.

Laravel Extra Intellisense saves me a lot of time by auto-completing routes names and parameters, configuration keys, views and variables, validation rules, and more.

Laravel Blade Spacer automatically adds spaces when you add a new curly brace pair: just a minor code style convenience.

Livewire Language Support provides autocompletion and other features for Livewire projects.

Laravel goto view provides one-click access to views from controllers.

Laravel Blade Snippets provides Blade snippets and syntax highlighting.

Laravel Pint provides automatic code formatting using Pint.

Laravel Blade formatter provides formatting tools for Blade templates.

Flux UI provides autocomplete for Livewire Flux components.

Javascript

Alpine.js IntelliSense provides intellisense and snippets for alpine.js.

Inertia.js provides support for linking to vue templates and autocompletes component names.

Other Languages

GraphQL: Syntax Highlighting provides language support for GraphQL files.

SCSS IntelliSense autocompletes mixins, functions, etc. in sass files.

YAML provides YAML language support.

SQL Beautify provides formatting support for SQL files. I don’t always love the output, but it’s better than nothing.

Vue – Official (previously Volar) provides language, autocompletion, and other features for vue framework.

Markdown All in One provides keyboard shortcuts, formatting helpers, preview, and more for markdown files.

Tailwind CSS IntelliSense provides suggestions, highlights duplicates, and more for Tailwind class names.

Git

GitLens — Git supercharged provides some great features; my favorite is the code history on the active or hovered line.

GitLab Workflow is a wonderful integration with GitLab; I use the “copy active link to clipboard” feature daily to copy a permalink for specific line(s) when discussing code with my colleagues. It also provides helpful CI features including autocompletion and hints when editing .gitlab-ci.yml files, as well as showing pipeline/job status right in the VS Code status bar.

MySQL Table Size

Ever wondered which database or tables are taking up disk space on a MySQL/MariaDB server?

This query will provide the size of each table:

SELECT TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS `Size (MB)`,
ROUND((data_free / 1024 / 1024), 2) AS `Reclaimable Size (MB)`
FROM information_schema.TABLES
-- WHERE `TABLE_SCHEMA` = 'database_name'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

Shimming MySQL Functions into SQLite for Laravel CI/CD Testing

Colin DeCarlo presented a talk at Laracon Online where among other useful tips, he demonstrated how to shim MySQL functions in an SQLite database (e.g., add functions that MySQL has but SQLite does not).

Here are two examples that I just needed in a project (FLOOR and DATEDIFF):

use Illuminate\Support\Facades\DB;

DB::getPdo()->sqliteCreateFunction('floor', fn ($value) => floor($value));
DB::getPdo()->sqliteCreateFunction('datediff', fn ($date1, $date2) => Carbon::parse($date1)->diff(Carbon::parse($date2))->days);

Redirect to Original URL with Laravel Socialite

We’re using Laravel Socialite with a self-hosted GitLab instance to authenticate users for an internal tool.

Every time the session times out, the OAuth flow redirects the user back to the default dashboard, regardless of what URL the user originally requested.

However, Laravel provides a url.intended session key with the original URL; here’s how I used it, with a fallback to the dashboard URL:

return redirect()->to(
    session()->get('url.intended', route('dashboard')
);

Retrieving Route and Parameters from an Arbitrary URL in Laravel

I build an oEmbed provider in a Laravel application the other day and needed to parse an arbitrary URL to determine the route and parameters passed in order to determine the response.

Since I already had the routes built for the possible URLs, I didn’t want to duplicate code and re-parse them.

Here’s how I ended up retrieving the route and parameters:

Migrating sermons from Sermon Manager for WordPress to SermonAudio

I build a Laravel-based command-line utility to import sermons from the Sermon Manager for WordPress plugin and migrate them into SermonAudio.

If it’s useful to you, see this repository for setup and usage details: https://gitlab.com/andrewminion/sermon-manager-to-sermon-audio

Counting Distinct Values in a Single Field

A quick MySQL snippet to count how many times a value appears in a single field—much easier to grok than multiple JOINs.

SELECT
COUNT(CASE WHEN meta_value = 'value1' THEN 1 END) AS value1,
COUNT(CASE WHEN meta_value = 'value2' THEN 1 END) AS value2
FROM wp_post_meta;

/** Results

| value1 | value2 |
|--------|--------|
| 75     | 56     |
*/