Search code examples
mysqlnode.jsloopbackjsloopback

How should I connect a LoopBack Framework app to a Pre-Existing / Data Filled MySQL Database / Data Source?

This question pertains specifically to LoopBack Node.js Framework from StrongLoop (owned by / backed by IBM). From what I know so far this should be a two step process.

  1. Write a script to discover / create Models from the existing MySQL Schema. Here are the official docs regarding that: https://loopback.io/doc/en/lb3/Discovering-models-from-relational-databases.html

I think I successfully handled the discovery / model creation piece using the following NPM package: https://www.npmjs.com/package/loopback-discovery (GitHub repo for project can be found here: https://github.com/akera-io/loopback-discovery)

  1. Use AutoUpdate (via script in loopback-project/server/boot directory) to check to make sure the MySQL DB fits with the Model definition that was created by the Discovery / Creation module. Here is a StackOverflow topic on the difference between LoopBack's AutoMigrate and AutoUpdate functions: loopback automigrate vs autoupdate

From there I think I should be able to hit my API and access the data in the MySQL datasource, but alas...

When I go through the above steps, I successfully get ALL of the models from my selected table in the loopback-project/common/models directory (my models happen to be posts from a blog).

In my loopback-project/server/model-config.json all of the tables / models from my MySQL database exist and I then set my "posts" model to public in order to expose it via API.

Since the NPM Module discussed above uses LoopBack and the LoopBack datasources I know that I can connect to my DB and my loopback-project/server/datasource.js file properly configures loopback to talk to my server / mysql instance.

When I launch my loopback instance using node . I can hit http://0.0.0.0/explore and I have the proper CRUD options for my 'Posts' model exactly as I would expect.

HOWEVER.

When I attempt to query my DB via GET Posts Api Endpoint I receive the following error that my table does not exist (even though it clearly does in MySQL since my blog is using it AND the previous NPM package was able to read from it:

{
  "error": {
    "statusCode": 500,
    "name": "Error",
    "message": "ER_NO_SUCH_TABLE: Table 'my_mysqlDB.Posts' doesn't exist",
    "code": "ER_NO_SUCH_TABLE",
    "errno": 1146,
    "sqlState": "42S02",
    "index": 0,
    "stack": "Error: ER_NO_SUCH_TABLE: Table 'my_mysqlDB.Posts' doesn't exist\n    at Query.Sequence._packetToError (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)\n    at Query.ErrorPacket (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)\n    at Protocol._parsePacket (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Protocol.js:280:23)\n    at Parser.write (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Parser.js:75:12)\n    at Protocol.write (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Protocol.js:39:16)\n    at Socket.<anonymous> (/app/AvailableTripsLBcode/node_modules/mysql/lib/Connection.js:103:28)\n    at emitOne (events.js:96:13)\n    at Socket.emit (events.js:188:7)\n    at readableAddChunk (_stream_readable.js:176:18)\n    at Socket.Readable.push (_stream_readable.js:134:10)\n    at TCP.onread (net.js:551:20)\n    --------------------\n    at Protocol._enqueue (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Protocol.js:141:48)\n    at PoolConnection.query (/app/AvailableTripsLBcode/node_modules/mysql/lib/Connection.js:208:25)\n    at runQuery (/app/AvailableTripsLBcode/node_modules/loopback-connector-mysql/lib/mysql.js:186:16)\n    at executeWithConnection (/app/AvailableTripsLBcode/node_modules/loopback-connector-mysql/lib/mysql.js:228:7)\n    at Ping.onOperationComplete [as _callback] (/app/AvailableTripsLBcode/node_modules/mysql/lib/Pool.js:110:5)\n    at Ping.Sequence.end (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/sequences/Sequence.js:86:24)\n    at Ping.Sequence.OkPacket (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/sequences/Sequence.js:95:8)\n    at Protocol._parsePacket (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Protocol.js:280:23)\n    at Parser.write (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Parser.js:75:12)\n    at Protocol.write (/app/AvailableTripsLBcode/node_modules/mysql/lib/protocol/Protocol.js:39:16)\n    at Socket.<anonymous> (/app/AvailableTripsLBcode/node_modules/mysql/lib/Connection.js:103:28)\n    at emitOne (events.js:96:13)\n    at Socket.emit (events.js:188:7)\n    at readableAddChunk (_stream_readable.js:176:18)\n    at Socket.Readable.push (_stream_readable.js:134:10)\n    at TCP.onread (net.js:551:20)"
  }
}

I only care about ONE table on my DB (posts) and I only need to read not / write. I am not sure if I need models for all the other models to be in common/models directory, or if I can have ONLY my Posts model which relates to my 'posts' table on the datasource, to that end I have tried both ways with no luck.

I feel like there is something I missing about how LoopBack handles schema creation based on Models but I haven't had any luck solving it as of yet.

Some other notes / details.

  1. Re-Creating via AutoMigrate is not an option since it drops the table and re-creates thus losing all data (which is the only reason I need to connect in the first place).
Solution

  • if your model is named "Post", then loopback will be looking for Post table in mysql.

        "name": "Post",
  "options": {
    "idInjection": false,
    "mysql": {
      "schema": "LOOPBACK",
      "table": "post"
    }
  }

    add this in the post.json file and it will work