Cómo crear un CRUD básico utilizando Node.js

por Jhoan Steven Delgado, el 13 de octubre de 2020

h_CRUD_basico_utilizando_Node_js

Continuando con nuestras lecciones sobre Cómo crear una base de datos en Amazon RDS, en la lección anterior vimos cómo conectarse a una instancia de BD con PGAdmin, en esta ocasión te enseñaremos a crear un CRUD básico utilizando Node.js para conectarte a la BD.

Experimentando con la BD

Una vez creada nuestra BD, ya podemos proceder a crear tablas y realizar consultas:

1. Creemos una tabla llamada customer. Hacemos click derecho en la BD Empresa y luego en query tool e ingresamos los siguiente query:

CREATE TABLE customer
(
id serial NOT NULL,
name character varying(200) NOT NULL,
address text NOT NULL,
email character varying(200) NOT NULL,
phone character varying(20) NOT NULL
)

insert into customer (id, name, address, email, phone) values (1, 'Whitaker Howchin', '68 Granby Hill', 'whowchin0@spotify.com', '752-199-8765');
insert into customer (id, name, address, email, phone) values (2, 'Aileen Penhalewick', '652 Cherokee Trail', 'apenhalewick1@topsy.com', '360-935-9390');
insert into customer (id, name, address, email, phone) values (3, 'Abbot Bosquet', '2236 Park Meadow Center', 'abosquet2@google.es', '829-941-5514');
insert into customer (id, name, address, email, phone) values (4, 'Sim Lawles', '05863 Center Parkway', 'slawles3@timesonline.co.uk', '902-432-9461');
insert into customer (id, name, address, email, phone) values (5, 'Ivy Casemore', '2 Buhler Pass', 'icasemore4@examiner.com', '157-846-5229');
insert into customer (id, name, address, email, phone) values (6, 'Delphinia MacNaughton', '9033 Westridge Pass', 'dmacnaughton5@japanpost.jp', '247-801-9190');
insert into customer (id, name, address, email, phone) values (7, 'Melissa Wilfing', '64 Eliot Circle', 'mwilfing6@yahoo.com', '474-343-0893');
insert into customer (id, name, address, email, phone) values (8, 'Kerstin Ixor', '0 Troy Drive', 'kixor7@newsvine.com', '935-682-9550');
insert into customer (id, name, address, email, phone) values (9, 'Benetta Wythill', '5513 New Castle Center', 'bwythill8@php.net', '234-885-0619');
insert into customer (id, name, address, email, phone) values (10, 'Fanchon Paoletto', '1467 Banding Street', 'fpaoletto9@biglobe.ne.jp', '950-130-4021');
insert into customer (id, name, address, email, phone) values (11, 'Kylen Hoppner', '256 Blackbird Street', 'khoppnera@noaa.gov', '738-158-2679');
insert into customer (id, name, address, email, phone) values (12, 'Enoch Cristoforetti', '54 Quincy Park', 'ecristoforettib@soup.io', '457-694-3396');
insert into customer (id, name, address, email, phone) values (13, 'Sacha Brunini', '2857 Colorado Terrace', 'sbruninic@paypal.com', '502-355-5367');
insert into customer (id, name, address, email, phone) values (14, 'Olly Brothwood', '40 Pond Point', 'obrothwoodd@weebly.com', '876-635-9232');
insert into customer (id, name, address, email, phone) values (15, 'Zacharia MacDonell', '58299 Kenwood Alley', 'zmacdonelle@mayoclinic.com', '435-401-5704');
insert into customer (id, name, address, email, phone) values (16, 'Petra Goggen', '67917 Lien Lane', 'pgoggenf@prnewswire.com', '900-518-9385');
insert into customer (id, name, address, email, phone) values (17, 'Bernardina Pocknoll', '186 Kennedy Trail', 'bpocknollg@skype.com', '162-842-9155');
insert into customer (id, name, address, email, phone) values (18, 'Ingelbert Godwyn', '7761 Prentice Point', 'igodwynh@princeton.edu', '463-126-6509');
insert into customer (id, name, address, email, phone) values (19, 'Saloma Frude', '3 Rowland Crossing', 'sfrudei@lulu.com', '836-439-1145');
insert into customer (id, name, address, email, phone) values (20, 'Sherman Balaam', '90109 Cordelia Street', 'sbalaamj@yale.edu', '250-881-7217');
insert into customer (id, name, address, email, phone) values (21, 'Pincas Sloley', '9 Porter Avenue', 'psloleyk@reddit.com', '902-264-9093');
insert into customer (id, name, address, email, phone) values (22, 'Tamar Stiebler', '82 6th Place', 'tstieblerl@java.com', '746-994-7837');
insert into customer (id, name, address, email, phone) values (23, 'Celisse Quadling', '6299 Welch Way', 'cquadlingm@friendfeed.com', '754-953-9854');
insert into customer (id, name, address, email, phone) values (24, 'Tabbie Kubecka', '79 Northridge Alley', 'tkubeckan@kickstarter.com', '533-793-5193');
insert into customer (id, name, address, email, phone) values (25, 'Roseline Beldom', '2054 Norway Maple Circle', 'rbeldomo@tiny.cc', '699-588-9443');
insert into customer (id, name, address, email, phone) values (26, 'Arabel Gierok', '35 Basil Hill', 'agierokp@newsvine.com', '950-519-2971');
insert into customer (id, name, address, email, phone) values (27, 'Dulcie Frowd', '2608 Pankratz Park', 'dfrowdq@va.gov', '242-567-2574');
insert into customer (id, name, address, email, phone) values (28, 'Delila Dallan', '0314 American Point', 'ddallanr@taobao.com', '815-805-0235');
insert into customer (id, name, address, email, phone) values (29, 'Shandra Kirkland', '850 Golf Point', 'skirklands@webnode.com', '986-599-7881');
insert into customer (id, name, address, email, phone) values (30, 'Carin Obee', '0142 Dennis Trail', 'cobeet@hp.com', '205-257-0136');
insert into customer (id, name, address, email, phone) values (31, 'Morena Posnette', '59163 Granby Terrace', 'mposnetteu@zimbio.com', '761-124-0382');
insert into customer (id, name, address, email, phone) values (32, 'Frank Widdup', '15692 Crescent Oaks Place', 'fwiddupv@census.gov', '947-493-5833');
insert into customer (id, name, address, email, phone) values (33, 'Georgena Teissier', '22 West Pass', 'gteissierw@imageshack.us', '841-970-2254');
insert into customer (id, name, address, email, phone) values (34, 'Birk Bianco', '8 Sachtjen Plaza', 'bbiancox@wix.com', '409-237-1293');
insert into customer (id, name, address, email, phone) values (35, 'Aliza Diplock', '9390 Butterfield Avenue', 'adiplocky@opensource.org', '268-497-8353');
insert into customer (id, name, address, email, phone) values (36, 'Amalie Pecht', '108 Duke Avenue', 'apechtz@dmoz.org', '756-166-4368');
insert into customer (id, name, address, email, phone) values (37, 'Corrianne Kamena', '318 Mifflin Plaza', 'ckamena10@zdnet.com', '219-488-9794');
insert into customer (id, name, address, email, phone) values (38, 'Aldwin Learoyde', '9 Ronald Regan Plaza', 'alearoyde11@miitbeian.gov.cn', '304-364-8058');
insert into customer (id, name, address, email, phone) values (39, 'Elie Lysons', '409 Fulton Road', 'elysons12@trellian.com', '330-883-8065');
insert into customer (id, name, address, email, phone) values (40, 'Corena Tomadoni', '9441 Ridge Oak Hill', 'ctomadoni13@elpais.com', '350-623-4861');

