I am using mysql2 to connect and execute a simple CREATE DATABASE statement with node:
import {ConnectionOptions, createConnection, createPool, FieldPacket, Pool, RowDataPacket} from "mysql2/promise";
...
this.dbConfig = {
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
};
const defaultDbConnection = await createConnection(defaultDbConfig);
await defaultDbConnection.execute('CREATE DATABASE ?', [this.dbConfig.database]);
It fails with:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
It seems it is not preparing the statement for some reason. According to this:
https://www.npmjs.com/package/mysql2#using-promise-wrapper
I seem to be using the API correctly.
If I change this to:
await defaultDbConnection.execute(`CREATE DATABASE ${this.dbConfig.database}`);
Then it works, but I'd like to use prepared statements instead given that this is not the only thing I'm planning to run.
My MySQL is running through docker:
db:
image: mysql:5.7
restart: always
environment:
MYSQL_DATABASE: 'db'
# So you don't have to use root, but you can if you like
MYSQL_USER: 'user'
# You can use whatever password you like
MYSQL_PASSWORD: 'password'
# Password for root access
MYSQL_ROOT_PASSWORD: 'password'
ports:
# <Port exposed> : < MySQL Port running inside container>
- '3306:3306'
expose:
# Opens port 3306 on the container
- '3306'
volumes:
# Where our data will be persisted
- my-db:/var/lib/mysql
What am I missing?