DEV Community: Rap2h

Runtime toolkit I built for my game

Rap2h — Mon, 28 Oct 2024 03:20:05 +0000

I lost a lot of time for my first game not having implemented a proper debug toolset on day one. Here is what I have done for my second game.

Autosave

First of all, we need autosave during tests. When playing, if I have to change something, restarting at the beginning could be a chore. Also, autosave is something that can be difficult to implement later. I implemented raw autosave, which takes all the game state and dumps it in a file.

Runtime debugger

It could be considered like cheat codes for doom in a more convenient way. Here is a screenshot of the in-game tool I developed. I can toggle this menu anytime when the game is running by pressing @.

Breakdown of the most important features below.

Teleport

The debug feature I use the most is teleport, heroes can visit any place anytime.

Add sprite

Instead of having to go back to the editor, I can add a sprite during gameplay to the current screen. Sprite properties are not automatically added to the code, but copied to the clipboard when I position and configure the sprite on the current screen; I can copy it into the code when the placement process is finished.

Database

To check the balance of everything, but also missing translations, list of spells, items, etc. Having it available at runtime helps me understand broken mechanics without reading code

Random or superheroes

To skip some scenes or try different ones, I can upgrade or change heroes, even during a battle, which is convenient to validate balance and interactions.

Log game state

When I want to see all the variables of the game and their current state. Maybe I should add a game state editor directly also.

Add time, items, xp, gold, etc.

There are also one-click options to add skills, all objects to inventory, reputation, gold, days, and so on.

What next?

The next tool I want to develop is an arena mode, where I can select heroes and set their attributes, then enemies (number, levels, etc.) to quickly test and improve balance. This tool would also be interesting for beta testers.

https://store.steampowered.com/app/3010290

The Secret of Darkwoods, two months after Steam Release

Rap2h — Thu, 25 May 2023 11:42:33 +0000

Two months ago, I released a free video game on Steam, an open-world text-based RPG inspired by the 'choose your own adventure' books. It was an exciting and rewarding experience, I will share my experiences here.

The Secret of Darkwoods on Steam

Open-world illustrated text-based RPG. Inspired by classic role-playing video games and fantasy gamebooks, the secret of darkwoods is a free game, a non-linear standalone adventure with side quests in a vast world.

store.steampowered.com

I had a few building blocks to start with. I do programming, music, and I'm interested in procedural generation. I've played several 'choose your own adventure' books when I was a child. I also like to draw, but I'm pretty bad at it.

Last September, I made a prototype that I tested in the form of a video on 2 forums. I received positive feedback and decided to give it a shot. I spent about 7 months in my free time, I think I spent more than 500 hours on it in total.

I completely messed up at the beginning with my estimates. I thought it would be a small project that I could wrap up in a few weeks. I then set a deadline (and postponed it once!)

It has become a game of 800 unique pages (with procedural variations). It takes between 3 and 6 hours to complete it. I'll put usage statistics at the end of the thread. In terms of programming, it is "loops" and "if" statements.

Initially, it was a music project. Several tracks that I have made in recent years have been published as illustrations for RPG sessions. I thought it could be interesting to use them in a video game.

Eight composers have offered their tracks for free, following a call for participation. It was great to be able to discuss the music and I think they were pleased to see their music in a game.

For the illustrations, I used midjourney. When I started the project, it seemed like an opportunity to develop an illustrated game, but now the subject seems more complicated.

The game is unexpectedly successful among blind players, which is reassuring about the game's interest itself and the fact that the images were secondary. I thank the testers who have greatly helped me make the game accessible.

It was a rewarding experience on a social level, surprisingly: I met lots of people who contributed and my friends and family supported me or participated in developing the game. All in all, more than 50 contributors. Thanks to everyone!

This game was developed as a hobby, I wanted it to be free. I was told that this could represent an additional difficulty (examples: game poorly perceived, difficulty in generating engagement, Steam has no interest in promoting, etc.), but I'm not quite sure.

My initial goal was to have 30 people play for 20 minutes. In the end, there were more than 1500 people for an average time of 2 hours of play, which was totally unexpected. 270 people probably finished it, there are 15 people playing every day.

