SQL storage

[NotADuplicate]

I'm a huge fan of this package and I'm very glad you made something so comprehensive and polished! I am working on a project that uses an SQLite db and I was wondering if there was any examples on how to implement this with a proper relational db so I don't need to store all the tournament information as JSON text. I have specifically been looking at this: https://github.com/Drarig29/brackets-storage/tree/master/brackets-sql-db and I'm wondering if you have any more information on how to implement this. I have a medium level of experience with SQL but have not used any ORMs before. Any help would be greatly greatly appreciated and thank you for this project!

[Drarig29]

Hey @NotADuplicate! Glad you like the project!

I think this would be a great use of AI: this file in brackets-model defines all the types for Participant, Stage, Group, Round, Match and MatchGame.

You should be able to ask an AI to generate typeorm entities. I did it for you (with Copilot):

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class Participant {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    tournament_id: number;

    @Column()
    name: string;
}

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
import { StageSettings, StageType } from 'brackets-model';

@Entity()
export class Stage {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    tournament_id: number;

    @Column()
    name: string;

    @Column()
    type: StageType;

    @Column('json')
    settings: StageSettings;

    @Column()
    number: number;
}

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class Group {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    stage_id: number;

    @Column()
    number: number;
}

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class Round {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    stage_id: number;

    @Column()
    group_id: number;

    @Column()
    number: number;
}

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
import { MatchResults, ParticipantResult, Status } from 'brackets-model';

@Entity()
export class Match implements MatchResults {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    stage_id: number;

    @Column()
    group_id: number;

    @Column()
    round_id: number;

    @Column()
    number: number;

    @Column()
    child_count: number;

    @Column()
    status: Status;

    @Column('json', { nullable: true })
    opponent1: ParticipantResult | null;

    @Column('json', { nullable: true })
    opponent2: ParticipantResult | null;
}

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
import { MatchResults, ParticipantResult, Status } from 'brackets-model';

@Entity()
export class MatchGame implements MatchResults {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    stage_id: number;

    @Column()
    parent_id: number;

    @Column()
    number: number;

    @Column()
    status: Status;

    @Column('json', { nullable: true })
    opponent1: ParticipantResult | null;

    @Column('json', { nullable: true })
    opponent2: ParticipantResult | null;
}

Then, it's only a matter of using the right TypeORM syntax to turn the example index.js SQL storage code into code that should work.

Here is what I got with Copilot: (I haven't tested it)

import { getRepository } from 'typeorm';
import { CrudInterface, Table, DataTypes, OmitId } from 'brackets-manager';
import { Team, Stage, Group, Round, Match, MatchGame } from 'brackets-model';

export class SqlDatabase implements CrudInterface {

    private getRepository(table: Table) {
        switch (table) {
            case 'participant':
                return getRepository(Team);
            case 'stage':
                return getRepository(Stage);
            case 'group':
                return getRepository(Group);
            case 'round':
                return getRepository(Round);
            case 'match':
                return getRepository(Match);
            case 'match_game':
                return getRepository(MatchGame);
            default:
                throw new Error(`Unknown table: ${table}`);
        }
    }

    async insert<T extends Table>(table: T, value: OmitId<DataTypes[T]>): Promise<number> {
        const repository = this.getRepository(table);
        const entity = repository.create(value);
        const result = await repository.save(entity);
        return result.id;
    }

    async insert<T extends Table>(table: T, values: OmitId<DataTypes[T]>[]): Promise<boolean> {
        const repository = this.getRepository(table);
        const entities = repository.create(values);
        await repository.save(entities);
        return true;
    }

    async select<T extends Table>(table: T): Promise<Array<DataTypes[T]> | null>;
    async select<T extends Table>(table: T, id: number): Promise<DataTypes[T] | null>;
    async select<T extends Table>(table: T, filter: Partial<DataTypes[T]>): Promise<Array<DataTypes[T]> | null>;
    async select<T extends Table>(table: T, arg?: number | Partial<DataTypes[T]>): Promise<DataTypes[T] | Array<DataTypes[T]> | null> {
        const repository = this.getRepository(table);

        if (arg === undefined) {
            return await repository.find();
        }

        if (typeof arg === 'number') {
            return await repository.findOne(arg);
        }

        return await repository.find({ where: arg });
    }

    async update<T extends Table>(table: T, id: number, value: DataTypes[T]): Promise<boolean>;
    async update<T extends Table>(table: T, filter: Partial<DataTypes[T]>, value: Partial<DataTypes[T]>): Promise<boolean>;
    async update<T extends Table>(table: T, arg: number | Partial<DataTypes[T]>, value: DataTypes[T] | Partial<DataTypes[T]>): Promise<boolean> {
        const repository = this.getRepository(table);

        if (typeof arg === 'number') {
            await repository.update(arg, value);
        } else {
            await repository.update(arg, value);
        }

        return true;
    }

    async delete<T extends Table>(table: T): Promise<boolean>;
    async delete<T extends Table>(table: T, filter: Partial<DataTypes[T]>): Promise<boolean>;
    async delete<T extends Table>(table: T, filter?: Partial<DataTypes[T]>): Promise<boolean> {
        const repository = this.getRepository(table);

        if (filter === undefined) {
            await repository.clear();
        } else {
            await repository.delete(filter);
        }

        return true;
    }
}

[Drarig29]

When you have something working, it would be nice if you could send your code so I can update https://github.com/Drarig29/brackets-storage/blob/master/brackets-sql-db 😁

[NotADuplicate]

Thank you for this, I will give it some testing and let you know if I find something that works!