MCP server that connects VS Code to SQLite Database

In this tutorial we will configure VS Code to use an MCP Server that connects to a SQLite database. In a similar manner, you can also connect to PostgreSQL, MySQL, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.

Prerequisites

You will need to install the following software to proceed:

• Visual Studio Code with the GitHub Copilot Chat extension
• Docker Desktop
• Latest versions of node.js, npm, and npx

The Database MCP Server

We will be using the MCP Server from the mcp-database-server GitHub Repo. Visit https://github.com/executeautomation/mcp-database-server for more details

Install and configure the SQLite MCP server

In a suitable working directory, clone the repo, then build, and publish the code by executing these commands in a terminal window:

git clone https://github.com/executeautomation/mcp-database-server.git
cd mcp-database-server
npm install
npm run build

We will next install the MCP server globally with:

npm install -g @executeautomation/database-server

We will run an SQLite using a sample Northwind database.

• Download the database file from http://medhat.ca/blog/northwind-sqlite.db.zip.
• Extract northwind-sqlite.db from the zip file and place it in the current working directory.

To use the MCP server with our SQLite database, run the following terminal window command:

node dist/src/index.js ./northwind-sqlite.db

Keep the above terminal window open and running.

Configuring VS Code

Open VS Code. Click on the settings gear in the bottom-left corner, followed by Settings.

In the search field, enter MCP, then click on "Edit in settings.json".

Under the mcp >> servers section, add the following MCP server settings:

"sqlite": {
  "command": "npx",
  "args": [
    "-y",
    "@executeautomation/database-server",
    "/path/to/your/northwind-sqlite.db"
  ]
},

Click on Start:

Open the GitHub Copilot Chat panel:

In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.

Click on the tools icon in the prompt window.

You will see a list of commands that the MCP server can carry out with SQLite.

We can now start querying the database using natural language. Start with this prompt:

You have access to the Northwind database through an MCP server. What are the tables in the database?

It detects that it can use the list_tables command.

Click on Continue. I got the following output:

Similarly, you can ask another question like: 

Display the contents of the suppliers table.

Yet, another question:

What are the products supplied by "Exotic Liquids"?

Conclusion

It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development.

MCP server that connects VS Code to PostgreSQL Database

In this tutorial we will configure VS Code to use an MCP Server that connects to a PostgreSQL database. In a similar manner, you can also connect to SQLite, MySQL, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.

Prerequisites

You will need to install the following software to proceed:

• Visual Studio Code with the GitHub Copilot Chat extension
• Docker Desktop
• Latest versions of node.js, npm, and npx

The Database

We will run a PostgreSQL database with a sample Northwind database in a Docker container. Therefore:

• Start Docker Desktop on your computer
• Run a PostgreSQL container by executing this command in a terminal window:

docker run --name psqlnw -e POSTGRES_PASSWORD=VerySecret -p 5433:5432 -d melmasry/nw-psql:1.0.0

The Database MCP Server

We will be using the MCP Server from the mcp-database-server GitHub Repo. Visit https://github.com/executeautomation/mcp-database-server for more details.

Install and configure the PostgreSQL MCP server

In a suitable working directory, clone the repo, then build, and publish the code by executing these commands in a terminal window:

git clone https://github.com/executeautomation/mcp-database-server.git
cd mcp-database-server
npm install
npm run build

We will next install the MCP server globally with:

npm install -g @executeautomation/database-server

To use the MCP server with our MySQL database, run the following terminal window command:

node dist/src/index.js --postgresql --host localhost --port 5433 --database northwind --user postgres --password VerySecret

Keep the above terminal window open and running.

Configuring VS Code

Open VS Code. Click on the settings gear in the bottom-left corner, followed by Settings.

In the search field, enter MCP, then click on "Edit in settings.json".

Under the mcp >> servers section, add the following MCP server settings:

"postgresql": {
  "command": "npx",
  "args": [
    "-y",
    "@executeautomation/database-server",
    "--postgresql",
    "--host", "localhost",
    "--port", "5433",
    "--database", "northwind",
    "--user", "postgres",
    "--password", "VerySecret"
  ]
},

Click on Start:

Open the GitHub Copilot Chat panel:

In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.

Click on the tools icon in the prompt window.

You will see a list of commands that the MCP server can carry out with PostgreSQL.

We can now start querying the database using natural language. Start with this prompt:

You have access to the Northwind database through an MCP server. What are the tables in the database?

It detects that it can use the list_tables command.

Click on Continue. I got the following output:

Similarly, you can ask another question like: 

 
Display the contents of the suppliers table.

Yet, another question:

     
What are the products supplied by "Exotic Liquids"?

Conclusion

It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development. 

MCP server that connects VS Code to MySQL database

In this tutorial we will configure VS Code to use an MCP Server that connects to a MySQL database. In a similar manner, you can also connect to SQLite, PostgresDB, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.