The alpha and beta tests significantly improved the quality of the game, I don't know if people were aware of it, I sometimes received emails of several pages from unknown people suggesting super relevant improvements.

I regret not planning to allow translations. Several people have offered to translate the game for free into many languages, but the game is not at all ready for that, it would take dozens of hours to prepare it.

That's it. I loved making this game, it took me a lot of time, it was full of emotions and now that it's over, it feels a bit weird. Don't hesitate if you have any questions or comments, I would be happy to discuss it!

Translated from my original tweet thread (in french): https://twitter.com/rap2h/status/1660952774966755329

SQL tips for beginners; what I learned after 10+ years of occasionally writing queries and schemas as a web dev

Rap2h — Tue, 28 Jun 2022 20:56:27 +0000

Index nearly everything, index early. Not indexing results in poor performances. Indexing too late can create downtime or inconsistent data. Do not consider removing/avoiding index to preserve disk space, buy more space instead. Remember that index may have an impact on write performance though.
PostgreSQL is your best choice, it has a skilled community, including StackOverflow heroes. There is no MySQL, MariaDB, or SQLite user group where I live.
ORM are great for simple CRUD operation. Don't use it to query specific stats or any complex query, use raw queries. BTW, for TS/JS devs, Prisma 3 is cool, Sequelize is obsolete.
SQL syntax is case insensitive, use lower case everywhere (and underscores), do not name your tables with camelCase. ALSO, YOU DON'T HAVE TO YELL. select id from users is the same as SELECT id FROM users. For string comparison, case sensitivity depends on collation.
Never miss a foreign key. Implicit links and relations between tables should be avoided.
Follow a pattern when naming tables and columns. Example: users, posts, comments (plural). post_user, comment_post, comment_user for junction tables (singular + alphabetical order). id for primary key, id_user, id_comment, and id_post (singular + id first) for foreign key. Reading a database schema is hard for newcomers. Be super consistent when naming tables.
1 model ≠ 1 table. Unfortunately, when designing a database you have to think at a lower level than "models" and "objects". You will have to deal with technical tables.
Use the right data type. Do not create "custom" data types defined by an implicit contract.
JSONB is great but don't overuse it. Also, learn to query it it's complex yet powerful. When you iterate a lot on designing a schema, you can use JSONB to avoid modifying the schema over and over.
PostgreSQL does not scale automatically. Expect migrations and downtime.
CTE is more readable than subqueries, you can read it from the top to the bottom. Also, you can understand the way it was designed.
Keep tables schema simple, avoid patterns such as EAV that makes tables hard to understand and read; try to keep most tables as simple as vertical CSV sheets, with one line for each record.
Don't be afraid of writing long and verbose queries to address a specific need, it's often more efficient.
The order of the words in a query is not the order in which they are evaluated. Know the order of execution: from, where, group by, having, order by, select, limit.
120 tables are way too much, even for big products. You can build a robust application with 25 tables.

Thank you for reading 🙏

Create a lookalike search engine with Next.js, Tailwind and Elasticsearch (10 steps)

Rap2h — Mon, 03 Jan 2022 22:00:13 +0000

In this post you will learn how to create a website that displays books similar to a selected book from scratch, using Next.js (React), Tailwind and Elasticsearch. Go to end of the post to check result.

List of steps:

Install Next.js
Add tailwind
Create a sample Elasticsearch database
Install missing dependencies
Create frontend page
Create API
Update frontend page to implement autocomplete
Update API to implement lookalike
Update frontend page to implement lookalike
Test

1. Install Next.js

First create your Next.js app:

npx create-next-app@latest --typescript lookalike-search-engine

Then run it:

cd lookalike-search-engine
npm run dev

Then you can goto http://localhost:3000 to see the welcome page.

2. Add tailwind

Install tailwind:

npm install -D tailwindcss postcss autoprefixer
npx tailwindcss init -p

Edit tailwind.config.js:

module.exports = {
+  content: [
+    "./pages/**/*.{js,ts,jsx,tsx}",
+    "./components/**/*.{js,ts,jsx,tsx}",
+  ],
  theme: {
    extend: {},
  },
  plugins: [],
}

