MySQL is the most popular database in the2022 Stack Overflow Developer Surveyand counts Facebook, Twitter, YouTube, and Netflix among its users.

View source here.

You can manipulate and query a MySQL database with Deno using themysql2 nodepackage and importing vianpm:mysql2. This allows us to use its Promisewrapper and take advantage of top-level await.

import mysqlfrom"npm:mysql2@^2.3.3/promise";

Connecting to MySQLJump to heading#

We can connect to our MySQL server using thecreateConnection() method. Youneed the host (localhost if you are testing, or more likely a cloud databaseendpoint in production) and the user and password:

const connection=await mysql.createConnection({  host:"localhost",  user:"root",  password:"password",});

You can also optionally specify a database during the connection creation. Herewe are going to usemysql2 to create the database on the fly.

Creating and populating the databaseJump to heading#

Now that you have the connection running, you can useconnection.query() withSQL commands to create databases and tables as well as insert the initial data.

First we want to generate and select the database to use:

await connection.query("CREATE DATABASE denos");await connection.query("use denos");

Then we want to create the table:

await connection.query("CREATE TABLE `dinosaurs` (   `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,   `name` varchar(255) NOT NULL,   `description` varchar(255) )",);

After the table is created we can populate the data:

await connection.query("INSERT INTO `dinosaurs` (id, name, description) VALUES (1, 'Aardonyx', 'An early stage in the evolution of sauropods.'), (2, 'Abelisaurus', 'Abels lizard has been reconstructed from a single skull.'), (3, 'Deno', 'The fastest dinosaur that ever lived.')",);

We now have all the data ready to start querying.

Querying MySQLJump to heading#

We can use the same connection.query() method to write our queries. First we tryand get all the data in ourdinosaurs table:

const results=await connection.query("SELECT * FROM `dinosaurs`");console.log(results);

The result from this query is all the data in our database:

[[{      id:1,      name:"Aardonyx",      description:"An early stage in the evolution of sauropods."},{      id:2,      name:"Abelisaurus",      description:`Abel's lizard" has been reconstructed from a single skull.`},{ id:3, name:"Deno", description:"The fastest dinosaur that ever lived."}],

If we want to just get a single element from the database, we can change ourquery:

const[results, fields]=await connection.query("SELECT * FROM `dinosaurs` WHERE `name` = 'Deno'",);console.log(results);

Which gives us a single row result:

[{ id:3, name:"Deno", description:"The fastest dinosaur that ever lived."}];

Finally, we can close the connection:

await connection.end();

For more onmysql2, check out their documentationhere.