Unable to Deploy Function when using Knex

[hgmeza]

Environment information

System:
  OS: macOS 14.5
  CPU: (8) arm64 Apple M1 Pro
  Memory: 133.95 MB / 16.00 GB
  Shell: /bin/zsh
Binaries:
  Node: 20.12.2 - ~/.nvm/versions/node/v20.12.2/bin/node
  Yarn: 1.22.19 - /usr/local/bin/yarn
  npm: 10.9.1 - ~/.nvm/versions/node/v20.12.2/bin/npm
  pnpm: undefined - undefined
NPM Packages:
  @aws-amplify/auth-construct: Not Found
  @aws-amplify/backend: Not Found
  @aws-amplify/backend-auth: Not Found
  @aws-amplify/backend-cli: Not Found
  @aws-amplify/backend-data: Not Found
  @aws-amplify/backend-deployer: Not Found
  @aws-amplify/backend-function: Not Found
  @aws-amplify/backend-output-schemas: Not Found
  @aws-amplify/backend-output-storage: Not Found
  @aws-amplify/backend-secret: Not Found
  @aws-amplify/backend-storage: Not Found
  @aws-amplify/cli-core: Not Found
  @aws-amplify/client-config: Not Found
  @aws-amplify/deployed-backend-client: Not Found
  @aws-amplify/form-generator: Not Found
  @aws-amplify/model-generator: Not Found
  @aws-amplify/platform-core: Not Found
  @aws-amplify/plugin-types: Not Found
  @aws-amplify/sandbox: Not Found
  @aws-amplify/schema-generator: Not Found
  aws-amplify: Not Found
  aws-cdk: Not Found
  aws-cdk-lib: Not Found
  typescript: Not Found
No AWS environment variables
No CDK environment variables

Describe the bug

I am trying to use knex to connect to an RDS server with a connection string.

When I create a js file with the following:

import knex from "knex";

const connectionString = "mssql://<user>:<password>@<rds-server>/<db>";
const knexDbConfig = {
  client: 'mssql',
  connection: connectionString,
  pool: {
    max: 20,
  },
};
const knexWebDb = knex(knexDbConfig);

const main = async () => {
  const result = await knexWebDb('<table>').select([
    '1',
    '2',
    '3',
  ])
    .limit(1000);

console.log(result)
}
main();

with a package.json

{
    "dependencies": {
      "knex": "^3.1.0",
      "mssql": "^11.0.1"
  },
}

everything works.

However, when trying to do the same for a lambda function, i get the error:

Unable to build the Amplify backend definition.
Caused By: ✘ [ERROR] Could not resolve "sqlite3"

    amplify/functions/say-hello/node_modules/knex/lib/dialects/sqlite3/index.js:42:19:
      42 │     return require('sqlite3');
         ╵                    ~~~~~~~~~

  You can mark the path "sqlite3" as external to exclude it from the bundle, which will remove this error and leave the unresolved path in the bundle. You can also surround this "require" call with a try/catch block to handle this failure at run-time instead of bundle-time.

✘ [ERROR] Could not resolve "better-sqlite3"

    amplify/functions/say-hello/node_modules/knex/lib/dialects/better-sqlite3/index.js:7:19:
      7 │     return require('better-sqlite3');
        ╵                    ~~~~~~~~~~~~~~~~

  You can mark the path "better-sqlite3" as external to exclude it from the bundle, which will remove this error and leave the unresolved path in the bundle. You can also surround this "require" call with a try/catch block to handle this failure at run-time instead of bundle-time.

✘ [ERROR] Could not resolve "pg-query-stream"

    amplify/functions/say-hello/node_modules/knex/lib/dialects/postgres/index.js:192:16:
      192 │       : require('pg-query-stream');
          ╵                 ~~~~~~~~~~~~~~~~~

  You can mark the path "pg-query-stream" as external to exclude it from the bundle, which will remove this error and leave the unresolved path in the bundle. You can also surround this "require" call with a try/catch block to handle this failure at run-time instead of bundle-time.

✘ [ERROR] Could not resolve "oracledb"

    amplify/functions/say-hello/node_modules/knex/lib/dialects/oracledb/index.js:44:29:
      44 │     const oracledb = require('oracledb');
         ╵                              ~~~~~~~~~~

  You can mark the path "oracledb" as external to exclude it from the bundle, which will remove this error and leave the unresolved path in the bundle. You can also surround this "require" call with a try/catch block to handle this failure at run-time instead of bundle-time.