Replace styles/globals.css with:

@tailwind base;
@tailwind components;
@tailwind utilities;

Replace pages/index.tsx with:

import type { NextPage } from "next";

const Home: NextPage = () => {
  return (
    <h1 className="text-3xl font-bold underline">
      Hello world!
    </h1>
  );
};

export default Home;

Delete styles/Home.module.css and pages/api/hello.ts.

3. Create a sample Elasticsearch database

Install Elasticsearch (MacOS: brew tap elastic/tap then brew install elastic/tap/elasticsearch-full, other: see Elasticsearch docs).

Run create-elasticsearch-dataset to create a sample database with 6800 books:

npx create-elasticsearch-dataset --dataset=books

Goto http://localhost:9200/books/_search?pretty to check that the Elasticsearch books index has been created.

4. Install missing dependencies

Install react-select and elasticsearch dependencies:

npm install @elastic/elasticsearch react-select

5. Create frontend page

We need a page that displays a search bar with autocomplete (AsyncSelect component) and the selected book displayed in a box.

We will create it without an API for now, with fake data.

Replace pages/index.tsx with:

import React from "react";
import type { NextPage } from "next";
import Head from "next/head";
import AsyncSelect from "react-select/async";

interface Book {
  _id: string;
  title: string;
  authors: string;
  description: string;
}

const testBook: Book = {
  _id: "1",
  title: "The Lord of the Rings",
  authors: "J.R.R. Tolkien",
  description: "A classic book",
};

const Home: NextPage = () => {
  return (
    <div>
      <Head>
        <title>Lookalike search engine</title>
      </Head>
      <div className="container mx-auto p-5">
        <AsyncSelect
          defaultOptions
          isClearable={true}
          placeholder="Start typing a book name..."
          onChange={async () => {}}
          loadOptions={async () => {}}
        />
        <div className="py-7">
          <Book book={testBook} />
        </div>
      </div>
    </div>
  );
};

function Book({ book }: { book: Book }) {
  return (
    <div
      key={book._id}
      className="border rounded-md shadow px-3 py-2"
    >
      <div className="text-lg text-bold py-2">
        {book.title}{" "}
        <span className="text-sm text-gray-500 ml-3">
          {book.authors}
        </span>
      </div>
      <div className="text-sm text-gray-700">
        ℹ️ {book.description}
      </div>
    </div>
  );
}

export default Home;

6. Create API

Create pages/api/autocomplete.ts that will return the result displayed in the search bar (autocomplete aka typeahead or combobox).

This page will be called with a query string:

GET /api/autocomplete?query=rings%20lord

It should return the first 10 books that contains rings and lord:

[
  {"_id": "30", "title": "The Lord of the Rings"},
  {"_id": "765", "title": "The Art of The Lord of the Rings"}
]

Create pages/api/autocomplete.ts:

import { Client } from "@elastic/elasticsearch";
import type { NextApiRequest, NextApiResponse } from "next";

// Return data from elasticsearch
const search = async (
  req: NextApiRequest,
  res: NextApiResponse
) => {
  const { query } = req.query;
  const client = new Client({
    node: "http://localhost:9200",
  });
  const r = await client.search({
    index: "books",
    size: 10,
    body: {
      query: {
        match_bool_prefix: {
          title: { operator: "and", query },
        },
      },
    },
  });
  const {
    body: { hits },
  } = r;
  return res
    .status(200)
    .json(
      hits.hits.map((hit: any) => ({
        _id: hit._id,
        ...hit._source,
      }))
    );
};

export default search;

7. Update frontend page to implement autocomplete

Call the API from pages/index.tsx in order to make the autocomplete work.

import React, { useState } from "react";
import type { NextPage } from "next";
import Head from "next/head";
import AsyncSelect from "react-select/async";

interface Book {
  _id: string;
  title: string;
  authors: string;
  description: string;
}

