Background #

Postgres Full Text Search (FTS) is a great way to implement site search on a website running Postgres already, without requiring additional infrastructure.

On a recent engagement with a client, we were deciding between Postgres FTS and ElasticSearch. Ultimately we chose FTS because we could spin it up without having to add extra infrastructure, as we would with ElasticSearch.

Since the project was written in Ruby on Rails, we were able to use the excellent PgSearch gem to implement FTS in ActiveRecord.

Multisearch #

As we wanted a general site search, we needed to utilize multisearch. Multisearch combines multiple ActiveRecord models into one search 'document' table that you can search against. For example, if a user searches for some search term, and the search is configured for multisearch, then every single model that we mark as multisearchable will be searched for that term at the same time. See here for more detail.

Search Features #

PgSearch allows for different search features, tsearch, trigram, and dmetaphone. The default is tsearch, which uses the built-in Postgres Full Text Search.

This was great for our use case, since it also comes with highlighting, a feature that was required. The highlighting is from a field returned by Postgres FTS, where it returns the text around the search term for context and bolds the search terms.

Spelling Mistakes #

Unfortunately, tsearch does not handle misspelled words. However, as I mentioned before, PgSearch allows for other search features!

And trigram is a feature that can be installed via a Postgres extension (pg_trgm) that does just that.

Trigram #

• The idea behind trigram search is to split pieces of text into sets of three-letter segments, and compare the sets to one another
• If two trigram sets are similar enough, we assume there was a spelling mistake, and return the document with the correctly-spelled term.
• As a quick example (ignoring whitespace): Consider the word Viget. Viget would make trigrams:

[vig, ige, get]

• Now, consider our evil twin agency, Qiget. They would make trigrams

[qig, ige, get]

• The two trigram sets match very closely, with only one of the trigrams not being the same. Thus, if we were to compare these with pg_trgm, we could reasonably tell that anyone typing 'Qiget' must have been actually looking for 'Viget', and just misspelled it.

Working Trigram into our existing solution #

PgSearch allows us to use multiple search features at once, so we can use tsearch and trigram side by side. Note that we cannot just replace tsearch with trigram due to needing some features in tsearch that are exclusive to it. Here is what an example configuration might look like.

PgSearch.multisearch_options = {
  using: {
    tsearch: {
      prefix: true,
      highlight: {
        MaxFragments: 1
      }
    },
    trigram: { 
      only: [:content]
    }
  }
}

Trigram (and timelines) causing issues #

While it was easy to slot Trigram into our multisearch, it caused a pretty serious performance hit. We were seeing 50x-75x slower searches with both features combined than with just tsearch. We needed to find a way to balance performance with handling misspellings

At the point that handling misspellings became prioritized, the entire search feature was almost fully QA'd and about ready to go out. There wasn't much time left in the budget to find a good solution for the issue.

This thread from the PgSearch repo sums it up pretty well – there were multiple other users that were/are having similar issues as we were. The top-rated comment in this thread is someone mentioning that the solution was to just use ElasticSearch ('top-rated' is doing a lot of heavy lifting. It did have the most likes...at two). We needed to find some sort of middle ground solution that we could act on quickly.

Postgres Documentation saves the day #

In the docs for the Trigram Postgres extension, the writers give an idea for using Trigram in conjunction with Full Text Search. The general idea is to create a separate words table that has a Trigram index on it.

Something like this worked for us. Note that we added an additional step with a temporary table. This was to allow us to filter out words that included non-alphabet characters.

execute <<-SQL
  -- Need to make a temp table so we can remove non-alphabet characters like websites
  CREATE TEMP TABLE temp_words AS
    SELECT word FROM ts_stat('SELECT to_tsvector(''simple'', content) FROM pg_search_documents');

  CREATE TABLE pg_search_words (
    id SERIAL PRIMARY KEY,
    word text
  );

  INSERT INTO pg_search_words (word)
    SELECT word
    FROM temp_words
    WHERE word ~ '^[a-zA-Z]+$';
  
  CREATE INDEX pg_words_idx ON pg_search_words USING GIN (word gin_trgm_ops);
  
  DROP TABLE temp_words;
SQL

This words table is therefore populated with every unique word that exists in your search content table. For us, this table was pretty large.

result = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM pg_search_words").first['count']
puts result.first['count']
# => 1118644

Keeping the words table up-to-date #

