Dex: SQL Query builder for Deno

import Dex from "https://deno.land/x/dex/mod.ts";

import Dex from "https://deno.land/x/dex/mod.ts";

// Setting the dialect for the query builder
let dex = Dex({client: "sqlite3"});


// Creating a table named Users with many types
let tableQuery = dex.schema.createTable("Users", (table) => {
    table.increments("userId").primary(); // auto incrementing primary key
    table.string("username", 32); // varchar of max length 32
    table.string("firstName"); // varchar of max length 255
    table.string("lastName"); // varchar of max length 255
    table.integer("age").unsigned().notNullable(); // unsigned, non-nullable integer
    table.decimal("funds", 2); // decimal with 2 point precision
    table.float("lastLoginTime") // floating point number with 8 point precision
    table.date("joinDate"); // date only (not datetime)
    table.boolean("isNewUser"); // boolean
    table.text("description"); // text
    table.timestamps(null, true); // createdAt and updatedAt datetimes
}).toString();

console.log(tableQuery);

/**
 * ========================================
 *  Table Query Result
 * ========================================
 *
 * create table `Users` (`userId` integer not null primary key autoincrement, `username` varchar(32), `firstName` varchar(255), `lastName` varchar(255), `age` integer not null, `funds` float, `lastLoginTime` float, `joinDate` date, `isNewUser` boolean, `description` text, `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP)
 */



// Creating and inserting data into the Names table
tableQuery = dex.schema.createTable("Names", (table) => {
    table.string("firstname");
    table.string("lastname");
}).toString();

let insertQuery = dex.queryBuilder()
    .insert([
        {firstname: "hello", lastname: "world"},
        {firstname: "deno", lastname: "land"},
        {firstname: "first", lastname: "last"},
    ])
    .into("Names")
    .toString();

console.log(tableQuery);
console.log(insertQuery);

/**
 * ========================================
 *  Table Query Result
 * ========================================
 *
 * create table `Names` (`firstname` varchar(255), `lastname` varchar(255))
 *
 *
 * ========================================
 *  Insert Query Result
 * ========================================
 *
 * insert into `Names` (`firstname`, `lastname`) select 'hello' as `firstname`, 'world' as `lastname` union all select 'deno' as `firstname`, 'land' as `lastname` union all select 'first' as `firstname`, 'last' as `lastname`
 */



// Selecting all data from the Names table
let selectQuery = dex.select().from("Names").toString();

console.log(selectQuery);

/**
 * ========================================
 *  SELECT Query Result
 * ========================================
 *
 * select * from `Names`
 */



// Selecting specific data from the Names table with a WHERE statement
let whereQuery = dex.select("firstname").from("Names").where({firstname: "hello"}).toString();

console.log(whereQuery);

/**
 * ========================================
 *  WHERE Query Result
 * ========================================
 *
 * select `firstname` from `Names` where `firstname` = 'hello'
 */



// Using all of the available dialects with SELECT, WHERE, and LIMIT statements
const CONFIG_ARRAY = [
    {client: 'mssql',},
    {client: 'mysql',},
    {client: 'mysql2',},
    {client: 'oracledb',},
    {client: 'postgres',},
    {client: 'redshift',},
    {client: 'sqlite3', useNullAsDefault: true},
]

CONFIG_ARRAY.forEach(config => {
    dex = Dex(config);
    
    let sqlQuery = dex.queryBuilder()
        .select("MyColumn")
        .from("MyTable")
        .where({
            myFirstRow: "Hello",
            mySecondRow: "World",
        })
        .limit(15)
        .toString();
    
    console.log(sqlQuery);
});

/**
 * ========================================
 *  MS SQL Query Result
 * ========================================
 *
 * select top (15) [MyColumn] from [MyTable] where [FirstName] = 'Hello' and [LastName] = 'World'
 *
 *
 * ========================================
 *  MySQL Query Result
 * ========================================
 *
 * select `MyColumn` from `MyTable` where `FirstName` = 'Hello' and `LastName` = 'World' limit 15
 *
 *
 * ========================================
 *  MySQL2 Query Result
 * ========================================
 *
 * select `MyColumn` from `MyTable` where `FirstName` = 'Hello' and `LastName` = 'World' limit 15
 *
 *
 * ========================================
 *  OracleDB Query Result
 * ========================================
 *
 * select * from (select "MyColumn" from "MyTable" where "FirstName" = 'Hello' and "LastName" = 'World') where rownum <= 15
 *
 *
 * ========================================
 *  Postgres Query Result
 * ========================================
 *
 * select "MyColumn" from "MyTable" where "FirstName" = 'Hello' and "LastName" = 'World' limit 15
 *
 *
 * ========================================
 *  Redshift Query Result
 * ========================================
 *
 * select "MyColumn" from "MyTable" where "FirstName" = 'Hello' and "LastName" = 'World' limit 15
 *
 *
 * ========================================
 *  Sqlite3 Query Result
 * ========================================
 *
 * select `MyColumn` from `MyTable` where `FirstName` = 'Hello' and `LastName` = 'World' limit 15
 */