SAP CAP with local JWT auth causes sqlite tables to not be generated

[octavian]

Hello,

We don't seem to get the db schema to generate if we use JWT auth locally.
If we use mocked / default auth the schemas are there just fine (see lines to remove from cdsrc.json below).
In both cases the cds files + mocked data and services are loaded successfully, and the server starts.

In case of JWT we get errors at runtime when trying to read / write data because the tables are missing.
Using the db in:memory or dumping to an sqlite file has the same outcome.

Any ideas?

cdsrc.json
{
    "odata": {
        "version": "v4"
    },
    "requires": {
        "uaa": {
            "kind": "xsuaa"
        },
        "messaging": {
            "kind": "local-messaging"
        }
    },
    "[development]": { // Removing this works fine
        "auth": {
            "passport": {
                "strategy": "JWT"
            }
        }
    }
}

[david_kunz2]

Hi,

- Can you show us the error message?
- Can you set DEBUG=sqlite and show us the output?
- What happens if you run `cds deploy --to sqlite`?

Best regards,
David

[octavian]

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
  <code>500</code>
  <message>SQLITE_ERROR: no such table: app_Profiles in: SELECT createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", userId AS "userId", firstName AS "firstName" FROM app_Profiles ALIAS_1 WHERE userId = ? LIMIT 1</message>
</error>

[cds] - model loaded from 3 file(s):

  ./db/schema.cds
  ./srv/my-profile-service.cds
  ./node_modules/@sap/cds/common.cds


[cds] - connect to db > sqlite { database: ':memory:' }
[cds] - using bindings from: { registry: '~/.cds-services.json' }
[sqlite] - BEGIN 
[sqlite] - DROP VIEW IF EXISTS MyProfileService_Profiles 
[sqlite] - DROP TABLE IF EXISTS app_Profiles 
[sqlite] - CREATE TABLE app_Profiles (
  createdAt TIMESTAMP_TEXT,
  createdBy NVARCHAR(255),
  modifiedAt TIMESTAMP_TEXT,
  modifiedBy NVARCHAR(255),
  userId NVARCHAR(255) NOT NULL,
  firstName NVARCHAR(80),
  PRIMARY KEY(userId)
) 
[sqlite] - CREATE VIEW MyProfileService_Profiles AS SELECT
  Profiles_0.createdAt,
  Profiles_0.createdBy,
  Profiles_0.modifiedAt,
  Profiles_0.modifiedBy,
  Profiles_0.userId,
  Profiles_0.firstName
FROM app_Profiles AS Profiles_0 
[sqlite] - COMMIT 
 > filling app.Profiles from ./db/data/app-Profiles.csv 
[sqlite] - BEGIN 
[sqlite] - INSERT INTO app_Profiles ( userId, firstName , createdAt, createdBy, modifiedAt, modifiedBy ) VALUES ( ?, ?, ?, ?, ?, ? ) [
  [
    'admin',
    'Admin',
    '2021-07-02T12:44:20.268Z',
    'anonymous',
    '2021-07-02T12:44:20.268Z',
    'anonymous'
  ],
  [
    'user',
    'User',
    '2021-07-02T12:44:20.268Z',
    'anonymous',
    '2021-07-02T12:44:20.268Z',
    'anonymous'
  ]
]
[sqlite] - COMMIT 

[cds] - GET /myprofile/getMyProfile()
[sqlite] - BEGIN 
[sqlite] - SELECT createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", userId AS "userId", firstName AS "firstName" FROM app_Profiles ALIAS_1 WHERE userId = ? LIMIT 1 [ 'admin' ]
[sqlite] - ROLLBACK 
[cds] - SQLITE_ERROR: no such table: app_Profiles in: 
SELECT createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", userId AS "userId", firstName AS "firstName" FROM app_Profiles ALIAS_1 WHERE userId = ? LIMIT 1
    at /Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds-runtime/lib/sqlite/execute.js:56:15
    at new Promise (<anonymous>)
    at executeSelectSQL (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds-runtime/lib/sqlite/execute.js:55:10)
    at executeSelectCQN (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds-runtime/lib/sqlite/execute.js:118:10)
    at SQLiteDatabase._read (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds-runtime/lib/db/query/read.js:49:10)
    at SQLiteDatabase.module.exports [as _READ] (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds-runtime/lib/db/generic/read.js:26:29)
    at next (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds/lib/serve/Service-dispatch.js:54:36)
    at SQLiteDatabase.dispatch (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds/lib/serve/Service-dispatch.js:58:6)
    at async next (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds/lib/serve/Service-dispatch.js:54:17)
    at async ApplicationService.dispatch (/Users/i346604/Desktop/workspace/long/ties/cap-test/node_modules/@sap/cds/lib/serve/Service-dispatch.js:52:10) {
  errno: 1,
  code: 'SQLITE_ERROR',
  query: 'SELECT createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", userId AS "userId", firstName AS "firstName" FROM app_Profiles ALIAS_1 WHERE userId = ? LIMIT 1',
  id: '1630757',
  level: 'ERROR',
  timestamp: 1625229862529

[octavian]

When running the `cds deploy --to sqlite` it seems to produce valid values inside the db.

[gregorw]

Dear Octavian,

please be aware that this is a public forum and linking to an SAP internal GitHub doesn't help.

Best regards
Gregor

[octavian · Accepted solution]

{
  "odata": {
    "version": "v4"
  },
  "requires": {
    "uaa": {
      "kind": "xsuaa"
    },
    "messaging": {
      "kind": "local-messaging"
    },
    "db": {
      "[development]": {
        "kind": "sqlite",
        "model": "*"
      }
    },
    "auth": {
      "[development]": {         // Had wrong nest level for [development]
        "strategy": "mock",      // Replace this with JWT
        "users": {
          "alice": false,        // In case you have want mocked users but want to remove the default ones
          "bob": false,
          "*": false,
        }
      }
    }
  }
}