Prerequisites

You will need to install the following software to proceed:

• Visual Studio Code with the GitHub Copilot Chat extension
• Docker Desktop
• Latest versions of node.js, npm, and npx

The Database

We will run a MySQL database with a sample Northwind database in a Docker container. Therefore:

• Start Docker Desktop on your computer
• Run a MySQL container by executing this command in a terminal window:

docker run -p 3366:3306 --name mariadb-nw -e MYSQL_ROOT_PASSWORD=secret -d melmasry/maria-nw:1.0.0

The Database MCP Server

We will be using the MCP Server from the mcp-database-server GitHub Repo. Visit https://github.com/executeautomation/mcp-database-server for more details.

Install and configure the MySQL MCP server

In a suitable working directory, clone the repo, then build, and publish the code by executing these commands in a terminal window:

git clone https://github.com/executeautomation/mcp-database-server.git
cd mcp-database-server
npm install
npm run build

We will next install the MCP server globally with:

npm install -g @executeautomation/database-server

To use the MCP server with our MySQL database, run the following terminal window command:

node dist/src/index.js --mysql --host localhost --database northwind --port 3366 --user root --password secret

Keep the above terminal window open and running.

Configuring VS Code

Open VS Code. Click on the settings gear in the bottom-left corner, followed by Settings.

In the search field, enter MCP, then click on "Edit in settings.json".

Under the mcp >> servers section, add the following MCP server settings:

 
"mysql": {
  "command": "npx",
  "args": [
    "-y",
    "@executeautomation/database-server",
    "--mysql",
    "--host", "localhost",
    "--database", "northwind",
    "--port", "3366",
    "--user", "root",
    "--password", "secret"
  ]
}

Click on Start:

Open the GitHub Copilot Chat panel:

In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.

Click on the tools icon in the prompt window.

You will see a list of commands that the MCP server can carry out with MySQL.

We can now start querying the database using natural language. Start with this prompt:

You have access to the Northwind database through an MCP server. What are the tables in the database?

Click on Continue. I got the following output:

Similarly, you can ask another question like: 

  
Display the contents of the suppliers table.

Yet, another question:

What are the products supplied by "Exotic Liquids"?

Conclusion

It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development. 

MCP server that connects VS Code to SQL Server

In this tutorial we will configure VS Code to use an MCP Server that connects to a SQL Server database. In a similar manner, you can also connect to SQLite, PostgresDB, and MySQL. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.

Companion Video: https://youtu.be/Ymxyjm3YrCI

Prerequisites

You will need to install the following software in order to proceed:

• Visual Studio Code with the GitHub Copilot Chat extension
• Docker Desktop
• Latest versions of node.js, npm, and npx

The Database

We will run a SQL Server database with the well known Northwind sample database in a Docker container. Therefore:

• Start Docker Desktop on your computer
• Run a SQL Server container by executing this command in a terminal window:

docker run --cap-add SYS_PTRACE -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD=SqlPassword! -p 1333:1433 --name nw -d melmasry/my-sqlserver-northwind:latest

The Database MCP Server

We will be using the MCP Server from the mcp-database-server GitHub Repo. Visit https://github.com/executeautomation/mcp-database-server for more details

Install and configure the SQL Server MCP server

In a suitable working directory, clone the repo, then build, and publish the code by executing these commands in a terminal window:

 
git clone https://github.com/executeautomation/mcp-database-server.git
cd mcp-database-server
npm install
npm run build

We will next install the MCP server globally with:

  
npm install -g @executeautomation/database-server

To use the MCP server with our SQL Server database, run the following terminal window command:

  
node dist/src/index.js --sqlserver --server localhost --port 1333 --database Northwind --user sa --password SqlPassword!

Keep the above terminal window open and running.

Configuring VS Code

Open VS Code. Click on the settings gear in the bottom-left corner, followed by Settings.

In the search field, enter MCP, then click on "Edit in settings.json".

Under the mcp >> servers section, add the following MCP server settings:

"sqlserver": {
  "command": "npx",
  "args": [
	"-y",
	"@executeautomation/database-server",
	"--sqlserver",
	"--server", "localhost",
	"--port", "1333",
	"--database", "Northwind",
	"--user", "sa",
	"--password", "SqlPassword!"
  ]
}

Click on Start:

Open the GitHub Copilot Chat panel:

In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.

Click on the tools icon in the prompt window.

You will see a list of commands that the MCP server can carry out with SQL Server

We can now start querying the database using natural language. Start with this prompt:

You have access to the Northwind database through an MCP server. What are the tables in the database?

It detects that it can use the list_tables command.

Click on Continue. I got the following output:

Similarly, you can ask another question like: 

Display the contents of the suppliers table.

Yet, another question:

What are the products supplied by "Exotic Liquids"?

Conclusion

It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers to any client C# application. MCP Servers open a ton of possibilities for AI aided software development. 

