Classification: Public Domain
MySQL Interfacehttps://github.com/vacenter/mysql_interface
What is this?
The MySQL Interface package is a NodeJS interface layer. The package allows you to provide credentials and a database schema (See below) and perform select, update, insert and delete operations. As well as run regular SQL statments.
The package supports a custom relational system.
Where can I get this?
Using NPM: https://www.npmjs.com/package/mysql_interface
Code Docs
Initialization
init(connectionParams, dbSchema)
-
connectionParams
is an object containing the host, port, user, password and name of the database -
dbSchema
is the object containing the schema to be created by the package, see the Schema header for more information
Example
const db = require('mysql_interface');
const fs = require("fs");
db.init({host: "127.0.0.1", port: 3306, user: "myUser", password: "myPassword", database: "myDatabase"}, JSON.parse(fs.readFileSync('schema.json', 'UTF-8')));
The package requires a schema to create the database, this should be supplied by your application in the dbSchema argument. The schema file is organised into the following structure: Database Schema
{
"tableName": {
"fields": {
"column":{
"type": "columnType",
"flags": "column flags",
"default": "'default value'",
"transform": null
}
},
"pk": "column"
}
}
Descriptions:
-
tableName
is the name of the table to be created, must be single words -
column
is the name of the column -
type
is the type of column (bigint, varchar, etc) -
flags
are the flags attached to this column (unsigned, NOT NULL, AUTO_INCREMENT) should be space seperated. -
default
is the default value for that column, strings should be enclosed in single quotes -
pk
is an optional property that just states which column is the primary key.
Select data
db.tableName.get(query)
-
tableName
is the name of the table -
query
is the object containing what is being searched (see example)
Example:
db.users.get({
username: "myUsername"
})
Update data
db.tableName.update(fields, where)
-
tableName
is the name of the table -
fields
is the object containing what fields are being updated (see example) -
where
is the object containing the search method (see example)
Example:
db.users.update({
fields: {
age: 18,
},
where: {
field: "username",
value: "myUsername",
operator: "="
}
})
Insert data
db.tableName.create(data)
-
tableName
is the name of the table -
data
is the object containing the fields
Example:
db.users.create({
username: "myUsername",
password: "myPassword",
age: 20
})
Delete data
db.tableName.delete(query)
-
tableName
is the name of the table -
query
is the object containing what is being searched (see example)
Example:
db.users.delete({
username: "myUsername"
})
Relations
This package does support relationships, both single and multiple. This can be done by adding a few more things to the schema.
Single Relationship Example
"car": {
"type": "bigint",
"flags": "unsigned",
"default": null,
"transform": null,
"related": {
"table": "users",
"field": "id"
},
"relation": "single"
}
In the return for a get statment, there will be the data from the linked row.
Multiple Relationship Example
"keys": {
"type": "string",
"flags": "",
"default": null,
"transform": null,
"related": {
"table": "keys",
"field": "id"
},
"relation": "multiple"
}
The data for this column should be stored in a stringified array: "[keyID1,keyID2,keyID3]" In a get statment the returned data will be in an array for each item. If the package fails to find a refrenced row, it will return null in its place.