README
@aponica/mysqlgoose-js
Use MySQL in much the same way as MongoDB and MongooseJS.
For anyone who likes the interface to Mongo(ose) and wants to use MySQL (and probably other relational databases like MariaDB, PostgreSQL, Oracle or SQLServer, with some modification) in a consistent manner.
Easily protects against SQL injection attacks and other problems caused by malformed queries (by using prepared statements under the covers)!
The interface is as close as possible to that provided by MongooseJS. Public classes and methods typically have the same names, and arguments appear in the same order.
Only a subset of the features provided by MongooseJS are currently supported, and not always in a fully-compatible way. For most cases, however, there's enough to get by.
Installation
npm i @aponica/mysqlgoose-js
Usage
Step 1: Specify Database Connection
Create a JSON file (we'll call it mysql.json
) containing the
database connection parameters as expected by:
mysqljs/mysql:
{"database":"mysqlgoose_test",
"host":"localhost",
"password":"password",
"user":"mysqlgoose_tester"}
Step 2: Generate Definitions
Because MySQL table schemas are predefined in the database, the definitions can be stored in a JSON document before your application runs, eliminating the time needed to introspect the database every time.
To do this, run
@aponica/mysqlgoose-schema-js,
passing the names of the database parameters file and your desired output file
(definitions.json
here):
npx @aponica/mysqlgoose-schema-js mysql.json definitions.json
Step 3: Create Models
In your code, create a connection to the database, then use it with your definitions file to create the models you'll need:
const fs = require( 'fs' );
const Mysqlgoose = require( '@aponica/mysqlgoose-js' );
const goose = new Mysqlgoose();
await goose.connect( JSON.parse( fs.readFileSync( 'mysql.json' ) ) );
const models = {};
const defs = JSON.parse( fs.readFileSync( 'definitions.json' ) );
for ( let [ table, def ] of Object.entries( defs ) )
if ( '//' !== table ) // skip comment member
models[ table ] = goose.model( table, new Mysqlgoose.Schema( def ) );
Step 4: Use Models as with MongooseJS
Invoke the model methods as in MongooseJS. But keep in mind that you're really working with tables, not documents, so some things won't be exactly the same!
const cust = await models.customer.create(
{ name: 'John Doe', phone: '123-456-7890' } );
const found = await models.customer.findById( cust.id );
const same = await models.customer.findOne( { phone: '123-456-7890' } );
const johns = await models.customer.find( { name: { $regex: '^John ' } } );
await models.customer.findByIdAndUpdate( cust.id, { phone: '123-456-1111' } );
Nested Results
If a table contains a foreign key, it's possible to retrieve the referenced table as a nested object of the current table. This happens automatically when you use the referenced table in the filter; for example:
// retrieve the order rows, each with embedded customer row.
const orders =
await models.order.find( { customer: { phone: '123-456-7890' } } );
You can also explicitly request the nested objects by specifying the desired
table names as the Mysqlgoose.POPULATE
option:
// retrieve the order_product with embedded order & product row.
const ordprod =
await models.order_product.findById( 123, null,
{ [ Mysqlgoose.POPULATE ]: [ 'order', 'product' ] } );
Unfortunately, it's not (currently) possible to populate in the other
direction; for example, when you find order
records, you can't populate
the associated order_product
records. Hopefully, someone will add this
capability in the future!
Please Donate!
Help keep a roof over our heads and food on our plates! If you find aponica® open source software useful, please click here to make a one-time or recurring donation via PayPal, credit or debit card. Thank you kindly!
Unit Testing
Before running the JEST unit tests, be sure to run
tests-config/initialize.sql
as root in your localhost MySQL server
(to create the user and database used by the tests).
Contributing
Ultimately, it would be great if this module completely and faithfully provided all of the (possible) features of MongoDB/MongooseJS.
Another goal is to factor out the generic functionality into a sqlgoose-js
base module that could be shared with other derivatives such as
sqlservergoose-js
and oraclegoose-js
modules.
Please contact us if you're willing to help!
Under the covers, the code is heavily commented and uses a form of Hungarian notation for data type guidance. If you submit a pull request, please try to maintain the (admittedly unusual) coding style, which is the product of many decades of programming experience.
Copyright
Copyright 2019-2021 Opplaud LLC and other contributors.
License
MIT License.
Trademarks
OPPLAUD and aponica are registered trademarks of Opplaud LLC.
Related Links
Official links for this project:
Related projects: