3.0.0 • Published 4 years ago

flora-mysql v3.0.0

Weekly downloads
50
License
MIT
Repository
github
Last release
4 years ago

flora-mysql

npm.io NPM version NPM downloads

MySQL data source for Flora, based on the mysql module.

Usage

Add flora-mysql to Flora config

module.exports = {
    …
    dataSources: {
        mysql: {
            constructor: require('flora-mysql'),
            options: {
                onConnect: ['SET SESSION max_execution_time = 30000'],
                servers: {
                    default: {
                        user: 'dbuser',
                        password: '…',
                        masters: [{ host: 'db01' }],
                        slaves: [{ host: 'db02' }, { host: 'db03' }],
                    }
                    specialServer: {
                        user: 'dbuser2',
                        password: '…',
                        masters: [{ host: 'specialdb01' }],
                    },
                },
            },
        },
        …
};

Use data source in resources

SELECT queries may be executed on one of the slaves servers (if present), unless useMaster is set to true.

// Get a Context instance
const db = api.dataSources.mysql.getContext({
    db: 'users', // database name
    // server: 'default', // default: 'default'
    // useMaster: false, // default: false
});

// Query
const allRows = await db.query(
    'SELECT firstname, lastname FROM profiles'
);

// Query with parameters
const someRows = await db.query(
    'SELECT firstname, lastname FROM profiles WHERE id = ?',
    [ 1000 ]
);

// Query with named parameters
const someRows = await db.query(
    'SELECT firstname, lastname FROM profiles WHERE id = :userId',
    { userId: 1000 }
);

// Single column
const ids = await db.queryCol(
    'SELECT id FROM profiles WHERE lastname = "Smith"'
);

// Single row (first result)
const { firstname, lastname } = await db.queryRow(
    'SELECT firstname, lastname FROM profiles WHERE id = 1000'
);

// Single value (first result)
const firstname = await db.queryOne(
    'SELECT firstname FROM profiles WHERE id = 1000'
);

Insert, update, delete

Write queries are executed on master servers automatically, even when useMaster was set to false in getContext.

// Insert a row
db.insert('profiles', {
    firstname: 'Max',
    lastname: 'Mustermann',
    updatedAt: db.raw('NOW()'), // pass through unescaped
});

// Upsert (insert or update)
db.upsert(
    'profiles', 
    { id: 1000, firstname: 'Max' },
    [ 'firstname' ] // Update firstname to "Max" if profile with id 1000 already exists
);

// Update
db.update(
    'profiles',
    { updatedAt: db.raw('NOW()') }, // SET updatedAt=NOW()
    { id: 1000 } // WHERE id=1000
);

// Delete
db.delete('profiles', { id: 1000 });

Transactions

const trx = await db.transaction();
try {
    await trx.update('profiles', …);
    await trx.insert('log', …);
    await trx.commit();
} catch (err) {
    try {
        // Ignore rollback errors
        await trx.rollback();
    } catch (ignoreErr) { }
    throw err;
}

// Same as above, but shorter:
await db.transaction(async (trx) => {
    await trx.update('profiles', …);
    await trx.insert('log', …);
});

Features

  • When being used as data source for resource-processor, SQL queries are optimized and only the columns needed are selected.

License

MIT

3.0.0

4 years ago

2.4.0

4 years ago

2.3.0

4 years ago

2.2.1

5 years ago

2.1.2

5 years ago

2.2.0

5 years ago

2.1.3

5 years ago

2.1.1

5 years ago

2.1.0

5 years ago

2.0.4

5 years ago

2.0.3

5 years ago

2.0.2

6 years ago

2.0.1

6 years ago

2.0.0

6 years ago

2.0.0-alpha.8

6 years ago

2.0.0-alpha.7

6 years ago

2.0.0-alpha.6

6 years ago

2.0.0-alpha.5

6 years ago

2.0.0-alpha.4

6 years ago

2.0.0-alpha.3

7 years ago

2.0.0-alpha.2

7 years ago

2.0.0-alpha.1

7 years ago

0.9.2

7 years ago

0.9.1

7 years ago

0.9.0

7 years ago

0.8.1

7 years ago

0.8.0

8 years ago

0.7.0

9 years ago

0.6.2

9 years ago

0.6.1

10 years ago

0.6.0

10 years ago

0.3.4

10 years ago

0.3.3

10 years ago

0.3.2

10 years ago

0.3.1

10 years ago

0.3.0

10 years ago

0.2.0

10 years ago