const Home: NextPage = () => {
  const [currentBook, setCurrentBook] =
    useState<Book | null>(null);

  return (
    <div>
      <Head>
        <title>Lookalike search engine</title>
      </Head>
      <div className="container mx-auto p-5">
        <AsyncSelect
          defaultOptions
          isClearable={true}
          placeholder="Start typing a book name..."
          onChange={async (newValue: any) => {
            setCurrentBook(newValue?.value || null);
          }}
          loadOptions={async (inputValue: string) => {
            if (inputValue.length < 2) return;
            const response = await fetch(
              `/api/autocomplete?query=${inputValue}`
            );
            const data = await response.json();
            return data.map((item: Book) => ({
              value: item,
              label: (
                <>
                  {item.title}
                  <span className="text-gray-400 text-sm ml-3">
                    {item.authors}
                  </span>
                </>
              ),
            }));
          }}
        />
        <div className="py-7">
          {currentBook !== null && (
            <Book book={currentBook} />
          )}
        </div>
      </div>
    </div>
  );
};

function Book({ book }: { book: Book }) {
  return (
    <div
      key={book._id}
      className="border rounded-md shadow px-3 py-2"
    >
      <div className="text-lg text-bold py-2">
        {book.title}{" "}
        <span className="text-sm text-gray-500 ml-3">
          {book.authors}
        </span>
      </div>
      <div className="text-sm text-gray-700">
        ℹ️ {book.description}
      </div>
    </div>
  );
}

export default Home;

8. Update API to implement lookalike

Use the more_like_this specialized query provided by Elasticsearch in order to display similar result as the one we selected in autocomplete.

So, create a new pages/api/lookalike.ts page that 10 most similar results.

This page will be called with a query string:

GET /api/lookalike?id=12345

It should return the first 10 books that are similar to 12345 document:

[
  {"_id": "30", "title": "The Lord of the Rings"},
  {"_id": "765", "title": "The Art of The Lord of the Rings"}
]

Create pages/api/lookalike.ts:

import { Client } from "@elastic/elasticsearch";
import type { NextApiRequest, NextApiResponse } from "next";

const search = async (
  req: NextApiRequest,
  res: NextApiResponse
) => {
  const id: string = req.query.id as string;
  const client = new Client({
    node: "http://localhost:9200",
  });
  const { body: similar } = await client.search({
    index: "books",
    body: {
      size: 12,
      query: {
        more_like_this: {
          fields: [
            "title",
            "subtitle",
            "authors",
            "description",
          ],
          like: [
            {
              _index: "books",
              _id: id,
            },
          ],
          min_term_freq: 1,
          max_query_terms: 24,
        },
      },
    },
  });
  res.status(200).json(
    similar.hits.hits.map((hit: any) => ({
      _id: hit._id,
      ...hit._source,
    }))
  );
};

export default search;

9. Update frontend page to implement lookalike

Call the new API route each time an book is selected in autocomplete. Then, display the similar book right after the "original" one. In order to help the users understand the similarity, we could highlight the result with yellow color.

import React, { useState } from "react";
import type { NextPage } from "next";
import Head from "next/head";
import AsyncSelect from "react-select/async";

interface Book {
  _id: string;
  title: string;
  authors: string;
  description: string;
}

const Home: NextPage = () => {
  const [currentBook, setCurrentBook] = useState<Book | null>(null);
  const [similarBooks, setSimilarBooks] = useState<Book[]>([]);

  return (
    <div>
      <Head>
        <title>Lookalike search engine</title>
      </Head>
      <div className="container mx-auto p-5">
        <AsyncSelect
          defaultOptions
          isClearable={true}
          placeholder="Start typing a book name..."
          onChange={async (newValue: any) => {
            if (!newValue) {
              setSimilarBooks([]);
              setCurrentBook(null);
              return;
            }
            const response = await fetch(
              `/api/lookalike?id=${newValue.value._id}`
            );
            const data = await response.json();
            setSimilarBooks(data);
            setCurrentBook(newValue.value);
          }}
          loadOptions={async (inputValue: string) => {
            if (inputValue.length < 2) return;
            const response = await fetch(
              `/api/autocomplete?query=${inputValue}`
            );
            const data = await response.json();
            return data.map((item: Book) => ({
              value: item,
              label: (
                <>
                  {item.title}
                  <span className="text-gray-400 text-sm ml-3">
                    {item.authors}
                  </span>
                </>
              ),
            }));
          }}
        />
        <div className="py-7">
          {currentBook !== null && <Book book={currentBook} />}
          {similarBooks.length > 0 && (
            <>
              <h1 className="text-2xl mt-5 mb-2">Lookalike books</h1>
              <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-3">
                {similarBooks.map((entry: Book) => (
                  <Book book={entry} key={entry._id} />
                ))}
              </div>
            </>
          )}
        </div>
      </div>
    </div>
  );
};

