1import assert from 'assert'; 2import { Asset } from 'expo-asset'; 3import * as FS from 'expo-file-system'; 4import { Platform } from 'expo-modules-core'; 5import * as SQLite from 'expo-sqlite'; 6 7export const name = 'SQLite'; 8 9// The version here needs to be the same as both the podspec and build.gradle for expo-sqlite 10const VERSION = '3.39.2'; 11 12// TODO: Only tests successful cases, needs to test error cases like bad database name etc. 13export function test(t) { 14 t.describe('SQLite', () => { 15 t.it('should be able to drop + create a table, insert, query', async () => { 16 const db = SQLite.openDatabase('test.db'); 17 await new Promise((resolve, reject) => { 18 db.transaction( 19 (tx) => { 20 const nop = () => {}; 21 const onError = (tx, error) => { 22 reject(error); 23 return false; 24 }; 25 26 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 27 tx.executeSql( 28 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64), k INT, j REAL);', 29 [], 30 nop, 31 onError 32 ); 33 tx.executeSql( 34 'INSERT INTO Users (name, k, j) VALUES (?, ?, ?)', 35 ['Tim Duncan', 1, 23.4], 36 nop, 37 onError 38 ); 39 tx.executeSql( 40 'INSERT INTO Users (name, k, j) VALUES ("Manu Ginobili", 5, 72.8)', 41 [], 42 nop, 43 onError 44 ); 45 tx.executeSql( 46 'INSERT INTO Users (name, k, j) VALUES ("Nikhilesh Sigatapu", 7, 42.14)', 47 [], 48 nop, 49 onError 50 ); 51 52 tx.executeSql( 53 'SELECT * FROM Users', 54 [], 55 (tx, results) => { 56 t.expect(results.rows.length).toEqual(3); 57 t.expect(results.rows.item(0).j).toBeCloseTo(23.4); 58 }, 59 onError 60 ); 61 }, 62 reject, 63 () => { 64 resolve(null); 65 } 66 ); 67 }); 68 69 if (Platform.OS !== 'web') { 70 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 71 t.expect(exists).toBeTruthy(); 72 } 73 }); 74 75 t.it(`should use specified SQLite version: ${VERSION}`, () => { 76 const db = SQLite.openDatabase('test.db'); 77 78 db.transaction((tx) => { 79 tx.executeSql('SELECT sqlite_version()', [], (_, results) => { 80 const queryVersion = results.rows._array[0]['sqlite_version()']; 81 t.expect(queryVersion).toEqual(VERSION); 82 }); 83 }); 84 }); 85 86 t.it(`unixepoch() is supported`, () => { 87 const db = SQLite.openDatabase('test.db'); 88 89 db.transaction((tx) => { 90 tx.executeSql('SELECT unixepoch()', [], (_, results) => { 91 const epoch = results.rows._array[0]['unixepoch()']; 92 t.expect(epoch).toBeTruthy(); 93 }); 94 }); 95 }); 96 97 if (Platform.OS !== 'web') { 98 t.it( 99 'should work with a downloaded .db file', 100 async () => { 101 await FS.downloadAsync( 102 Asset.fromModule(require('../assets/asset-db.db')).uri, 103 `${FS.documentDirectory}SQLite/downloaded.db` 104 ); 105 106 const db = SQLite.openDatabase('downloaded.db'); 107 await new Promise((resolve, reject) => { 108 db.transaction( 109 (tx) => { 110 const onError = (tx, error) => { 111 reject(error); 112 return false; 113 }; 114 tx.executeSql( 115 'SELECT * FROM Users', 116 [], 117 (tx, results) => { 118 t.expect(results.rows.length).toEqual(3); 119 t.expect(results.rows._array[0].j).toBeCloseTo(23.4); 120 }, 121 onError 122 ); 123 }, 124 reject, 125 () => { 126 resolve(null); 127 } 128 ); 129 }); 130 db.closeAsync(); 131 }, 132 30000 133 ); 134 } 135 136 t.it('should be able to recreate db from scratch by deleting file', async () => { 137 { 138 const db = SQLite.openDatabase('test.db'); 139 await new Promise((resolve, reject) => { 140 db.transaction( 141 (tx) => { 142 const nop = () => {}; 143 const onError = (tx, error) => { 144 reject(error); 145 return false; 146 }; 147 148 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 149 tx.executeSql( 150 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64), k INT, j REAL);', 151 [], 152 nop, 153 onError 154 ); 155 tx.executeSql( 156 'INSERT INTO Users (name, k, j) VALUES (?, ?, ?)', 157 ['Tim Duncan', 1, 23.4], 158 nop, 159 onError 160 ); 161 162 tx.executeSql( 163 'SELECT * FROM Users', 164 [], 165 (tx, results) => { 166 t.expect(results.rows.length).toEqual(1); 167 }, 168 onError 169 ); 170 }, 171 reject, 172 () => { 173 resolve(null); 174 } 175 ); 176 }); 177 } 178 179 if (Platform.OS !== 'web') { 180 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 181 t.expect(exists).toBeTruthy(); 182 } 183 184 if (Platform.OS !== 'web') { 185 await FS.deleteAsync(`${FS.documentDirectory}SQLite/test.db`); 186 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 187 t.expect(exists).toBeFalsy(); 188 } 189 190 { 191 const db = SQLite.openDatabase('test.db'); 192 await new Promise((resolve, reject) => { 193 db.transaction( 194 (tx) => { 195 const nop = () => {}; 196 const onError = (tx, error) => { 197 reject(error); 198 return false; 199 }; 200 201 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 202 tx.executeSql( 203 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64), k INT, j REAL);', 204 [], 205 nop, 206 onError 207 ); 208 tx.executeSql( 209 'SELECT * FROM Users', 210 [], 211 (tx, results) => { 212 t.expect(results.rows.length).toEqual(0); 213 }, 214 onError 215 ); 216 217 tx.executeSql( 218 'INSERT INTO Users (name, k, j) VALUES (?, ?, ?)', 219 ['Tim Duncan', 1, 23.4], 220 nop, 221 onError 222 ); 223 tx.executeSql( 224 'SELECT * FROM Users', 225 [], 226 (tx, results) => { 227 t.expect(results.rows.length).toEqual(1); 228 }, 229 onError 230 ); 231 }, 232 reject, 233 () => { 234 resolve(null); 235 } 236 ); 237 }); 238 } 239 }); 240 241 t.it('should maintain correct type of potentialy null bind parameters', async () => { 242 const db = SQLite.openDatabase('test.db'); 243 await new Promise((resolve, reject) => { 244 db.transaction( 245 (tx) => { 246 const nop = () => {}; 247 const onError = (tx, error) => { 248 reject(error); 249 return false; 250 }; 251 252 tx.executeSql('DROP TABLE IF EXISTS Nulling;', [], nop, onError); 253 tx.executeSql( 254 'CREATE TABLE IF NOT EXISTS Nulling (id INTEGER PRIMARY KEY NOT NULL, x NUMERIC, y NUMERIC)', 255 [], 256 nop, 257 onError 258 ); 259 tx.executeSql('INSERT INTO Nulling (x, y) VALUES (?, ?)', [null, null], nop, onError); 260 tx.executeSql('INSERT INTO Nulling (x, y) VALUES (null, null)', [], nop, onError); 261 262 tx.executeSql( 263 'SELECT * FROM Nulling', 264 [], 265 (tx, results) => { 266 t.expect(results.rows.item(0).x).toBeNull(); 267 t.expect(results.rows.item(0).y).toBeNull(); 268 t.expect(results.rows.item(1).x).toBeNull(); 269 t.expect(results.rows.item(1).y).toBeNull(); 270 }, 271 onError 272 ); 273 }, 274 reject, 275 () => { 276 resolve(null); 277 } 278 ); 279 }); 280 281 if (Platform.OS !== 'web') { 282 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 283 t.expect(exists).toBeTruthy(); 284 } 285 }); 286 287 // Do not try to test PRAGMA statements support in web 288 // as it is expected to not be working. 289 // See https://stackoverflow.com/a/10298712 290 if (Platform.OS !== 'web') { 291 t.it('should support PRAGMA statements', async () => { 292 const db = SQLite.openDatabase('test.db'); 293 await new Promise((resolve, reject) => { 294 db.transaction( 295 (tx) => { 296 const nop = () => {}; 297 const onError = (tx, error) => { 298 reject(error); 299 return false; 300 }; 301 302 tx.executeSql('DROP TABLE IF EXISTS SomeTable;', [], nop, onError); 303 tx.executeSql( 304 'CREATE TABLE IF NOT EXISTS SomeTable (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 305 [], 306 nop, 307 onError 308 ); 309 // a result-returning pragma 310 tx.executeSql( 311 'PRAGMA table_info(SomeTable);', 312 [], 313 (tx, results) => { 314 t.expect(results.rows.length).toEqual(2); 315 t.expect(results.rows.item(0).name).toEqual('id'); 316 t.expect(results.rows.item(1).name).toEqual('name'); 317 }, 318 onError 319 ); 320 // a no-result pragma 321 tx.executeSql('PRAGMA case_sensitive_like = true;', [], nop, onError); 322 // a setter/getter pragma 323 tx.executeSql('PRAGMA user_version = 123;', [], nop, onError); 324 tx.executeSql( 325 'PRAGMA user_version;', 326 [], 327 (tx, results) => { 328 t.expect(results.rows.length).toEqual(1); 329 t.expect(results.rows.item(0).user_version).toEqual(123); 330 }, 331 onError 332 ); 333 }, 334 reject, 335 () => { 336 resolve(null); 337 } 338 ); 339 }); 340 }); 341 } 342 343 t.it('should return correct rowsAffected value', async () => { 344 const db = SQLite.openDatabase('test.db'); 345 await new Promise((resolve, reject) => { 346 db.transaction( 347 (tx) => { 348 const nop = () => {}; 349 const onError = (tx, error) => { 350 reject(error); 351 return false; 352 }; 353 354 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 355 tx.executeSql( 356 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 357 [], 358 nop, 359 onError 360 ); 361 tx.executeSql( 362 'INSERT INTO Users (name) VALUES (?), (?), (?)', 363 ['name1', 'name2', 'name3'], 364 nop, 365 onError 366 ); 367 }, 368 reject, 369 () => { 370 resolve(null); 371 } 372 ); 373 }); 374 await new Promise((resolve, reject) => { 375 db.transaction( 376 (tx) => { 377 const onError = (tx, error) => { 378 reject(error); 379 return false; 380 }; 381 tx.executeSql( 382 'DELETE FROM Users WHERE name=?', 383 ['name1'], 384 (tx, results) => { 385 t.expect(results.rowsAffected).toEqual(1); 386 }, 387 onError 388 ); 389 tx.executeSql( 390 'DELETE FROM Users WHERE name=? OR name=?', 391 ['name2', 'name3'], 392 (tx, results) => { 393 t.expect(results.rowsAffected).toEqual(2); 394 }, 395 onError 396 ); 397 tx.executeSql( 398 // ensure deletion succeedeed 399 'SELECT * from Users', 400 [], 401 (tx, results) => { 402 t.expect(results.rows.length).toEqual(0); 403 }, 404 onError 405 ); 406 }, 407 reject, 408 () => { 409 resolve(null); 410 } 411 ); 412 }); 413 }); 414 415 if (Platform.OS !== 'web') { 416 // It is not expected to work on web, since we cannot execute PRAGMA to enable foreign keys support 417 t.it('should return correct rowsAffected value when deleting cascade', async () => { 418 const db = SQLite.openDatabase('test.db'); 419 db.exec([{ sql: 'PRAGMA foreign_keys = ON;', args: [] }], false, () => {}); 420 await new Promise((resolve, reject) => { 421 db.transaction( 422 (tx) => { 423 const nop = () => {}; 424 const onError = (tx, error) => { 425 reject(error); 426 return false; 427 }; 428 429 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 430 tx.executeSql('DROP TABLE IF EXISTS Posts;', [], nop, onError); 431 tx.executeSql( 432 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 433 [], 434 nop, 435 onError 436 ); 437 tx.executeSql( 438 'CREATE TABLE IF NOT EXISTS Posts (post_id INTEGER PRIMARY KEY NOT NULL, content VARCHAR(64), userposted INTEGER, FOREIGN KEY(userposted) REFERENCES Users(user_id) ON DELETE CASCADE);', 439 [], 440 nop, 441 onError 442 ); 443 tx.executeSql( 444 'INSERT INTO Users (name) VALUES (?), (?), (?)', 445 ['name1', 'name2', 'name3'], 446 nop, 447 onError 448 ); 449 450 tx.executeSql( 451 'INSERT INTO Posts (content, userposted) VALUES (?, ?), (?, ?), (?, ?)', 452 ['post1', 1, 'post2', 1, 'post3', 2], 453 nop, 454 onError 455 ); 456 tx.executeSql('PRAGMA foreign_keys=off;', [], nop, onError); 457 }, 458 reject, 459 () => { 460 resolve(null); 461 } 462 ); 463 }); 464 await new Promise((resolve, reject) => { 465 db.transaction( 466 (tx) => { 467 const nop = () => {}; 468 const onError = (tx, error) => { 469 reject(error); 470 return false; 471 }; 472 tx.executeSql('PRAGMA foreign_keys=on;', [], nop, onError); 473 tx.executeSql( 474 'DELETE FROM Users WHERE name=?', 475 ['name1'], 476 (tx, results) => { 477 t.expect(results.rowsAffected).toEqual(1); 478 }, 479 onError 480 ); 481 tx.executeSql( 482 'DELETE FROM Users WHERE name=? OR name=?', 483 ['name2', 'name3'], 484 (tx, results) => { 485 t.expect(results.rowsAffected).toEqual(2); 486 }, 487 onError 488 ); 489 490 tx.executeSql( 491 // ensure deletion succeeded 492 'SELECT * from Users', 493 [], 494 (tx, results) => { 495 t.expect(results.rows.length).toEqual(0); 496 }, 497 onError 498 ); 499 500 tx.executeSql( 501 'SELECT * from Posts', 502 [], 503 (tx, results) => { 504 t.expect(results.rows.length).toEqual(0); 505 }, 506 onError 507 ); 508 tx.executeSql('PRAGMA foreign_keys=off;', [], nop, onError); 509 }, 510 reject, 511 () => { 512 resolve(null); 513 } 514 ); 515 }); 516 }); 517 } 518 519 if (Platform.OS !== 'web') { 520 t.it('should delete db on filesystem from the `deleteAsync()` call', async () => { 521 const db = SQLite.openDatabase('test.db'); 522 let fileInfo = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 523 t.expect(fileInfo.exists).toBeTruthy(); 524 525 await db.closeAsync(); 526 await db.deleteAsync(); 527 fileInfo = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 528 t.expect(fileInfo.exists).toBeFalsy(); 529 }); 530 } 531 }); 532 533 if (Platform.OS !== 'web') { 534 t.describe('SQLiteAsync', () => { 535 const throws = async (run) => { 536 let error = null; 537 try { 538 await run(); 539 } catch (e) { 540 error = e; 541 } 542 t.expect(error).toBeTruthy(); 543 }; 544 545 t.it('should support async transaction', async () => { 546 const db = SQLite.openDatabase('test.db'); 547 548 // create table 549 await db.transactionAsync(async (tx) => { 550 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 551 await tx.executeSqlAsync( 552 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 553 [] 554 ); 555 }); 556 557 // fetch data from network 558 async function fakeUserFetcher(userID) { 559 switch (userID) { 560 case 1: { 561 return Promise.resolve('Tim Duncan'); 562 } 563 case 2: { 564 return Promise.resolve('Manu Ginobili'); 565 } 566 case 3: { 567 return Promise.resolve('Nikhilesh Sigatapu'); 568 } 569 default: { 570 return null; 571 } 572 } 573 } 574 575 const userName = await fakeUserFetcher(1); 576 await db.transactionAsync(async (tx) => { 577 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', [userName]); 578 const result = await tx.executeSqlAsync('SELECT * FROM Users LIMIT 1'); 579 assert(!isResultSetError(result)); 580 const currentUser = result.rows[0].name; 581 t.expect(currentUser).toEqual('Tim Duncan'); 582 }); 583 }); 584 585 t.it('should load crsqlite extension correctly', async () => { 586 const db = SQLite.openDatabase('test.db'); 587 await db.transactionAsync(async (tx) => { 588 await tx.executeSqlAsync('DROP TABLE IF EXISTS foo;', []); 589 await tx.executeSqlAsync('create table foo (a primary key, b);', []); 590 await tx.executeSqlAsync('select crsql_as_crr("foo");', []); 591 await tx.executeSqlAsync('insert into foo (a,b) values (1,2);', []); 592 await tx.executeSqlAsync('insert into foo (a,b) values (1,2);', []); 593 const result = await tx.executeSqlAsync('select * from crsql_changes;', []); 594 assert(!isResultSetError(result)); 595 const table = result.rows[0].table; 596 const value = result.rows[0].val; 597 t.expect(table).toEqual('foo'); 598 t.expect(value).toEqual(2); 599 }); 600 }); 601 602 t.it('should support Promise.all', async () => { 603 const db = SQLite.openDatabase('test.db'); 604 605 // create table 606 await db.transactionAsync(async (tx) => { 607 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 608 await tx.executeSqlAsync( 609 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 610 [] 611 ); 612 }); 613 614 await db.transactionAsync(async (tx) => { 615 await Promise.all([ 616 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['aaa']), 617 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['bbb']), 618 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['ccc']), 619 ]); 620 621 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 622 assert(!isResultSetError(result)); 623 const recordCount = result.rows[0]['COUNT(*)']; 624 t.expect(recordCount).toEqual(3); 625 }); 626 }); 627 628 t.it( 629 'should return `could not prepare ...` error when having write statements in readOnly transaction', 630 async () => { 631 const db = SQLite.openDatabase('test.db'); 632 633 // create table in readOnly transaction 634 await db.transactionAsync(async (tx) => { 635 const result = await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 636 assert(isResultSetError(result)); 637 t.expect(result.error).toBeDefined(); 638 t.expect(result.error.message).toContain('could not prepare '); 639 }, true); 640 } 641 ); 642 643 t.it('should rollback transaction when exception happens inside a transaction', async () => { 644 const db = SQLite.openDatabase('test.db'); 645 646 // create table 647 await db.transactionAsync(async (tx) => { 648 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 649 await tx.executeSqlAsync( 650 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 651 [] 652 ); 653 }); 654 await db.transactionAsync(async (tx) => { 655 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['aaa']); 656 }); 657 await db.transactionAsync(async (tx) => { 658 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 659 assert(!isResultSetError(result)); 660 const recordCount = result.rows[0]['COUNT(*)']; 661 t.expect(recordCount).toEqual(1); 662 }, true); 663 664 await throws(() => 665 db.transactionAsync(async (tx) => { 666 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['bbb']); 667 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['ccc']); 668 // exeuting invalid sql statement will throw an exception 669 await tx.executeSqlAsync(null); 670 }) 671 ); 672 673 await db.transactionAsync(async (tx) => { 674 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 675 assert(!isResultSetError(result)); 676 const recordCount = result.rows[0]['COUNT(*)']; 677 t.expect(recordCount).toEqual(1); 678 }, true); 679 }); 680 681 t.it('should support async PRAGMA statements', async () => { 682 const db = SQLite.openDatabase('test.db'); 683 await db.transactionAsync(async (tx) => { 684 await tx.executeSqlAsync('DROP TABLE IF EXISTS SomeTable;', []); 685 await tx.executeSqlAsync( 686 'CREATE TABLE IF NOT EXISTS SomeTable (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 687 [] 688 ); 689 // a result-returning pragma 690 let result = await tx.executeSqlAsync('PRAGMA table_info(SomeTable);', []); 691 assert(!isResultSetError(result)); 692 t.expect(result.rows.length).toEqual(2); 693 t.expect(result.rows[0].name).toEqual('id'); 694 t.expect(result.rows[1].name).toEqual('name'); 695 // a no-result pragma 696 await tx.executeSqlAsync('PRAGMA case_sensitive_like = true;', []); 697 // a setter/getter pragma 698 await tx.executeSqlAsync('PRAGMA user_version = 123;', []); 699 result = await tx.executeSqlAsync('PRAGMA user_version;', []); 700 assert(!isResultSetError(result)); 701 t.expect(result.rows.length).toEqual(1); 702 t.expect(result.rows[0].user_version).toEqual(123); 703 }); 704 }); 705 }); // t.describe('SQLiteAsync') 706 } 707} 708 709function isResultSetError( 710 result: SQLite.ResultSet | SQLite.ResultSetError 711): result is SQLite.ResultSetError { 712 return 'error' in result; 713} 714