Question Re: SQLITE_ERROR: no such table: localized_AdminService_Authors in Technology Q&A

SQLITE_ERROR: no such table: localized_AdminService_Authors

jbm1991 — Mon, 10 Aug 2020 08:03:31 GMT

Hi, in an attempt to learn about the authentication and authorisation in CAP apps on Cloud Foundry I have been using this documentation https://cap.cloud.sap/docs/node.js/authentication.

In summary, I have run through the https://cap.cloud.sap/docs/get-started/in-a-nutshell guide in the CAP documentation, followed by the "Token-Based Authentication (JWT)" section in the authentication documentation I linked to above, all the way through to the final step just before the "Custom Authentication Middleware" section.

When I am running my CAP service to test it, in an incognito window, I am made to log in as expected, but then when I try and access my Admin service which is protected by the admin role I receive the following error.

GET /admin/Authors
[2020-08-10T07:47:48.189Z | ERROR | 1431319]: SQLITE_ERROR: no such table: localized_AdminService_Authors
[2020-08-10T07:47:48.189Z | ERROR | 1431319]: Error stacktrace: Error: SQLITE_ERROR: no such table: localized_AdminService_Authors

I have tried a few things I found in other answers which sounded similar however none of those have worked. Any help would be much appreciated. I have included file I think may be relevant below.

package.json
{
  "name": "authtest",
  "version": "1.0.0",
  "description": "A simple CAP project.",
  "repository": "<Add your repository here>",
  "license": "UNLICENSED",
  "private": true,
  "dependencies": {
    "@sap/audit-logging": "^3.1.1",
    "@sap/cds": "^4.1.5",
    "@sap/xsenv": "^3.0.0",
    "@sap/xssec": "^2.2.5",
    "express": "^4",
    "passport": "^0.4.1"
  },
  "devDependencies": {
    "sqlite3": "^4.2.0"
  },
  "scripts": {
    "start": "npx cds run"
  },
  "cds": {
    "hana": {
      "deploy-format": "hdbtable"
    },
    "requires": {
      "db": {
        "kind": "sql"
      },
      "auth": {
        "strategy": "JWT"
      },
      "uaa": {
        "kind": "xsuaa"
      }
    }
  }
}

xs-app.json
{
  "routes": [ {
    "source": "^/(.*)",
    "destination": "srv_api"
   } ]
 }

admin-service.cds

using { sap.capire.bookshop as my } from '../db/schema';

service AdminService @(requires:'admin') {
  entity Books as projection on my.Books;
  entity Authors as projection on my.Authors;
}

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

david_kunz2 — Mon, 10 Aug 2020 08:28:51 GMT

Hi John,

Did you deploy your database artifacts?

-> cds deploy --to sqlite

Alternatively, you can run

-> cds watch
to automatically redeploy and restart on file changes.

Best regards,
David

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

jbm1991 — Mon, 10 Aug 2020 08:38:56 GMT

Thanks for the quick reply david.kunz2

cds watch is how I'm currently running the data service already, however I have tried your other suggestion, but that hasn't made any difference unfortunately.

I think an extension to my original question might be, why is it asking for/trying to use localized tables only after I added authentication? If I remove the "requires:'admin'" from the admin service then it returns the data correctly

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

david_kunz2 — Mon, 10 Aug 2020 09:20:12 GMT

Hi John,
This is indeed a strange behavior. Which version of @sap/cds are you using?

cds -v

Did you try with the latest one (4.1.5)

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

jbm1991 — Mon, 10 Aug 2020 12:14:24 GMT

Hi David,

I am using the latest version of @sap/cds. Please see below for reference:

cds -v         
@sap/audit-logging: 3.1.1
@sap/cds: 4.1.5
@sap/cds-compiler: 1.35.0
@sap/cds-dk: 2.0.7
@sap/cds-foss: 2.0.0
@sap/cds-reflect: 2.12.2
@sap/cds-runtime: 2.1.5
@sap/xsenv: 3.0.0
@sap/xssec: 2.2.5
Node.js: v12.16.1
authtest: 1.0.0

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

david_kunz2 — Wed, 19 Aug 2020 06:57:09 GMT

Hi John,

Can you please share your project so we can reproduce?

Thanks and best regards,
David

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

former_member268422 — Fri, 04 Sep 2020 14:41:23 GMT

Hi John,

I've encountered the same issue after updating my schema.

To solve it, I've recreate/redeploy to the SQLite dev. Other script was inserted in package.json

{
...,
"scripts": {
    "start": "npx cds run",
    "dev":"npx cds watch",
    "dev:db-sync": "cds deploy --to sqlite:raprincis.db",
    "dev:db-deploy": "npx sqlite3 raprincis.db .dump && sqlite3 raprincis.db .tables",
    "dev:db": "npm run dev:db-sync && npm run dev:db-deploy"
  },
  "cds": {
    "requires": {
      "db": {
        "kind": "sqlite",
        "credentials": {
          "database": "raprincis.db"
        }
      }
    }
  }
}

And at the end I run:

npm run dev:db

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

former_member596519 — Wed, 06 Jan 2021 15:21:14 GMT

Hi John,

I had the exact same issue, and it took me a couple of time to figure out the solution, though, I am not sure what was the reason.

I was using BAS, and what I had to do was to cds deploy --to sqlite again and then re-start the service.

Before, I had the issue with CDS watch returning "Cannot find module 'sqlite3'", so I had to do the following to get at least the metadata displayed:

In root folder of application (e.g. bookshop/): npm install
Afterwardds, for all terminals: unset NODE_PATH
Afterwards: cds deploy --to sqlite

Maybe somebody from SAP could enlighten us what went wrong. I just followed the instructions from here:

https://open.sap.com/courses/cp7/items/5QKEq8Yz5ZcCticDU8KGAz

as shown by johannesvogel

Best regards,

Aleks

Re: SQLITE_ERROR: no such table: localized_AdminService_Authors

MattRook — Mon, 09 Sep 2024 19:43:55 GMT

Hi I know this is an old post but I am currently doing this same training project and I have the same issue. I'm surprised there isn't a more definitive solution 4 years later but I got it to work.

I have had this problem before on other frameworks and the problem lies in the package-lock.json file. Delete that file, do an 'npm install' and redeploy with 'cds deploy'. That should refresh the package-lock file and update it and work.