{"id":16606,"library":"cds-mysql","title":"CAP MySQL Database Adapter","description":"cds-mysql is a database adapter for the SAP Cloud Application Programming Model (CAP) framework, enabling applications to connect to and interact with MySQL and MariaDB databases. It is heavily inspired by the cds-pg module. The current stable version is 7.9.0, supporting Node.js 18 and npm 9 or higher. This module allows CAP applications to leverage MySQL/MariaDB for data persistence, offering features such as fundamental CRUD operations, deep insert for associations/compositions, Fiori draft support, temporal aspects (without time-travel queries), incremental IDs, CSV-based initial data provisioning, full-text search, schema migration optimization, media attachment support, localized data, and multi-tenancy including experimental `@sap/cds-mtxs` integration. It differentiates itself by providing comprehensive CAP feature support for MySQL, aiming for seamless integration with the CAP runtime.","status":"active","version":"7.9.0","language":"javascript","source_language":"en","source_url":"https://github.com/Soontao/cds-mysql","tags":["javascript","CDS","CAP","MySQL","MariaDB","typescript"],"install":[{"cmd":"npm install cds-mysql","lang":"bash","label":"npm"},{"cmd":"yarn add cds-mysql","lang":"bash","label":"yarn"},{"cmd":"pnpm add cds-mysql","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required as the underlying MySQL driver for database communication.","package":"mysql2","optional":false},{"reason":"Peer dependency as it is an adapter for the SAP Cloud Application Programming Model (CAP) framework.","package":"@sap/cds","optional":false}],"imports":[{"note":"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.","wrong":"const mysql = require('mysql2/promise');","symbol":"mysql2","correct":"import mysql from 'mysql2/promise';"},{"note":"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.","symbol":"Service","correct":"import { Service } from '@sap/cds';"},{"note":"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.","symbol":"connect","correct":"import cds from '@sap/cds';\nconst db = await cds.connect.to('db');"}],"quickstart":{"code":"npm i cds-mysql mysql2\n\n// package.json (or .cdsrc.json)\n// Add this to your CAP project's package.json to configure cds-mysql\n// This tells CAP to use 'mysql' as the database kind.\n{\n  \"requires\": {\n    \"db\": {\n      \"kind\": \"mysql\"\n    }\n  }\n}\n\n// .env file in your local CDS project root\n// Fill with your database credentials\nCDS_REQUIRES_DB_CREDENTIALS_HOST=127.0.0.1\nCDS_REQUIRES_DB_CREDENTIALS_PORT=3306\nCDS_REQUIRES_DB_CREDENTIALS_DATABASE=your_database_name\nCDS_REQUIRES_DB_CREDENTIALS_USER=your_db_user\nCDS_REQUIRES_DB_CREDENTIALS_PASSWORD=your_db_password\n\n// To start the CAP server with MySQL:\nnpx cds-serve","lang":"bash","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"breaking","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"The database schema has not been deployed, or the `cds` configuration is pointing to an incorrect database or kind.","error":"Error: SQLITE_ERROR: no such table: MyService_MyEntity"},{"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`.","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.","error":"Failed to connect to MySQL: Host 'X.X.X.X' is not allowed to connect to this 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.","cause":"The database specified in `CDS_REQUIRES_DB_CREDENTIALS_DATABASE` environment variable does not exist on the MySQL server.","error":"ER_BAD_DB_ERROR: Unknown database 'your_database_name'"},{"fix":"Run `npm install mysql2` in your project directory to install the necessary database driver.","cause":"The `mysql2` package, which is a required peer dependency for `cds-mysql`, has not been installed.","error":"Error: Cannot find module 'mysql2'"}],"ecosystem":"npm"}