1
// Copyright (c) 2023 Cloudflare, Inc.
2
// Licensed under the Apache 2.0 license found in the LICENSE file or at:
3
//     https://opensource.org/licenses/Apache-2.0
4
 
5
import * as assert from 'node:assert';
6
 
7
// Recurse through nested objects/arrays looking for 'anything' and deleting that
8
// key/value from both objects. Gives us a way to get expect.toMatchObject behavior
9
// with only deepEqual
10
const anything = Symbol('anything');
11
const deleteAnything = (expected, actual) => {
12
  Object.entries(expected).forEach(([k, v]) => {
13
    if (v === anything) {
14
      // eslint-disable-next-line @typescript-eslint/no-dynamic-delete
15
      delete actual[k];
16
      // eslint-disable-next-line @typescript-eslint/no-dynamic-delete
17
      delete expected[k];
18
    } else if (typeof v === 'object' && typeof actual[k] === 'object') {
19
      deleteAnything(expected[k], actual[k]);
20
    }
21
  });
22
};
23
 
24
// Test helpers, since I want everything to run in sequence but I don't
25
// want to lose context about which assertion failed.
26
export const itShould = async (description, ...assertions) => {
27
  if (assertions.length % 2 !== 0)
28
    throw new Error('itShould takes pairs of cb, expected args');
29
 
30
  try {
31
    for (let i = 0; i < assertions.length; i += 2) {
32
      const cb = assertions[i];
33
      const expected = assertions[i + 1];
34
      const actual = await cb();
35
      deleteAnything(expected, actual);
36
      try {
37
        assert.deepEqual(actual, expected);
38
      } catch (e) {
39
        console.log(actual);
40
        throw e;
41
      }
42
    }
43
  } catch (e) {
44
    throw new Error(`TEST ERROR!\n❌ Failed to ${description}\n${e.message}`);
45
  }
46
};
47
 
48
// Make it easy to specify only a the meta properties we're interested in.
49
// Anything specified here as `anything` won't be checked.
50
const meta = (values) => ({
51
  duration: anything,
52
  served_by: anything,
53
  served_by_primary: anything,
54
  served_by_region: anything,
55
  served_by_colo: anything,
56
  timings: anything,
57
  changes: anything,
58
  last_row_id: anything,
59
  changed_db: anything,
60
  size_after: anything,
61
  rows_read: anything,
62
  rows_written: anything,
63
  total_attempts: anything,
64
  ...values,
65
});
66
 