function Book({ book }: { book: Book }) {
  return (
    <div key={book._id} className="border rounded-md shadow px-3 py-2">
      <div className="text-lg text-bold py-2">
        {book.title}{" "}
        <span className="text-sm text-gray-500 ml-3">{book.authors}</span>
      </div>
      <div className="text-sm text-gray-700">ℹ️ {book.description}</div>
    </div>
  );
}

export default Home;

10. Test

Goto http://localhost:3000/ and test.

Voilà. Feel free to ask questions in the comment section.

A few weeks maintaining React components, using various approaches

Rap2h — Mon, 13 Dec 2021 22:37:30 +0000

Context

This post is a short made-up, based on a real experience. It aims to show different approaches to developing and maintaining React components in an application.

In order to use react-select in a french government project, we have to translate placeholders and other default options. As far as I know, there is no i18n extension for react-select; anyway, it may not have fit our need since there may be some domain-specific text. Here we go, let's use the component's props:

<Select
  loadingMessage={() => "Chargement…"}
  noOptionsMessage={() => "Aucun résultat"}
  placeholder="Choisissez une valeur"
  // …
/>

It works.

👉 Now, we want to re-use this component with same options elsewhere, then everywhere else.

Week 1 - Create a custom select component

We could create a MySelect that has some default options. Since the only thing that changes for each instance are the name and the change handler, we need two props:

export default function MySelect({ name, onChange }) {
  return (
    <Select
      loadingMessage={() => "Chargement…"}
      noOptionsMessage={() => "Aucun résultat"}
      placeholder="Choisissez une valeur"
      name={name}
      onChange={onChange}
    />
  );
}

Thus, we could use it in various files and components:

<MySelect name="user" onChange={(v) => setUser(v)} />


<label>Liste des projets</label>
<MySelect 
  name="project" 
  onChange={(v) => setProject(v)} 
/>


// Another file
<MySelect name="foo" onChange={bar} />

It still works, it is factorized, well done!

In order to add new options we could add new properties.