As mentioned in the docs, this table is separate from your search table. Therefore, it needs to be either periodically regenerated or at least have any new words added to search content also added to this table.

One way to achieve this is with a trigger, which adds all new words (still filtering out non-alphabet characters) that are inserted into the documents table to the words table

create_trigger("pg_search_documents_after_insert_update_row_tr", generated: true, compatibility: 1)
  .on("pg_search_documents")
  .after(:insert, :update) do
  <<-SQL_ACTIONS
    CREATE TEMP TABLE temp_words AS
      SELECT word FROM ts_stat('SELECT to_tsvector(''simple'', ' || quote_literal(NEW.content) || ')');

    INSERT INTO pg_search_words (word)
      SELECT word
      FROM temp_words
      WHERE word ~ '^[a-zA-Z]+$';

    DROP TABLE temp_words;
  SQL_ACTIONS

end

Note that this does not handle records being deleted from the table – that would need to be something separate.

How we used the words table #

Assuming for simplicity the user's search term is a single word, if the search returns no results, we compare the search term's trigram set to the trigram index on the words table, and return the closest match.

Then, we'd show the closest match in a "Did you mean {correctly-spelled word}?" that hyperlinks to a search of the correctly-spelled word

Given more time, I would have liked to explore options to speed up the combined FTS and Trigram search. I'm certain we could have improved on the performance issues, but I can't say for sure that we could have gotten the search time down to a reasonable amount.

A future enhancement that would be pretty simple is to automatically search for that correctly-spelled word, removing the prompt to click the link. We could also change the text to something like "Showing results for {correctly-spelled word}".

Ultimately, I think with the situation at hand, we made the right call implementing Trigram this way. The search is just as fast as before, and now in the case of misspellings, a user just has to follow the link to the correctly-spelled word and they will see the results they wanted very quickly.

In 2022 we changed our dev tooling for new Craft CMS projects. Goodbye complex esoteric Webpack configuration, hello Vite. Goodbye complex esoteric Docker Compose configuration, hello DDEV. This small change in tooling has completely transformed our development experience. We start work faster and avoid wasting billable time debugging Webpack and Docker.

From Webpack to Vite #

Webpack has been the defacto way of bundling JavaScript and front end assets. It’s a powerful tool… but with that great power comes great responsibility complexity.

Vite bills itself as the “next generation” of frontend tooling. Vite is much faster at bundling. But more importantly… its default configurations work great for most website projects.

Before (Webpack) #

Well over 300 lines of configuration spanning three files. Good luck making changes!

After (Vite) #

A crisp 30 - 50 lines of code. Want to switch to TypeScript? Need to drop in a popular front-end framework? Easy! All it takes is adding a plugin and 2-3 lines of config.

Deleting old code has never felt this good!

From Docker to DDEV #

Docker is another development staple. It isolates server infrastructure into virtual “containers.” This helps avoid issues that arise from each developer having a slightly different setup. However, Docker can have a learning curve. Config changes, PHP upgrades and unexpected issues often eat up precious project time.

Enter DDEV! DDEV describes itself as “Container superpowers with zero required Docker skills: environments in minutes, multiple concurrent projects, and less time to deployment.” We’ve found that statement to be 100% true.

Before (Docker) #

Every Craft project has a different Docker config. Bugs and upgrades required deep Docker experience. Last (but not least), it was difficult to run several projects at one time (ports often conflict).

After (DDEV) #

Performance is consistently better than our hand-rolled setup thanks to Mutagen and faster DB import/exports. Simultaneous projects run out of the box. DDEV provides (and maintains) a growing list of helpful shortcuts and DX features.

Getting started #

Ready to make the switch? Here’s how to set up DDEV, Vite and Tailwind on your own Craft project.

Show me the config files already! #

If you would rather see full config files instead of following step by step, check out our Craft Site Starter on GitHub.

DDEV #

Let’s set up a fresh DDEV project and start customizing.

1. Make sure you have DDEV installed on your computer.
2. If you’re a PHPStorm user, install the exceedingly helpful DDEV plugin. VS Code users have a similar plugin too!
3. Follow Craft’s guide for creating a new project (they love DDEV too).

Now you have a fresh .ddev/config.yaml just waiting to be customized.

Node Version #

Open your DDEV config and make sure your Node JS version matches Vite’s recommendations.

Ports for Vite’s dev server #

Next, expose ports that Vite’s dev server uses will use to serve assets.

Routing ports can sometimes be confusing. This diagram might help!

