A Dapper Raccoon Comments - Answer Overflow

This is just a weird artifact of NPM's module resolution strategy - it tries to reconcile all dependency versions, even for optional packages which you are not using. If you're not using React Native, forcing the installation with --force or --legacy-peer-deps as mentioned in the error message should be fine. If you are using React Native, then you need to use the exact version of React which is compatible with your React Native version (here, downgrading React to 18.2.0 would mitigate the error). Alternately, using a package manager with a more flexible resolution strategy is also an option. You shouldn't see this error when using Yarn, PNPM, or Bun, unless you are actually using React Native and the optional peer with an incompatible React version.

6 replies

DTDrizzle Team

•Created by Daniel Drozdov on 6/7/2024 in #help

Not sure why we need to make relations when we have foreign keys?

Alright... then I understand even less 👍

16 replies

DTDrizzle Team

•Created by Daniel Drozdov on 6/7/2024 in #help

Not sure why we need to make relations when we have foreign keys?

I've been wondering about this as well, but still haven't explored it to any depth. My best understanding is that you use relations() to define "virtual fields" which reference other models, for use specifically inside Drizzle's db.query RQB interface (maybe?). It is also my understanding that relations() are not presently factored in to the Typescript types as you might expect - a db.query for a User containing a with: { posts: true } will not automatically have a return type which includes the posts property, but it will exist on the data. I believe I read that this is a planned feature though. So you need to use foreign keys either way, but relations() enables you to create a "virtual field" for the relation to query for and access the data more intuitively, wherein Drizzle handles building the appropriate query. You can do the same without it, but then you're responsible for factoring the foreign keys and joins and such into your query. But using relations() and Typescript, you currently also need to somehow cast or coerce the query results to a type including the relational fields to make full use of the feature. This is all just my current understanding - I also would love to hear a more canonical/informed answer.

16 replies

DTDrizzle Team

•Created by diegoalzate on 6/13/2024 in #help

snapshots from v6 to v7

This is all just general conjecture conjecture on my part, but I think that all sounds normal. Since Kit is still closed source (and I can't be bothered to diff the builds) it's hard to know exactly what changed, but the only other note on the release is pertaining to PostGIS. If you're not using postgis, or not using it as described in that issue, it makes sense that nothing in your migrations or migration meta would change. That version of Kit's code is written with the expectation that your migrations/meta adheres to the v7 format/standard. It seems reasonable to require you to execute the whole update routine against your files to definitively ensure their v7-compliance just based on version numbers alone, rather than using any sort of logic to test if your migrations/meta rely on any of the functionality which has been patched.

2 replies

DTDrizzle Team

•Created by xvx on 6/12/2024 in #help

How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)

To upsert multiple rows in one query in PostgreSQL and SQLite you can use sql operator and excluded keyword. excluded is a special reference that refer to the row that was proposed for insertion, but wasn’t inserted because of the conflict. This is how you can do it:
// ...
await db
 .insert(users)
 .values(values)
 .onConflictDoUpdate({
   target: users.id,
   set: { lastLogin: sql.raw(`excluded.${users.lastLogin.name}`) },
 });
// ...
await db
 .insert(users)
 .values(values)
 .onConflictDoUpdate({
   target: users.id,
   set: { lastLogin: sql.raw(`excluded.${users.lastLogin.name}`) },
 });

13 replies

DTDrizzle Team

•Created by Caspian Nightworth on 6/10/2024 in #help

conflicting peer dependency when installing drizzle-orm

I don't think there's really anything Drizzle could do to mitigate this issue, apart from splitting ORM for Native into a completely separate package... Next.js doesn't really support Native out of the box, so fresh Next projects start off with a version of React unconstrained by Native compatibility. But NPM sees that ORM has an optional peer to support Native and demands that your dependencies meet that constraint, even if your project does not use that optional peer. Solutions (any one should work): - If your project will not use React Native: - Use a different package manager with a more flexible resolution strategy (Yarn definitely handles this gracefully, PNPM and Bun probably do too). - Using NPM, install the package with the --force or --legacy-peer-deps flag as suggested in the error message. - Whether or not your project will use React Native, downgrading react to 18.2.0 will canonically solve the incompatibility.

4 replies

DTDrizzle Team

•Created by Deleted_user_f8439ab4ed59 on 5/30/2024 in #help

unable to run drizzle studio

It's definitely super strange. I don't know enough about Kit/Studio to have any useful insights, but I did experience a similar situation using the (now deprecated in Kit v0.22) d1 configuration for drizzle.config.ts to connect to a local mock database... Migrate worked fine, but Studio would stall as yours does. Turns out Studio just didn't support the configuration at all. But your config relies on the core postgresql functionality - it's really hard to imagine what might be going wrong. Seems like Studio generally really needs to improve upon it's error messages.

12 replies

DTDrizzle Team

•Created by icomad on 6/7/2024 in #help

Table factory typescript problem

I'm not great with typescript and I really hope someone else might provide a better solution, but I couldn't see any obvious way to shim the extra columns into TColumnsMap while using the *TableFn interface for the wrapper, since it's explicitly inferred from the fields parameter. I ended up typing my function similar to:

const baseColumns = { /* ... */ };

const baseTable = <
  TTableName extends string,
  TColumnsMap extends Record<string, SQLiteColumnBuilderBase>
>(
  name: TTableName,
  fields: TColumnsMap,
  extraConfig?: (self: BuildColumns<TTableName, TColumnsMap & typeof baseColumns, 'sqlite'>) => SQLiteTableExtraConfig
) => {
  const {id, ...baseColumnsRest} = baseColumns;

  return sqliteTable(
    name,
    {
      id,
      ...fields,
      ...baseColumnsRest,
    },
    extraConfig
  );
}

const baseColumns = { /* ... */ };

const baseTable = <
  TTableName extends string,
  TColumnsMap extends Record<string, SQLiteColumnBuilderBase>
>(
  name: TTableName,
  fields: TColumnsMap,
  extraConfig?: (self: BuildColumns<TTableName, TColumnsMap & typeof baseColumns, 'sqlite'>) => SQLiteTableExtraConfig
) => {
  const {id, ...baseColumnsRest} = baseColumns;

  return sqliteTable(
    name,
    {
      id,
      ...fields,
      ...baseColumnsRest,
    },
    extraConfig
  );
}

I did try my hand at a generic interface to serve as a type for various "table factory functions" using whatever default columns - and it did work - but it uses a lot of copy & paste from the Drizzle ORM implementation, which kind of scared me away given that Drizzle is still fairly unstable. I would love to know if there's a more canonical solution to typing these sorts of functions.

3 replies

DTDrizzle Team

•Created by Deleted_user_f8439ab4ed59 on 5/30/2024 in #help

unable to run drizzle studio

I think dot's suggesting that you should obtain the connection string from the environment instead of a file... but if it's the same value either way, it's hard to imagine that would change anything. Maybe upgrade your ORM and Kit packages to latest and try again, just to make sure you're not running into some bug which has since been squashed? It is very suspect that your db:studio script specifies --config but the studio CLI mentions "No config path provided," but if the default path which it is falling back to is correct, that's still unlikely to be the culprit... Have you thrown like a console.log({DATABASE_URL: env.DATABASE_URL}) into your drizzle.config.ts to ensure that kit is indeed using using that configuration whilst attempting to launch studio, and that your connection string is correct in that instance?

12 replies

DTDrizzle Team

•Created by pato on 6/4/2024 in #help

Queries require "await"?

Some drivers like better-sqlite3 optionally support synchronous queries by ending your query builder chain with .all()/.get()/.values()/.all() - but yeah. Most will be async and should be used as a promise.

3 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

Yeah that's what I was thinking. I just like that you can iterate over the array and test it's length and such, instead of pulling k/v pairs from the object. I also like that the format has a better parity with your data model. Likewise, the original request format would map better to the row-per-ballot strategy, as then rank_15 directly corresponds to a column/property on your model. When the API data format closely aligns with the resource model, you get to skip over some parsing/mapping work which you'd otherwise need to do 👍

11 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

Someone also suggested making it unique on the userId, week, and year but it would require a 3rd column

