CAP MySQL Database Adapter

Common errors

• Error: SQLITE_ERROR: no such table: MyService_MyEntity

  cause The database schema has not been deployed, or the `cds` configuration is pointing to an incorrect database or kind.
  fix Ensure `package.json` or `.cdsrc.json` correctly specifies `"kind": "mysql"` for the `db` service. Run `npx cds deploy --to mysql` to create the database schema based on your CDS model.

• Failed to connect to MySQL: Host 'X.X.X.X' is not allowed to connect to this MySQL server

  cause The MySQL user configured in `.env` (or other credentials) does not have privileges to connect from the specified host, or the MySQL server is not configured to accept remote connections.
  fix Verify the `CDS_REQUIRES_DB_CREDENTIALS_HOST` in your `.env` file. Grant the MySQL user permissions to connect from `%` (any host) or the specific IP address of your application server (e.g., `GRANT ALL PRIVILEGES ON your_db.* TO 'your_user'@'%' IDENTIFIED BY 'your_password';`). Ensure `bind-address` in MySQL config allows external connections if not `127.0.0.1`.

• ER_BAD_DB_ERROR: Unknown database 'your_database_name'

  cause The database specified in `CDS_REQUIRES_DB_CREDENTIALS_DATABASE` environment variable does not exist on the MySQL server.
  fix Create the database manually on your MySQL server (e.g., `CREATE DATABASE your_database_name;`) or adjust the `CDS_REQUIRES_DB_CREDENTIALS_DATABASE` variable to an existing database.

• Error: Cannot find module 'mysql2'

  cause The `mysql2` package, which is a required peer dependency for `cds-mysql`, has not been installed.
  fix Run `npm install mysql2` in your project directory to install the necessary database driver.

Warnings

• gotcha The `cds-mysql` adapter itself does not automatically perform database deployment (DDL generation and execution) when running `npx cds-serve`. Database artifacts (tables, views) must be deployed manually or via a separate script before starting the server. This is a common pitfall for new users expecting automatic schema sync like with SQLite.
  Fix: Add a `deploy` script to your `package.json` (e.g., `"deploy": "cds deploy --to mysql"`) and run `npm run deploy` before `npx cds-serve`. The `cds deploy` command generates and applies DDL.
• breaking The documentation mentions experimental `@sap/cds-mtxs` support, noting that its behavior may change later. Relying on experimental features for multi-tenancy could lead to breaking changes in future versions without major version bumps.
  Fix: Carefully review the official documentation for `@sap/cds-mtxs` and `cds-mysql` before using experimental multi-tenancy features in production. Pin `cds-mysql` to a specific version if stability is critical.
• gotcha When dealing with `temporal` aspects in CAP, `cds-mysql` supports the aspect itself but does not currently support 'time-travel queries'. Queries against historical data based on timestamps may not work as expected or require manual SQL construction.
  Fix: If time-travel query capabilities are required, consider alternative database solutions or implement custom handlers to manually construct SQL queries for historical data retrieval.
• gotcha Similar to other database adapters, `cds-mysql` relies on the proper configuration of `localized data`. If `localized_*` tables are missing or not properly populated, queries involving localized entities may fail with 'no such table' errors.
  Fix: Ensure your CAP project's `db/data` folder contains CSV files for localized data (e.g., `MyEntity_texts.csv`) and that `cds deploy` is executed to generate and populate the corresponding `localized_*` tables.

Install

• npm install cds-mysql npm
• yarn add cds-mysql yarn
• pnpm add cds-mysql pnpm

Imports

• mysql2
  wrong:

  const mysql = require('mysql2/promise');

  correct:

  import mysql from 'mysql2/promise';

  While cds-mysql is configured, you might directly interact with the underlying `mysql2` driver for advanced scenarios or connection pooling configuration. Use the promise-based API for modern Node.js applications.
• Service

  import { Service } from '@sap/cds';

  When writing custom CAP handlers or extensions, you'll often import `Service` from `@sap/cds` to register custom logic that interacts with the database via the CAP runtime, which then delegates to cds-mysql.
• connect

  import cds from '@sap/cds';
  const db = await cds.connect.to('db');

  To programmatically obtain a database service instance configured by cds-mysql, use `cds.connect.to('db')`. This returns a CAP service instance through which you can interact with the configured MySQL database. This is not a direct import from `cds-mysql` but the primary way to interact with the database service it provides.

Quickstart

Demonstrates how to install cds-mysql, configure it in `package.json` or `.cdsrc.json`, and set up database credentials using environment variables for a local CAP project.

npm i cds-mysql mysql2

// package.json (or .cdsrc.json)
// Add this to your CAP project's package.json to configure cds-mysql
// This tells CAP to use 'mysql' as the database kind.
{
  "requires": {
    "db": {
      "kind": "mysql"
    }
  }
}

// .env file in your local CDS project root
// Fill with your database credentials
CDS_REQUIRES_DB_CREDENTIALS_HOST=127.0.0.1
CDS_REQUIRES_DB_CREDENTIALS_PORT=3306
CDS_REQUIRES_DB_CREDENTIALS_DATABASE=your_database_name
CDS_REQUIRES_DB_CREDENTIALS_USER=your_db_user
CDS_REQUIRES_DB_CREDENTIALS_PASSWORD=your_db_password

// To start the CAP server with MySQL:
npx cds-serve