MariaDB connection using ORM in Deno


Let’s move into another interesting sections, the database where we store information processed. In this post we are connecting MariaDB local database using ORM , denodb.

We can use the same MySQL connector for MariaDB with almost no change

Denodb-ORM

This is a third party ORM module for deno which will help us to MariaDB in Deno. The ORM module work through Model, so we can perform operations using Model, no worry about confusing, queries and statements.

APP

Our app is a simple Todo application, which store Todo’s in a MariaDB database.

  • create a configuration
  • Create Models
  • Link Model and Sync

Configuration

Usually we kept the database file under a config folder, the name of our file will database.ts and content s follows. In the final steps of the configuration we export the model.

import { Model, Database, MySQLConnector, DataTypes } from 'https://deno.land/x/denodb/mod.ts';
//config for MariaDB using MySQKConnector
const connector = new MySQLConnector({
  database: 'todo_collections',
  host: '127.0.0.1',
  username: 'root',
  password: '123',
  port: 3306, // optional
});


const db = new Database(connector)

// NOTE Models 
class Todo extends Model {
    static table = 'todos';
    static fields = {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true,
        },
        item: {
            type: DataTypes.STRING,
        }
        ,
        description: {
            type: DataTypes.STRING,
        }
    };
}
// NOTE Linking Model with DB
db.link([Todo])
await db.sync()

 export default Todo;

You have to specify the host which is localhost, user and password, database name (you should create them first) and we are ready for the controllers.

Sync () – will create the tables for you. You have to create the database which in not created by the sync. Optionally you can use {drop:true} for deleting table every time, the app runs

SQLite app using denodb in Deno


Let’s move into another interesting sections, the database where we store information processed. In this post we are store data into local SQLite database/

Denodb-ORM

This is a third party ORM module for deno which will help us to connect MySQL, MariaDB, SQLite and Postgres databases. The ORM module work through Model, so we can perform operations using Model, no worry about confusing, queries and statements.

APP

Our app is a simple Todo application, which store Todo’s in a MySQL database.

  • create a configuration
  • Create a SQLite file
  • Create Models
  • Link Model and Sync

Configuration

Usually we kept the database file under a config folder, the name of our file will database.ts and content s follows. In the final steps of the configuration we export the model.

import { Model, Database, SQLite3Connector, DataTypes } from "../deps.ts";

const connector = new SQLite3Connector({
    filepath: './database.sqlite',
});

const db = new Database(connector)

// NOTE Models 
class Todo extends Model {
    static table = 'todos';
    static fields = {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
        },
        item: {
            type: DataTypes.STRING,
        }
        ,
        description: {
            type: DataTypes.STRING,
        }
    };
}
// NOTE Linking Model with DB
db.link([Todo])
await db.sync()

 export default Todo;

Sync () – will create the tables for you. You have to create the database which in not created by the sync.

MySQL connection using ORM in Deno


Let’s move into another interesting sections, the database where we store information processed. In this post we are connecting MySQL local database using ORM , denodb.

Denodb-ORM

This is a third party ORM module for deno which will help us to connectMySQL db in Deno. The ORM module work through Model, so we can perform operations using Model, no worry about confusing, queries and statements.

APP

Our app is a simple Todo application, which store Todo’s in a MySQL database.

  • create a configuration
  • Create Models
  • Link Model and Sync

Configuration

Usually we kept the database file under a config folder, the name of our file will database.ts and content s follows. In the final steps of the configuration we export the model.

import { Model, Database, MySQLConnector, DataTypes } from 'https://deno.land/x/denodb/mod.ts';

const connector = new MySQLConnector({
  database: 'todo_collections',
  host: '127.0.0.1',
  username: 'root',
  password: '123',
  port: 3306, // optional
});


const db = new Database(connector)

// NOTE Models 
class Todo extends Model {
    static table = 'todos';
    static fields = {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true,
        },
        item: {
            type: DataTypes.STRING,
        }
        ,
        description: {
            type: DataTypes.STRING,
        }
    };
}
// NOTE Linking Model with DB
db.link([Todo])
await db.sync()

 export default Todo;

You have to specify the host which is localhost, user and password, database name (you should create them first) and we are ready for the controllers. I use the default port for MySQL.

Sync () – will create the tables for you. You have to create the database which in not created by the sync. Optionally you can use {drop:true} for deleting table every time, the app runs

Dapper ORM for .Net


Dapper is opensource micro ORM framework that will help you access data from database in .Net projects. The project is maintained by stackoverflow community and hosted on GitHub

Usually database query return table which contains rows of information. In the object based programming world require data as class/objects. ORM help us to convert row into an object. It can also perform all CURD operations too.

The ORM can be utilized in Model-View-Model View pattern and it will help minimize the complexity of the code.

Dapper is not a database specific package. It can be used with any database (SQL Server,MySQL, MS Access etc). It uses a IDbConnection for performing operations.

It does not replacing anything at all, a simple example will explain the concept.

Dapper Object List example

using Dapper;

class Group
  {
       public int ID { get; set; }
       public string g_name { get; set; }
       
       public double g_dr_loc { get; set; }
       public static List<Group> GetAll()
      {
           using (IDbConnection db = new System.Data.OleDb.OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["cstring"].ConnectionString))
          {
               return db.Query<Group>("select id,g_name from groups").ToList();
          }
      }

  }

//calling
var groups=Group.GetAll()

Here in the example, the Group model’s GetAll() static method will query all the rows into List of Group(model) using the Dapper. Without dapper you have to use Linq or a loop to store the row as a list of objects.

You can see how Linq can be used to achieve this in the following posts

Linq – suming rows,create list of custom fields,create model class object list from data table - Linq - suming rows,create list of custom fieds,create model class list from data table