Skip to content

Commit cb4914a

Browse files
igalklebanovigalklebanov
authored
docs: MERGE examples (#1188)
1 parent 5b79e88 commit cb4914a

16 files changed

+241
-25
lines changed

‎package.json

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,7 @@
6161
"script:copy-interface-doc": "node scripts/copy-interface-documentation.js",
6262
"script:add-deno-type-references": "node scripts/add-deno-type-references.js",
6363
"script:align-site-version": "node scripts/align-site-version.js",
64+
"script:generate-site-examples": "node scripts/generate-site-examples.js",
6465
"prepublishOnly": "npm run build"
6566
},
6667
"author": "Sami Koskimäki <[email protected]>",

‎scripts/generate-site-examples.js

Lines changed: 40 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,24 @@ const moreExamplesByCategory = {
7171
'returning method':
7272
'https://kysely-org.github.io/kysely-apidoc/classes/DeleteQueryBuilder.html#returning',
7373
},
74+
merge: {
75+
'mergeInto method':
76+
'https://kysely-org.github.io/kysely-apidoc/classes/Kysely.html#mergeInto',
77+
'using method':
78+
'https://kysely-org.github.io/kysely-apidoc/classes/MergeQueryBuilder.html#using',
79+
'whenMatched method':
80+
'https://kysely-org.github.io/kysely-apidoc/classes/WheneableMergeQueryBuilder.html#whenMatched',
81+
'thenUpdateSet method':
82+
'https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenUpdateSet',
83+
'thenDelete method':
84+
'https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenDelete',
85+
'thenDoNothing method':
86+
'https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenDoNothing',
87+
'whenNotMatched method':
88+
'https://kysely-org.github.io/kysely-apidoc/classes/WheneableMergeQueryBuilder.html#whenNotMatched',
89+
'thenInsertValues method':
90+
'https://kysely-org.github.io/kysely-apidoc/classes/NotMatchedThenableMergeQueryBuilder.html#thenInsertValues',
91+
},
7492
transactions: {
7593
'transaction method':
7694
'https://kysely-org.github.io/kysely-apidoc/classes/Kysely.html#transaction',
@@ -88,6 +106,7 @@ function main() {
88106
const lines = readLines(filePath)
89107
const state = {
90108
filePath,
109+
/** @type {string | null} */
91110
line: null,
92111
lineIndex: 0,
93112
annotation: null,
@@ -153,7 +172,8 @@ function writeSiteExample(state) {
153172
const codeVariable = _.camelCase(name)
154173

155174
const fileName = `${priority.padStart(4, '0')}-${_.kebabCase(name)}`
156-
const filePath = path.join(SITE_EXAMPLE_PATH, category, fileName)
175+
const folderPath = path.join(SITE_EXAMPLE_PATH, category)
176+
const filePath = path.join(folderPath, fileName)
157177

158178
const codeFile = `export const ${codeVariable} = \`${deindent(code)
159179
.replaceAll('`', '\\`')
@@ -197,7 +217,22 @@ function writeSiteExample(state) {
197217

198218
const exampleFile = parts.join('\n')
199219

200-
fs.writeFileSync(filePath + '.js', codeFile)
220+
if (!fs.existsSync(folderPath)) {
221+
fs.mkdirSync(folderPath, { recursive: true })
222+
fs.writeFileSync(
223+
path.join(folderPath, '_category_.json'),
224+
`{
225+
"label": "${_.startCase(category)}",
226+
"position": 0, // TODO: set the position of the category.
227+
"link": {
228+
"type": "generated-index",
229+
"description": "Short and simple examples of using the ${category} functionality." // TODO: review this.
230+
}
231+
}`,
232+
)
233+
}
234+
235+
fs.writeFileSync(filePath + '.js', codeFile, {})
201236
fs.writeFileSync(filePath + '.mdx', exampleFile)
202237
}
203238

@@ -298,10 +333,10 @@ function deindent(str) {
298333
let ws = Number.MAX_SAFE_INTEGER
299334
for (const line of lines) {
300335
if (line.trim().length > 0) {
301-
const wsExec = /^(\s*)/.exec(line)
336+
const [, wsExec] = /^(\s*)/.exec(line) || []
302337

303-
if (wsExec[1].length < ws) {
304-
ws = wsExec[1].length
338+
if (wsExec != null && wsExec.length < ws) {
339+
ws = wsExec.length
305340
}
306341
}
307342
}

‎site/docs/examples/cte/_category_.json

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
{
22
"label": "CTE",
3-
"position": 7,
3+
"position": 9,
44
"link": {
55
"type": "generated-index",
66
"description": "Short and simple examples of how to use Common Table Expressions (CTE) in queries."

‎site/docs/examples/merge/0010-source-row-existence.js

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
export const sourceRowExistence = `const result = await db
2+
.mergeInto('person as target')
3+
.using('pet as source', 'source.owner_id', 'target.id')
4+
.whenMatchedAnd('target.has_pets', '!=', 'Y')
5+
.thenUpdateSet({ has_pets: 'Y' })
6+
.whenNotMatchedBySourceAnd('target.has_pets', '=', 'Y')
7+
.thenUpdateSet({ has_pets: 'N' })
8+
.executeTakeFirstOrThrow()
9+
10+
console.log(result.numChangedRows)`

‎site/docs/examples/merge/0010-source-row-existence.mdx

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
---
2+
title: 'Source row existence'
3+
---
4+
5+
# Source row existence
6+
7+
Update a target column based on the existence of a source row:
8+
9+
import {
10+
Playground,
11+
exampleSetup,
12+
} from '../../../src/components/Playground'
13+
14+
import {
15+
sourceRowExistence
16+
} from './0010-source-row-existence'
17+
18+
<div style={{ marginBottom: '1em' }}>
19+
<Playground code={sourceRowExistence} setupCode={exampleSetup} />
20+
</div>
21+
22+
:::info[More examples]
23+
The API documentation is packed with examples. The API docs are hosted [here](https://kysely-org.github.io/kysely-apidoc/),
24+
but you can access the same documentation by hovering over functions/methods/classes in your IDE. The examples are always
25+
just one hover away!
26+
27+
For example, check out these sections:
28+
- [mergeInto method](https://kysely-org.github.io/kysely-apidoc/classes/Kysely.html#mergeInto)
29+
- [using method](https://kysely-org.github.io/kysely-apidoc/classes/MergeQueryBuilder.html#using)
30+
- [whenMatched method](https://kysely-org.github.io/kysely-apidoc/classes/WheneableMergeQueryBuilder.html#whenMatched)
31+
- [thenUpdateSet method](https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenUpdateSet)
32+
- [thenDelete method](https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenDelete)
33+
- [thenDoNothing method](https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenDoNothing)
34+
- [whenNotMatched method](https://kysely-org.github.io/kysely-apidoc/classes/WheneableMergeQueryBuilder.html#whenNotMatched)
35+
- [thenInsertValues method](https://kysely-org.github.io/kysely-apidoc/classes/NotMatchedThenableMergeQueryBuilder.html#thenInsertValues)
36+
:::

‎site/docs/examples/merge/0020-temporary-changes-table.js

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
export const temporaryChangesTable = `const result = await db
2+
.mergeInto("wine as target")
3+
.using(
4+
"wine_stock_change as source",
5+
"source.wine_name",
6+
"target.name",
7+
)
8+
.whenNotMatchedAnd("source.stock_delta", ">", 0)
9+
.thenInsertValues(({ ref }) => ({
10+
name: ref("source.wine_name"),
11+
stock: ref("source.stock_delta"),
12+
}))
13+
.whenMatchedAnd(
14+
(eb) => eb("target.stock", "+", eb.ref("source.stock_delta")),
15+
">",
16+
0,
17+
)
18+
.thenUpdateSet("stock", (eb) =>
19+
eb("target.stock", "+", eb.ref("source.stock_delta")),
20+
)
21+
.whenMatched()
22+
.thenDelete()
23+
.executeTakeFirstOrThrow()`

‎site/docs/examples/merge/0020-temporary-changes-table.mdx

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
---
2+
title: 'Temporary changes table'
3+
---
4+
5+
# Temporary changes table
6+
7+
Merge new entries from a temporary changes table:
8+
9+
import {
10+
Playground,
11+
exampleSetup,
12+
} from '../../../src/components/Playground'
13+
14+
import {
15+
temporaryChangesTable
16+
} from './0020-temporary-changes-table'
17+
18+
<div style={{ marginBottom: '1em' }}>
19+
<Playground code={temporaryChangesTable} setupCode={exampleSetup} />
20+
</div>
21+
22+
:::info[More examples]
23+
The API documentation is packed with examples. The API docs are hosted [here](https://kysely-org.github.io/kysely-apidoc/),
24+
but you can access the same documentation by hovering over functions/methods/classes in your IDE. The examples are always
25+
just one hover away!
26+
27+
For example, check out these sections:
28+
- [mergeInto method](https://kysely-org.github.io/kysely-apidoc/classes/Kysely.html#mergeInto)
29+
- [using method](https://kysely-org.github.io/kysely-apidoc/classes/MergeQueryBuilder.html#using)
30+
- [whenMatched method](https://kysely-org.github.io/kysely-apidoc/classes/WheneableMergeQueryBuilder.html#whenMatched)
31+
- [thenUpdateSet method](https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenUpdateSet)
32+
- [thenDelete method](https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenDelete)
33+
- [thenDoNothing method](https://kysely-org.github.io/kysely-apidoc/classes/MatchedThenableMergeQueryBuilder.html#thenDoNothing)
34+
- [whenNotMatched method](https://kysely-org.github.io/kysely-apidoc/classes/WheneableMergeQueryBuilder.html#whenNotMatched)
35+
- [thenInsertValues method](https://kysely-org.github.io/kysely-apidoc/classes/NotMatchedThenableMergeQueryBuilder.html#thenInsertValues)
36+
:::

‎site/docs/examples/merge/_category_.json

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
{
2+
"label": "MERGE",
3+
"position": 7,
4+
"link": {
5+
"type": "generated-index",
6+
"description": "Short and simple examples of how to write MERGE queries."
7+
}
8+
}

‎site/docs/examples/select/0070-distinct.mdx

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,6 @@ title: 'Distinct'
44

55
# Distinct
66

7-
### Examples
8-
97
import {
108
Playground,
119
exampleSetup,

‎site/docs/examples/transactions/_category_.json

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
{
22
"label": "Transactions",
3-
"position": 6,
3+
"position": 8,
44
"link": {
55
"type": "generated-index",
66
"description": "Short and simple examples of how to use transactions."

‎site/package-lock.json

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎site/package.json

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
{
22
"name": "kysely",
3-
"version": "0.27.3",
3+
"version": "0.27.4",
44
"private": true,
55
"scripts": {
66
"docusaurus": "docusaurus",

‎site/src/components/Playground.tsx

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -77,8 +77,10 @@ interface PlaygroundState {
7777
export const exampleSetup = `import { Generated } from 'kysely'
7878
7979
export interface Database {
80-
person: PersonTable
81-
pet: PetTable
80+
person: PersonTable
81+
pet: PetTable
82+
wine: WineTable
83+
wine_stock_change: WineStockChangeTable
8284
}
8385
8486
interface PersonTable {
@@ -87,6 +89,7 @@ interface PersonTable {
8789
last_name: string | null
8890
created_at: Generated<Date>
8991
age: number
92+
has_pets: Generated<'Y' | 'N'>
9093
}
9194
9295
interface PetTable {
@@ -96,4 +99,16 @@ interface PetTable {
9699
species: 'cat' | 'dog'
97100
is_favorite: boolean
98101
}
102+
103+
interface WineTable {
104+
id: Generated<string>
105+
name: string
106+
stock: number
107+
}
108+
109+
interface WineStockChangeTable {
110+
id: Generated<string>
111+
wine_name: string
112+
stock_delta: number
113+
}
99114
`

‎site/src/css/custom.css

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,11 @@
3737
-14.8px 50.6px 59.3px -2.5px hsl(var(--shadow-color) / 0.1);
3838
}
3939

40+
/* remove default theme max-width in examples content */
41+
:root [class^='col docItemCol_'] {
42+
max-width: unset !important;
43+
}
44+
4045
[data-theme='dark'] {
4146
--ifm-color-primary: var(--sky9);
4247
--ifm-color-primary-dark: var(--sky10);

‎src/query-builder/select-query-builder.ts

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -454,10 +454,10 @@ export interface SelectQueryBuilder<DB, TB extends keyof DB, O>
454454
/**
455455
* Makes the selection distinct.
456456
*
457-
* <!-- siteExample("select", "Distinct", 70) -->
458-
*
459457
* ### Examples
460458
*
459+
* <!-- siteExample("select", "Distinct", 70) -->
460+
*
461461
* ```ts
462462
* const persons = await db.selectFrom('person')
463463
* .select('first_name')

‎src/query-creator.ts

Lines changed: 59 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -512,14 +512,18 @@ export class QueryCreator<DB> {
512512
*
513513
* ### Examples
514514
*
515+
* <!-- siteExample("merge", "Source row existence", 10) -->
516+
*
517+
* Update a target column based on the existence of a source row:
518+
*
515519
* ```ts
516520
* const result = await db
517-
* .mergeInto('person')
518-
* .using('pet', 'pet.owner_id', 'person.id')
519-
* .whenMatched((and) => and('has_pets', '!=', 'Y'))
521+
* .mergeInto('person as target')
522+
* .using('pet as source', 'source.owner_id', 'target.id')
523+
* .whenMatchedAnd('target.has_pets', '!=', 'Y')
520524
* .thenUpdateSet({ has_pets: 'Y' })
521-
* .whenNotMatched()
522-
* .thenDoNothing()
525+
* .whenNotMatchedBySourceAnd('target.has_pets', '=', 'Y')
526+
* .thenUpdateSet({ has_pets: 'N' })
523527
* .executeTakeFirstOrThrow()
524528
*
525529
* console.log(result.numChangedRows)
@@ -529,11 +533,56 @@ export class QueryCreator<DB> {
529533
*
530534
* ```sql
531535
* merge into "person"
532-
* using "pet" on "pet"."owner_id" = "person"."id"
533-
* when matched and "has_pets" != $1 then
534-
* update set "has_pets" = $2
535-
* when not matched then
536-
* do nothing
536+
* using "pet"
537+
* on "pet"."owner_id" = "person"."id"
538+
* when matched and "has_pets" != $1
539+
* then update set "has_pets" = $2
540+
* when not matched by source and "has_pets" = $3
541+
* then update set "has_pets" = $4
542+
* ```
543+
*
544+
* <!-- siteExample("merge", "Temporary changes table", 20) -->
545+
*
546+
* Merge new entries from a temporary changes table:
547+
*
548+
* ```ts
549+
* const result = await db
550+
* .mergeInto("wine as target")
551+
* .using(
552+
* "wine_stock_change as source",
553+
* "source.wine_name",
554+
* "target.name",
555+
* )
556+
* .whenNotMatchedAnd("source.stock_delta", ">", 0)
557+
* .thenInsertValues(({ ref }) => ({
558+
* name: ref("source.wine_name"),
559+
* stock: ref("source.stock_delta"),
560+
* }))
561+
* .whenMatchedAnd(
562+
* (eb) => eb("target.stock", "+", eb.ref("source.stock_delta")),
563+
* ">",
564+
* 0,
565+
* )
566+
* .thenUpdateSet("stock", (eb) =>
567+
* eb("target.stock", "+", eb.ref("source.stock_delta")),
568+
* )
569+
* .whenMatched()
570+
* .thenDelete()
571+
* .executeTakeFirstOrThrow()
572+
* ```
573+
*
574+
* The generated SQL (PostgreSQL):
575+
*
576+
* ```sql
577+
* merge into "wine" as "target"
578+
* using "wine_stock_change" as "source"
579+
* on "source"."wine_name" = "target"."name"
580+
* when not matched and "source"."stock_delta" > $1
581+
* then insert ("name", "stock") values ("source"."wine_name", "source"."stock_delta")
582+
* when matched and "target"."stock" + "source"."stock_delta" > $2
583+
* then update set "stock" = "target"."stock" + "source"."stock_delta"
584+
* when matched
585+
* then delete
537586
* ```
538587
*/
539588
mergeInto<TR extends keyof DB & string>(

0 commit comments

Comments
 (0)