Edwards Posts - Answer Overflow

Edwards

•Created by Edwards on 12/4/2024 in #help

Is this a proper way to type a table and column?

I want to make certain crud action reusable in my codebase. Is this the proper way to type a SQLite table and column? Load activeItem function

import { type InferSelectModel } from 'drizzle-orm';
import type { SQLiteColumn, SQLiteTable} from 'drizzle-orm/sqlite-core';

export async function loadActiveItems<T extends SQLiteTable>(
    table: T,
    orderBy: keyof InferSelectModel<T> = 'name'
) {
    const activeColumn = table._.columns['active'];
    const orderByColumn = table._.columns[orderBy];

    return db.select().from(table).where(eq(activeColumn, true)).orderBy(orderByColumn);
}

import { type InferSelectModel } from 'drizzle-orm';
import type { SQLiteColumn, SQLiteTable} from 'drizzle-orm/sqlite-core';

export async function loadActiveItems<T extends SQLiteTable>(
    table: T,
    orderBy: keyof InferSelectModel<T> = 'name'
) {
    const activeColumn = table._.columns['active'];
    const orderByColumn = table._.columns[orderBy];

    return db.select().from(table).where(eq(activeColumn, true)).orderBy(orderByColumn);
}

2 replies

DTDrizzle Team

•Created by Edwards on 9/13/2023 in #help

Help with improving database query

Hello, I am seeking advice on how to improve a database query I am currently working with. My objective is to get the data of a prompt using its ID, and this data includes the names of the tags associated with it.The currently way I am doing this works, but feels hacking so wondering if theres a more efficient way to achieve this, possibly through restructuring the database or altering the query itself. Below are the definitions for my "prompts" and "tags" tables from supabase DB:

CREATE TABLE public.prompts (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    profile_id UUID NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    is_favorited BOOLEAN NOT NULL DEFAULT FALSE,
    tag_ids UUID[] NULL,
    visibility public.prompt_visibility NOT NULL DEFAULT 'private'::prompt_visibility,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT prompts_pkey PRIMARY KEY (id),
    CONSTRAINT prompts_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
) TABLESPACE pg_default;

CREATE TABLE public.prompts (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    profile_id UUID NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    is_favorited BOOLEAN NOT NULL DEFAULT FALSE,
    tag_ids UUID[] NULL,
    visibility public.prompt_visibility NOT NULL DEFAULT 'private'::prompt_visibility,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT prompts_pkey PRIMARY KEY (id),
    CONSTRAINT prompts_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
) TABLESPACE pg_default;

create table
  public.tags (
    id uuid not null default gen_random_uuid (),
    profile_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint tags_pkey primary key (id),
    constraint unique_user_tag_name unique (profile_id, name),
    constraint tags_profile_id_fkey foreign key (profile_id) references profiles (id) on delete cascade
  ) tablespace pg_default;

create table
  public.tags (
    id uuid not null default gen_random_uuid (),
    profile_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint tags_pkey primary key (id),
    constraint unique_user_tag_name unique (profile_id, name),
    constraint tags_profile_id_fkey foreign key (profile_id) references profiles (id) on delete cascade
  ) tablespace pg_default;

50 replies

Gaming

Programming