Jest Integration Test can't open database

[deepakaggarwal7]

Expected Behavior

Be able to open a pre-populated database and run tests to ensure that it has expected schema and data. (I am not talking about mocks - That's not the need. Need to open real database).

Current Behavior

test is unable to open database

Possible Solution

Not sure if it has to do with location of the file or the library cannot run standalone in jest

Steps to Reproduce (for bugs)

import SQLite from 'react-native-sqlite-storage';

describe('config', () => {
  it('should be able to open database', () => {
    console.log(SQLite);
    SQLite.openDatabase(
      {
        name: 'users.db',
        createFromLocation: '~www/users.db'
      },
      () => console.log('success'),
      () => console.log('fail')
    );
  });
});

ERROR:

 TypeError: Cannot read property 'open' of undefined

      4 |   it('should be able to open database', () => {
      5 |     console.log(SQLite);
    > 6 |     SQLite.openDatabase(
        |            ^
      7 |       {
      8 |         name: 'users.db',
      9 |         createFromLocation: '~www/users.db'

      at Object.exec (node_modules/react-native-sqlite-storage/lib/sqlite.core.js:88:3)
      at SQLitePlugin.open (node_modules/react-native-sqlite-storage/lib/sqlite.core.js:285:12)
      at new SQLitePlugin (node_modules/react-native-sqlite-storage/lib/sqlite.core.js:116:8)
      at SQLiteFactory.call (node_modules/react-native-sqlite-storage/lib/sqlite.core.js:785:10)
      at SQLiteFactory.openDatabase (node_modules/react-native-sqlite-storage/lib/sqlite.core.js:77:18)
      at Object.<anonymous> (__tests__/Utils/sqlite.spec.js:6:12)

  console.log __tests__/Utils/sqlite.spec.js:5

Note: database is inside www folder, relative to the spec file having test

Context

I want to write a data layer that my react native app can use to get data. I want to test this data layer's methods but need to be able to open the database.

Your Environment

Mac OS Jest 24.9.0

[lucascnascimento]

I'm running into the same problem

[asavardplante]

~Bump, I'm wondering the same thing~

I don't know if you still need it, but I managed to make the test passes by adding SQLite.enablePromise(true); after importing SQLite

import SQLite from 'react-native-sqlite-storage';
SQLite.enablePromise(true);

const DB_NAME = 'database.db';

describe('open Database', () => {
  it('should be able to open database', () => {
    console.log(SQLite);
    const db = SQLite.openDatabase(
      {
        name: DB_NAME,
        createFromLocation: 1,
      },
      () => console.log('success'),
      () => console.log('fail'),
    );
  });
});

[haseebeqx]

It is a clear case of using mocks. for methods like open it needs to pass it to a native module. And native module will be undifined.

Here is an example mock I use,

jest.mock('react-native-sqlite-storage', () => ({
  DEBUG: jest.fn,
  enablePromise: jest.fn(),
  openDatabase: (...args) => {
    return {
      transaction: (...args) => Promise.resolve({
        executeSql: (query) => {
          return Promise.resolve([]);
        }
      }),
      cleanDb: ()=> Promise.resolve(),
      executeSql: (query) => {
        return Promise.resolve([]);
      }
    };
  },
}));

[PawelLampliapps]

Is it even possible to test created queries using jest? I have the same issue Cannot read property 'open' of undefined and after all I think (like author of the Issue wrote) that this library can not be runned standalone in jest :/

[PawelLampliapps]

Ok, definitely it is not possible to run react-native-sqlite-storage in jest because it uses native module for SQLite operations (I may be wrong and it's possible, but I am not the jest expert and I couldn't find anything for that). However, if you just want to test your queries/functions with jest that uses react-native-sqlite-storage you can do something like this below. This solution uses sqlite3 library. The main goal is to use this library and create objects with similar interface like react-native-sqlite-storage have. In a result we will be able to test our queries because sqlite3 will create database in memory.

My functions for accessing data looks like this (just to let you know what will be tested):

import { SQLiteDatabase } from 'react-native-sqlite-storage';
...
export const getUser = async (db: SQLiteDatabase, userID: string) => {
  let users: User[] = [];
  await db.transaction(tx => {
    tx.executeSql(
      'SELECT * FROM Users WHERE id = ?',
      [userID],
      (tx, results) => {
        users = results.rows.raw();
      },
    );
  });
  return users;
};

So, example test in jest should look like this (but it will not work because of that aspect I wrote at the beginning):

describe('queries test', () => {
  it('get user from database', async () => {
    // Setup
    const db = await SQLite.openDatabase({
      name: 'test.db',
      location: 'default',
      createFromLocation: '~www/test.db',
    });
    // Execute
    const users = getUser(db, 'string-id');

    // check if user match expectations
  });
});

So now let's create classes that will have the same interface like SQLiteDatabase, but it will use sqlite3 library

Class SQLiteExecutor will replace SQLite. It will open our database in memory. If you need to make openDatabase more similar to original (name, location, createFromLocation as params) just give it proper parameter type. Original openDatabase returns SQLiteDatabase, so I created another class: DatabaseExecutor visible below.

import { Database } from 'sqlite3';
import { ResultSet } from 'react-native-sqlite-storage';
const sqlite3 = require('sqlite3').verbose();

export class SQLiteExecutor {
  constructor() {}
  public static openDatabase(name: string): DatabaseExecutor {
    return new DatabaseExecutor(name);
  }
}

As you can see, here is a creation of sqlite3 object instance in constructor that takes name as an argument (It can take more arguments, but I only need :memory:). Another important thing, SQLiteDatabase have many others functions, but I only needed transaction and close. However, the rest can be probably easily done based on that one I created. transaction function in SQLiteDatabase takes callback with Transaction class as an argument, so I created TransactionExecutor.

export class DatabaseExecutor {
  private db: Database;
  constructor(name: string) {
    this.db = new sqlite3.Database(name);
  }

  public transaction(calback: (tx: TransactionExecutor) => void): Promise<any> {
    return new Promise((resolve, reject) => {
      const transaction = new TransactionExecutor(this.db, resolve, reject);
      this.db.serialize(() => {
        calback(transaction);
      });
    });
  }

  public close() {
    this.db.close();
  }
}

Variable callsAmount is used in case of multiple queries in transaction like:

  await db.transaction(tx => {
    tx.executeSql(...); // First query in transaction
    tx.executeSql(...); // Second query in transaction
  });

export class TransactionExecutor {
  private db: Database;
  private resolve: () => void;
  private reject: (mess: any) => void;
  private callsAmount = 0;
  constructor(
    _db: Database,
    _resolve: (value?: unknown) => void,
    _reject: (reason?: any) => void,
  ) {
    this.db = _db;
    this.resolve = _resolve;
    this.reject = _reject;
  }

  public executeSql(
    statement: string,
    args?: string[],
    callback?: (tx: TransactionExecutor, results: ResultSet) => void,
    errorCallback?: (tx: TransactionExecutor, results: any) => void,
  ): void {
    this.callsAmount++;
    // sqlite3 have many dedicated methods for calling sql, but 'all' is quite universal 
    this.db.all(statement, args, (err: any, rows: any[]) => {  
      if (err) {
        if (errorCallback) errorCallback(this, err);
        this.reject(err);
      }

      const tmp: ResultSet = { // sqlite3 returns rows in array, so we need to imitate ResultSet object 
        insertId: 0,
        rowsAffected: 0, // As you can see, unfortunately we are losing insertId and rowsAffected data, I am not sure if sqlite3 returns such a information
        rows: {
          length: rows.length,
          raw: () => rows,
          item: (index: number) => rows[index],
        },
      };
      if (callback) callback(this, tmp);

      this.callsAmount--;

      if (this.callsAmount == 0) {
        this.resolve();
      }
    });
  }
}

After creating that classes I can make jest test like this:

describe('queries test', () => {
  it('get user from database', async () => {
    // Setup
    const db = SQLiteExecutor.openDatabase(':memory:')

    // Execute
    const users = getUser((db as undefined) as SQLiteDatabase, 'string-id');

    // check if user match expectations
  });
});

Of course, one have to 'cast' db to SQLiteDatabase because types are not matching, bu interfaces of created objects should match and you would be able to test your queries. That solution worked for me.