Finalmente verificamos que se encuentren los 40 registros que acabamos de ingresar.

Ejemplo de CRUD con Node.js

Una vez tengamos algunos datos en nuestra BD, podemos proceder a realizar un ejemplo de CRUD empleando Node.js.

npm init
npm install pg --save
Crear una clase main.js
código:

const { Client } = require('pg');

client = new Client({
host: 'pragmaexample.cheoaiihkc4s.us-east-1.rds.amazonaws.com',
user: 'pragma',
password: 'Pragma2020',
database: 'Empresa',
});
client.connect()

getAllcustomers()

function getAllcustomers(){

client.query('SELECT * FROM customer', (err, res) => {
if (err) {
console.log(err.stack)
} else {
console.log(res.rows)
client.end(err => {
console.log('client has disconnected')
if (err) {
console.log('error during disconnection', err.stack)
}
})
}
})

}

function insertCostumer(values){

const text = 'INSERT INTO customer(id, name, address, email, phone) VALUES($1, $2, $3, $4, $5) RETURNING *'
client.query(text, values, (err, res) => {
if (err) {
console.log(err.stack)
} else {
console.log("User inserted")
client.end(err => {
console.log('client has disconnected')
if (err) {
console.log('error during disconnection', err.stack)
}
})
}
})

}

function deleteByID(id){

const text = 'DELETE FROM customer WHERE id=$1'
client.query(text, [id], (err, res) => {
if (err) {
console.log(err.stack)
} else {
console.log('User deleted')
client.end(err => {
console.log('client has disconnected')
if (err) {
console.log('error during disconnection', err.stack)
}
})

}
})
}

function getCustomerByID(id){

const text = 'SELECT * FROM customer WHERE id=$1'
client.query(text, [id], (err, res) => {
if (err) {
console.log(err.stack)
} else {
console.log(res.rows[0])
client.end(err => {
console.log('client has disconnected')
if (err) {
console.log('error during disconnection', err.stack)
}
})

}
})

}

function updateCustomerById(values){

const text = 'UPDATE customer SET name=$1, address=$2,email=$3, phone=$4 WHERE id=$5'
client.query(text, values, (err, res) => {
if (err) {
console.log(err.stack)
} else {
console.log('User updated')
client.end(err => {
console.log('client has disconnected')
if (err) {
console.log('error during disconnection', err.stack)
}
})

}
})

}