Database deploy - Automatic schema evolution

[federicobelotti]

Hi all,
I faced a little problem while trying to take advantage of the automatic schema evolution within a PostgreSQL database (but the issue has been replicated with SQLite as well).
Months ago, whenever a change to the data model was made, while doing a cds deploy I could automatically have the changes reflected to the database schema without losing data without setting anything specific in the app configuration.
After upgrading the CDS libraries (cds-js as well as cds-dk) to the current version (7.9.X) I noticed that at each deploy, I kept losing the data since tables were dropped and created. Then I found out that the cds_model table has never been created since the first deployement, so no schema evolution was happening at all.
The documentation regarding this topic states you can have automatic schema evolution out-of-the-box and that, in order to not use it, you can disable it inside the package.json.
I actually had to do the opposite, which was to explicitly enable it through the "schema_evolution": "auto" parameter set in the package.json file.

Checking the env data prior to enabling that setting, the cds.requires.db.schema_evolution variable value turned out to be undefined.

Is the documentation wrong or is the other way around and it is, in fact, a bug?

Thanks,
Federico

[renejeglinsky]

Hi @federicobelotti ,
Thank you. We're checking your issue and will get back to you.

All the best,
René

[patricebender]

Hi @federicobelotti,

I have tried it out in my local bookshop, with the following versions:

❯ cds --version

@cap-js/cds-types: 0.3.0
@cap-js/postgres: 1.8.0
@cap-js/sqlite: 1.6.0
@capire/bookshop: 1.0.0
@sap/cds: 7.9.0
@sap/cds-compiler: 4.9.0
@sap/cds-dk: 7.9.0
@sap/eslint-plugin-cds: 2.7.0
Node.js: v18.18.2

after adding postgres:

❯ npm add @cap-js/postgres

added 136 packages, and audited 138 packages in 6s

21 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
❯ cds env requires.db --production
{
  impl: '@cap-js/postgres',
  dialect: 'postgres',
  vcap: { label: 'postgresql-db' },
  schema_evolution: 'auto', -- ⬅️ this looks correct
  kind: 'postgres'
}

I can see that schema_evolution is indeed set to auto.

Now I connect to my local postgres instance and insert a new record:

> await INSERT.into(Authors).entries({ID: 42, name: 'Stephen King'} )
InsertResult { results: [ { changes: 1 } ] }
> await SELECT.from(Authors).where('ID = 42')
[
  {
    ID: 42,
    name: 'Stephen King',
    createdAt: '2024-05-13T08:04:54.307Z',
    createdBy: 'anonymous',
    modifiedAt: '2024-05-13T08:04:54.307Z',
    modifiedBy: 'anonymous',
    dateOfBirth: null,
    dateOfDeath: null,
    placeOfBirth: null,
    placeOfDeath: null
  }
]

to reproduce your scenario, I will add a new field to the Authors entity and re-deploy:

after re-deployment, I execute the query from above again:

> { Authors } = cds.entities
[object Function]
> await SELECT.from(Authors).where('ID = 42')
[
  {
    ID: 42,
    age: null,
    name: 'Stephen King',
    createdAt: '2024-05-13T08:04:54.307Z',
    createdBy: 'anonymous',
    modifiedAt: '2024-05-13T08:04:54.307Z',
    modifiedBy: 'anonymous',
    dateOfBirth: null,
    dateOfDeath: null,
    placeOfBirth: null,
    placeOfDeath: null
  }
]

on my side, everything works as expected. cds env also looks correct to me:

❯ cds env requires.db --production
{
  impl: '@cap-js/postgres',
  dialect: 'postgres',
  vcap: { label: 'postgresql-db' },
  schema_evolution: 'auto',
  kind: 'postgres'
}

---

could you please provide a sample repository, with detailed steps to reproduce your scenario?

[federicobelotti]

Hello @patricebender,
I figured out what the problem is. In my application I use a .cdsrc.json file where I store credentials and other sensitive stuff. Inside the package.json, however, I do not use the pg profile, but the default one (I then don't specify any profile name inside the configuration). Unfortunately in this way is seems that the default values are not taken into account and they need to be explicitly defined.
For example, inside package.json:

  "cds": {
      "requires": {
          "db": {
              "dialect": "postgres",
              "impl": "@cap-js/postgres",
              "kind": "postgres"
          }
      }
  }

And in the .cdsrc.json:

"requires": {
    "db": {
        "credentials": {
            "host": "...",
            "port": 5432,
            "user": "postgres",
            "password": "...",
            "database": "..."
       },
    }
}

Inside the package.json file I have to also add "schema_evolution": "auto" in order to have it defined. Am I correct?

Thank you for your clarifications,
Federico