✘ [ERROR] Could not resolve "mysql"

    amplify/functions/say-hello/node_modules/knex/lib/dialects/mysql/index.js:24:19:
      24 │     return require('mysql');
         ╵                    ~~~~~~~

  You can mark the path "mysql" as external to exclude it from the bundle, which will remove this error and leave the unresolved path in the bundle. You can also surround this "require" call with a try/catch block to handle this failure at run-time instead of bundle-time.

✘ [ERROR] Could not resolve "oracledb"

    amplify/functions/say-hello/node_modules/knex/lib/dialects/oracledb/utils.js:40:27:
      40 │   const oracledb = require('oracledb');
         ╵                            ~~~~~~~~~~

  You can mark the path "oracledb" as external to exclude it from the bundle, which will remove this error and leave the unresolved path in the bundle. You can also surround this "require" call with a try/catch block to handle this failure at run-time instead of bundle-time.

What have I done to solve this? I was trying to follow: https://stackoverflow.com/questions/77131329/when-synth-or-deploy-with-aws-cdk-getting-bundling-error-related-to-knex/77147118#77147118

But i do not know how to apply that solution into any amplify backend config nor how to manually override that within backend.ts and using CDK directly to add the externalModules property.

Any help would be appreciated!

Reproduction steps

Create an amplify backend with the following:

amplify/
  functions/
    say-hello/
      resource.ts
      handler.ts
  backend.ts

where:

// functions/say-hello/hander.ts ----------------------------------------------------
import knex from "knex";
import type { Schema } from "../../data/resource";

const knexDbConfig: knex.Knex.Config = {
  client: 'mssql',
  connection: "mssql://<user>:<password>@<rds-server>/<db>",
  pool: {
    max: 20,
  },
};
const knexWebDb = knex(knexDbConfig);

export const handler: Schema["sayHello"]["functionHandler"] = async (event, context) => {
  try {
    const result = await knexWebDb('<table>').select([
      '1',
      '2',
      '3',
    ])
      .limit(1000);
  console.log({ results });
  return "Hello World!"
} catch (error) {
    const err = error as Error;
    console.error(err.message, err.name, err.stack);
    return err.message;
  }
};

// functions/say-hello/resource.ts ----------------------------------------------------
import { defineFunction } from '@aws-amplify/backend';

export const sayHello = defineFunction({
  // optionally specify a name for the Function (defaults to directory name)
  name: 'say-hello',
  // optionally specify a path to your handler (defaults to "./handler.ts")
  entry: './handler.ts',
  timeoutSeconds: 30,
});

// backend.ts ----------------------------------------------------
import { defineBackend } from '@aws-amplify/backend';
import { data } from './data/resource';
import { sayHello } from './functions/say-hello/resource';

defineBackend({
  data,
  sayHello
});

// data/resource.ts in case this is relevant ----------------------------------------------------
import { type ClientSchema, a, defineData } from "@aws-amplify/backend"
import { sayHello } from "../functions/say-hello/resource"

const schema = a.schema({
  sayHello: a
    .query()
    .arguments({
      name: a.string(),
    })
    .returns(a.string())
    .handler(a.handler.function(sayHello))
    .authorization(allow => [allow.authenticated('oidc')])
})

export type Schema = ClientSchema<typeof schema>

export const data = defineData({
  schema,
  authorizationModes: {
    defaultAuthorizationMode: "oidc",
    oidcAuthorizationMode: {
      oidcProviderName: 'Auth0',
      oidcIssuerUrl: 'https://login.auth0.com/',
      clientId: '<clientId>',
      tokenExpiryFromAuthInSeconds: 0,
      tokenExpireFromIssueInSeconds: 0,
    },
  },
})

The package json is (located in amplify/package.json):

{
  "type": "module",
  "devDependencies": {
    "@types/mssql": "9.1.6"
  },
  "dependencies": {
    "knex": "3.1.0",
    "mssql": "11.0.1"
  }
}

You should get the above error

[josefaidt]

Hey @hgmeza 👋 thanks for raising this! You're on the right path that this is a dependency that needs to be externalized and installed alongside the resulting bundle. While this is not currently supported as the bundling options are abstracted, you can use the newer custom functions feature to bring your own NodejsFunction construct

The custom functions feature is intended for bringing functions using other runtimes, but could be used to escape out of the Node.js limitations, https://docs.amplify.aws/react/build-a-backend/functions/custom-functions/

Following that stackoverflow post you will need to first externalize the module, then add it to the bundling.nodeModules array. Note this will require docker to bundle and features like the automatic secrets resolution will not work with custom functions
https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk-lib.aws_lambda_nodejs.BundlingOptions.html#nodemodules