How to Connect to a MySQL Database with JavaScript in a Few Steps
by Kevin McGahey • October 3, 2023
The DreamFactory REST API enables database connections using a wide variety of front end scenarios. This simple sample app demonstrates how DreamFactory easily can be used to connect to a MySQL database with JavaScript. It's a simple address book, where contacts can be created, shown, updated, deleted and grouped: basically, CRUD operations.
The Address Book is a very simple one page app based on plain JavaScript and jQuery. All the functions used for DreamFactory 2.0 REST API requests are extended in the file functions.js, and the code examples show how to use them.
Here's the simple steps to connect to a MySQL Database with JavaScript:
- To get started with a sample address book app, access the code on the GitHub repository or download the free DreamFactory open source project from Bitnami.
- Upon logging into the DreamFactory Admin Console, follow these steps to set up the address book app.
- The process involves importing an application package file and configuring CORS settings in the DreamFactory instance.
- JavaScript code examples are provided for connecting to the DreamFactory REST API, including the format of API requests and AJAX call parameters.
- The article explains how to handle user login, registration, and authentication, including obtaining a session token for authenticated API requests.
Why Connect a MySQL Database with JavaScript?
Simplicity and efficiency are the key reasons:
- User-Friendly Interaction: JavaScript allows you to create dynamic and interactive web applications. Connecting to a MySQL database empowers your app to handle data seamlessly.
- Real-Time Updates: By connecting JavaScript to MySQL, you can provide users with real-time updates and information without the need for page refreshes.
- Data Accessibility: JavaScript's integration with MySQL ensures your data is readily available and can be retrieved or updated on the fly, enhancing the user experience.
- Enhanced Performance: It enables you to build fast and responsive web apps that can efficiently manage and display data from a MySQL database.
- Versatility: JavaScript's versatility makes it suitable for both client-side and server-side scripting, making it a valuable tool for web development tasks.
Connecting MySQL with JavaScript simplifies the development process and offers a wide range of possibilities for creating feature-rich web applications.
Getting Started
The code is available at the GitHub repository, and to get the sample app up and running, just sign up for a free hosted developer account or download the free DreamFactory open source project from Bitnami.
Once you've logged into the DreamFactory Admin Console, follow the steps below to get the address book app up and running.
Connect to a MySQL Database with JavaScript
Step 1: Import the provided application package file:
After importing the package, the app will be assigned an API Key, which you can see in the Apps tab. This API Key is needed to set up the app.
Step 2: Configure CORS
Navigate to the Config tab and select CORS. Click the '+' button to add a new CORS entry and select '*' from the list box. This will pre-populate the fields in the form. Enter 3600 for Max Age, make sure that the 'Enabled' checkbox is checked, and save.
Note: '*' denotes open access to your API endpoints and should only be used in for development.
Now edit the first lines of code in the script.js file.
var INSTANCE_URL = '';
var APP_API_KEY = '';
If the JavaScript Address Book Sample App code is not running on the DreamFactory instance, specify the instance host (e.g. https://www.my-instance.com) in INSTANCE_URL, otherwise leave INSTANCE_URL blank. Insert the app API Key in APP_API_KEY.
See the readme for more detailed instructions.
JavaScript-MySQL Code Examples
The JavaScript Address Book shows how to log in, log out and perform common CRUD operations.
DreamFactory REST API requests are formatted like this:
http[s]://<server-name>/api/v2/[<service-api-name>]/[<resource-path>][?<param-name>=<param-value>All API requests executed from the Address Book are done with AJAX calls, with the following parameters:
dataType: String
contentType: String
url: String
data: String,Object
method: String
headers: Object
Breaking down each parameter:
dataType - format of the returned data. This depends on the application, but will typically be JSON.
contentType - the format of data sent to the API. This is commonly JSON and charset UTF-8.
url - the URL to the REST API endpoint (see format in Code Examples). You can include the query parameters here. However, it is easier and cleaner to pass in the query parameters by using the data parameter than it is to format them into the url.
data - the (optional) query parameter(s).
method - the REST verb (e.g. GET, POST).
headers - the header object must include the app-specific API key and a session token.
Read about the MySQL Integration with DreamFactory for more information.
Logging in
The Address Book implements DreamFactory's built-in user management for easy user creation and authentication. New users can register and existing users can log in to the app.
If the app is imported to the DreamFactory 2.0 instance, leave INSTANCE_HOST blank. Email and password are typically input fields in the app UI (like in the Address Book app).
var INSTANCE_URL = 'http[s]://<server-name>';
var email = 'my@email.com';
var password = 'mypassword';
POST email and password to the endpoint user/session to retrieve a session token:
$.ajax({
dataType: 'json',
contentType: 'application/json; charset=utf-8',
url: INSTANCE_URL + '/api/v2/user/session',
data: JSON.stringify({
'email': email,
'password': password
}),
method: 'POST',
success: function (response) {
// Handle success
},
error: function (response) {
// Handle error
}
});
If the email and password matches users in the DreamFactory instance, a session token will be returned. This session token is used for API requests requiring authentication.
Getting Records
After logging in and retrieving a session token, we can go ahead and get some records from the instance database. The approach is very similar to logging in and retrieving the session token. This time we use the extended function getRecords() in functions.js. The usage is like this:
getRecords(table, params, token, callback) - params can be fields, filters etc.
Use callback functions to get the requested data. The session token and the API key are inserted in the AJAX call as an object:
headers: {
"X-DreamFactory-API-Key": API_KEY,
"X-DreamFactory-Session-Token": token
}
In this example, all contact groups in the database are retrieved. Since the query retrieves all records without filtering, no parameters are passed to the function (null).
$.api.getRecords('contact_group', null, session_token, callback);
// create a callback function and handle the returned data there
var callback = function(response) {
// The response contains an array of group objects
}
Now let's get all contacts, but we only interested in the id, first name, and last name. With the parameter fields, the response will be limited to the specified fields.
var params = 'fields=contact_id,first_name,last_name';
$.api.getRecords('contacts', params, session_token, callback);
Use a callback function like in the previous example to get the API request response.
Now let's get all contact ids belonging to a specific group instead (group id = 10). Note the equal sign in the filter value is url encoded (%3D).
var params = 'filter=contact_group_id%3D10';
$.api.getRecords('contact_group_relationship', params, session_token, callback);
Again, use a callback function like in the previous example to get the API request response. Parameters (e.g. fields, limits) can be combined in the same API request.
Creating Records
As the last example, a new record is created, and it's very similar to getting a record. Instead of executing a GET request, a POST request is executed to an API endpoint. In this example a new group named 'My Group' is created.
var group = { name: 'My Group' };
var params = JSON.stringify(group);
$.api.setRecord('contact_group', params, session_token, callback);
The parameters (request payload) must be stringified. If the API request was successful, and the group was created in the instance database, the id of the new group is returned (use callback function to get the response).
DreamFactory in JavaScript apps
Using DreamFactory as a backend in JavaScript applications is easy. We hope this tutorial is useful and will get you started using DreamFactory in your own projects. You can also try our Gold Version by clicking the button below to see the full functionality of DreamFactory.
Kevin McGahey is an accomplished solutions engineer and product lead with expertise in API generation, microservices, and legacy system modernization, as demonstrated by his successful track record of facilitating the modernization of legacy databases for numerous public sector organizations.
