Theo's Typesafe Cult•3y 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

venusOP•3y 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•3y ago

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

Unknown User•3y ago

Message Not Public

venusOP•3y 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•3y ago

Message Not Public

Leonidas•3y 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

venusOP•3y 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•3y ago

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

venusOP•3y ago

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

Leonidas•3y 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

venusOP•3y ago

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

Leonidas•3y ago

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

venusOP•3y ago

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

Leonidas•3y 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

venusOP•3y ago

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

Leonidas•3y ago

can you exlain what you mean more precisely?

venusOP•3y ago

One sec, I will create a demo. As I've check the docs about JSON objects in Prisma, the manipulation sucks. Adding new properties to existing object is nearly impossible, or am I wrong?

Leonidas•3y ago

very limited atleast you can push to json arrays that can useful but for regular data where super performant code is unneccesary i would rely on first querying the database for the curent JSON, modify it and store it again the database.

venusOP•3y ago

But there's no other way than just getting the object first, editing it and then saving the edited object, right?

Leonidas•3y ago

Reqires one more db call than absolutely neccessary, but is way more declarative For arrays there is when the performance implication does not allow for this use seperate columns for every setting , that needs to be changed often

venusOP•3y ago

This is not the case really. Data won't be updated often. I was just curious 😄

Leonidas•3y ago

perfectly, that sounds like the use case the json column was intended for - in modern relational databases 👍🏼

venusOP•3y ago

According to your tips, this is what I created. I am not sure if I understood fully to your thought, but feedback would be appreciated

// Available setting fields
enum DashboardSettingField {
  DISCORD_GUILD_ID = "DISCORD_GUILD_ID",
  DISCORD_WEBHOOK_URL = "DISCORD_WEBHOOK_URL",
}

// Helper function to update data
const prismaUpdateSettingField = async (
  prisma: PrismaClient,
  { field, value }: { field: keyof typeof DashboardSettingField; value: any }
) => {
  // Name of the setting row
  const name = "dashboard";
  const settingObj =
    (await prisma.settings.findFirst({ where: { name } })) ?? {};

  // Update or create the new field
  const updatedObj = { ...settingObj, [field]: value };

  // Upsert the data
  return prisma.settings.upsert({
    where: { name },
    create: { name, value: updatedObj },
    update: { name, value: updatedObj },
  });
};

export const settingsRouter = t.router({
  // Custom ZOD validator for `DISCORD_GUILD_ID` field
  upsertDiscordGuildID: authedProcedure
    .input(z.object({ value: z.string().regex(/^\d+$/) }))
    .mutation(({ ctx, input }) => {
      return prismaUpdateSettingField(ctx.prisma, {
        field: DashboardSettingField.DISCORD_GUILD_ID,
        value: input.value,
      });
    }),
});

// Available setting fields
enum DashboardSettingField {
  DISCORD_GUILD_ID = "DISCORD_GUILD_ID",
  DISCORD_WEBHOOK_URL = "DISCORD_WEBHOOK_URL",
}

// Helper function to update data
const prismaUpdateSettingField = async (
  prisma: PrismaClient,
  { field, value }: { field: keyof typeof DashboardSettingField; value: any }
) => {
  // Name of the setting row
  const name = "dashboard";
  const settingObj =
    (await prisma.settings.findFirst({ where: { name } })) ?? {};

  // Update or create the new field
  const updatedObj = { ...settingObj, [field]: value };

  // Upsert the data
  return prisma.settings.upsert({
    where: { name },
    create: { name, value: updatedObj },
    update: { name, value: updatedObj },
  });
};

export const settingsRouter = t.router({
  // Custom ZOD validator for `DISCORD_GUILD_ID` field
  upsertDiscordGuildID: authedProcedure
    .input(z.object({ value: z.string().regex(/^\d+$/) }))
    .mutation(({ ctx, input }) => {
      return prismaUpdateSettingField(ctx.prisma, {
        field: DashboardSettingField.DISCORD_GUILD_ID,
        value: input.value,
      });
    }),
});

Leonidas•3y ago

looks good to me! highly recommended: use immer to update the settings json - makes your life a lot easier

Leonidas•3y ago

https://github.com/immerjs/immer

GitHub

GitHub - immerjs/immer: Create the next immutable state by mutating...

Create the next immutable state by mutating the current one - GitHub - immerjs/immer: Create the next immutable state by mutating the current one

Leonidas•3y ago

One last recommendation: insteaf of storing all such settings in an own settings table, add a settings column to the specific table (for user settings the user table would be right)

venusOP•3y ago

Yeah, I wanted to do it like so, but can't think of any suitable table to add this column to

Leonidas•3y ago

in that case I would tend to create a new table called discordSettings / dashboardSettings for storing this specific data format. it can still be easily extended with additional columns. just remember to add default values when you add new columns - else your prisma migration will fail with a unqiue constraint error. Been there a few times 😄

venusOP•3y ago

Good point. As long as the input field for the mutation is required, this shouldn't happen

Leonidas•3y ago

and when it happens the good point is that you atleast exactly now when & where a logic error was introduced - 100x nicer than finding these bugs weeks to months in with almost no sane method to debug

venusOP•3y ago

done ✅

Leonidas•3y ago

I wish you continued maximum success with the project

venusOP•3y ago

Thank you for your help & time. 🥰

Gaming

Programming

Best practice how to store settings in MySQL?