Implementing Folder System in PostgreSQL

Turns out there are multiple way to implement folder system, and after doing some research I found that this approach Adjacency List Model easier to understand for me.

This was my finding when trying to implement folder system in PostgreSQL for readclip.site.

Table Folders

This query to create table "folders" table with columns for folder information, including a primary key for "folder_id" and a foreign key constraint to reference the parent folder.

CREATE TABLE folders (
    folder_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_folder_id INT,
    created_at TIMESTAMP DEFAULT current_timestamp,
    updated_at TIMESTAMP DEFAULT current_timestamp,
    CONSTRAINT fk_parent_folder
        FOREIGN KEY (parent_folder_id)
        REFERENCES folders (folder_id)
);

Table files

CREATE TABLE files (
    file_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    folder_id INT,
    content BYTEA,
    file_type VARCHAR(50),
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT current_timestamp,
    updated_at TIMESTAMP DEFAULT current_timestamp,
    CONSTRAINT fk_folder
        FOREIGN KEY (folder_id)
        REFERENCES folders (folder_id)
);

This query creates a "files" table to store information about files, including a primary key for "file_id" and a foreign key constraint to reference the parent folder.

Adding Index

CREATE INDEX idx_folder_parent_folder_id ON folders (parent_folder_id);
CREATE INDEX idx_file_folder_id ON files (folder_id);

These queries create indexes on the "parent_folder_id" column in the "folders" table and the "folder_id" column in the "files" table, which can help optimize query performance when filtering by these columns.

Sample data

INSERT INTO folders (name, parent_folder_id)
VALUES ('Documents', NULL);

This inserts a new folder named "Documents" with no parent folder (hence, NULL).

More Data Insertion

-- Inserting folders and files
INSERT INTO folders (name, parent_folder_id)
VALUES ('New Folder Name', 1);

INSERT INTO folders (name, parent_folder_id)
VALUES ('Download', 1);

INSERT INTO folders (name, parent_folder_id)
VALUES ('Zip', 3);

INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile1.txt', 1, E'file_content_here', 'txt', 12345);

INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile.txt', 2, E'file_content_here', 'txt', 12345);

INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile 4_1.txt', 4, E'file_content_here', 'txt', 12345);

INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile 4_1.txt', 4, E'file_content_here', 'txt', 12345);

These INSERT statements add sample data to the "folders" and "files" tables. Folders are created with specified names and parent folder IDs. Files are created with names, folder associations, content, file types, and sizes.

SELECT from Files:

SELECT * FROM files
WHERE folder_id = 4;

This query retrieves all files in the folder with a "folder_id" of 4. It returns all columns for the matching files.

Recursive Query:

WITH RECURSIVE folder_path AS (
  SELECT folder_id, name, parent_folder_id
  FROM folders
  WHERE folder_id = 4
  UNION ALL
  SELECT f.folder_id, f.name, f.parent_folder_id
  FROM folders f
  JOIN folder_path p ON f.folder_id = p.parent_folder_id
)
SELECT folder_id, name
FROM folder_path;

This is a recursive common table expression (CTE) that retrieves the path of folders leading to the folder with "folder_id" 4. It starts with the folder itself and recursively goes up through its parent folders, building a hierarchical path. The final SELECT statement then retrieves the "folder_id" and "name" of each folder in the path.