export default function MySelect({ 
  name, 
  onChange, 
  value, 
}) {

Then…

export default function MySelect({ 
  name, 
  onChange, 
  value, 
  customStyles = [],
}) {

And so on…

export default function MySelect({ 
  name, 
  onChange, 
  value, 
  customStyles = [],
  disabled = false,
  // etc.
}) {

🙅 OK, let's refactor it! It does not scale anymore. ❌

Week 2 - Passing props down to a custom component

Since adding properties seems like a useless layer over the MySelect component, let's just pass props to the component thanks to spread operator:

export default function MySelect(props) {
  return (
    <Select
      loadingMessage={() => "Chargement…"}
      noOptionsMessage={() => "Aucun résultat"}
      placeholder="Choisissez une valeur"
      {...props}
    />
  );
}

We just removed some complexity, that seems easier to maintain.

Week 3 - Specialization

A few days later, we figure out that we are repeating code by writing the same sub-component again and again:

<MySelect
  isSearchable
  isClearable
  isMulti
  name="products"
  // …
/>

<MySelect
  isSearchable
  isClearable
  isMulti
  name="services"
  // …
/>

<MySelect
  isSearchable
  isClearable
  isMulti
  name="things"
  // …
/>

It seems many MySelect needs the same 3 attributes: isSearchable, isClearable, isMulti. We could create a new component that looks like MySelect:

export default function MySearchableSelect(props) {
  return (
    <Select
      loadingMessage={() => "Chargement…"}
      noOptionsMessage={() => "Aucun résultat"}
      placeholder="Choisissez une valeur"
      isSearchable
      isClearable
      isMulti
      {...props}
    />
  );
}

Still, MySearchableSelect has some code in common with MySelect so maybe we could consider MySearchableSelect should render a specific version of MySelect (specialization):

export default function MySearchableSelect(props) {
  return (
    <MySelect
      isSearchable
      isClearable
      isMulti
      {...props}
    />
  );
}

This approach seems compatible with the DRY principle. However, it makes the code harder to debug after a few more days. When we have a problem in a code that uses MySearchableSelect component, we may have to jump to MySearchableSelect, then MySelect, then Select and back again to understand where the problem is and to choose where to fix it. The code is oversimplified in this example: in a real-world example, it could take some time to debug. There is also a risk to create MyCreatableSelect, MySearchableAndCreatableSelect and so on. As a side note, using DRY as a main principle could be considered harmful.

Maybe we could remove abstraction.

Week 4 - Adding conditions

To avoid this complexity, we could get rid of MyCreatableSelect, then add a boolean prop to MySelect that allows handling the case where MySelect is a specialized component:

export default function MySelect({ 
  searchable = false
  ...props
}) {
  let searchableProps = {};
  if (searchable) {
    searchableProps = {
      isSearchable: true,
      isClearable: true,
      isMulti: true,
    };
  }
  return (
    <Select
      loadingMessage={() => "Chargement…"}
      noOptionsMessage={() => "Aucun résultat"}
      placeholder="Choisissez une valeur"
      {...searchableProps}
      {...props}
    />
  );
}

It has less abstraction than the previous approach, still it has more cognitive complexity. For now, it's still easy to read, but it will be harder and harder to maintain due to the potential proliferation of specific cases:

export default function MySelect(/* ... */) {
  let searchableProps = {};
  if (searchable) {
    if (something) {
      // ...
    }
    // ...
  } else if (!name) {
    name = "defaultName"; 
  } 
  // ...
}

Also, changing a condition could have a side-effect on any MySelect component that could be hard to predict without a lot of tests.

What if we actually do not need to create a custom component?

Week 5 - Create shared default properties

Since creating a new component MySelect is for sharing properties, we could create exported properties that we could use in various places:

export const frenchProps = {
  loadingMessage: () => "Chargement…",
  noOptionsMessage: () => "Aucun résultat",
  placeholder: "Choisissez une valeur",
}

export const searchableProps = {
  isSearchable: true,
  isClearable: true,
  isMulti: true,
}

Then we could use it and repeat it here and there.

<Select name="users" {...frenchProps} />

<Select name="teams" {...frenchProps} {...searchableProps} />

<Select name="inEnglish" {...searchableProps} />

There are some advantages in using this approach:

There is no abstraction layer over the base component.
There is no cognitive complexity.
There is no home-made hard-to-maintain component.
We are using the actual react-select library.
Code is more boring, no need to be smart.

Conclusion

I'm sure there are other approaches than the ones I've described in this short post. Nevertheless, this is more or less what I've seen in the React repositories I've browsed or contributed to. I'm also sure there are hidden pitfalls in the "last week" approach and omitted benefits in the first weeks' approach.

I may have missed some approaches, though! I would be glad to hear feedback on other people's chosen approaches.

Fast & reliable JSONB deep structure update in PostgreSQL

Rap2h — Wed, 11 Nov 2020 23:10:55 +0000

Assuming you have a PostgreSQL database with a survey table. A survey has a questions column (JSONB), each question has choices.

Sample content of the questions column for the "web dev" survey:

[
  {
    "id": "c98b53c5",
    "text": "pick a language",
    "choices": [
      {"id": "235d0482", "text": "Rust"},
      {"id": "652ee824", "text": "PHP"},
      {"id": "f543366f", "text": "Forth"}
      // etc.
    ]
  },
  {
    "id": "73294354",
    "text": "pick a frontend framework",
    "choices": [
      {"id": "3feba90d", "text": "React"},
      {"id": "d6aa4dbd", "text": "Vue"},
      {"id": "9beb5da3", "text": "Angular"}
      // etc.
    ]
  },
]

We want to replace the choice with ID 9beb5da3 (Angular) in question 73294354 (pick a frontend framework) for survey 8bf9fedb (the ID of the survey for web dev survey)

- {"id": "9beb5da3", "text": "Angular"}
+ {"id": "1acffbaf", "text": "Svelte"}

To update a subpart of a JSONB column, we could use jsonb_set. Given a path with indices and keys, we can replace the target object with a new object:

update survey
set questions = jsonb_set(
    questions,
    '{1,choices,2}', -- path to angular
    '{"id": "1acffbaf", "text": "Svelte"}'::jsonb,
    false)
where id = '8bf9fedb'; -- the survey id

Still, the problem is that we don't know the actual positions of the items in the arrays. We have to find them first. The only thing we know is the IDs of the object.

🤔 "I know I want to update choice 9beb5da3 in question 73294354; I don't know what is their positions in their respective arrays."

One could have run one first query to get the whole column data, then iterate over objects with their favorite programming language to find item positions, then use these positions to configure the jsonb_set path parameter in order to run the update query.

This approach has two caveats:

It's slow; get all the record data from DB, find positions programmatically, then update the data in the database.
It's not reliable: the positions of data could have changed during this process, between the two queries.

We can do this with only one operation: one update main query that contains two sub-queries to build indices.

Solution

First, we need to get the index of the question 73294354:

select question, questionIndex
from survey
cross join jsonb_array_elements(questions) 
    with ordinality arr(question, questionIndex)
where id='8bf9fedb' and question->>'id'='73294354'

jsonb_array_elements function expands the array to a set of JSON values, with ordinality adds a number (starting from 1, increasing by 1) for each row of the function output. This query returns:

question	questionindex
{"id": "73294354", "text": "pick a frontend framework", "choices": [{"id": "3feba90d", "text": "React"}, {"id": "d6aa4dbd", "text": "Vue"}, {"id": "9beb5da3", "text": "Angular"}]}	2

Then, we need to get the index of the choice 9beb5da3 (which is in the question 73294354: we have to be sure we search only in this question, in case some choices share the same index in different questions).

To do so, we have to use the first query as a subquery, then get the index:

-- This is the previous query, see above.
-- Using "WITH", also known as Common Table Expressions (CTE),
-- helps in reading complex queries (avoid nested subquery).
with questions_table as (
    select question, questionIndex
    from survey
    cross join jsonb_array_elements(questions)
        with ordinality arr(question, questionIndex)
    where id = '8bf9fedb' and question->>'id'='73294354'
)
-- We can now use questions_table to find choice index.
select choice, choiceIndex
from questions_table
cross join jsonb_array_elements((question->'choices')::jsonb)
    with ordinality arr(choice, choiceIndex)
where choice->>'id'='9beb5da3'

We just used again jsonb_array_elements and with ordinality, nothing new here. As a result, we have got the choice we want to change and it's position.

choice	choiceindex
{"id": "9beb5da3", "text": "Angular"}	3

Now, we are ready to update the record, since we have the index to build the path. Building the path is simply creating a string thanks to the index we have from previous steps:

'{' || questionIndex-1 || ',choices,' || choiceIndex-1 || '}'

☝️ Since ordinality starts to 1, we have subtracted 1 to the indices.

We can modify the second query to directly return the path we want:

with questions_table as (
    select question, questionIndex
    from survey
    cross join jsonb_array_elements(questions)
        with ordinality arr(question, questionIndex)
    where id = '8bf9fedb' and question->>'id'='73294354'
)
select
  ('{'||questionIndex-1||',choices,'||choiceIndex-1||'}')
  as path
from questions_table
cross join jsonb_array_elements((question->'choices')::jsonb)
    with ordinality arr(choice, choiceIndex)
where choice->>'id'='9beb5da3'

path
{1,choices,2}

For the last step, we need to create the update query that uses the two previous queries to actually update the choice:

with questions_table as (
    select question, questionIndex
    from survey
    cross join jsonb_array_elements(questions)
        with ordinality arr(question, questionIndex)
    where id = '8bf9fedb' and question->>'id'='73294354'
), choices_table as (
    select ('{'||questionIndex-1||',choices,'||choiceIndex-1||'}')::text[] as path
    from questions_table
    cross join jsonb_array_elements((question->'choices')::jsonb)
        with ordinality arr(choice, choiceIndex)
    where choice->>'id'='9beb5da3'
)
update survey
set questions = jsonb_set(
    questions,
    path, -- the path we built just before.
    '{"id": "1acffbaf", "text": "Svelte"}'::jsonb,
    false)
from choices_table
where id = '8bf9fedb';

1 row affected in 7 ms

It's fast, since we are only reading and writing one row in survey. It's reliable because we are doing only one query.

Some links:

Feel free to post a comment if you need more information.

Export 10M+ rows in XLSX with Laravel without memory issues

Rap2h — Fri, 30 Aug 2019 10:14:30 +0000

TL;DR: This post introduce FastExcel for Laravel, gives a quick overview over PHP generators and finally shows how to use both to generate Excel files from collections while preserving memory.

About FastExcel

Laravel FastExcel is intended at being Laravel-flavoured Spout, with the goal of simplifying imports and exports. It could be considered as a faster (and memory friendly) alternative to Laravel Excel with a different approach and less features. Getting started in 2 steps.

Install with composer:

composer require rap2hpoutre/fast-excel

Then export a Model or a Collection to XLSX, CSV or ODS:

fastexcel($collection)->export('file.xlsx');

More information on the README of the project homepage.

Generators

Generators were introduced in PHP 5, many years ago. A generator function looks just like a normal function, except that instead of returning a value, a generator yields values. Then you can iterate over the generator function.

On of the goal of such a function is to lazy iterate over data without building an array. So it preserves memory when manipulating large datasets. Quoting PHP documentation:

A generator allows you to write code that uses foreach to iterate over a set of data without needing to build an array in memory, which may cause you to exceed a memory limit, or require a considerable amount of processing time to generate.

Assuming you have a User model with 10M+ entries in database and you want to iterate over it in your code, instead of just calling User::all(), you could use a generator:

function usersGenerator() {
    foreach (User::cursor() as $user) {
        yield $user;
    }
}

$users = usersGenerator();
foreach($users as $user) {
    // Do something with each user without hitting memory limit
}

The example above runs queries to only take users one by one. It only uses the memory needed to load 1 user N times.

Export large dataset using FastExcel and Generators

Since v1.3.0, FastExcel accepts generator function as a parameter. Using the previous example, you could just pass the generator to fastexcel function:

function usersGenerator() {
    foreach (User::cursor() as $user) {
        yield $user;
    }
}

// Export consumes only a few MB, even with 10M+ rows.
$users = usersGenerator();
fastexcel($users)->export('test.xlsx');

FastExcel internally creates rows one by one when using generator, so it will not consume extra memory. It will be a long process, so make sure it does not hit max_execution_time (you could use Queue, any asynchronous technique, increase max execution time, or even run it from CLI). Still, your server will be grateful to not consume all its memory for one export.

So thanks to generators, you can now export thousands of models to XLSX, CSV and ODS files, with a few lines of code in a Laravel project.

Learn more about he library in the repository: https://github.com/rap2hpoutre/fast-excel

Introducing taskz

Rap2h — Wed, 24 Jul 2019 12:48:52 +0000

Taskz is a library for Node.js, a simple sequential and parallel task list runner for terminal.

Getting started

Install it via npm i taskz. Create your task sequence in any script file then run it.

const taskz = require("taskz");

taskz([
  {
    text: "first task - sleeps for 200ms",
    task: async () => await new Promise(resolve => setTimeout(resolve, 200));
  },
  {
    text: "this task will fail",
    task: async () => {
      throw new Error("this task failed");
    }
  }
]).run();

So in other word, you have to create a array of tasks:

const myTasks = [
  { text: "task 1", task: () => { /* ... */ } },
  { text: "task 2", task: () => { /* ... */ } }
];

Then pass it to the taskz function and call run to start the process:

taskz(myTasks).run();

You can also run task in parallel:

taskz(myTasks, { parallel: true }).run();

Other features: subtasks, stop on fail, pass context from task to task,
change text within a task during execution.

Have fun with it: link to github repository