67
export async function testD1ApiQueriesHappyPath(DB) {
68
  await itShould(
69
    'create a Users table',
70
    () =>
71
      DB.prepare(
72
        ` CREATE TABLE users
73
        (
74
            user_id    INTEGER PRIMARY KEY,
75
            name       TEXT,
76
            home       TEXT,
77
            features   TEXT,
78
            land_based BOOLEAN
79
        );`
80
      ).run(),
81
    { success: true, results: [], meta: anything }
82
  );
83
 
84
  await itShould(
85
    'select an empty set',
86
    () => DB.prepare(`SELECT * FROM users;`).all(),
87
    {
88
      success: true,
89
      results: [],
90
      meta: meta({ changed_db: false }),
91
    }
92
  );
93
 
94
  await itShould(
95
    'have no results for .run()',
96
    () => DB.prepare(`SELECT * FROM users;`).run(),
97
    { success: true, results: [], meta: anything }
98
  );
99
 
100
  await itShould(
101
    'delete no rows ok',
102
    () => DB.prepare(`DELETE FROM users;`).run(),
103
    { success: true, results: [], meta: anything }
104
  );
105
 
106
  await itShould(
107
    'insert a few rows with a returning statement',
108
    () =>
109
      DB.prepare(
110
        `
111
        INSERT INTO users (name, home, features, land_based) VALUES
112
          ('Albert Ross', 'sky', 'wingspan', false),
113
          ('Al Dente', 'bowl', 'mouthfeel', true)
114
        RETURNING *
115
    `
116
      ).all(),
117
    {
118
      success: true,
119
      results: [
120
        {
121
          user_id: 1,
122
          name: 'Albert Ross',
123
          home: 'sky',
124
          features: 'wingspan',
125
          land_based: 0,
126
        },
127
        {
128
          user_id: 2,
129
          name: 'Al Dente',
130
          home: 'bowl',
131
          features: 'mouthfeel',
132
          land_based: 1,
133
        },
134
      ],
135
      meta: anything,
136
    }
137
  );
138
 
139
  await itShould(
140
    'delete two rows ok',
141
    () => DB.prepare(`DELETE FROM users;`).run(),
142
    { success: true, results: [], meta: anything }
143
  );
144
 
145
  // In an earlier implementation, .run() called a different endpoint that threw on RETURNING clauses.
146
  await itShould(
147
    'insert a few rows with a returning statement, but ignore the result without erroring',
148
    () =>
149
      DB.prepare(
150
        `
151
        INSERT INTO users (name, home, features, land_based) VALUES
152
          ('Albert Ross', 'sky', 'wingspan', false),
153
          ('Al Dente', 'bowl', 'mouthfeel', true)
154
        RETURNING *
155
    `
156
      ).run(),
157
    {
158
      success: true,
159
      results: [
160
        {
161
          user_id: 1,
162
          name: 'Albert Ross',
163
          home: 'sky',
164
          features: 'wingspan',
165
          land_based: 0,
166
        },
167
        {
168
          user_id: 2,
169
          name: 'Al Dente',
170
          home: 'bowl',
171
          features: 'mouthfeel',
172
          land_based: 1,
173
        },
174
      ],
175
      meta: anything,
176
    }
177
  );
178
 
179
  // Results format tests
180
 
181
  const select_1 = DB.prepare(`select 1;`);
182
  await itShould(
183
    'return simple results for select 1',
184
    () => select_1.all(),
185
    {
186
      results: [{ 1: 1 }],
187
      meta: anything,
188
      success: true,
189
    },
190
    () => select_1.raw(),
191
    [[1]],
192
    () => select_1.first(),
193
    { 1: 1 },
194
    () => select_1.first('1'),
195
    1
196
  );
197
 
198
  const select_all = DB.prepare(`SELECT * FROM users;`);
199
  await itShould(
200
    'return all users',
201
    () => select_all.all(),
202
    {
203
      results: [
204
        {
205
          user_id: 1,
206
          name: 'Albert Ross',
207
          home: 'sky',
208
          features: 'wingspan',
209
          land_based: 0,
210
        },
211
        {
212
          user_id: 2,
213
          name: 'Al Dente',
214
          home: 'bowl',
215
          features: 'mouthfeel',
216
          land_based: 1,
217
        },
218
      ],
219
      meta: anything,
220
      success: true,
221
    },
222
    () => select_all.raw(),
223
    [
224
      [1, 'Albert Ross', 'sky', 'wingspan', 0],
225
      [2, 'Al Dente', 'bowl', 'mouthfeel', 1],
226
    ],
227
    () => select_all.first(),
228
    {
229
      user_id: 1,
230
      name: 'Albert Ross',
231
      home: 'sky',
232
      features: 'wingspan',
233
      land_based: 0,
234
    },
235
    () => select_all.first('name'),
236
    'Albert Ross'
237
  );
238
 
239
  const select_one = DB.prepare(`SELECT * FROM users WHERE user_id = ?;`);
240
 
241
  await itShould(
242
    'return the first user when bound with user_id = 1',
243
    () => select_one.bind(1).all(),
244
    {
245
      results: [
246
        {
247
          user_id: 1,
248
          name: 'Albert Ross',
249
          home: 'sky',
250
          features: 'wingspan',
251
          land_based: 0,
252
        },
253
      ],
254
      meta: anything,
255
      success: true,
256
    },
257
    () => select_one.bind(1).raw(),
258
    [[1, 'Albert Ross', 'sky', 'wingspan', 0]],
259
    () => select_one.bind(1).first(),
260
    {
261
      user_id: 1,
262
      name: 'Albert Ross',
263
      home: 'sky',
264
      features: 'wingspan',
265
      land_based: 0,
266
    },
267
    () => select_one.bind(1).first('name'),
268
    'Albert Ross'
269
  );
270
 
271
  await itShould(
272
    'return the second user when bound with user_id = 2',
273
    () => select_one.bind(2).all(),
274
    {
275
      results: [
276
        {
277
          user_id: 2,
278
          name: 'Al Dente',
279
          home: 'bowl',
280
          features: 'mouthfeel',
281
          land_based: 1,
282
        },
283
      ],
284
      meta: anything,
285
      success: true,
286
    },
287
    () => select_one.bind(2).raw(),
288
    [[2, 'Al Dente', 'bowl', 'mouthfeel', 1]],
289
    () => select_one.bind(2).first(),
290
    {
291
      user_id: 2,
292
      name: 'Al Dente',
293
      home: 'bowl',
294
      features: 'mouthfeel',
295
      land_based: 1,
296
    },
297
    () => select_one.bind(2).first('name'),
298
    'Al Dente'
299
  );
300
 
301
  await itShould(
302
    'return the results of two commands with batch',
303
    () => DB.batch([select_one.bind(2), select_one.bind(1)]),
304
    [
305
      {
306
        results: [
307
          {
308
            user_id: 2,
309
            name: 'Al Dente',
310
            home: 'bowl',
311
            features: 'mouthfeel',
312
            land_based: 1,
313
          },
314
        ],
315
        meta: anything,
316
        success: true,
317
      },
318
      {
319
        results: [
320
          {
321
            user_id: 1,
322
            name: 'Albert Ross',
323
            home: 'sky',
324
            features: 'wingspan',
325
            land_based: 0,
326
          },
327
        ],
328
        meta: anything,
329
        success: true,
330
      },
331
    ]
332
  );
333
 
334
  await itShould(
335
    'allow binding all types of parameters',
336
    () =>
337
      DB.prepare(`SELECT count(1) as count FROM users WHERE land_based = ?`)
338
        .bind(true)
339
        .first('count'),
340
    1,
341
    () =>
342
      DB.prepare(`SELECT count(1) as count FROM users WHERE land_based = ?`)
343
        .bind(false)
344
        .first('count'),
345
    1,
346
    () =>
347
      DB.prepare(`SELECT count(1) as count FROM users WHERE land_based = ?`)
348
        .bind(0)
349
        .first('count'),
350
    1,
351
    () =>
352
      DB.prepare(`SELECT count(1) as count FROM users WHERE land_based = ?`)
353
        .bind(1)
354
        .first('count'),
355
    1,
356
    () =>
357
      DB.prepare(`SELECT count(1) as count FROM users WHERE land_based = ?`)
358
        .bind(2)
359
        .first('count'),
360
    0
361
  );
362
 
363
  await itShould(
364
    'create two tables with overlapping column names',
365
    () =>
366
      DB.batch([
367
        DB.prepare(`CREATE TABLE abc (a INT, b INT, c INT);`),
368
        DB.prepare(`CREATE TABLE cde (c TEXT, d TEXT, e TEXT);`),
369
        DB.prepare(`INSERT INTO abc VALUES (1,2,3),(4,5,6);`),
370
        DB.prepare(
371
          `INSERT INTO cde VALUES ("A", "B", "C"),("D","E","F"),("G","H","I");`
372
        ),
373
      ]),
374
    [
375
      {
376
        success: true,
377
        results: [],
378
        meta: meta({
379
          changed_db: true,
380
          changes: 0,
381
          last_row_id: 2,
382
          rows_read: 1,
383
          rows_written: 2,
384
        }),
385
      },
386
      {
387
        success: true,
388
        results: [],
389
        meta: meta({
390
          changed_db: true,
391
          changes: 0,
392
          last_row_id: 2,
393
          rows_read: 1,
394
          rows_written: 2,
395
        }),
396
      },
397
      {
398
        success: true,
399
        results: [],
400
        meta: meta({
401
          changed_db: true,
402
          changes: 2,
403
          last_row_id: 2,
404
          rows_read: 0,
405
          rows_written: 2,
406
        }),
407
      },
408
      {
409
        success: true,
410
        results: [],
411
        meta: meta({
412
          changed_db: true,
413
          changes: 3,
414
          last_row_id: 3,
415
          rows_read: 0,
416
          rows_written: 3,
417
        }),
418
      },
419
    ]
420
  );
421
 
422
  await itShould(
423
    'still sadly lose data for duplicate columns in a join',
424
    () => DB.prepare(`SELECT * FROM abc, cde;`).all(),
425
    {
426
      success: true,
427
      results: [
428
        { a: 1, b: 2, c: 'A', d: 'B', e: 'C' },
429
        { a: 1, b: 2, c: 'D', d: 'E', e: 'F' },
430
        { a: 1, b: 2, c: 'G', d: 'H', e: 'I' },
431
        { a: 4, b: 5, c: 'A', d: 'B', e: 'C' },
432
        { a: 4, b: 5, c: 'D', d: 'E', e: 'F' },
433
        { a: 4, b: 5, c: 'G', d: 'H', e: 'I' },
434
      ],
435
      meta: meta({
436
        changed_db: false,
437
        changes: 0,
438
        rows_read: 8,
439
        rows_written: 0,
440
      }),
441
    }
442
  );
443
 
444
  await itShould(
445
    'not lose data for duplicate columns in a join using raw()',
446
    () => DB.prepare(`SELECT * FROM abc, cde;`).raw(),
447
    [
448
      [1, 2, 3, 'A', 'B', 'C'],
449
      [1, 2, 3, 'D', 'E', 'F'],
450
      [1, 2, 3, 'G', 'H', 'I'],
451
      [4, 5, 6, 'A', 'B', 'C'],
452
      [4, 5, 6, 'D', 'E', 'F'],
453
      [4, 5, 6, 'G', 'H', 'I'],
454
    ]
455
  );
456
 
457
  await itShould(
458
    'add columns using  .raw({ columnNames: true })',
459
    () => DB.prepare(`SELECT * FROM abc, cde;`).raw({ columnNames: true }),
460
    [
461
      ['a', 'b', 'c', 'c', 'd', 'e'],
462
      [1, 2, 3, 'A', 'B', 'C'],
463
      [1, 2, 3, 'D', 'E', 'F'],
464
      [1, 2, 3, 'G', 'H', 'I'],
465
      [4, 5, 6, 'A', 'B', 'C'],
466
      [4, 5, 6, 'D', 'E', 'F'],
467
      [4, 5, 6, 'G', 'H', 'I'],
468
    ]
469
  );
470
 
471
  await itShould(
472
    'not add columns using  .raw({ columnNames: false })',
473
    () => DB.prepare(`SELECT * FROM abc, cde;`).raw({ columnNames: false }),
474
    [
475
      [1, 2, 3, 'A', 'B', 'C'],
476
      [1, 2, 3, 'D', 'E', 'F'],
477
      [1, 2, 3, 'G', 'H', 'I'],
478
      [4, 5, 6, 'A', 'B', 'C'],
479
      [4, 5, 6, 'D', 'E', 'F'],
480
      [4, 5, 6, 'G', 'H', 'I'],
481
    ]
482
  );
483
 
484
  await itShould(
485
    'return 0 rows_written for IN clauses',
486
    () =>
487
      DB.prepare(
488
        `SELECT * from cde WHERE c IN ('A','B','C','X','Y','Z')`
489
      ).all(),
490
    {
491
      success: true,
492
      results: [{ c: 'A', d: 'B', e: 'C' }],
493
      meta: meta({ rows_read: 3, rows_written: 0 }),
494
    }
495
  );
496
 
497
  await itShould(
498
    'delete all created tables',
499
    () =>
500
      DB.batch([
501
        DB.prepare(`DROP TABLE users;`),
502
        DB.prepare(`DROP TABLE abc;`),
503
        DB.prepare(`DROP TABLE cde;`),
504
      ]),
505
    [
506
      {
507
        success: true,
508
        results: [],
509
        meta: meta({
510
          changed_db: true,
511
          changes: 0,
512
          last_row_id: 3,
513
          rows_read: anything,
514
          rows_written: 0,
515
        }),
516
      },
517
      {
518
        success: true,
519
        results: [],
520
        meta: meta({
521
          changed_db: true,
522
          changes: 0,
523
          last_row_id: 3,
524
          rows_read: anything,
525
          rows_written: 0,
526
        }),
527
      },
528
      {
529
        success: true,
530
        results: [],
531
        meta: meta({
532
          changed_db: true,
533
          changes: 0,
534
          last_row_id: 3,
535
          rows_read: anything,
536
          rows_written: 0,
537
        }),
538
      },
539
    ]
540
  );
541
}
542
 
543
// Regression test for https://github.com/cloudflare/workerd/pull/5218.
544
// exec() with invalid SQL should throw a proper D1 error, not a TypeError
545
// from accessing properties on undefined meta during span aggregation.
546
export async function testD1Exec(DB) {
547
  await itShould('run a simple exec', () => DB.exec('select 1'), {
548
    count: 1,
549
    duration: anything,
550
  });
551
 
552
  await assert.rejects(
553
    () => DB.exec('INVALID SQL'),
554
    (e) => {
555
      assert.notEqual(e.constructor, TypeError);
556
      assert.ok(
557
        e.message.includes('D1_EXEC_ERROR'),
558
        `Expected D1 error, got: ${e.message}`
559
      );
560
      return true;
561
    }
562
  );
563
}