Not necessarily - the UNIQUE constraint can be applied at the table level to multiple columns together (creating a "composite key") without explicitly needing an extra column (instead that data is stored separately from the table row data as an index). It looks like in Drizzle ORM this is achieved through the refiner callback you can provide as the second argument to a table schema constructor (https://orm.drizzle.team/docs/indexes-constraints#unique), so your refiner might look something like

(table) => ({
  isUniqueVote: unique().on(table.userId, table.year, table.week),
})

(table) => ({
  isUniqueVote: unique().on(table.userId, table.year, table.week),
})

I believe this is one of the ways in which you could prevent duplicate entries with out the extra SELECT query, and I think you'd do that by just throwing .onConflictDoNothing() on to your db.insert() chain - but it may need to be further configured to target those columns (https://orm.drizzle.team/docs/insert#on-conflict-do-nothing).

Would it be “better” to just have a single row per ballot but have a column for every rank?

I think it depends on what else you intend to be doing with that data... like if you want to calculate a team's overall ranking across all users in a week or season, or a user's most favored team, or some such. Consider how you'd achieve those calculations with Drizzle/SQL for either scenario. Row-per-ballot is definitely smaller and would likely outperform row-per-vote for most tasks, but I feel like row-per-vote is more flexible and the queries would be easier to compose without resorting to SQL... I think I'd probably stick with row-per-vote unless it becomes evident that row-per-ballot offers necessary advantages.

11 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

My IDs for the teamId are basically coming from Sanity CMS, so I guess I could make a query to Ssnity to validate the teamIds

Maybe not a bad idea. My thought with all the validation was mostly just to avoid any more expensive calls like DB operations is there was any hint of anything wrong or malicious with the request. To that end, you might validate the type and format of all of the teamIds first, then after the request body's been successfully mapped to vote objects hit up Sanity to make sure they actually exist. But if you're just prototyping, this might also be a little much - I have a long history of over-engineering things before I really need to 👍. To the end of over-engineering things, if Sanity hits are costly in time or money, I might seek to find a way to cache teamIds somewhere less expensive.

Your suggestion on the array I’ll look into.

No biggie either way - an array is just my subjective preference. I'm also presently calf-deep in JSON:API where all data is moved around in arrays, so my personal preference might be somewhat skewed :P

Right now I have a zod schema for a form [...]

Glad to hear it! Writing validation functions from scratch sucks... I haven't tried zod yet, but I believe you could parse the request body against the schema to skip most of this validation?

Another question I have for you since I’ve gotten mixed responses on it… you touched on the indexing part.

Definitely don't take anything I say about SQL as gospel or even well-informed - I really need to spend some dedicated time studying it some day instead of just relying on what I've picked up over the years. So take the below as only my best understanding and preference rather than anything close to a "best practice."

11 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

if (!Array.isArray(body))
  throw new Error('Invalid request body.');

// ...

const ranksSubmitted = new Set();
const votes = body.map(
  ({teamId, rank, ...rest}) => {
    if (Object.keys(rest).length > 0)
      throw new Error('Unknown properties in vote object.');

    if (!teamIdIsValid(teamId))
      throw new Error('Invalid vote teamId.');

    if (!rankIsValid(rank))
      throw new Error('Invalid vote rank.');

    if (ranksSubmitted.has(rank))
      throw new Error('Duplicate vote rank.');
    else
      ranksSubmitted.add(rank);

    return {
      userId: user.userId,
      week: 1,
      teamId,
      rank,
    };
  }
);

// ...

if (hasAlreadyVoted)
  throw new Error('User has already voted this week.');

// ...

if (!Array.isArray(body))
  throw new Error('Invalid request body.');

// ...

const ranksSubmitted = new Set();
const votes = body.map(
  ({teamId, rank, ...rest}) => {
    if (Object.keys(rest).length > 0)
      throw new Error('Unknown properties in vote object.');

    if (!teamIdIsValid(teamId))
      throw new Error('Invalid vote teamId.');

    if (!rankIsValid(rank))
      throw new Error('Invalid vote rank.');

    if (ranksSubmitted.has(rank))
      throw new Error('Duplicate vote rank.');
    else
      ranksSubmitted.add(rank);

    return {
      userId: user.userId,
      week: 1,
      teamId,
      rank,
    };
  }
);

// ...

if (hasAlreadyVoted)
  throw new Error('User has already voted this week.');

// ...

11 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

Other things to consider: - It's not great that a request with a key like asdfasdgrank_23456245734567asdfasdf will still hit the database. That's clearly a malformed key - the server should respond with an error before ever touching the db. You know where rank_'s going to appear in the string - match the whole key, and restrict the digits to like (\d|1[0-9]|2[0-5]). My preference would be to send an array of objects in the request body and just specify rank as it's own dedicated property; no reason to encode data into a string then decode it back to data again. - Make sure the schema has some sort of relational constraint on teamId, and ideally perform some sort of validation on it prior to the db query. - That second .map() can error out with a vague cannot read properties of null error since the first .map() can return null - that's not terribly useful information to return to the front-end or send to Sentry. All together, short of using a dedicated validation library and any sort of better query, the following might be an improvement:

function teamIdIsValid(teamId: any) {
  if (typeof teamId !== 'string') return false;
  // ...
  return true;
}

function rankIsValid(rank: any) {
  if (typeof rank !== 'number') return false;
  if (rank % 1) return false;
  if (rank < 1 || rank > 25) return false;
  return true;
}

function teamIdIsValid(teamId: any) {
  if (typeof teamId !== 'string') return false;
  // ...
  return true;
}

function rankIsValid(rank: any) {
  if (typeof rank !== 'number') return false;
  if (rank % 1) return false;
  if (rank < 1 || rank > 25) return false;
  return true;
}

11 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

"Quick and dirty" and "the best way" are sort of at odds with one another, but the former could just be

const hasAlreadyVoted = !!await db.select(1).from(votes).where(
  and(
    eq(votes.userId, user.userId),
    eq(votes.year, sql`(YEAR(CURDATE()))`),
    eq(votes.week, 1),
  )
).limit(1);

const hasAlreadyVoted = !!await db.select(1).from(votes).where(
  and(
    eq(votes.userId, user.userId),
    eq(votes.year, sql`(YEAR(CURDATE()))`),
    eq(votes.week, 1),
  )
).limit(1);

I'm not terribly proficient with SQL, but I think better would be to just use some .notExists() conditional operator in the db.insert() chain, or to combine the SELECT and INSERT into a single transaction. Ideally, you might implement some measures to prevent spammed requests from sneaking through on a race condition - API rate-limiting and SQL locks if your db supports them. If the table is expected to be large, you may also benefit from indexing userId, year, and week - or combining them into a compound key/index. But maybe just make a note of that and save it for a later benchmarking/tweaking pass down the road.

11 replies

Gaming

Programming