• Vite’s dev server runs inside of DDEV’s web container (a Docker container).
• Until we expose these extra ports, any custom port within DDEV is unavailable to your host machine (your computer).
• When it’s time to configure Vite, we’ll use port 3000
• HTTP and HTTPS traffic must use separate ports.
• We use port 3000 for http traffic and 3001 for https

Run Vite automatically #

Usually, you’ll want Vite to watch and build files automatically after you start a DDEV project. Using web_extra_daemons adds a separate background process (daemon) for Vite.

Use hooks to improve DX #

DDEV’s powerful hooks system can run tasks before or after various DDEV commands. These post-start tasks keep dependencies and schemas up to date every time you start DDEV.

Time for Vite #

Vite is a Node app that’s installed with NPM. Your project will need a package.json. If you don’t have one set up yet, follow NPMs initialization script.

????Why ddev at the start of the command? This let’s us run NPM from within DDEV’s Docker containers. This means you’ll always be using the Node version configured for this project. DDEV has a bunch of shortcuts and aliases for running CLI commands (such as npm, yarn, craft and composer).

Make sure your NPM package is configured for ES Modules #

Our various config files will be using ES Module syntax for imports and exports.

Install Vite! #

Add convenience scripts to package.json #

npm run dev runs Vite in dev mode. It watches and builds your files every save. Files are served through Vite’s dev server.

npm run build bundles your JavaScript, CSS and static images for production. Your deploy process will usually call this script.

Configure vite.config.js #

Running Vite for a server rendered CMS requires some extra configuration. These options put production files in the right spot and keeps Vite’s dev server running on a specific port.

Add JavaScript and CSS files (Entrypoint) #

Vite needs an entry point to determine what JavaScript, CSS and Front End assets it needs to compile. Remember src/js/app.js that we defined in vite.config.js? Let's make that file now.

We’ll also add our CSS as an import in app.js . In plain-old-JavaScript you can’t import CSS files. However, Vite uses this to figure out CSS dependencies for the project.

Once Vite builds everything for production, you end up with a separate CSS file. The Craft Vite plugin includes this automatically with along your JavaScript bundle.

Install the Vite Craft Plugin #

Vite assets have different URLs in dev mode vs. production. In dev mode, assets are served from Vite’s dev server. It uses the ports that we defined in our DDEV & Vite configs.

When Vite builds for production, filenames are hashed (app.js becomes app-BZi_KJSq.js). These hashes change when the contents of the file changes. Browser can cache these files indefinitely. When an asset changes, a whole new file is served.

To help find these hashed filenames, Vite creates a manifest.json file. The manifest associates the name of your asset src/js/app.js to the hashed file that ends up on your server web/dist/assets/app-BZi_KJSq.js

The Craft Vite Plugin by NYStudio107 takes care of all this routing for you.

Configure the Vite Craft Plugin #

Make a new plugin config file in config/vite.php

Include your Vite bundles in Twig #

The script and asset functions includes the appropriate files depending on in if you’re in dev mode or production. Clear out your templates/index.twig file and add the following snippet to your <head> tag.

Whew! ???? We’re at a point now where we can test our integration. Run ddev restart and then ddev launch . You should see “Hello Craft CMS” in your browser console.

Setup Tailwind #

Now that Vite is processing src/css/app.css, it’s time to install Tailwind and really get cooking.

These steps are based on Tailwind’s official installation guide. But make sure to run all commands from within DDEV.

Install packages #

Configure template paths in tailwind.config.js #

Configure postcss.config.js for production #

Add Tailwind directives to src/css/app.css #

You’ll most likely need to run ddev restart again to get Vite to recognize your new Tailwind config.

❓ Do i need to set up live reload of Twig? Turns out it’s already done for you! Styling a Tailwind project means editing Twig files to change styles. It’s super handy to reload your browser every time you save. Normally you’d reach for vite-plugin-restart to get this functionality. However, Tailwind’s JIT mode automatically notifies Vite when CSS has compiled and the page should reload.

That's a wrap! #

That’s all it takes to configure a minimal DDEV and Vite project! We’ve found that both of these tools are easy to extend as a project get more complo'ex. Adding things like Redis or React are just a plugin install and a few lines of config away.

???? If you'd like to see this setup (and more) in a real-world Craft CMS project, check out our Craft Site Starter on GitHub.

Go forth and Vite + DDEV to your heart’s desire.