Deno – Raw MySQL/MariaDB Access

In the last tutorial we learned about how to quickly spin up a REST API using deno and oak.

In this tutorial we will use the https://github.com/manyuanrong/deno_mysql library to talk to mysql and do basic CRUD operation (here we won’t be using any ORM’s).

NOTE: Before we use any ORM’s its very important to have a working knowledge of using the base data access libraries. So, that’s the goal of this article.

So, let’s begin by establishing a connection to our database. (I assume you have access to a working mysql or mariadb server).

Connecting to MySQL Server

import { Client } from "https://deno.land/x/mysql/mod.ts";

const client = await new Client().connect({
  hostname: "127.0.0.1",
  username: "root",
  password: "root123",
});

Here we begin by importing the Client from the mysql module. We then create a new instance of Client and invoke the connect method passing in the hostname, username and password.

Create a Database

Let’s now programatically create a database dynamically using the below helper function.

// Create database
async function createDatabase(dbname: string) {
  console.log("Creating database");
  await client.execute(`CREATE DATABASE IF NOT EXISTS ${dbname}`);
}

So, here we created a helper function createDatabase that takes in a dbname as a parameter. Note that our function is marked as async.

We then execute the client.execute method and passing issue the CREATE DATABASE command.

To test the above function execute the below piece of code. On successful execution if you log into MySQL and observe the schema, you should find a database called “blogs”.

await createDatabase("blogs");

Set Current Database

Now there could be lot of databases in your mysql server. To set the current database (which all query works on) use the below helper function.

async function useDatabase(dbname: string) {
  console.log("Set current database");
  await client.execute(`USE ${dbname}`);
}

To set “blogs” as the current database, execute the below code block.

await useDatabase("blogs");

NOTE: Now any insert, update, delete operation using the client object will be affecting the “blogs” database.

Create Table

Let’s now create tables in our databases.

Let’s create a helper method for the same (You can make it even better, I am just putting down my quick draft here).

async function createTable(tablename: string, fields: string, dropIfExist: boolean = false) {
  console.log("Creating table");
  if (dropIfExist) {
    await client.execute(`DROP TABLE IF EXISTS ${tablename}`);
  }

  let sql = `
    CREATE TABLE ${tablename} (
        ${fields}
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  `;
  console.log(`Executing ${sql}`);
  await client.execute(sql);
}

So, our createTable functions takes in the tablename, fields (as strings) and a boolean, dropIfExist which is set to default value of false.

If the dropIfExist is true we execute the drop table statement. Then we finally construct our string representing the create table statement.

Then we execute the create table statement with our blogDB db connection.

Inserting Rows in Table

Let’s add some rows to our newly created table. As usual we will create a helper method to do so.

async function insertRecord(table:string, rowObject: any) {
  console.log("Inserting Records: ", rowObject);
  let sql = `INSERT INTO ${table}(`;
  Object.keys(rowObject).forEach( field => {
    console.log(field);
    sql += field;
    sql += ",";
  });
  sql = sql.substring(0, sql.lastIndexOf(",")) + ")";
  
  let valuesPlaceholder = "values(";
  let values: any = [];
  Object.keys(rowObject).forEach( field => {
    console.log(field);
    valuesPlaceholder += "?";
    valuesPlaceholder += ",";
    values.push(rowObject[field]);
  });

  valuesPlaceholder = valuesPlaceholder.substring(0, 
            valuesPlaceholder.lastIndexOf(",")) + ")";
  
  console.log("SQL: ", sql, valuesPlaceholder, values);

  let result = await client.execute(sql + valuesPlaceholder, values);
  console.log("Insert Record Result: ", result);
}

The above helper method may seem complex but what it essentially doing is outlined below.

• Sets up basic insert statement variable
• Loop through all the keys of rowObject and builds up the fields list for insert
• Then it setsup the values placeholder variable.
• Then loop through and build up the row values list.
• Note, the trailing “,” is removed in both cases.
• Finally we execute the sql statement using our blogDB connection.

Let’s use our helper method to insert couple of posts records.

await insertRecord("posts", {
  title: "Post 1",
  body: "Content 1"
});

await insertRecord("posts", {
  title: "Post 2",
  body: "Content 2"
});

NOTE: This is quite a good initial effort to create a reusable method. You can insert into any table using this method (Provided the table is already created).

Querying Records

Let’s now select all records and columns from posts table.

// Query all posts
const posts = await client.query(`select * from posts`);
console.log("Posts: ", posts);

Let’s select post with an id of 1 (and let’s make the selection dynamic).

// Query with parameters
const postOne = await client.query(
  "select ??, title from ?? where id = ?",
  ["id", "posts", 1]
);

console.log("Post:id:1", postOne);

Update Record

Let’s now update our post record with an id of 1.

let updateResult = await client.execute(`update posts set ?? = ? where id=?`, ["title", "Updated Content 2", 1]);
console.log("Update Result: ", updateResult);

And now the final part the delete method.

Delete Record

Let’s delete the post with id 1.

// Delete
let result = await 
    client.execute(`delete from posts where ?? = ?`, ["id", 1]);
console.log("Delete Result: ", result);

Exercise:

As an exercise try creating reusable query methods for select, update and delete.

Also, if you master any language, library or framework this is the good opportunity to create your own ORM using the above knowledge.

Watch out for future articles, we may exactly do the same.

NOTE: Being a quick write I intentionally kept the code simple, avoided validations etc.