AngularJS and SQL - The Coding College

AngularJS is a powerful front-end framework for building dynamic web applications, but it doesn’t interact directly with databases like SQL. Instead, AngularJS relies on back-end services (such as PHP, Node.js, or Python) and APIs to connect with SQL databases. In this guide, we’ll explore how AngularJS can work with SQL databases to fetch, display, and manipulate data efficiently.

For more programming resources, visit The Coding College.

How AngularJS Interacts with SQL Databases

Client-Server Model: AngularJS works on the client side (browser), while SQL databases like MySQL, PostgreSQL, or SQL Server operate on the server side.
API/Back-End Middleware: APIs (like REST or GraphQL) or back-end languages handle communication between AngularJS and the database.
HTTP Requests: AngularJS’s $http or $resource service sends requests to the API, which then interacts with the database.

Architecture

AngularJS: Manages the user interface and sends HTTP requests.
API Layer: Acts as a bridge, handling requests and responses.
SQL Database: Stores and retrieves data based on queries.

Example: Displaying SQL Data in AngularJS

Step 1: Create an API Endpoint

Using Node.js and Express:

const express = require('express');
const mysql = require('mysql');
const app = express();

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'example_db'
});

db.connect((err) => {
    if (err) throw err;
    console.log('MySQL Connected...');
});

app.get('/users', (req, res) => {
    let sql = 'SELECT * FROM users';
    db.query(sql, (err, results) => {
        if (err) throw err;
        res.json(results);
    });
});

app.listen(3000, () => {
    console.log('Server started on port 3000');
});

Step 2: AngularJS Front-End Code

<!DOCTYPE html>
<html>
<head>
    <title>AngularJS SQL Example</title>
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.8.2/angular.min.js"></script>
</head>
<body ng-app="myApp" ng-controller="myCtrl">
    <h1>Users List</h1>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
        </tr>
        <tr ng-repeat="user in users">
            <td>{{ user.id }}</td>
            <td>{{ user.name }}</td>
            <td>{{ user.email }}</td>
        </tr>
    </table>

    <script>
        var app = angular.module('myApp', []);
        app.controller('myCtrl', function($scope, $http) {
            $http.get('http://localhost:3000/users')
            .then(function(response) {
                $scope.users = response.data;
            });
        });
    </script>
</body>
</html>

Sending Data to the SQL Database

To add new data to the database, use AngularJS $http.post.

Example: Adding a New User

Back-End Code (Node.js):

app.post('/add-user', (req, res) => {
    let user = { name: req.body.name, email: req.body.email };
    let sql = 'INSERT INTO users SET ?';
    db.query(sql, user, (err, result) => {
        if (err) throw err;
        res.send('User added...');
    });
});

AngularJS Code:

<div>
    <h2>Add New User</h2>
    <form ng-submit="addUser()">
        <input type="text" ng-model="newUser.name" placeholder="Name" required>
        <input type="email" ng-model="newUser.email" placeholder="Email" required>
        <button type="submit">Add User</button>
    </form>
</div>

<script>
    app.controller('myCtrl', function($scope, $http) {
        $scope.newUser = {};

        $scope.addUser = function() {
            $http.post('http://localhost:3000/add-user', $scope.newUser)
            .then(function(response) {
                alert('User added successfully!');
            });
        };
    });
</script>

Filtering Data

AngularJS’s filtering features, combined with SQL queries, can display refined datasets.

Example: Fetch Users Based on Search

Back-End Code:

app.get('/users/search', (req, res) => {
    let searchQuery = req.query.name || '';
    let sql = `SELECT * FROM users WHERE name LIKE '%${searchQuery}%'`;
    db.query(sql, (err, results) => {
        if (err) throw err;
        res.json(results);
    });
});