Swap Posts - Answer Overflow

Swap

•Created by Swap on 8/1/2023 in #help

Plugin for auto-update "updated_at" field

Hello Team, Greetings! I am trying to write a plugin that will auto-update the "updated_at" column when there will be an update operation. So far, I have implemented the following code:

export class SetUpdatedAtTransformer extends OperationNodeTransformer {

    protected transformUpdateQuery(node: UpdateQueryNode): UpdateQueryNode {
        return this.#transformQuery(super.transformUpdateQuery(node));
    }

    #transformQuery<T extends UpdateQueryNode>(node: T): T {
        if (!node.updates || node.updates.length === 0) {
            return node;
        }

        return freeze({
            ...node,
            updates: this.#setUpdatedAt(node.updates),
        });
    }

    #setUpdatedAt(updates: ReadonlyArray<ColumnUpdateNode>): ReadonlyArray<ColumnUpdateNode> {
        const out: ColumnUpdateNode[] = [];
        out.push(...updates);

        return freeze(out);
    }
}

export class SetUpdatedAtTransformer extends OperationNodeTransformer {

    protected transformUpdateQuery(node: UpdateQueryNode): UpdateQueryNode {
        return this.#transformQuery(super.transformUpdateQuery(node));
    }

    #transformQuery<T extends UpdateQueryNode>(node: T): T {
        if (!node.updates || node.updates.length === 0) {
            return node;
        }

        return freeze({
            ...node,
            updates: this.#setUpdatedAt(node.updates),
        });
    }

    #setUpdatedAt(updates: ReadonlyArray<ColumnUpdateNode>): ReadonlyArray<ColumnUpdateNode> {
        const out: ColumnUpdateNode[] = [];
        out.push(...updates);

        return freeze(out);
    }
}

export class UpdatedAtPlugin implements KyselyPlugin {
    readonly #transformer = new SetUpdatedAtTransformer();

    transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
        return this.#transformer.transformNode(args.node);
    }

transformResult(
        args: PluginTransformResultArgs
    ): Promise<QueryResult<UnknownRow>> {
        return Promise.resolve(args.result);
    }
}

export class UpdatedAtPlugin implements KyselyPlugin {
    readonly #transformer = new SetUpdatedAtTransformer();

    transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
        return this.#transformer.transformNode(args.node);
    }

transformResult(
        args: PluginTransformResultArgs
    ): Promise<QueryResult<UnknownRow>> {
        return Promise.resolve(args.result);
    }
}

But not sure how to implement further for auto-updating the column, any help or suggestion will really helpful. Thanks 🙂 Note: I am using Postgres.

11 replies

KKysely

•Created by Swap on 7/11/2023 in #help

Postgres Full Text Search

Hello, I am trying to search queries using Postgres full-text search, and here is my current implementation:

await this.db.selectFrom("city")
            .where(sql`to_tsvector('english', name) @@ to_tsquery('english', 'nas:*')`)
            .selectAll()
            .execute();

await this.db.selectFrom("city")
            .where(sql`to_tsvector('english', name) @@ to_tsquery('english', 'nas:*')`)
            .selectAll()
            .execute();

the above implementation is working but my IntelliJ lint seems unhappy. I was wondering if is there any better way to write the same in a more typesafe way? Thank you!

19 replies

KKysely

•Created by Swap on 6/5/2023 in #help

Unable to insert geometry Postgres

Hey everyone 👋🏻 Greetings! I am trying to insert point data into my Postgresql table but getting error: parse error - invalid geometry error. Here is the code

await this.db.insertInto("location")
            .values({
                name: "Anom",
                coordinates: point({ x: -71.060316, y: 48.432044 })
            })
            .returningAll()
            .execute();

await this.db.insertInto("location")
            .values({
                name: "Anom",
                coordinates: point({ x: -71.060316, y: 48.432044 })
            })
            .returningAll()
            .execute();

and here is the schema:

export interface LocationTable {
    id: Generated<string>;
    name: string;
    coordinates: Point;
}

export interface Point {
    x: number;
    y: number;
}

export function point(pt: Point): RawBuilder<Point> {
    const point = `(${pt.x},${pt.y})`;
    return sql<Point>`${point}`;
}

export interface LocationTable {
    id: Generated<string>;
    name: string;
    coordinates: Point;
}

export interface Point {
    x: number;
    y: number;
}

export function point(pt: Point): RawBuilder<Point> {
    const point = `(${pt.x},${pt.y})`;
    return sql<Point>`${point}`;
}

and here is my database schema:

CREATE TABLE location
(
    "id"          UUID NOT NULL DEFAULT gen_random_uuid(),
    "name"        VARCHAR,
    "coordinates" geometry(Point, 4326)
);

CREATE TABLE location
(
    "id"          UUID NOT NULL DEFAULT gen_random_uuid(),
    "name"        VARCHAR,
    "coordinates" geometry(Point, 4326)
);

i tried solution provided by @koskimas in the https://github.com/kysely-org/kysely/issues/133#issuecomment-1209233409 but no luck. attaching the log generated by kysely


Query: insert into "location" ("name", "coordinates") values ($1, $2) returning * [91.9 ms]
$1: Anom
$2: (-71.060316,48.432044)

Unable to figure out whats wrong. Thanks.

8 replies

Gaming

Programming