Currently both caching and batching are optional and caching takes an arbitrary number of seconds to store the results.

Ideally, I would like to always implement batching (there's no reason not to that I can see) and allow cache configuration similar to Apollo's REST dataSource.

This project could really use some test coverage.

Jest is configured, but mocking and testing that the cache is working when expected is necessary.

For example:

https://github.com/apollographql/apollo-server/blob/master/packages/apollo-datasource-rest/src/RESTDataSource.ts#L117-L141

and a method to override:

https://github.com/apollographql/apollo-server/blob/master/packages/apollo-datasource-rest/src/RESTDataSource.ts#L100-L102

(I'm not asking for this feature for myself—just a suggestion for future dev ☺️)

Does anyone know what can cause this?

src/datasources/order-api.ts(17,8): error TS2551: Property 'cache' does not exist on type 'QueryBuilder<{}, DeferredKeySelection<{}, string, false, {}, false, {}, undefined>>'. Did you mean 'catch'?

Relevant packages:
"knex": "^2.0.0"
"datasource-sql": "^2.0.1",

Instead of just knexConfig. This way knex can be used outside of the data source. (Knex recommends a single instance per server.)

I have trouble refactoring the codes in the class corresponding to my database in order to adapt to the changes in v1.0.0 (particularly the removal of batching support).
Furthermore, ever since I updated my copy of the "datasource-sql" package, I get a server error with status code 500 when I initiate the app. Moving back to v0.2.0 prevents this from happening.

How do I rectify this without rolling back to v0.2.0?

I'm looking to support a customizable cache key strategy. It seems like a simple approach would just be to support an optional cacheKey parameter to cacheQuery (and the builder cache method) and only use the currently generated approach if the parameter is not provided. Thoughts?

Hi and thanks for the library.
Since I started using it I noticed that the connections to my db never get closed, and that results in me getting an sorry, too many clients already error quite too often.
I'm wondering if there might be something I'm doing wrong in the way I'm using the library or if I should be closing the connection manually at some point.

// server.js

const MainDataSource = require('./dataSource')

const config = {
  schema,
  extensions: process.env.NODE_ENV !== 'production' ? extensions : undefined,
  tracing: process.env.NODE_ENV !== 'production',
  dataSources: () => ({ db: new MainDataSource() }),
  context: async ({ req }) => {
    const token = req.headers.authorization || ''
    const user = await getUser({ token, dataSources: { db: new MainDataSource() } })

    return { user }
  },
  mocks: process.env.NODE_ENV === 'test'
}

// ./dataSource.js

const knexConfig = require('../knexfile')
const knex = knexConfig[process.env.NODE_ENV || 'development']

const Accounts = require('../modules/accounts/dataSource')
...

const MINUTE = 60

class MainDataSource {
  constructor () {
    this.knex = knex

    this.accounts = new Accounts(knex)
    this.companies = new Companies(knex)
    this.companyFields = new CompanyFields(knex)
    this.deliveries = new Deliveries(knex)
    this.fields = new Fields(knex)
    this.formFields = new FormFields(knex)
    this.formMetadata = new FormMetadata(knex)
    this.forms = new Forms(knex)
    this.integrations = new Integrations(knex)
    this.oAuthTokens = new OAuthTokens(knex)
    this.responses = new Responses(knex)
    this.subscriberDeliveries = new SubscriberDeliveries(knex)
    this.subscriberMetadata = new SubscriberMetadata(knex)
    this.subscribers = new Subscribers(knex)
    this.templateMessages = new TemplateMessages(knex)
    this.userIntegrations = new UserIntegrations(knex)
    this.users = new Users(knex)
  }

  static CACHE_LENGTH () {
    return MINUTE
  }
}

module.exports = MainDataSource

// ../modules/accounts/dataSource.js

const DataSourcesMethods = require('../../helpers/dataSourcesMethods')
const { NAMESPACE } = require('./constants')

class AccountsDataSource extends DataSourcesMethods {
  constructor (props) {
    super(props)
    this.name = NAMESPACE
  }

  someMethod (id) {
    return this.knex(this.name)
      .where('accounts.id', id)
  }
}

module.exports = AccountsDataSource

// helpers/dataSourcesMethods.js

const { SQLDataSource } = require('datasource-sql')

class DataSourcesMethods extends SQLDataSource {
  findById (id) {
    return this.knex(this.name)
      .where('id', id)
      .first()
  }

  findAll () {
    return this.knex(this.name)
  }
}

Hello, I'm trying to update SQLDataSource to its last version but it seems the peer dependency is blocking the usage with graphql@^15.5.0

 "peerDependencies": {
    "graphql": "^14.0.2"
  },

Error message : ERESOLVE unable to resolve dependency tree Found: [email protected].. peer graphql@"^14.0.2" from [email protected]

Is this version restriction intended?

After adding extends SQLDataSource to the datasource class I get the above error and the component doesn't render.

It happens even if I don't call any of the functions (getBatched(), getCached(), getBatchedAndCached())

If I remove just those two words (extends SQLDataSource) then the app works fine.

Is this a bug?

https://github.com/cvburgess/SQLDataSource/blob/master/SQLCache.js#L7

Is this possible with the current data source API?

It's recommended both for security and memory usage (the Map used grows until DataLoader is released):

https://github.com/graphql/dataloader#creating-a-new-dataloader-per-request

In the Readme it says The knex instance is made available as this.knex or this.db..
The typescript type doesn't have the db variable available.

This package looks like just what I need to connect Apollo with SQL database using Datasources, but I see that Knex is a hard dependency.

Was there a reason for choosing to use Knex over Sequelize?

Would it be possible to either to remove the hard dependency so that it can work with Sequelize, or add support for Sequelize using an initialization parameter?

I believe this is related to knex issue 3553, but wanted to surface it here because of a potential security issue that could result.

For PG, query.toString() will omit bigint values, yet the actual query can succeed.

Here's an example of the resulting string where a bigint id is used:
select "id", "username" from "users" where "id" = '' limit 1
Notice that id = ''. So in this case, the first user queried will be returned for subsequent queries as the cache key is always the same.

Also, the Knex docs suggest that toString() should be used for debugging, but we're using it for something quite important.

For example, create the instance of DB connection asynchronously.

constructor() {
    super();
    // Add your instance of db to the DataSource
    // this.knex = await createKnex()
  }

How can I do this?

@cvburgess I use MySQL as the DB. This is knex raw method feature.

Originally posted by @a2exfr in #19 (comment)

In order to:

• avoid confusion between having multiple dbs: dataSources.db.* in resolvers and this.db.* in data source
• more closely match knex docs (which say eg knex.select().from('users')), so we'd do

this.knex.select().from('users')
instead of
this.db.select().from('users')

https://github.com/cvburgess/SQLDataSource/blob/master/index.js#L17

knex not initializing correctly, still looking into details

fyi

First of thanks for this nice data source.

The reason why I'm writing is your given example in the usage section. You're creating the MyDatabase instance outside of the context creation, thus, it will only get created once. Reusing this instance (data source) will result in context being overwritten by resolvers. A more detailed example:

1. user 1 makes a request
   • database gets initialized with context that contains the user id
   • the resolver waits 5 seconds
   • then executes a database call
2. user 2 makes a request after user 1 (first requests resolver still waits!)
   • database gets initialized with context (overwrites context) that contains the user id
   • the resolver waits 5 seconds
   • then executes a database call

Both request have different context, but the database instance context is being overwritten, meaning the first requests database call will have the context of request 2. Generally this won't happen since queries are fast, but when using a websocket server that will create the context only once on subscribe this becomes a major problem.

This is how I implemented it instead:

- const db = new MyDatabase(knexConfig);
+ const knexInstance = knex(knexConfig)

const server = new ApolloServer({
  typeDefs,
  resolvers,
  cache,
  context,
-  dataSources: () => ({ db })
+  dataSources: () => ({ db: new MyDatabase(knexInstance) })
});

Maybe I'm not understanding the example correctly. Anyway, I'd love to hear you feedback, thanks.

For batching and deduplication.

I've followed instructions from README and using sqlite3 with webpack config results into error message in graphql playground. -- "this.driver.Database is not a constructor" for both queries (post and posts queries below)
Following is my config:

PostsDB.js

const {SQLDataSource} = require("datasource-sql")

class PostsDB extends SQLDataSource {

    async getAllPosts() {
        return this.knex
            .select("*")
            .from("posts_fts")
    }

    async getPost(rid) {
        return this.knex
            .select("*")
            .from("posts_fts")
            .where({rid: rid})
            .then(row => {
                return row[0]
            })
    }
}

module.exports = PostsDB

resolvers.js

module.exports = {
    Query: {
        post: async (root, {rid}, {dataSources}) => {
             return dataSources.db.getPost(rid)
        },
        posts: async (root, args, {dataSources}) => {
            return dataSources.db.getAllPosts()
        },
    },
}

schema.js

const { gql } = require('apollo-server-cloudflare')

module.exports = gql`  
  type Posts {
    rid: ID!
    title: String!
    body: String!
    tags: String!
    rslug: String!
   }

  type Query {
    post(rid: ID!): Posts,
    posts : [Posts]

  }  
`

apollo.js

const {ApolloServer} = require('apollo-server-cloudflare')
const {graphqlCloudflare} = require('apollo-server-cloudflare/dist/cloudflareApollo')

const KVCache = require('../kv-cache')

const PostsDB = require('../datasources/PostsDB')

const knexConfig = {
    client: 'sqlite3',
    connection: {
        filename: "../datasources/sqlite-db/blog.sqlite"
    },
    useNullAsDefault: true
}

const db = new PostsDB(knexConfig)

const resolvers = require('../resolvers')
const typeDefs = require('../schema')


const kvCache = {cache: new KVCache()}

const createServer = graphQLOptions =>
    new ApolloServer({
        typeDefs,
        resolvers,
        introspection: true,
        dataSources: () => ({ db }),
        ...(graphQLOptions.kvCache ? kvCache : {}),
    })

const handler = (request, graphQLOptions) => {
    const server = createServer(graphQLOptions)
    return graphqlCloudflare(() => server.createGraphQLServerOptions(request))(request)
}

module.exports = handler

webpack.config.js (without using following config webpack fails to build pointing to knex unused modules)

const path = require('path')
const webpack = require('webpack')

module.exports = {
    target: 'webworker',
    resolve: {
        alias: {
            fs: path.resolve(__dirname, './null.js'),
        },
    },
    mode: 'production',
    optimization: {
        usedExports: true,
    },
    plugins: [
        new webpack.NormalModuleReplacementPlugin(/\.\.\/migrate/, '../util/noop.js'),
        new webpack.NormalModuleReplacementPlugin(/\.\.\/seed/, '../util/noop.js'),
        new webpack.IgnorePlugin(/mariasql/, /\/knex\//),
        new webpack.IgnorePlugin(/mssql/, /\/knex\//),
        new webpack.IgnorePlugin(/mysql/, /\/knex\//),
        new webpack.IgnorePlugin(/mysql2/, /\/knex\//),
        new webpack.IgnorePlugin(/oracle/, /\/knex\//),
        new webpack.IgnorePlugin(/oracledb/, /\/knex\//),
        new webpack.IgnorePlugin(/pg-query-stream/, /\/knex\//),
        // new webpack.IgnorePlugin(/sqlite3/, /\/knex\//),
        new webpack.IgnorePlugin(/strong-oracle/, /\/knex\//),
        new webpack.IgnorePlugin(/pg-native/, /\/pg\//)
    ]
}

package.json

{
  "name": "sqlite-graphql",
  "private": true,
  "description": " Apollo GraphQL server using Cloudflare Workers",
  "version": "1.1.0",
  "dependencies": {
    "apollo-server-cloudflare": "^2.4.8",
    "datasource-sql": "^1.1.1",
    "graphql": "^14.2.1",
    "sqlite3": "^4.1.1",
    "webpack": "^4.41.5"
  },
  "license": "MIT",
  "main": "src/index.js"
}

This POC is based on graphql on cloudflare workers based on https://github.com/signalnerve/workers-graphql-server
(rest-datasource works fine with above template)

Not sure if this is a misconfig or a bug, but my guess is misconfig with sql datasource or webpack config. Can someone please point me in right direction? Much appreciated.

@lorensr @cvburgess

@signalnerve Is there an off chance cloudflare graphql doesn't support sqlite (with or without kv) ?

Hello,

is it possible to programmatically invalidate the cache for a specific query? This could be helpful for example when properties in that query are changed by a mutation.

Thanks!

key for memcached or redis must by without spaces
Error: The key should not contain any whitespace or new lines

reproduce when you have two extended classes

Thank you for sharing this module.
I'm writing tests for Datasource extending it and I've found the cache getting in the way.
I'm mocking knex using jest-mock-knex to return different responses.
The challenge is the query is the same, therefore the key in the cache is the same and the object is returned.
I've tried passing .cache(0) so as to prevent caching at all but this did not work.
Please advise. If there is a potential change in the module needed, I would be glad to collaborate with a PR.
Thanks

When initing a new SQLDataSource in the datasources function with an existing knex connection it throws the following error:

Can't extend QueryBuilder with existing method ('cache')

Option 1: This does not work:

const dbConnection = Knex(knexConfig)

const server = new ApolloServer({
         schema,
         dataSources: () => ({ db: new SQLDataSource(dbConnection) })
})

The below both works:
Option 2: when you insert a dataSource that's called outside of the of the datasource function

const dbConnection = Knex(knexConfig)

const db = new SQLDataSource(dbConnection)

const server = new ApolloServer({
         schema,
         dataSources: () => ({ db })
})

Option 3: Or when you call the SQLDataSource function with the config instead of an existing connection

const server = new ApolloServer({
         schema,
         dataSources: () => ({ db: new SQLDataSource(knexConfig) })
})

The first example which doesn't work is what's most appealing to me. I have an external rest api that also uses the db connection so would be good to pass that one along. And according to the docs the datasources function should create a new instance of each data source for each operation which is not the case for option 2.

Great package! Thanks.

Is there any particular reason you've pinned your node engine at 10.6.0?
I'm currently developing on 11.1.0 and when I install this package I get a warning:

error [email protected]: The engine "node" is incompatible with this module. Expected version "10.6.0". Got "11.1.0"
error Found incompatible module

It would be great to avoid that warning on an install.

Is there a way to dynamically set/reset custom Postgres run-time configuration parameters?

I have tried to set a custom parameter using the pool "afterCreate" event, in the api definition, with:

getData(value) {

this.knex.client.pool.on('createSuccess', (eventId, resource) => {
resource.query("SET custom.param = '" + value+ "'", () => {})
});
const res = this.knex
.from("table")
.select()
.limit(count)
.cache(MINUTE);
console.log(res);
return res;
}

This works once, but the parameter could not be updated after the first query.

I have also tried to use "raw" but without success:

const res = this.knex.raw("SET custom.param = 'actual_value'; SELECT * FROM table limit 10; RESET custom.param;")
.then((result) => {
})
.catch((error) => {
});
console.log(res);
return res;

Hi there,
I'm new to GraphQL and have spent most of today trying to get MySQL database working. So far I have unbounded queries working but can't for the life of me work out how to pass parameters in for creating a row or querying an individual row - would it be possible to provide some examples for an idiot please?

Many thanks ...

If the schemas are statically defined how could we use aggregation with knex?
For instance the following query:
SELECT count(*) AS column1, MAX(Id) AS column2 FROM some_table

We are currently using https://github.com/khmm12/knex-tiny-logger for optional query logging but it does not seem to be working correctly. When making a single trip to the PostgreSQL database, it appears that multiple logs are created and the number per query goes up with every request. When logging the actual PostgreSQL database, it was verified that only a single request to the database is made.

TinyLogger Output:

SQL (5.926 ms) select * from "tablename1" where "username" = '--username--'
SQL (5.926 ms) select * from "tablename1" where "username" = '--username--'
SQL (5.926 ms) select * from "tablename1" where "username" = '--username--'
SQL (3.081 ms) select * from "tablename2"
SQL (3.081 ms) select * from "tablename2"
SQL (6.299 ms) select * from "tablename3" where "user_id" = 234
SQL (7.602 ms) select * from "tablename3" where "user_id" = 234
SQL (7.602 ms) select * from "tablename3" where "user_id" = 234
SQL (7.602 ms) select * from "tablename3" where "user_id" = 234
SQL (9.030 ms) select * from "tablename4" where "id" in (1)
SQL (7.189 ms) select * from "tablename5" where "tsg_id" in (1)
SQL (7.189 ms) select * from "tablename5" where "tsg_id" in (1)
SQL (7.189 ms) select * from "tablename5" where "tsg_id" in (1)
SQL (7.189 ms) select * from "tablename5" where "tsg_id" in (1)
SQL (7.389 ms) select * from "tablename6" where "id" = 2
SQL (7.808 ms) select * from "tablename6" where "id" = 2

PostgreSQL Logs:

LOG:  execute <unnamed>: select * from "tablename1" where "username" = '--username--'
LOG:  execute <unnamed>: select * from "tablename2"
LOG:  execute <unnamed>: select * from "tablename3" where "user_id" = 234
LOG:  execute <unnamed>: select * from "tablename4" where "id" in (1)
LOG:  execute <unnamed>: select * from "tablename5" where "tsg_id" in ($1)
LOG:  execute <unnamed>: select * from "tablename6" where "id" = 2

TinyLogger uses the .on method of knex - I'm not entirely sure if this is an issue with TinyLogger, Knex, or SQLDataSource.

When I provide the .cache attribute to knex query I receive this error

(node:10584) UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'get' of undefined
    at MyDatabase.cacheQuery (F:\Code\menext\node_modules\datasource-sql\index.js:50:23)

The line that is not liked is this:
return this.cache.get(cacheKey).then(entry => {

I have added apollo-server-caching module added to project.
I would appreciate guidance on what I am missing here to enable cache.
Once I remove it, I can get results from DB.

This is my example class:

const { SQLDataSource } = require("datasource-sql");

const MINUTE = 60;

class MyDatabase extends SQLDataSource {
    getLists() {
        return this.knex
          .select("*")
          .from("lists")
          .where({ id: 24 })
          .limit(100)
          .cache(MINUTE);
    }
}

module.exports = MyDatabase;

When I trace this further, this part with the creation of cache store via init never gets called.

  initialize(config) {
    this.context = config.context;
    this.cache = config.cache || new InMemoryLRUCache();

    if (DEBUG && !hasLogger) {
      hasLogger = true; // Prevent duplicate loggers
      knexTinyLogger(this.db); // Add a logging utility for debugging
    }
  }

Hi there. Thanks for the great library :)

In the docs, you show this example:
`// index.js

const MyDatabase = require("./MyDatabase");

const knexConfig = {
client: "pg",
connection: {
/* CONNECTION INFO */
}
};

// you can also pass a knex instance instead of a configuration object
const db = new MyDatabase(knexConfig);

const server = new ApolloServer({
typeDefs,
resolvers,
cache,
context,
dataSources: () => ({ db })
});`

However, in the apollo docs for data sources,, they say:

As shown, the dataSources option is a function. This function returns an object containing instances of your DataSource subclasses (in this case, MoviesAPI and PersonalizationAPI).

Apollo Server calls this function for every incoming operation. It automatically assigns the returned object to the dataSources field of the context object that's passed between your server's resolvers.

Also as shown, the function should create a new instance of each data source for each operation. If multiple operations share a single data source instance, you might accidentally combine results from multiple operations.

Do I understand correctly from your docs that in the case of your library, we should divert from the standard apollo practice, and instead create an instance and just pass it into dataSources? I suppose this is necessary for your caching approach?

Do I understand correctly that if I instantiating the data source the way apollo recommends, I would not be able to use your .cache() functionality across separate queries?

Thanks again for the library and for any advice :)

I want to Redis for caching but it does not seem to be working. Without redis caching specified it works fine (InMemoryLRUCache).

What I did was:

import { RedisCache } from 'apollo-server-cache-redis';

//passed to ApolloServer setup:

cache: new RedisCache({
host: 'localhost',
port: 6379
}),

then I am caching with getBatchedAndCached(knexquery)

When I run it I am getting these errors:

node_redis: Deprecated: The SET command contains a argument of type Object.
This is converted to "[object Object]" by using .toString() now and will return an error from v.3.0 on.
Please handle this in your code to make sure everything works as you intended it to.
...
error: uncaughtException: ERR syntax error
ReplyError: ERR syntax error
at parseError (C:\Users\Tiago Alcobia\Documents\crossviewer-services\v2-query-service\node_modules\redis-parser\lib\parser.js:193:12)
at parseType (C:\Users\Tiago Alcobia\Documents\crossviewer-services\v2-query-service\node_modules\redis-parser\lib\parser.js:303:14)
[nodemon] app crashed - waiting for file changes before starting...

This is more of a question.. Will you start supporting Apollo version 4 and above. I have started a migration on our server where we use this lib allot.

It says here that you are using the deprecated package "apollo-datasource".

So my question is, will you support Apollo 4 fully?

Enhance the documentation to include examples for:

• getBatched
• getCached
• getCachedAndBatched

And explain some of the under-the-hood dynamics:

• DEBUG environment variable
• Batching via DataLoader
• Caching with Apollo-supported cache mechanisms

Hey thanks for this package!

I'm running v0.1.4 and noticed that when using getBatchedAndCached, passing a TTL doesn't do anything, and the set value seems to live in the cache forever. I tried:

getBatchedAndCached(query, 1);
getBatchedAndCached(query, 0);
getBatchedAndCached(query, -1);
getBatchedAndCached(query, { ttl: 0 });

I think the issue might be that [email protected] doesn't actually use a TTL in set.

class InMemoryLRUCache {
  constructor({ maxSize = Infinity } = {}) {
    this.store = LRU({ max: maxSize, length: item => item.length });
  }
  get(key) {
    return __awaiter(this, void 0, void 0, function* () {
      return this.store.get(key);
    });
  }
  set(key, value) {
    return __awaiter(this, void 0, void 0, function* () {
      this.store.set(key, value);
    });
  }
}

I can get around it by installing [email protected] and passing in { cache: new InMemoryLRUCache() } in initialize.

Not sure if I'm missing something, but I think you can just update the dependency.

https://github.com/cvburgess/SQLDataSource/blob/master/SQLCache.js#L24-L40

Last param to .set is an object:

https://github.com/apollographql/apollo-server/tree/master/packages/apollo-server-caching#implementing-your-own-cache

Also, ttl is in seconds, so example in README shouldn't multiply by 1000

Currently Knex converts encrypted PG fields to Buffers, which are not consistently encoded to and from JSON.

Add support for optionally passing a param to .cache() that handles buffers ( becuase it will have a perf impecation that not all apps may need ).

Idea: .cache({ hasBuffers: true })

Hi

What is the best place to destroy the database connection?

This is what i have now but it doesn't work:

The problem is that i get "message": "Unable to acquire a connection" on every request.

Example: https://codesandbox.io/s/strange-austin-2ccpm?file=/src/index.ts

I think this happen after knex moved in peerDependencies - my project start using latest version of knex (0.95.x), but before SQLDataSource using types from 0.21.x

I guess between 0.21.x and 0.95.x changed export typings on naming exports:
import knex from 'knex' -> import { knex } from 'knex', but if you do this fix, you can broke projects with older version of knex

The change made in #5 is not compatible with multiple datasources.
We are using two knex instances (2 DB's) in our project.

Since the variable hasLogger is not a class/object property it will be set true for the first object (knex instance) and will not be initialized for the following ones since hasLogger is already true.