Using knex (websql or sqlite3 dialect?) with Expo.SQLite

#1

I’m trying to use knex in combination with https://github.com/stems/join-monster to try to tame my sqlite tables using some autogenerated GraphQL goodness. I got a PoC working in NodeJS (found the .db file on my Mac, pointed knex with the sqlite3 dialect at it) but when I tried to adapt the code to work in React Native, all of my attempts have fallen flat.

I’ve tried to use the sqlite3 dialect, pointing knex at the .db file using FileSystem to resolve it. However, sqlite3 is a dependency for this to work (and which itself depends on core node libs like fs), so it seems like a non-starter.

Now I’m trying to use the websql dialect, but the documentation is sparse and I can’t seem to figure out how to make it play nice with RN. Oddly enough, I still get the same Unable to resolve module sqlite3 error even when using the websql dialect.

Is this feasible at all or am I barking up the wrong tree here?

Here’s my NodeJS code to give you an idea about what I’m trying to do:

const joinMonsterAdapt = require('join-monster-graphql-tools-adapter')
const typeDefs = require('./types')

const joinMonster = require('join-monster').default
const { makeExecutableSchema } = require('graphql-tools')

const resolvers = {
  Query: {
    // call joinMonster in the "attraction" resolver, and all child fields that are tagged with "sqlTable" are handled!
    attraction(parent, args, ctx, resolveInfo) {
      return joinMonster(resolveInfo, ctx, sql => {
        return knex.raw(sql)
      })
    }
  },
  Attraction: {}
}

const schema = makeExecutableSchema({
  typeDefs,
  resolvers
})

// tag the schema types with the extra join monster metadata
joinMonsterAdapt(schema, {
  Query: {
    fields: {
      // add a function to generate the "where condition"
      attraction: {
        where: (table, args) => `${table}.id = "${args.id}"`
      }
    }
  },
  Attraction: {
    // map the Attraction object type to its SQL table
    sqlTable: 'attractions',
    uniqueKey: 'id',
    // tag the Attraction's fields
    fields: {
      images: {
        sqlJoin: (attractionTable, imageTable) => `${attractionTable}.id = ${imageTable}.attraction_id`,
      }
    }
  },
  Image: {
    // map the Image object type to its SQL table
    sqlTable: 'attraction_images',
    uniqueKey: 'id',
    // tag the Image's fields
    fields: {
      smThumbnailUrl: {
        sqlColumn: 'sm_thumbnail_url'
      },
      lgThumbnailUrl: {
        sqlColumn: 'lg_thumbnail_url'
      },
    }
  },
})

/*
* knex setup (TODO: REPLACE with Expo.SQLite methods)
*/
const dataFilePath = '/Users/s/Library/Developer/CoreSimulator/Devices/XYZ/data/Containers/Data/Application/ABC/Documents/ExponentExperienceData/%40smoll%2Fstuff/SQLite/offlinemaps.db' // make this the path to the database file
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: dataFilePath
  }
})

/****** TEST QUERY ******/
const { graphql } = require('graphql')

const query = `{
  attraction(id: "QXR0cmFjdGlvbjo4Mjgx") {
    id
    name
    address
    images {
      id
      smThumbnailUrl
    }
  }
}`
graphql(schema, query)
  .then(res => console.log('res', JSON.stringify(res)))
  .catch(err => console.error('err', err))
#2

I stumbled on this repo, where the author passes a promise wrapped around the SQLite transaction, so it looks like skipping knex altogether is a viable solution :+1:

Also, thanks to the author for using “node-libs-browser”, I was just wondering how I was supposed to get around all the calls to node core libs like buffer, os, etc.