
## Install the plugin
Installing the plugin is straight forward - just follow the [README in the GitHub](https://github.com/tauri-apps/tauri-plugin-sql).

## Migrations
This part I was unable to find documentation for but was able to infer how to add them from the code in plugin code ([`plugin.rs`](https://github.com/tauri-apps/tauri-plugin-sql/blob/dev/src/plugin.rs#L277)).

The migrations can be added by specifying the database URL and a vector of migrations:
```rust
tauri::Builder::default()
    .setup(|app| setup(app))
    .plugin(
        tauri_plugin_sql::Builder::default()
            .add_migrations(
                "sqlite:database_name.db",
                vec![Migration {
                    version: 1,
                    description: "setup tables",
                    sql: include_str!("./migrations/001.setup.sql"),
                    kind: MigrationKind::Up,
                }],
            )
            .build(),
    )
    .run(tauri::generate_context!())
    .expect("error while running tauri application");
```

For the migration SQL, I've put them in a folder `./migrations` and just numbered them sequentially.

I'm still new to rust so this might not be the best approach, but to move the migration definitions out of `main.rs` I moved it to a module `db_migrations.rs`:
```rust
use tauri_plugin_sql::{Migration, MigrationKind};

pub fn get_migrations() -> Vec<Migration> {
    return vec![Migration {
        version: 1,
        description: "setup tables",
        sql: include_str!("./migrations/001.setup.sql"),
        kind: MigrationKind::Up,
    }];
}
```
And then in `main.rs` updated the plugin definition to use this function:
```rust
// Include the added module
mod db_migrations;
...
.plugin(
    tauri_plugin_sql::Builder::default()
        .add_migrations(
            "sqlite:database_name.db",
            // Get the migrations using the functin defined in the module
            db_migrations::get_migrations()
        )
        .build(),
)
...
```

## Running Migrations on Launch
This is another part that I was unable to find in the documentation but was able to figure out reading the relevant code in [`plugin.rs`](https://github.com/tauri-apps/tauri-plugin-sql/blob/dev/src/plugin.rs#L296). For each database that you would like to run the migrations for at startup, you can add them to the `preload` option of the `sql` plugin configuration in `src-tauri/tauri.conf.json`:
```json
{
  "build": {
    ...
  },
  ...
  "plugins": {
    "sql": {
      "preload": [
        "sqlite:database_name.db"
      ]
    }
  }
}
```

## References
- [Plugin tauri-plugin-sql GitHub](https://github.com/tauri-apps/tauri-plugin-sql)
- [Tauri plugins GitHub](https://github.com/tauri-apps/plugins-workspace)
- [Tauri configuration docs](https://tauri.app/v1/references/configuration-files)

Setting up the Tauri SQL Plugin


These are the settings that I use in tandem with [ESLint](https://eslint.org/) and [Prettier](https://prettier.io/).

1. Go to `File > Preferences > Settings`
2. Search for `On Save`
3. Under `Editor: Code Actions On Save` click `Edit in settings.json`
4. Set the following:
    ```json
    "editor.codeActionsOnSave": [
        "source.organizeImports",
        "source.fixAll.eslint"
    ]
    ```

This will organize imports alphabetically (`source.organizeImports`) and use `eslint` to fix any auto-fixable errors. Using prettier this will take care of formatting as well.

For organizing imports, I initially was only using `source.organizeImports` but this was removing the trailing commas on imports which conflicted with my prettier settings. By following it with `source.fixAll.eslint`, the prettier rules fix this at the same time.

## References
- [[StackOverflow] Enable trailing comma in vscode](https://stackoverflow.com/questions/66508443/enable-trailing-comma-in-vscode)

VSCode: Auto Format on Save (Prettier)


## Parent Expanding to the Size of Child Elements
```scss
.container {
  display: grid;
  // Fits columns to the available space, with a min width of 100px
  // and a max width of 1 fraction of the container
  grid-template-columns: repeat(auto-fit, minmax(100px, 1fr));
  gap: 8px;
  min-width: 150px;
}
```

## Child Elements Filling Parent
```scss
.container{
  display: flex;
  gap: 8px;
  min-width: 150px;

  .child {
    // flex: {flex-grow} {flex-shrink} {flex-basis}
    // Using flex-basis of 0, all elements have the same starting point
    flex: 1 1 0;
  }
}
```

## References
- [[StackOverflow] How to make flexbox items the same size?](https://stackoverflow.com/questions/29503227/how-to-make-flexbox-items-the-same-size)
- [[MDN] CSS Grid Layout](https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Grid_Layout)
- [[CSS-Tricks] Auto-Sizing Columns in CSS Grid: `auto-fill` vs `auto-fit`](https://css-tricks.com/auto-sizing-columns-css-grid-auto-fill-vs-auto-fit/)

CSS Equal Width Elements

- [Window Size Changes](#window-size-changes)
- [Watching a Specific Element for Size Changes](#watching-a-specific-element-for-size-changes)
- [React Hook](#react-hook)
- [References](#references)

----

## Window Size Changes
If it your logic based off of the window size and not specific element sizing, an event listener can be added to the window for the `resize` event with:
```typescript
const callback = (evt: UIEvent) => console.log(evt);
window.addEventListener('resize', callback);
```

When done, be sure to remove the event listener with:
```typescript
window.removeEventListener('resize', callback);
```

## Watching a Specific Element for Size Changes
For cases where a specific element's size needs to be known, a `ResizeObserver` can be used. This is helpful for when the window doesn't resize but a element's size does (e.g. reflow due to content being added or removed).

To watch an element, the following can be used:
```typescript
const testDiv = document.getElementById('test');
const callback = (entries: ResizeObserverEntry[], observer: ResizeObserver) => console.log(entries);
const resizeObserver = new ResizeObserver(callback);
resizeObserver.observe(testDiv);
```

When done watching an element, it can be unobserved using:

```typescript
// Only stop watching a single element
resizeObserver.unobserve(testDiv);

// Or Alternatively, all elements can be unobserved using:
resizeObserver.disconnect();
```

## React Hook
This hook is a general purpose resize hook. It can be used to track the resizing of a specific element or when the window itself is resized.

If `elementToWatch` isn't passed, the whole window will be watched for resize events.

```typescript
import { useEffect } from 'react';

/**
 * Hookup a resize listener to the window or a target element
 * @param {() => void} callback The callback to call on resize (should be memoized with `useCallback` to avoid re-registering each render)
 * @param {boolean} callOnMount Whether to trigger the callback on mount
 * @param {HTMLElement | undefined} elementToWatch The element to watch for size changes
 */
export default function useOnResize(
  callback: () => void,
  callOnMount: boolean = false,
  elementToWatch?: HTMLElement,
) {
  useEffect(() => {
    // Watch a particular element if specified
    if (elementToWatch) {
      const resizeObserver = new ResizeObserver(callback);
      resizeObserver.observe(elementToWatch);
      if (callOnMount) {
        callback();
      }

      return () => {
        resizeObserver.disconnect();
      };
    }

    // If no element is specified, watch for window size changes
    window.addEventListener('resize', callback);
    if (callOnMount) {
      callback();
    }
    return () => {
      window.removeEventListener('resize', callback);
    };
  }, [callback, callOnMount, elementToWatch]);
}

```


## References
- [[MDN] ResizeObserver
](https://developer.mozilla.org/en-US/docs/Web/API/ResizeObserver)
- [[MDN] Window: resize event
](https://developer.mozilla.org/en-US/docs/Web/API/Window/resize_event)



Monitor for Resize in JavaScript

- [Configuration](#configuration)
  - [Redirect HTTP to HTTPS via 301](#redirect-http-to-https-via-301)
  - [Including SSL Certificate for HTTPS](#including-ssl-certificate-for-https)
  - [WebSocket Connections](#websocket-connections)
  - [Load Balancing](#load-balancing)
  - [Basic Full Example](#basic-full-example)
  - [Adding Custom Stream Configs](#adding-custom-stream-configs)
- [Commands](#commands)
- [PID Issue on Some Alpine Linux Docker Images](#pid-issue-on-some-alpine-linux-docker-images)
- [References](#references)

----

## Configuration

### Redirect HTTP to HTTPS via 301
```nginx
server {
  listen *:80;

  server_name k3s.functionalbytes.com;
  server_tokens off; ## Don't show the nginx version number

  location / {
    return 301 https://k3s.functionalbytes.com:443$request_uri;
  }
}
```

### Including SSL Certificate for HTTPS
```nginx
ssl_certificate /secret/keys/proxy.functionalbytes.com.fullchain;
ssl_certificate_key /secret/keys/proxy.functionalbytes.com.key;
```

### WebSocket Connections
See: [[nginx Documentation] WebSocket proxying](http://nginx.org/en/docs/http/websocket.html)
```nginx
location /web_socket_path/ {
    proxy_pass http://backend;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
}
```

### Load Balancing
See: [[nginx Documentation] HTTP Load Balancing](https://docs.nginx.com/nginx/admin-guide/load-balancer/http-load-balancer/)
```nginx
upstream k3s_servers {
  # Round Robin by default - can add "weight=#" after each server for weighting, e.g.:
  # server k3s-s1.functionalbytes.com:80 weight=3;
  # With this set and nothing on k3s-s2 (default = 1), 3 out of 4 requests will be sent to k3s-s1

  # Alternatively one of the following can be uncommented for other load balancing options

  # Uncomment the line below to use the one with the fewest connections
  # least_conn;

  # Uncomment to send requests to the server based on a hash of the first 3 octets of their IP (guaranteeing requests go to the same server unless it is unavailable)
  # ip_hash;
  server k3s-s1.functionalbytes.com:80;
  server k3s-s2.functionalbytes.com:80;
}
```

### Basic Full Example
```nginx
upstream k3s_servers {
  server k3s-s1.functionalbytes.com:80;
  server k3s-s2.functionalbytes.com:80;
}

server {
  listen *:80;

  server_name k3s.functionalbytes.com;
  server_tokens off; ## Don't show the nginx version number, a security best practice

  location / {
    return 301 https://k3s.functionalbytes.com:443$request_uri;
  }
}

server {
  listen 443 ssl http2;

  server_name k3s.functionalbytes.com;
  server_tokens off; ## Don't show the nginx version number, a security best practice

  ssl_certificate /secret/keys/proxy.functionalbytes.com.fullchain;
  ssl_certificate_key /secret/keys/proxy.functionalbytes.com.key;

  # Allow large attachments
  client_max_body_size 128M;

  location / {
    proxy_pass http://k3s_servers;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
  }
}
```

### Adding Custom Stream Configs
1. Add an include directive to `/config/nginx/nginx.conf`
    > File: `/config/nginx/nginx.conf`
    ```nginx
    stream {
        include /config/nginx/stream-confs/*;
    }
    ```
2. Add the stream configs in `/config/nginx/stream-confs/`, e.g.:
    > File: `/config/nginx/stream-confs/k3s.conf`
    ```nginx
    upstream k3s_servers {
      server k3s-s1.functionalbytes.com:6443;
      server k3s-s2.functionalbytes.com:6443;
    }

    server {
      listen *:6443;

      ssl_certificate /secret/keys/proxy.functionalbytes.com.fullchain;
      ssl_certificate_key /secret/keys/proxy.functionalbytes.com.key;

      proxy_pass k3s_servers;
    }
    ```

## Commands
Reload the nginx config without any downtime (sends `SIGHUP` signal):
```bash
nginx -s reload
```

Verify configuration is valid:
```bash
nginx -t
```

## PID Issue on Some Alpine Linux Docker Images
**Note:** I am currently using `nginx:1.21-alpine` (official) which does not have this issue, but do still see the issue with `linuxserver/nginx:1.20.2` ([linuxserver.io](https://www.linuxserver.io/) image that I was using this on [Unraid](https://unraid.net/))

There is an issue where the PID file isn't created in the alpine linux version of nginx docker image. Running the reload command will result in the following error:
```bash
> nginx -s reload
nginx: [error] open() "/run/nginx/nginx.pid" failed (2: No such file or directory)
```

Two options for fixing this:

1. Create the required directory
    > File: `Dockerfile`
    ```Dockerfile
    ...
    RUN mkdir -p /run/nginx
    ...
    ```
1. Override the PID location
    ```sh
    nginx -g 'pid /tmp/nginx.pid; daemon off;'
    ```

## References
- [[Ubuntu Manpage] nginx](https://manpages.ubuntu.com/manpages/jammy/man8/nginx.8.html)
- [[nginx Documentation] WebSocket proxying](http://nginx.org/en/docs/http/websocket.html)
- [[nginx Documentation] HTTP Load Balancing](https://docs.nginx.com/nginx/admin-guide/load-balancer/http-load-balancer/)
- [[GitHub Issue #185] latest image run nginx problems](https://github.com/gliderlabs/docker-alpine/issues/185)



Configuring Nginx


- [Rough Notes](#rough-notes)
  - [Commands](#commands)
  - [Init Systems](#init-systems)
  - [Service Managers](#service-managers)
- [Important Reading](#important-reading)
- [References](#references)

----

## Rough Notes

- [systemd](https://en.wikipedia.org/wiki/Systemd) is for both system init and service management
- [supervisord](http://supervisord.org/) is only for service managment

### Commands
- The [`service`](https://manpages.ubuntu.com/manpages/jammy/en/man8/service.8.html) command is a "wrapper"/"high-level" command that will call the respective "low-level" manager such as [`systemctl`](https://manpages.ubuntu.com/manpages/jammy/man1/systemctl.1.html)
- The [`service`](https://manpages.ubuntu.com/manpages/jammy/en/man8/service.8.html) command allows for basic interatctions such as `status`, `start`, `reload`, etc.
- The [`systemctl`](https://manpages.ubuntu.com/manpages/jammy/man1/systemctl.1.html) command allows for advanced commands such as checking if a service is enabled (activated when booting): `systemctl is-enabled sshd.service`

**Quick Reference**
- Enable service to run at startup
    ```bash
    sudo systemctl enable sshd.service
    ```
- Disable service from running at startup
    ```bash
    sudo systemctl disable sshd.service
    ```

### Init Systems
- Init systems are those that run in process #1 - responsible for bootstrapping the system
- May also be responsible for starting and managing services (long running, background process)
- Ubuntu used to use [upstart](https://upstart.ubuntu.com/) but switched to [systemd](https://en.wikipedia.org/wiki/Systemd) in version 15.04 (2015)
- Alpine linux uses [OpenRC](https://wiki.alpinelinux.org/wiki/OpenRC)
- `systemd` is linux specific - (BSD distros such as FreeBSD have their own ["BSD-style init"](https://docs.freebsd.org/en/articles/linux-users/#startup))
- Although highly adopted, there is a lot of contreversy around `systemd` (see: [[Reddit] ELI5: The SystemD vs. init/upstart controversy](https://www.reddit.com/r/linux/comments/132gle/comment/c709592/?utm_source=share&utm_medium=web2x&context=3))

### Service Managers
Outside of init systems, there are also dedicated service managers such as [Supervisor (supervisord)](http://supervisord.org/)
> It shares some of the same goals of programs like launchd, daemontools, and runit. Unlike some of these programs, it is not meant to be run as a substitute for init as “process id 1”. Instead it is meant to be used to control processes related to a project or a customer, and is meant to start like any other program at boot time.

*[[supervisord.org] Supervisor: A Process Control System](http://supervisord.org/#supervisor-a-process-control-system)*

- Advantages of using something like `supervisord` are that it is OS agnostic and since it is outside of process #1, processes can be managed by users other than root/sudo

## Important Reading
- [[StackExchange] What is the difference between "init" and "service manager"?](https://unix.stackexchange.com/a/489949)
- [[gentoo wiki] Comparison of init systems](https://wiki.gentoo.org/wiki/Comparison_of_init_systems)

## References
- [[Stack Overflow] Difference between Systemctl and service command](https://stackoverflow.com/a/43539946)
- [[Ask Ubuntu] Difference between systemctl and service commands](https://askubuntu.com/questions/903354/difference-between-systemctl-and-service-commands)

Linux Service Managers and Init Systems


> "In quantum mechanics, wave function collapse occurs when a wave function — initially in a superposition of several eigenstates — reduces to a single eigenstate due to interaction with the external world."

*[Wikipedia: Wave function collapse](https://en.wikipedia.org/wiki/Wave_function_collapse)*

## How WFC Applies to World Generation
- For world generation, each tile has a set of possible types of tiles
  - Until one is chosen, it is in its "superposition" state
- The tile map is procedurally iterated over, randomly selecting one of the possible types for each tile
  - This is the collapse phase
- There are adjacency rules for which tiles can be next to each other, so as tiles are chosen, the set of possible types for adjacent tiles will be reduced (probability space reduced)
  - These rules can be different depending on direction from the tile

## Determining Valid Neighbors
Multiple approaches:
- Map of valid neighbors for each tile / direction
- "Socketing" system where each tile / direction has a socket ID that is used to match valid neighbors
- [Relevant section of "Superpositions, Sudoku, the Wave Function Collapse algorithm" Video](https://youtu.be/2SuvO4Gi7uY?t=265)

## Ideas for Pathing
  - There can be an additional step after each tile is collapsed to check that pathing is still possible and the previous tile can be regenerated on failure until pathing is solvable
  - The adjacency rules could include pathing when reducing neighboring sets (i.e. if there are are walls on all other sides, walls are excluded from the possible set for the remaining tile)
  - The entire world/map could be generated and then checked that pathing is solvable using something like [A*](https://en.wikipedia.org/wiki/A*_search_algorithm) - either breaking down paths or re-generating until solvable

## References

- [Wikipedia: Wave function collapse](https://en.wikipedia.org/wiki/Wave_function_collapse)
- [Video: Why I'm Using Wave Function Collapse for Procedural Terrain | Unity Devlog
](https://www.youtube.com/watch?v=20KHNA9jTsE)
  - [Jump to Summary section](https://youtu.be/20KHNA9jTsE?t=388)
- [Video: Superpositions, Sudoku, the Wave Function Collapse algorithm.
](https://www.youtube.com/watch?v=2SuvO4Gi7uY)


Wave Function Collapse For World Generation

- [Context Code](#context-code)
- [Usage](#usage)

----

## Context Code
The context is used to store the previous path. This is done by watching the routing path (`router.asPath` for NextJS) and updating the context state when it changes. It is initialized to `undefined` and the current path is stored in a mutable ref so that it can be stored when the path changes.

> File: `src/contexts/RoutingContext.tsx`
```tsx
import { useRouter } from 'next/router';
import React, { ReactNode, useEffect, useRef, useState } from 'react';

export type RoutingContextState = {
  previousPath?: string;
};

export const DEFAULT_ROUTING_CONTEXT_VALUE: RoutingContextState = {
  previousPath: undefined,
};

export const RoutingContext = React.createContext(
  DEFAULT_ROUTING_CONTEXT_VALUE,
);

export type RoutingContextWrapperProps = {
  children: ReactNode;
};

export const RoutingContextWrapper = ({
  children,
}: RoutingContextWrapperProps) => {
  const router = useRouter();

  const [previousPath, setPreviousPath] = useState<string>();
  const currentPath = useRef<string>();

  useEffect(() => {
    setPreviousPath(currentPath.current);
    currentPath.current = router.asPath;
  }, [router.asPath]);

  return (
    <RoutingContext.Provider
      value={{
        previousPath,
      }}
    >
      {children}
    </RoutingContext.Provider>
  );
};

export default RoutingContext;
```

## Usage

First wrap your app/page with the context. In NextJS this is done in `pages/_app.tsx`:
```tsx
...

function AppName<P>({ Component, pageProps }: AppNameProps<P>) {
  return (
    <RoutingContextWrapper>
      <Head>
        <title>{APP_NAME}</title>
      </Head>
      <Component {...pageProps} />
    </RoutingContextWrapper>
  );
}

...
```

Then, where ever the previous path is needed, it can be retreived from the context:

```typescript
const { previousPath } = useContext(RoutingContext);
```




Tracking the Previous Page using React Context


- [Quick Notes](#quick-notes)
- [Examples](#examples)
  - [Set the initial Mode](#set-the-initial-mode)
  - [Toggling the Mode](#toggling-the-mode)
  - [Hook Tracking the Mode in state using a `MutationObserver`](#hook-tracking-the-mode-in-state-using-a-mutationobserver)
  - [Applying Styles Using CSS Variables](#applying-styles-using-css-variables)
  - [Applying Styles Directly in CSS/SCSS Modules](#applying-styles-directly-in-cssscss-modules)
- [References](#references)

----

## Quick Notes
- Use global class to indicate when dark mode is enabled
- For SPAs, the global class can be applied at the root component level
- For NextJS, set the global class on the body in the `_document.tsx`
- When using [styled components](https://styled-components.com/), leverage the `ThemeProvider`
- Set the default dark/light mode based on the `prefers-color-scheme` media query to honor user's preference
  - See related: [Using Users' Preferences for Dark Mode](/posts/detecting-dark-mode)
- When the mode is changed, store the user's selection in [`localStorage`](https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage)


## Examples
### Set the initial Mode
```typescript
// Constants for the dark mode CSS class and local storage key
export const DARK_MODE_CLASS = 'dark_mode';
export const DARK_MODE_STORAGE_KEY = 'dark_mode';
```

```typescript
if (
  localStorage?.getItem(DARK_MODE_STORAGE_KEY}) ??
  window.matchMedia('(prefers-color-scheme: dark)').matches
) {
  document.body.classList.add(DARK_MODE_CLASS);
}
```

To ensure this happens as early as possible in NextJS:
> File: `_document.tsx`
```tsx
class CustomDocument extends Document {
  render() {
    return (
      <Html lang="en">
        <Head />
        <body>
          <Main />
          <NextScript />
          <Script id="dark_mode_init" strategy="afterInteractive">
            {`
            if (
              localStorage?.getItem('${DARK_MODE_STORAGE_KEY}') ??
              window.matchMedia('(prefers-color-scheme: dark)').matches
            ) {
              document.body.classList.add('${DARK_MODE_CLASS}');
            }
            `}
          </Script>
        </body>
      </Html>
    );
  }
}
```

### Toggling the Mode
```typescript
// Constants for the dark mode CSS class and local storage key
export const DARK_MODE_CLASS = 'dark_mode';
export const DARK_MODE_STORAGE_KEY = 'dark_mode';
```

```typescript
export function toggleDarkMode() {
  if (document.body.classList.contains(DARK_MODE_CLASS)) {
    document.body.classList.remove(DARK_MODE_CLASS);
    localStorage?.setItem(DARK_MODE_STORAGE_KEY, '');
  } else {
    document.body.classList.add(DARK_MODE_CLASS);
    localStorage?.setItem(DARK_MODE_STORAGE_KEY, 'true');
  }
}
```

### Hook Tracking the Mode in state using a `MutationObserver`
```typescript
export default function useIsDarkMode() {
  const [isDarkMode, setIsDarkMode] = useState<boolean>(false);

  useEffect(() => {
    const observer = new MutationObserver(() => {
      setIsDarkMode(document.body.classList.contains(DARK_MODE_CLASS));
    });
    observer.observe(document.body, {
      attributes: true,
      attributeFilter: ['class'],
      characterData: false,
      childList: false,
      subtree: false,
    });

    // Set the initial value
    // For NextJS this has to be done within the effect since
    // it must be run client side - for SSR projects it can be done
    // in the state initialization
    setIsDarkMode(document.body.classList.contains(DARK_MODE_CLASS));

    return () => {
      observer.disconnect();
    };
  }, []);

  return isDarkMode;
}
```

### Applying Styles Using CSS Variables
```css
body {
  --background-color: #ffffff;
  --text-color: #000000;

  /**
   * The variables can be used anywhere below body
   * (even in components using css modules)
   */
  color: var(--text-color);
  background-color: var(--background-color);
}

body.dark_mode {
  --background-color: #000000;
  --text-color: #ffffff;
}
```

### Applying Styles Directly in CSS/SCSS Modules
```css
.my_component {
  // Light mode styles
  background-color: #ffffff;
  color: #000000;
}

:global(body.dark_mode) {
  .my_component {
    /** Dark mode styles */
    background-color: #ffffff;
    color: #000000;
  }
}
```

## References
-  [MDN Docs - Using CSS custom properties (variables)](https://developer.mozilla.org/en-US/docs/Web/CSS/Using_CSS_custom_properties)
-  [NextJS Docs - Scripts: `afterInteractive`](https://nextjs.org/docs/basic-features/script#afterinteractive)
-  [styled components Docs - Theming](https://styled-components.com/docs/advanced)

Dark Mode Toggle in React

- [Summary of the Approach](#summary-of-the-approach)
- [An example using Prisma](#an-example-using-prisma)
- [Other Ideas and Considerations](#other-ideas-and-considerations)
  - [Using a Base Table](#using-a-base-table)
  - [Using an ORM](#using-an-orm)
  - [Indexed Search](#indexed-search)
- [References](#references)

----

One of the projects I am currently working on uses [Prisma](https://www.prisma.io/) as an ORM. The project requires a search feature as well as a feature where there is a feed of changed items. As Prisma doesn't have a built in concept for handling these types of scenarios, I've investigated options to come up with an approach that best works for the project.

The main approach discussed here avoids using the ORM itself and is the one that I went forward with but the later section [Using an ORM](#using-an-orm) covers some ideas for tackling it within an ORM itself.

## Summary of the Approach
- Create a type for the result/feed items (e.g. `SearchResult` / `ActivityItem`)
- Use raw SQL with `UNION` to query tables for common/like fields in the same operation (table sub queries need matching # columns for `UNION` to work)
- Include a field in each table sub query to indicate the type
- Load results as the created type(s)

**Pros**
- Single DB operation
- Paging / ordering is done across tables/models

**Cons**
- It can be awkward to include data specific to individual tables/models as unapplicable rows will require an empty field in their place
- If an ORM is being used, it may not be supported or require extra effort and code that is inconsistent with the code base

## An example using Prisma
This example should work for any case that an ORM isn't used or when the ORM allows for raw queries. Prisma allows for raw SQL queries to be performed using the `prisma.$queryRaw()` function - this can be subtitled with any raw query options.

For this example, I'll use an application that has companies, users, and notes (for companies) that are searchable.

```
+--------------------------+
|          Company         |
+--------------------------+
| - id          (string)   |
| - name        (string)   |
| - createdAt   (datetime) |
| - updatedAt   (datetime) |
+--------------------------+

+--------------------------+
|           User           |
+--------------------------+
| - id          (string)   |
| - firstName   (string)   |
| - lastName    (string)   |
| - createdAt   (datetime) |
| - updatedAt   (datetime) |
+--------------------------+

+--------------------------+
|           Note           |
+--------------------------+
| - id          (string)   |
| - title       (string)   |
| - companyId   (string)   |
| - createdAt   (datetime) |
| - updatedAt   (datetime) |
+--------------------------+
```

1. Create a type for the results
    ```typescript
    type SearchResult = {
      entityType: EntityType;
      entityId: string;
      title: string;
      createdAt: Date;
      updatedAt: string;
    };
    ```

2. Create a search function
    ```typescript
    /**
     * Search for companies, users, and notes based on their name/title
    * @param {string} search The search query to match
    * @param {number} pageNumber The page to return
    * @param {number} pageSize The number of results to include per-page
    * @returns {Promise<SearchResult[]>} A promise for the array of search results
    */
    export default function search(
      searchQuery: string,
      pageNumber: number = 0,
      pageSize: number= 10,
    ) {
      return prisma.$queryRaw<SearchResult[]>(Prisma.sql`
        SELECT
            'COMPANY' as entityType,
            id as entityId,
            name as title,
            createdAt,
            updatedAt
          FROM Company
          WHERE name LIKE '%${searchQuery}%'
        UNION
        SELECT
            'USER' as entityType,
            id as entityId,
            CONCAT(firstName, ' ', lastName) as title,
            createdAt,
            updatedAt
          FROM User
          WHERE
            firstName LIKE '%${searchQuery}%'
            OR
            lastName LIKE '%${searchQuery}%'
        UNION
        SELECT
            'NOTE' as entityType,
            id as entityId,
            title,
            createdAt,
            updatedAt
          FROM Note
          WHERE title LIKE '%${searchQuery}%'
        ORDER BY updatedAt DESC
        LIMIT ${pageSize}
        OFFSET ${pageSize * pageNumber}
      `);
    }
    ```
    *(Note: This example is using `LIKE` with wild cards, for better search results consider using full-text search - see [Prisma Docs](https://www.prisma.io/docs/concepts/components/prisma-client/full-text-search), [MySQL Docs](https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html), [PostgreSQL Docs](https://www.postgresql.org/docs/current/textsearch.html))*
3. Use the search as needed
    ```typescript
    // Get the search query and page number from the request
    const searchResults = await search(searchQuery, pageNumber);
    // Serialize / return the results to the frontend for display
    ```
When displaying the data, the `entityType` property can be used to indicate the type of result and link to it accordingly.

For paging, you'll likely need the total results in order to determine the number of pages. This can be done with a query such as the following:

```typescript
prisma.$queryRaw<number>(Prisma.sql`
  SELECT (
    SELECT COUNT(*)
    FROM Company
    WHERE name LIKE '%${searchQuery}%'
  ) + (
    SELECT  COUNT(*)
    FROM User
    WHERE
      firstName LIKE '%${searchQuery}%'
      OR
      lastName LIKE '%${searchQuery}%'
  ) + (
    SELECT COUNT(*)
    FROM Note
    WHERE title LIKE '%${searchQuery}%'
  ) AS totalResults
`);
```

Both the total and the results can be retrieved in a single transaction if required, for Prisma this is done by using `prisma.$transaction()`:
```typescript
const [totalResults, searchResults] = await prisma.$transaction([
  prisma.$queryRaw<number>(Prisma.sql`/** The count query */`),
  prisma.$queryRaw<SearchResult[]>(Prisma.sql`/** The search query  */`),
]);

```

## Other Ideas and Considerations

### Using a Base Table
If all items being searched have a common base (e.g. Movies, Shows, Songs could be considered "Media"), they can be consolidated in a single table that has the common fields (title, release date, length, etc.) along with a `type` field. This would allow for searching the single table, though other considerations would need to be made, depending on the application:
- How to handle item specific fields
  - JSON field?
  - Separate linked tables?
- What if a item with fewer common fields needs to be added in the future such as a Book?

### Using an ORM
When first considering approaches using Prisma, one of the original ideas I had was to search each model using the ORM and paginate that, for example:

```typescript
async function searchVideos(search: string, page: number, pageSize: number) {
  // Query Movies
  const [totalMovies, movies] = await prisma.$transaction([
    prisma.movie.count({
      where: {
        name: {
          contains: search,
        },
      },
    }),
    prisma.movie.findMany({
      where: {
        name: {
          contains: search,
        },
      },
      skip: page * pageSize,
      take: pageSize,
    }),
  ]);

  // Query Shows
  const [totalShows, shows] = await prisma.$transaction([
    prisma.shows.count({
      where: {
        name: {
          contains: search,
        },
      },
    }),
    prisma.shows.findMany({
      where: {
        name: {
          contains: search,
        },
      },
      skip: page * pageSize,
      take: pageSize,
    }),
  ]);

  return {
    movies,
    shows,
    totalMovies,
    totalShows
  }
}
```
*(Note: This specific example using `Movies` and `Shows`, is a good candidate to use a more generic `Video` or `Media` type/table as described in the above [Using a Base Table](#using-a-base-table))*

This works ok if results are being shown in separate groups where paging is being done per-type, e.g. a quick search grouped by type such as:
```
Search Results:
  Movies:
    - Movie Match 1
    - Movie Match 2
    - Movie Match 3
  TV Shows:
    - TV Show Match 1
```

If results are shown in one list and you'd like to have a consistent total page size you will need to handle manipulating the page size - dividing the page size by the number of types of items and querying more from other types when reaching the end of results for any of the types.

Another issue comes with sorting as taking equal chunks from each table does not guarantee ordering across models. For example, if you take the first 5 Movies and first 5 Shows ordered ascending by date, it is possible that the 6th movie has an earlier date than one of the Shows but is excluded since sorting is done per model.

I did end up using this approach for a quick search feature since results are being grouped by type (i.e. show the latest 5 of each type that match) but chose to go with raw queries for the full search and activity feed.


### Indexed Search
Rather than querying the tables directly, the items in them could be indexed externally for searching. This could be a separate table that is populated as data changes or using a provider such as [algolia](https://www.algolia.com/). This will help with performance with the tradeoff of maintaining the index.


## References
- [MySQL Docs - UNION Clause](https://dev.mysql.com/doc/refman/8.0/en/union.html)
- [PostgreSQL Docs - Combining Queries (UNION, INTERSECT, EXCEPT)](https://www.postgresql.org/docs/current/queries-union.html)
- [MSSQL Docs - Set Operators - UNION (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql)
- [Prisma Docs - Raw database access](https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access)