Build a socket.io chat web app with node.js

Socket.IO is a JavaScript library for real-time web applications. It enables real-time, bi-directional communication between web clients and servers. 

This article addresses the very basic building blocks to creating a socket.io  application in Node.js. 

Source code: https://github.com/medhatelmasry/socket-chat.git

Pre-requisites

The following pre-requisites are assumed:

• The latest versions of Node.js & npm are installed on your computer
• You have some basic knowledge of Node.js and Express

Getting started

In a working directory, create a folder for our web app named socket-chat then change into that directory with the following commands:

mkdir socket-chat

cd socket-chat

Initialize a Node.js application with the following command:

npm init -y

Install packages ejs,  express, nodemon and  socket.io with the following command:

npm install ejs express nodemon socket.io

We need to create our server. To accomplish this, create a file in the root of your app named index.js with the the following content:

const express = require("express");

const app = express();

const server = require("http").createServer(app);

const io = require("socket.io")(server, { cors: { origin: "*" } }); 

app.set("view engine", "ejs");

app.get("/", (req, res) => {

  res.render("index", {title: "Socket.io chat example"});

}); 

const port = process.env.PORT || 3030;

server.listen(port, () => {

  console.log(`Server started and listening on port ${port}`);

});

// emit & receive messages

io.on('connection', (socket) => {

  console.log(`Someone connected with socket id: ${socket.id}`);

});

What does the above code do?

• we initialize express, server, and io 
• EJS is declared as our view engine
• the / route points to a view page named index. This will be created later.
• the server will be started on port 3030
• whenever someone connects, the unique socket id is displayed in the server console

Edit file package.json and add the following to the "scripts" section:

"dev": "nodemon index.js"

The above allows us to run the web app using command "npm run dev". The advantage of nodemon is that it watches for any changes in the file system and restarts the server whenever a file changes.

 

Create a folder named views and add to it a file named index.ejs with the following content:

<!DOCTYPE html>

<html lang="en">

<head>

    <title><%- title %></title>

    <script src="https://cdn.socket.io/socket.io-3.0.1.min.js"></script>

    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"/>

</head>

<body>

    <div class="container">

        <h3><%- title %></h3>

    </div>

</body>

</html>

Also in views/index.ejs, add the following JavaScript code below the closing </div> tag:

<script>

    const socket = io('http://localhost:3030');

    socket.on("connection", () => {});

</script>

The above code does a web socket hand-shake with the server. Start your server, if it is not started already, then point your browser to http://localhost:3030. The page looks like this:

More interesting is that your console window reveals information about socket IDs:

This proves that we have established a socket.io connection between the server and clients. Let us write some code that sends messages from one client to all the others. To do that, we will create a simple input field and a button in our frontend view. Add the following markup in views/index.ejs right under " <h3><%- title %></h3>":

<input type="text" id="msg" />

<button onclick="sendMessage()" class="btn btn-success btn-sm">

   Send Message

</button>

<hr />

<ul id="msgList" class="list-group"></ul>

In our backend index.js file,  add the following just under the console.log(. . .) statement:

socket.on('chat', (data) => {

   console.log(data);

   // broadcast the same message to all except the sender

   socket.broadcast.emit('chat', data);

});

The above code indicates that when a message named "chat" is received, it will broadcast that same message to all other clients except the sender. The variable data represents the actual message.

Now, back in our views/index.ejs frontend, we will actually emit (or send) a message. Type the following code right under "socket.on("connection", () => {});":

const sendMessage = () => {

   const msgInput = document.querySelector("#msg");

   socket.emit("chat", msgInput.value);

   document.querySelector("#msg").value = "";

}

The above is the sendMessage() handler for the button click. It sends the text entered by user to the message event named chat and, subsequently, clears the input field.

To actually see the message that is being passed around, we need to add some code to the frontend that will append each chat message received to the <ul> . . . </ul> unordered list with id msgList. Add this code below the "const sendMessage = () => { . . . . });" block: 

socket.on("chat", (data) => {

  const msg = data

    .replace(/&/g, "&amp;")

    .replace(/</g, "&lt;")

    .replace(/>/g, "&gt;");

  var li = document.createElement("li");

  li.classList.add("list-group-item", "list-group-item-warning");

  li.textContent = msg;

  document.querySelector("#msgList").appendChild(li);

});

What does the above code do?

• data is sanitized such that & is replaced by &amp; and < is replaced by &lt; and > is replaced by &gt;
• an li element is created and given bootstrap styling
• data is assigned as content to the li element
• finally, the li item is appended to the unordered list with id msgList

Testing our chat app

Point two (or more) browser windows to http://localhost:3030. In the first window enter a message then click on "Send message". You will notice the second window will pickup the same message:

This is a very simple socket.io app. I hope you will use this starting point to do much more useful and interesting applications.

Reference: Socket.IO Tutorial (tutorialspoint.com)