NodeJS, KnexJS and Postgres to build a File Tree System

So I'm trying to build a simple assets manager from scratch using my custom Express framework.

Here is the test I've started to achieve the file tree,

Many thanks to : https://stackoverflow.com/questions/19834400/what-is-the-simplest-way-to-save-a-file-tree-in-a-postgres-database

The goal is to upload files from a frontend (Vue 3 web app)

Then using my custom file upload module, I'll be able to split the path to fill the "nodes" table.
Then create the file tree "automatically". Meaning that this proposed structure has an advantage of flattening the whole structure, so yes it duplicates the data, but... I can fetch the whole tree quickly and easily.

This way my frontend will be able to call an API to fetch only the required files and directories. (That data is saved in the "parents_children" table)
From there a second API call will be able to fetch the file that will be saved in the "nodes" table

This code is only a POC to validate that it works with knexjs and the way I plan to upload files using Express and Vue 3.

// Based on this post: https://stackoverflow.com/questions/19834400/what-is-the-simplest-way-to-save-a-file-tree-in-a-postgres-database
// How to build a file tree with
// knexjs, postgres 14 & nodejs

const sqlToPrepareTheBasics = `
drop table if exists parents_children;
drop table if exists nodes;

create table nodes (
    path varchar primary key,
    isRoot boolean,
    isDir boolean default true
);

create table parents_children (
    parent_path varchar,
    child_path varchar,
    primary key(parent_path,child_path),
    foreign key (parent_path) references nodes (path),
    foreign key (child_path) references nodes (path)
);

insert into nodes (path, isRoot) values ('/', true);
insert into parents_children values ('/','/');

insert into nodes (path, isRoot) values ('/assets/', true);
insert into parents_children
select parent_path, '/assets/' from parents_children where child_path = '/'
union all select '/assets/','/assets/';

insert into nodes (path) values ('/assets/POLYGON - Apocalypse Pack/');
insert into parents_children
select parent_path, '/assets/POLYGON - Apocalypse Pack/' from parents_children where child_path = '/assets/'
union all select '/assets/POLYGON - Apocalypse Pack/','/assets/POLYGON - Apocalypse Pack/';

insert into nodes (path) values ('/assets/POLYGON - Apocalypse Pack/FBX/');
insert into parents_children
select parent_path, '/assets/POLYGON - Apocalypse Pack/FBX/' from parents_children where child_path = '/assets/POLYGON - Apocalypse Pack/'
union all select '/assets/POLYGON - Apocalypse Pack/FBX/','/assets/POLYGON - Apocalypse Pack/FBX/';

insert into nodes (path) values ('/assets/POLYGON - Apocalypse Pack/Textures/');
insert into parents_children
select parent_path, '/assets/POLYGON - Apocalypse Pack/Textures/' from parents_children where child_path = '/assets/POLYGON - Apocalypse Pack/'
union all select '/assets/POLYGON - Apocalypse Pack/Textures/','/assets/POLYGON - Apocalypse Pack/Textures/';

insert into nodes (path, isDir) values ('/assets/POLYGON - Apocalypse Pack/POLYGON Apocalypse Unity Package 2020 3 v1 12.unitypackage', false);
insert into parents_children
select parent_path, '/assets/POLYGON - Apocalypse Pack/POLYGON Apocalypse Unity Package 2020 3 v1 12.unitypackage' from parents_children where child_path = '/assets/POLYGON - Apocalypse Pack/'
union all select '/assets/POLYGON - Apocalypse Pack/POLYGON Apocalypse Unity Package 2020 3 v1 12.unitypackage','/assets/POLYGON - Apocalypse Pack/POLYGON Apocalypse Unity Package 2020 3 v1 12.unitypackage';

insert into nodes (path) values ('/assets/POLYGON - City Characters Pack/');
insert into parents_children
select parent_path, '/assets/POLYGON - City Characters Pack/' from parents_children where child_path = '/assets/'
union all select '/assets/POLYGON - City Characters Pack/','/assets/POLYGON - City Characters Pack/';

insert into nodes (path) values ('/assets/POLYGON - City Pack/');
insert into parents_children
select parent_path, '/assets/POLYGON - City Pack/' from parents_children where child_path = '/assets/'
union all select '/assets/POLYGON - City Pack/','/assets/POLYGON - City Pack/';

insert into nodes (path) values ('/assets/POLYGON - City Zombies Pack/');
insert into parents_children
select parent_path, '/assets/POLYGON - City Zombies Pack/' from parents_children where child_path = '/assets/'
union all select '/assets/POLYGON - City Zombies Pack/','/assets/POLYGON - City Zombies Pack/';
`;

// I use my custom NodeJS/Express framework, it wraps knexjs.. 
// I did nothing special. So you can use knexjs directly as well.
const Webux = require("../backend/dam/backend/app");

(async () => {
  try {
    await Webux.Initialize(); // 2021-03-13 dirty fix ..

    let paths = [
      "/assets/POLYGON - City Zombies Pack/Textures/",
      "/assets/POLYGON - City Zombies Pack/FBX/",
      "/assets/POLYGON - City Zombies Pack/PNG/",
      "/assets/POLYGON - City Zombies Pack/JPG/",
      "/assets/POLYGON - City Zombies Pack/Sources/",
      "/assets/POLYGON - City Zombies Pack/Sources/ZIP/",
      "/assets/POLYGON - City Zombies Pack/Sources/ZIP/index.zip",
      "/stuffs/",
      "/",
    ];

    for await (let currentPath of paths) {
      // The child path is the current path minus the added suffix
      // so length -1
      const childPath = currentPath.split("/").slice(0, -1).join("/") + "/";

      console.log("Child Path", childPath);

      //   Create the node, it create the asset reference
      //   It will be later on "flatten"
      //   This row contains more information for an assets
      //   In order to build the tree see below...
      await Webux.db
        .sql("nodes")
        .insert({
          path: currentPath,
          isdir: true,
          isroot: false,
        })
        .onConflict("path")
        .ignore();

      // Equivalent to
      // select parent_path, '/assets/POLYGON - City Zombies Pack/' from parents_children where child_path = '/assets/'
      // union all select '/assets/POLYGON - City Zombies Pack/','/assets/POLYGON - City Zombies Pack/';
      let result = await Webux.db
        .sql("parents_children")
        .select(Webux.db.sql.raw("parent_path, ? as child_path", currentPath))
        .from("parents_children")
        .where({ child_path: childPath })
        .unionAll(
          Webux.db.sql.raw("select ?,?", [currentPath, currentPath]),
          true
        );
      // .toSQL()
      // .toNative();

      console.log(result);

      // let add new child
      //   We are building the tree file view
      //   For each parents, we add the new child, this way the file structure is flatten
      //   We can easily and quickly get a file, or all files below a parent and etc.
      let added = await Webux.db
        .sql("parents_children")
        .insert(result)
        .returning("parent_path")
        .onConflict(["parent_path", "child_path"])
        .ignore();

      console.log(added);

      //   Fetching the data
      const allEntries = await Webux.db
        .sql("parents_children")
        .select("*")
        .where({ parent_path: childPath });

      console.log(allEntries);

      const parents = await Webux.db
        .sql("parents_children")
        .select("*")
        .where({ child_path: childPath });

      console.log(parents);
    }
    process.exit();
  } catch (e) {
    console.error("ERR", e.message);
    process.exit(123);
  }
})();