This note explains how to create your database for the Sales Report template.
This note explains how to create your database for the Sales Report sample template.
NPM packages used for a MySQL database
npm i -S sequelize
npm i -S mysql2
npm i -S tablify
See this blog post to see alternative databases
Copy/Paste and save the code below into Custom functions section to connect your database 👉 (bottom right button f(x))
async function localSalesSQL(sqlQuery) {
const { Sequelize, QueryTypes } = require('sequelize');
const sequelize = new Sequelize("sales", "root", "root", {
dialect: "mysql"/* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */,
host: "localhost"
});
sequelize.authenticate();
const [results, metadata] = await sequelize.query(sqlQuery);
sequelize.close()
return results;
}
-- Mysql schema
CREATE TABLE Customers (
customer_id int NOT NULL AUTO_INCREMENT,
customer_name varchar(255),
region varchar(255),
PRIMARY KEY (customer_id)
);
CREATE TABLE Products (
product_id int NOT NULL AUTO_INCREMENT,
product_name varchar(255),
category varchar(255),
PRIMARY KEY (product_id)
);
CREATE TABLE Sales (
sales_id int NOT NULL AUTO_INCREMENT,
customer_id int,
product_id int,
quantity int,
price DECIMAL(10,2),
sale_date DATE,
channel varchar(255),
PRIMARY KEY (sales_id)
);
INSERT INTO Customers (customer_name, region)
VALUES
('Charlie Chaplin', 'North'),
('Beyonce Beatle', 'South'),
('Mick Mercury', 'East'),
('Diana Drake', 'West'),
('Freddie Fitzgerald', 'North'),
('Amy Adams', 'South'),
('Jennifer Joplin', 'East'),
('Elvis Evans', 'West');
INSERT INTO Products (product_name, category)
VALUES
('Falcon Flux', 'Hypercar'),
('Nebula Navigator', 'Spacecraft'),
('Quantum Quattro', 'Electric Car'),
('Galaxy Glider', 'Airbike'),
('Orion Overdrive', 'Hypercar'),
('Zephyr Zest', 'Spacecraft'),
('Comet Cruiser', 'Electric Car'),
('Astro Antelope', 'Airbike');
INSERT INTO Sales (customer_id, product_id, quantity, price, sale_date, channel)
VALUES
(1, 1, 5, 150000.00, '2023-01-15', 'Facebook'),
(1, 1, 5, 50000.00, '2023-01-15', 'Google'),
(1, 1, 5, 25000.00, '2023-01-15', 'Linkedin'),
(1, 2, 10, 200000.00, '2023-01-20', 'Facebook'),
(2, 1, 3, 150000.00, '2023-02-05', 'Google'),
(3, 3, 8, 60000.00, '2023-02-10', 'Linkedin'),
(4, 4, 2, 50000.00, '2023-02-15', 'Google'),
(2, 2, 5, 200000.00, '2023-02-20', 'Facebook'),
(3, 1, 7, 150000.00, '2023-03-05', 'Referral'),
(4, 3, 4, 60000.00, '2023-03-10', 'Google'),
(1, 4, 6, 50000.00, '2023-03-15', 'Referral'),
(3, 2, 9, 200000.00, '2023-03-20', 'Facebook'),
(5, 5, 7, 180000.00, '2023-04-05', 'Facebook'),
(6, 6, 4, 220000.00, '2023-04-10', 'Facebook'),
(7, 7, 5, 65000.00, '2023-04-15', 'Linkedin'),
(8, 8, 6, 52000.00, '2023-04-20', 'Referral');