5 months ago I did a little weekend project trying to generate REST APIs from a simple json database schema.
It only worked for mongoDB and overall it tried to do too much, but it worked. By far the number one issue was that people wanted to use it for MySQL.
I’ve created a hosted version that will create a REST API from an existing MySQL database. SQL has been around for a long time now, and there are a ton of useful tools to help model a schema, I didn’t feel I had much to add on that front and so for now we’re just working with existing databases, though if enough of you chime in and want to be able to define your schema as well as create the API, then we can and will build it.
The use case, for now, is quite small. Ideal projects are those that:
- Have an existing MySQL Database
- Need a NodeJS REST API
Or those of you who are just curious.
Database Connection
Hopefully, your MySQL instance doesn’t allow anyone in the outside world to try and authenticate against it. You’ll need to whitelist our IP address to allow us to connect to your instance and read the models. This is the part of our app I worry most about. It’s a huge hurdle to get someone to trust your product enough to whitelist an IP address to directly hit the database. So here are some ways you can minimize risk and still easily generate a REST API.
- Create a username and password just for this project. Generate the API, then delete the MySQL user so we can’t access it anymore.
- Give us read-only credentials.
- Give us access to a replica instead of the primary instance.
- Point us to your dev or staging DB instead of production.
And yes, your data is encrypted in our backend.
Selecting APIs
Once we’ve read your schema, you have the option to toggle on or off which endpoints you want us to generate. If for example, this is just an internal API for the sales team, perhaps you don’t want to give them the ability to update or delete items.
Download and Run
At the moment we only support NodeJS but will quickly be adding Python as well as other databases. This will show how to get started with the NodeJS API.
After downloading the zip file, unzip it and
npm i && npm run testThis will kick off the tests we created for you. Make sure your local IP address is whitelisted to access your database as well otherwise the connection won’t work. (We populate the repo with your connection information you gave us).
To generate your documentation you can
npm run docsTo start the api you can run
npm run devand check out http://localhost:3030 to view your swagger docs!
During our testing we pointed it to a production database and generated over 350 APIs, which took about 4 seconds. The documentation is beautiful and more importantly, it works.
I cringe a little to think about how much time I had spent before this project writing essentially commodity APIs.
Tips
- There’s a middleware hook already in the project to do things like handle authentication or decoode a user token. It’s under middleware/hook.js
module.exports = (req, res, next) => {
/*
Middleware layer called before every route.
Here you could de-code an auth token, or other stuff!
*/
return next();
}This hook is already baked into all your routes, so if you decide to pass in an authorization header with a JWT, you can decode it here and you could fully deploy this in production as an internal tool, or perhaps a limited set of external tools with minimal code changes.
Questions For You
Where do we take this from here? We could:
- Add more SQL databases
- Add more languages
- Deploy the API for you
- Allow you to model your database, then generate an API based on the model
- Deploy both the API and the database for you
- Integrate with GitHub so you can progressively add new APIs as your app grows without having to manage zip files.
Let me know!
Try It Out
If you’d like to try it out, head on over to https://noco.io and give it a shot! It’s free for now.
