@mysql/xdevapi node.js example: (re)create mysql database, test table insert, select and delete

create_db.js

// https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html
var mysqlx = require('@mysql/xdevapi');

const MIGRATIONS_USER = 'migrationsuser';

var server = {
    host     : 'localhost',
    user     : 'intendeduser',
    database : 'mydatabase',
    password : 'mypassword'
};

(async function () {
    let session;
    try {
        session = await mysqlx.getSession({
            host: server.host,
            user: 'root',
            password: process.env.DB_ROOT_PASSWORD
        });

        try {
            await session.sql(`DROP DATABASE ${server.database}`).execute();
            console.log('database dropped');
        } catch (err){
            console.log('database could not be dropped');
        }

        // https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
        await session.sql(`CREATE DATABASE ${server.database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`).execute();
        console.log('database created');

        try {
            await session.sql(`DROP USER '${MIGRATIONS_USER}'@'localhost';`).execute();
            console.log(`existing user ${MIGRATIONS_USER} deleted`);    
        } catch (err){
            console.log(`user ${MIGRATIONS_USER} could not be deleted`);
        }
        try {
            await session.sql(`DROP USER '${server.user}'@'localhost';`).execute();
            console.log(`existing user ${server.user} deleted`);    
        } catch (err){
            console.log(`user ${server.user} could not be deleted`);
        }

        await session.sql(`flush privileges;`).execute();
        console.log('flushed privileges');

        await session.sql(`CREATE USER '${MIGRATIONS_USER}'@'localhost' IDENTIFIED BY '${process.env.DB_MIGRATIONS_USER_PASSWORD}';`).execute();
        console.log(`user ${MIGRATIONS_USER} created`);
        await session.sql(`GRANT ALL PRIVILEGES ON ${server.database}.* TO '${MIGRATIONS_USER}'@'localhost';`).execute();
        console.log(`user ${MIGRATIONS_USER} granted all privileges`);

        await session.sql(`CREATE USER '${server.user}'@'localhost' IDENTIFIED BY '${server.password}';`).execute();
        console.log(`user ${server.user} created`);
        await session.sql(`GRANT SELECT, INSERT, UPDATE ON ${server.database}.* TO '${server.user}'@'localhost';`).execute();
        console.log(`user ${server.user} granted select, insert, update privileges`);

        // migrations table
        await session.sql(`USE ${server.database}`).execute();
        await session.sql(`CREATE TABLE migrations (
            id int NOT NULL,
            filename varchar(255),
            PRIMARY KEY (id)
        );`).execute();
        console.log(`migrations table created`);

        // test migrations table queries
        let db = await session.getSchema(server.database);
        let migrations = db.getTable('migrations');
      
        // https://dev.mysql.com/doc/x-devapi-userguide/en/sql-crud-functions.html
        migrations.insert(['id', 'filename']).
            values(0, 'empty database').
            values(1, 'test value to be deleted').
            execute();
        console.log('initial data inserted');

        // retrieve row to be deleted
        migrations.select('id', 'filename')
            .where('filename like :filename')
            .orderBy('id')
            .bind('filename', '%test value%')
            .execute(function(row) {
                // for each row
                console.log(`${row[0]}: ${row[1]}`);
            })
            .catch((err) => { console.error(err.message); })
            .then(() => {
                // once rows have all been printed, delete target row
                migrations.delete()
                    .where('id = :id')
                    .bind('id', 1)
                    .execute()
                    .catch((err) => { console.error(err.message); })
                    .then((result) => {
                        console.log(`${result.getAffectedRowsCount()} row(s) deleted`);
                        session.close();
                    });
            });
    } catch (err) {
        console.error(err);
        session && session.close();
    }
})();