Theo's Typesafe Cult•2y ago

Best practice how to store settings in MySQL?

Hi. I would like to store some settings / preferences in the database, but don't know what's the common structure, to do so. Would it be something like so?

model DashboardSettings {
    name  String @unique
    value String
}

model DashboardSettings {
    name  String @unique
    value String
}

name                |   value
-----------------------------
DISCORD_GUILD_ID    |   63meEMh385emhm

name                |   value
-----------------------------
DISCORD_GUILD_ID    |   63meEMh385emhm

33 Replies

venus•2y ago

But don't know, how to make ZOD validator for each value variable, so I guess I have to create mutation with its own validator for each field

enum SettingFields {
  DISCORD_GUILD_ID = "DISCORD_GUILD_ID",
}

const prismaUpdateSettingField = (
  prisma: PrismaClient,
  { field, value }: { field: SettingFields; value: string }
) => {
  return prisma.dashboardSettings.upsert({
    where: { name: field },
    create: { name: field, value: value },
    update: { name: field, value: value },
  });
};

export const settingsRouter = t.router({
  getDashboardSettings: authedProcedure.query(({ ctx, input }) => {
    return ctx.prisma.dashboardSettings
      .findMany()
      .then((res) => res.reduce((a, v) => ({ ...a, [v.name]: v.value }), {} as SettingsObject));
  }),
  upsertDiscordGuildID: authedProcedure
    .input(z.object({ value: z.string().regex(/^\d+$/) }))
    .mutation(({ ctx, input }) => {
      return prismaUpdateSettingField(ctx.prisma, {
        field: SettingFields.DISCORD_GUILD_ID,
        value: input.value,
      });
    }),
});

enum SettingFields {
  DISCORD_GUILD_ID = "DISCORD_GUILD_ID",
}

const prismaUpdateSettingField = (
  prisma: PrismaClient,
  { field, value }: { field: SettingFields; value: string }
) => {
  return prisma.dashboardSettings.upsert({
    where: { name: field },
    create: { name: field, value: value },
    update: { name: field, value: value },
  });
};

export const settingsRouter = t.router({
  getDashboardSettings: authedProcedure.query(({ ctx, input }) => {
    return ctx.prisma.dashboardSettings
      .findMany()
      .then((res) => res.reduce((a, v) => ({ ...a, [v.name]: v.value }), {} as SettingsObject));
  }),
  upsertDiscordGuildID: authedProcedure
    .input(z.object({ value: z.string().regex(/^\d+$/) }))
    .mutation(({ ctx, input }) => {
      return prismaUpdateSettingField(ctx.prisma, {
        field: SettingFields.DISCORD_GUILD_ID,
        value: input.value,
      });
    }),
});

Scot•2y ago

Bruh that seems complex Does it need to be in db? Some settings work better as an env variable

Unknown User•2y ago

Message Not Public

venus•2y ago

I know it is, but some variables have to be customizable by user when the site's running, not at the build time (like ENV). Good point. How would you structure the model? Should I just have one row with all settings inside that one Json object?

Unknown User•2y ago

Message Not Public

Leonidas•2y ago

I would start out with one json column in the database. Ensure type safety with zod. As soon as you feel the need to write that parsing logic again extract that into a helper function make sure to reuse the helper across your app in case you migrate the schema, you can add falbbacks / defaults here

venus•2y ago

Can I ensure type-safety for that JSON object with Prima? I did some googling, but couldnt find any good resource https://github.com/prisma/prisma/issues/3219

Leonidas•2y ago

no you cannot, rely on zod for runtime safety - make sure to add fallbacks

venus•2y ago

This is kinda out of my scope of knowledge. What are zod fallbacks?

Leonidas•2y ago

zod is a library to parse input and retrieve type-safety thereby: https://github.com/colinhacks/zod

GitHub

GitHub - colinhacks/zod: TypeScript-first schema validation with st...

TypeScript-first schema validation with static type inference - GitHub - colinhacks/zod: TypeScript-first schema validation with static type inference

venus•2y ago

Yeah ik what zod does, but I got confused by the word "fallback"

Leonidas•2y ago

really useful for developing api's - by parsing the incomming data you avoid all kinds of security breaches

venus•2y ago

So should I do for each setting field its own route with zod validator for key and its value?

Leonidas•2y ago

lets say your current settings json looks like this: { wantsNotifications: boolean } now you want to add a setting, your schema would look like this: { wantsNotifications: boolean useColorBlindMode: boolean } In your helper function you would add a fallback for the "useColorBlindMode" property -> it should default to false when it is undefined use z.boolean().default(false) for this

venus•2y ago

Yes and would you recommend me to control the properties that are parsed in the object?

Gaming

Programming

Best practice how to store settings in MySQL?