https://kysely.dev/assets/js/f8ff5439.28865427.js

1"use strict";(globalThis.webpackChunkkysely_site=globalThis.webpackChunkkysely_site||[]).push([[7618],{1995(e,s,n){n.r(s),n.d(s,{assets:()=>o,contentTitle:()=>l,default:()=>d,frontMatter:()=>a,metadata:()=>r,toc:()=>c});const r=JSON.parse('{"id":"recipes/reusable-helpers","title":"Reusable helpers","description":"Build reusable, type-safe Kysely query helpers with expressions, the expression builder, subqueries, and the sql template tag.","source":"@site/docs/recipes/0001-reusable-helpers.md","sourceDirName":"recipes","slug":"/recipes/reusable-helpers","permalink":"/docs/recipes/reusable-helpers","draft":false,"unlisted":false,"editUrl":"https://github.com/kysely-org/kysely/tree/master/site/docs/recipes/0001-reusable-helpers.md","tags":[],"version":"current","sidebarPosition":1,"frontMatter":{"description":"Build reusable, type-safe Kysely query helpers with expressions, the expression builder, subqueries, and the sql template tag."},"sidebar":"tutorialSidebar","previous":{"title":"Relations","permalink":"/docs/recipes/relations"},"next":{"title":"Data types","permalink":"/docs/recipes/data-types"}}');var i=n(1058),t=n(4801);const a={description:"Build reusable, type-safe Kysely query helpers with expressions, the expression builder, subqueries, and the sql template tag."},l="Reusable helpers",o={},c=[{value:"Reusable helpers using <code>ExpressionBuilder</code>",id:"reusable-helpers-using-expressionbuilder",level:2},{value:"Subquery",id:"subquery",level:4},{value:"SQL function + calculation",id:"sql-function--calculation",level:4},{value:"Dealing with nullable expressions",id:"dealing-with-nullable-expressions",level:2},{value:"Passing select queries as expressions",id:"passing-select-queries-as-expressions",level:2}];function p(e){const s={a:"a",admonition:"admonition",code:"code",h1:"h1",h2:"h2",h4:"h4",header:"header",p:"p",pre:"pre",...(0,t.R)(),...e.components};return(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(s.header,{children:(0,i.jsx)(s.h1,{id:"reusable-helpers",children:"Reusable helpers"})}),"\n",(0,i.jsx)(s.admonition,{type:"info",children:(0,i.jsxs)(s.p,{children:[(0,i.jsx)(s.a,{href:"https://play.kysely.dev/#cN4IgpgJglgLg9gJwM4gFyhgTwA5jSKAW20RgAJgAdAOzLIHExqwEBDGSAGhroGUwANmADGMVgCMh3WmQCq2COzASpPMgElqSFmMlhpAXzIAzBHEJlKIANaZtAzFYDcNGmAAeJBOSjUOCY1ZhMDIAEXYJVm0KNVxkOGpUMgAFFiQEgBUVMBcZXBgk1JgsvVyDV2ZPUjJff0DglLTM7JiZKAgkxmY2DggAHmoAV0JxFgA+XLpjKGQYAH1qVkIwJKQYBF8Ac0myASj5xeXV9a2dzaYIFk6LljKKjy8fPxZ6kKKSoVa6duvupX6hiNxjtDisyGsNtRtmo4AB3bpzH5kQGjBA7JC4YRQMBIJK8THYpB3ahuKreMhYXAMG4IMgAXksIEIrCEVjIAB9GcYwMzWSAOYy4DAABYsZykx4UnAhfEiQn0xkQOCbNmcqzCdiqxnCpZrMUgXIS6qUt5NWgM-hCUTZPqpeLUD5gCZG8kmsgAOTAsLt6XNGi0OhtPuaemdlUlbuD1Hkig4CpjSiDZsdYZd5EjYHIFsEIl0QltmZTuQexulHq9RQVmm03iTxWyYZLrrLRQTcYZbeUegL9dDuRAnBAAEdBixMPgiJLgGQAKKeBA4pBQBKcMi8IcCABCcDgAlXDwXSCXCU3gygAkuCFXSA3ZCMpnMjNs9kcBpok+q0-CuiiISiYU3O8TDMCwrBNABaSBYEQcUSWoYQEjWXZ9ndJYQgZKxeAyABBAAZXCAHl3RnWCaAQrRyDiX0kAARgVVhYVYWAyAgcQ1AAOnsXMADEQIACisKiEisABKDiuNEPiAG0rHaKxVysaZZgWNCrAAXTEmQAHotLIGj2LIAADG8BEMikeWwPY4zETYDIyYUoCQGonJFEIhH2KVcHApBWG5Mg4GwGBl2odi1B0sgAE04EGAByBcyGwMwADd2i2ClRTIBCBGGWhQTIWFYGFaLyFYahME8sBwOEUVhGsLZpDocLSogBLss2XwnNhBIYpK4xuVEGoYFCmR2NhUUFz4ky+ghLYxkMwZsDiPi9jWFTlhEwyFJAOl5OQtZUPWsLdIAJgM4yNzMjhiCskIbPywqjIXYxDIM7CypFNLsCibQWvgR6wGeo6yHOGAXOlKqausSAyFYTYmIo9K-2EYRhkGG6WpHMdMoSDh3CGjixpYMA+L46cwHEVcnrvET6TGNQ6HJyaN2mk4oTmhaloAEmAJ6BJAFaDlUkARIMDatp2gc9pgA6wBEhqyE0xrdIAZjsjLDOMagzOMQZ4KChIyFFARsCczBophpK4HaMhCEcpcoQS1gFz8UVtCQLSEMIZkkCBlgzGQGHqBawY3bIJ6nP-GikOEFaI4QTZhiYUHhroUbxuJ0myHJ1dNep2n6az8Q+M1lnIU2MY+Y5-VVxk-n9jWsB1LlxkJdXAWZbltRFbIdiPBEQYOD4kTDWoHW9eCsgq4QPiDySOdEsXYLS9mmmqBkBcYEGBBaCmma2fmxaWD47mD1FwziRoMfRAngQ4SP2fZ3nReEmXtnV7UDet535m9-Lwzb9hEfE+84z4X1Hrra+BtyIahgHxdi8CDy4kfgvI8S9f5jCkhpL4YdMxf3BBudiAArK21AZ7ziQNeC67J2SGREuxTmGo1gZDgK-cuQ8wHkSQkJLQx16KMWYqxcSOZRC8XMHzbhokhFWlgbXOSktFIzFWqCJuBN04kzJhTHBxg850jGIXPiU8+K8ysALBuolm5WFblLDu3de7uH7oPYeFROGUTNEgZWfCmLkEESNCSMBRGEHEWaSRvjhEyNkhAXaCjlLKJABpVRRN1GF0pgDVcSUWQ6LpjIBmRcC50GgewAS2S6AlIAUfYxIAlJKKFiJTuxSSlkHSQIPmjI6kNIaYYippjYm1Plg0tp7TLHyTyYyJAmABBJRxP4NcOF8JERIgOAuAzbF9xRo4keLiEpuIACyeIEWxUJ0iAlBPtCE1OvhugACkSFBJgLtPixDfA010QXR5IUEgk3Js8rJ7T9GCTNOxKpgtlhRO2rtMp09ybsQqfkdiPTelLPlrYvxSSnppIyaLWmZAZLFOidUkFizinhXUNos2gxJ7RFYIbQQcQai0GMmEzaZAyXIjANDP6LJGJ2ASsldof5aAPAEFALEdyiW6TysYRAg0KVpVckZKIZllgijgBAFOJSCmwJhQCoFZjhbouaWyUSKTjC3LhTUuhUQim-KsKCOYBURRzHyMM4pAz4kjUQJeTcmBUUA0ybsO+08Kk6vhSs+xaziZOLglffWtB2hIAIsYKMSB7LsAABKsEmaEZUMsIB8VBHPJ+qCX7oPfjITZ5MFQHmftQU855Lx9FCJuVczBJkIArpGoG2FwSDHEJjBA5URTsBwZvbeLkMp8r8FAaYaR-LaJZAILZ9ofbaV0oO8gOpJkwxYsqZEaEWqGVBC9D+uDt6FwLpxMJxz-l3OFuelF172JwgRHI7uqdCYTQfRiOUOJQVWKsEqFUt7ilp0SQ+2J4tdqgkjeUO

1CmyAMywVFYLNmxNhwFIvBRCril0AFY9neIOanPxV6QASKA4RsJ2EBAGpI8EsjPd30Zy+fnYpjMRn-PtOxORfSSmyRJIS35caE1JpTTAdNmbs17pnuIdiTS+LwbQgil1SKOKrIHhGke0aJ6OQIheFgKbSGwzBPPQ8x5qADGGKiMYpa6Cf1PVNdcW4dwCDmoZsgejuaGYMOfGgMGyKYcXdRAAbHhliBGe5Ef4ux30ZzwthOkhE0FwahZurfWozOTS-Xad0wgfTfFZOBYAAy1NDQ49TFRNMG3gJoGAfQMhZzxhcJyv8BRDCoxXB+xnq21as9g8K9lHLDq-k5QynWi1mZRCwFraNnNmSnUZA8s2nKtb2HoIGzUjKjdM+ZoEbazJCnGgVbQarbPfwEH0AudWPAcCDkt6bZAAD8yILOTc5MtsgSQJsIDUHNY+wBT4iVQKgWoYBzgIG89QXzGGEbcKQAAdhC6xC9RzIs0dOcLJHuZPniG+ditQCX+NckUcCxuBPDEF3JiMjHIiUdOrow0qnsCbU1MpwxgASgDE5vpOORPkWC3nsKn0sERDz19JT2JCttrAmiovU6cyiLwDUexp403Cn0cC4EyD9acgN5gkBICU85tQIU7pptDyRexS117dV1I0v2Awg5oAslzPgEgaxNiHgHDYOwghxzoBAK20z+ACvsWOgAThDyAAwBggA",children:"Here's"})," a playground link containing all the code in this recipe."]})}),"\n",(0,i.jsx)(s.p,{children:"Let's say you want to write the following query:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-sql",children:"SELECT id, first_name\nFROM person\nWHERE upper(last_name) = $1\n"})}),"\n",(0,i.jsxs)(s.p,{children:["Kysely doesn't have a built-in ",(0,i.jsx)(s.code,{children:"upper"})," function but there are at least three ways you could write this:"]}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const lastName = 'STALLONE'\n\nconst persons = await db\n  .selectFrom('person')\n  .select(['id', 'first_name'])\n  // 1. `sql` template tag. This is the least type-safe option.\n  // You're providing the column name without any type-checking,\n  // and plugins won't affect it.\n  .where(\n    sql<string>`upper(last_name)`, '=', lastName\n  )\n  // 2. `sql` template tag with `ref`. Anything passed to `ref`\n  // gets type-checked against the accumulated query context.\n  .where(({ eb, ref }) => eb(\n    sql<string>`upper(${ref('last_name')})`, '=', lastName\n  ))\n  // 3. The `fn` function helps you avoid missing parentheses/commas\n  // errors and uses refs as 1st class arguments.\n  .where(({ eb, fn }) => eb(\n    fn<string>('upper', ['last_name']), '=', lastName\n  ))\n  .execute()\n"})}),"\n",(0,i.jsx)(s.p,{children:"but each option could be more readable or type-safe."}),"\n",(0,i.jsx)(s.p,{children:"Fortunately Kysely allows you to easily create composable, reusable and type-safe helper functions:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"import { Expression, sql } from 'kysely'\n\nfunction upper(expr: Expression<string>) {\n  return sql<string>`upper(${expr})`\n}\n\nfunction lower(expr: Expression<string>) {\n  return sql<string>`lower(${expr})`\n}\n\nfunction concat(...exprs: Expression<string>[]) {\n  return sql.join<string>(exprs, sql`||`)\n}\n"})}),"\n",(0,i.jsxs)(s.p,{children:["Using the ",(0,i.jsx)(s.code,{children:"upper"})," helper, our query would look like this:"]}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const lastName = 'STALLONE'\n\nconst persons = await db\n  .selectFrom('person')\n  .select(['id', 'first_name'])\n  .where(({ eb, ref }) => eb(\n    upper(ref('last_name')), '=', lastName\n  ))\n  .execute()\n"})}),"\n",(0,i.jsxs)(s.p,{children:["The recipe for helper functions is simple: take inputs as ",(0,i.jsx)(s.code,{children:"Expression<T>"})," instances where ",(0,i.jsx)(s.code,{children:"T"})," is the type of the expression. For example ",(0,i.jsx)(s.code,{children:"upper"})," takes in any ",(0,i.jsx)(s.code,{children:"string"})," expression since it transforms strings to upper case. If you implemented the ",(0,i.jsx)(s.code,{children:"round"})," function, it'd take in ",(0,i.jsx)(s.code,{children:"Expression<number>"})," since you can only round numbers."]}),"\n",(0,i.jsxs)(s.p,{children:["The helper functions should then use the inputs to create an output that's also an ",(0,i.jsx)(s.code,{children:"Expression"}),". Everything you can create using the expression builder is an instance of ",(0,i.jsx)(s.code,{children:"Expression"}),". So is the output of the ",(0,i.jsx)(s.code,{children:"sql"})," template tag and all methods under the ",(0,i.jsx)(s.code,{children:"sql"})," object. Same goes for ",(0,i.jsx)(s.code,{children:"SelectQueryBuilder"})," and pretty much everything else in Kysely. Everything's an expression."]}),"\n",(0,i.jsxs)(s.p,{children:["See ",(0,i.jsx)(s.a,{href:"https://kysely.dev/docs/recipes/expressions",children:"this recipe"})," to learn more about expressions."]}),"\n",(0,i.jsx)(s.p,{children:"So we've learned that everything's an expression and that expressions are composable. Let's put this idea to use:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const persons = await db\n  .selectFrom('person')\n  .select(['id', 'first_name'])\n  .where(({ eb, ref, val }) => eb(\n    concat(\n      lower(ref('first_name')),\n      val(' '),\n      upper(ref('last_name'))\n    ),\n    '=',\n    'sylvester STALLONE'\n  ))\n  .execute()\n"})}),"\n",(0,i.jsxs)(s.p,{children:["So far we've only used our helper functions in the first argument of ",(0,i.jsx)(s.code,{children:"where"})," but you can use them anywhere:"]}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const persons = await db\n  .selectFrom('person')\n  .innerJoin('pet', (join) => join.on(eb => eb(\n    'person.first_name', '=', lower(eb.ref('pet.name'))\n  )))\n  .select(({ ref, val }) => [\n    'first_name',\n    // If you use a helper in `select`, you need to always provide an explicit\n    // name for it using the `as` method.\n    concat(ref('person.first_name'), val(' '), ref('pet.name')).as('name_with_pet')\n  ])\n  .orderBy(({ ref }) => lower(ref('first_name')))\n  .execute()\n"})}),"\n",(0,i.jsxs)(s.h2,{id:"reusable-helpers-using-expressionbuilder",children:["Reusable helpers using ",(0,i.jsx)(s.code,{children:"ExpressionBuilder"})]}),"\n",(0,i.jsx)(s.h4,{id:"subquery",children:"Subquery"}),"\n",(0,i.jsx)(s.p,{children:"Here's an example of a helper function that uses the expression builder to build a subquery that gets filtered by a dynamic string expression."}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"import { Expression, expressionBuilder } from 'kysely'\n\nfunction idsOfPersonsThatHaveDogNamed(name: Expression<string>) {\n  const eb = expressionBuilder<DB>()\n\n  // A subquery that returns the identifiers of all persons\n  // that have a dog named `name`.\n  return eb\n    .selectFrom('pet')\n    .select('pet.owner_id')\n    .where('pet.species', '=', 'dog')\n    .where('pet.name', '=', name)\n}\n"})}

1),"\n",(0,i.jsx)(s.p,{children:"And here's how you could use it:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const dogName = 'Doggo'\n\nconst persons = await db\n  .selectFrom('person')\n  .selectAll('person')\n  .where((eb) => eb(\n    'person.id', 'in', idsOfPersonsThatHaveDogNamed(eb.val(dogName))\n  ))\n  .execute()\n"})}),"\n",(0,i.jsxs)(s.p,{children:["Note that ",(0,i.jsx)(s.code,{children:"idsOfPersonsThatHaveDogNamed"})," doesn't execute a separate query but instead returns a subquery expression that's compiled as a part of the parent query:"]}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-sql",children:"select\n  person.*\nfrom\n  person\nwhere\n  person.id in (\n    select pet.owner_id\n    from pet\n    where pet.species = 'dog'\n    and pet.name = ?\n  )\n"})}),"\n",(0,i.jsx)(s.p,{children:"In all our examples we've used the following syntax:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:".where(eb => eb(left, operator, right))\n"})}),"\n",(0,i.jsxs)(s.p,{children:["When the expression builder ",(0,i.jsx)(s.code,{children:"eb"})," is used as a function, it creates a binary expression. All binary expressions with a comparison operator are represented as a ",(0,i.jsx)(s.code,{children:"Expression<SqlBool>"}),". You don't always need to return ",(0,i.jsx)(s.code,{children:"eb(left, operator, right)"})," from the callback though. Since ",(0,i.jsx)(s.code,{children:"Expressions"})," are composable and reusable, you can return any ",(0,i.jsx)(s.code,{children:"Expression<SqlBool>"}),"."]}),"\n",(0,i.jsx)(s.p,{children:"This means you can create helpers like this:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"function isOlderThan(age: Expression<number>) {\n  return sql<SqlBool>`age > ${age}`\n}\n"})}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const persons = await db\n  .selectFrom('person')\n  .select(['id', 'first_name'])\n  .where(({ val }) => isOlderThan(val(60)))\n  .execute()\n"})}),"\n",(0,i.jsx)(s.h4,{id:"sql-function--calculation",children:"SQL function + calculation"}),"\n",(0,i.jsxs)(s.p,{children:["Here we pass a dynamic ",(0,i.jsx)(s.code,{children:"Date"})," expression to a function expression that takes part in a calculation."]}),"\n",(0,i.jsxs)(s.p,{children:["It is a timestamp to milliseconds helper and it is quite handy as a way to keep dates consistent, even after JSON functions throw away data type information denying drivers from transforming to ",(0,i.jsx)(s.code,{children:"Date"})," in TypeScript-side."]}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"export function timestampToUnix(expr: Expression<Date>) {\n  const eb = expressionBuilder<DB>()\n  \n  return eb(eb.fn<number>('UNIX_TIMESTAMP', [expr]), '*', 1_000)\n}\n"})}),"\n",(0,i.jsx)(s.p,{children:"Here's how to use it:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"await kysely.selectFrom('table').select((eb) => timestampToUnix(eb.ref('created_at')).as('created_at'))\n"})}),"\n",(0,i.jsx)(s.h2,{id:"dealing-with-nullable-expressions",children:"Dealing with nullable expressions"}),"\n",(0,i.jsx)(s.p,{children:"If you want your helpers to work with nullable expressions (nullable columns etc.), you can do something like this:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"import { Expression } from 'kysely'\n\n// This function accepts both nullable and non-nullable string expressions.\nfunction toInt<T extends string | null>(expr: Expression<T>) {\n  // This returns `Expression<number | null>` if `expr` is nullable\n  // and `Expression<number>` otherwise.\n  return sql<T extends null ? (number | null) : number>`(${expr})::integer`\n}\n"})}),"\n",(0,i.jsx)(s.h2,{id:"passing-select-queries-as-expressions",children:"Passing select queries as expressions"}),"\n",(0,i.jsx)(s.p,{children:"Let's say we have the following query:"}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const expr: Expression<{ name: string }> = db\n  .selectFrom('pet')\n  .select('pet.name')\n"})}),"\n",(0,i.jsxs)(s.p,{children:["The expression type of our query is ",(0,i.jsx)(s.code,{children:"Expression<{ name: string }>"})," but SQL allows you to use a query like that as an ",(0,i.jsx)(s.code,{children:"Expression<string>"}),". In other words, SQL allows you to use single-column record types like scalars. Most of the time Kysely is able to automatically handle this case but with helper functions you need to use ",(0,i.jsx)(s.code,{children:"$asScalar()"})," to convert the type. Here's an example:"]}),"\n",(0,i.jsx)(s.pre,{children:(0,i.jsx)(s.code,{className:"language-ts",children:"const persons = await db\n  .selectFrom('person')\n  .select((eb) => [\n    'id',\n    'first_name',\n    upper(\n      eb.selectFrom('pet')\n        .select('name')\n        .whereRef('person.id', '=', 'pet.owner_id')\n        .limit(1)\n        .$asScalar() // <-- This is needed\n        .$notNull()\n    ).as('pet_name')\n  ])\n"})}),"\n",(0,i.jsxs)(s.p,{children:["The subquery is an ",(0,i.jsx)(s.code,{children:"Expression<{ name: string }>"})," but our ",(0,i.jsx)(s.code,{children:"upper"})," function only accepts ",(0,i.jsx)(s.code,{children:"Expression<string>"}),". That's why we need to call ",(0,i.jsx)(s.code,{children:"$asScalar()"}),". ",(0,i.jsx)(s.code,{children:"$asScalar()"})," has no effect on the generated SQL. It's simply a type-level helper."]}),"\n",(0,i.jsxs)(s.p,{children:["We also used ",(0,i.jsx)(s.code,{children:"$notNull()"})," in the example because our simple ",(0,i.jsx)(s.code,{children:"upper"})," function doesn't support nullable expressions."]})]})}function d(e={}){const{wrapper:s}={...(0,t.R)(),...e.components};return s?(0,i.jsx)(s,{...e,children:(0,i.jsx)(p,{...e})}):p(e)}}}]);