loading...

Deno API + Postgres: without SQL

andreespirela profile image andreespirela ・9 min read

Introduction

Throughout my years of programming, I have seen many applications that combine the database layer with the business logic of the app. In one way or the other, some ORM's fail to disperse the concepts of application layers & make your app not singly-responsible. When I was first getting into programming, I did this too but after learning & learning, it started seeming like an odd thing to do.
In this article, we will be exploring how to create a simple API that will run on Deno and will use Postgres as its database, but the interesting thing is that we will not use SQL whatsoever, thus, we won't mix the business logic of our application with the database layer.

What is Deno?

According to the Deno website

Deno is a simple, modern and secure runtime for JavaScript and TypeScript that uses V8 and is built in Rust.
It was first created by Ryan Dahl, the same creator of Node.JS.

What we'll need

We will be exploring the following

  • Deno
  • Mandarine.TS framework (A framework to build applications, specially web apps)
  • Mandarine.TS ORM
  • PostgreSQL

Goals

  • Create a simple API that uses Postgres without writing SQL on our application.

Getting Started

Step 1: tsconfig.json

Mandarine requires us to use a tsconfig.json in order for the typescript compiler to work properly.

tsconfig.json

{
    "compilerOptions": {
        "strict": false,
        "noImplicitAny": false,
        "noImplicitThis": false,
        "alwaysStrict": false,
        "strictNullChecks": false,
        "strictFunctionTypes": true,
        "strictPropertyInitialization": false,
        "experimentalDecorators": true,
        "emitDecoratorMetadata": true,
        "allowUmdGlobalAccess": false,
    }
}

Step 2: Database configuration

In order for Mandarine's internal ORM to work, we will need to establish our connection configuration inside Mandarine's property file.
For this, we will create a file called properties.json under /src/main/resources (your-project/src/main/resources/properties.json)

properties.json

{
    "mandarine": {
        "dataSource": {
            "dialect": "postgresql",
            "data": {
                "host": "localhost",
                "port": 5432,
                "username": "postgres",
                "password": "",
                "database": ""
            }
        }
    }
}

Now you just have to put the your connection information.

Step 3: Creating our Model.

A model is an object that represents an entity (table) in our database. The properties are used to represent columns.
To create our model, we will create a folder called "database" (which we will divide into two folders: models and repositories). Inside the models folder, we will locate our model called: booksModel.ts

booksModel.ts

import { Table } from "https://deno.land/x/mandarinets/mod.ts";

export interface IBook {
    id: number;
    name: string;
    author: string;
}

@Table({ schema: "public", name: "books"})
export class BooksModel implements IBook {

}

In the above example, we are importing the decorator Table from Mandarine.TS framework repository & we are decorating our class BooksModel with it, basically telling Mandarine that this class is an entity. Note that if you do not specify the property name in the decorator then Mandarine will take the name of the class as it.

Step 4: Adding columns to our Model.

Now that we have created our model, it's time to add some columns.

Our class BooksModel (booksModel.ts) will look like this.

import { Table, Id, GeneratedValue, Column } from "https://deno.land/x/mandarinets/mod.ts";

export interface IBook {
    id: number;
    name: string;
    author: string;
}

@Table({ schema: "public", name: "books"})
export class BooksModel implements IBook {

    @Column()
    @Id()
    @GeneratedValue({ strategy: "SEQUENCE" })
    public id: number;

    @Column()
    public name: string;

    @Column()
    public author: string;
}

In the code above, we are indicating that we will have 3 columns: id, name, and author (We are decorating them with @Column), but we are also indicating that the column id will be a primary key since we are decorating it with @Id & we are also indicating that it will be auto-increment (@GeneratedValue). Note that GeneratedValue must always be used if we want to insert data directly from Mandarine's ORM.
There is a lot of documentation on how to use those three decorators. Click here to see the official documentation.

Note that you do not have to create the table manually. Instead, Mandarine will create this table (if it doesn't exist) called books at mandarine compile time so you do not have to worry about handling database operations at all.

Step 5: Creating our SQL-free Repository

A repository is a class that handles your database queries. Unlike many ORM providers, Mandarine.TS framework gives us one of its most amazing features: MQL (Mandarine Query Language), which is the equivalent to the CRUD interface in Java for example. Mandarine-powered repositories are extremely easy to use and practical as they save us a lot of time and code.
We will create our repository under the folder database/repositories that we created during Step 3.

booksRepository.ts

import { Repository, MandarineRepository } from "https://deno.land/x/mandarinets/mod.ts";
import { BooksModel } from "../models/booksModel.ts";

@Repository()
export abstract class BooksRepository extends MandarineRepository<BooksModel> {

    constructor() {
        super(BooksModel);
    }

}

In the code above, we have created the an abstract class BooksRepository and we have decorated it with @Repository, this way, we will let Mandarine know that this class should be considered a Mandarine-powered repository. Note that for a class to be a Mandarine-powered repository, we must extend it to MandarineRepository which is a generic class that will take our model. After extending it, we need to call super from our repository's constructor. super will take one argument which is the class representation of our model (without being initialized, just the class)

Step 6: Adding methods to our repository

Now that we have created our repository, we will be adding methods that we will use later to request our database. This is where the fun begins because these methods we will write will be empty methods, without anything inside but they will actually bring or send information to our database. Let's see..

@Repository()
export abstract class BooksRepository extends MandarineRepository<BooksModel> {

    constructor() {
        super(BooksModel);
    }

    public findById(id: number) {}
    public findByAuthor(author: string) {}
    public findByName(name: string) {}
    public findByIdAndName(id: number, name: string) {}
}

In the example above, we have added 4 new methods to our repository: findById, findByAuthor, findByName, findByIdAndName.
Note our columns in the model have to match the name in the name of our methods, we can't just write whatever (This should be self-explanatory but I don't mind taking the time to remind about it).
These methods will then be processed by MQL.

Step 7: Creating our Controller

In this step, we will be creating our API controller which we will request to interact with our database. We will be injecting our repository in this controller but note, for production environments, I would highly recommend to have a service where your repository will be injected, and the service class will be the one to interact with your controller, although, since this is a tutorial, I will not get into that to not make it harder.

We will create a folder in the root of our project called "controllers" and in that folder we will create the following file

booksController.ts

import { Controller } from "https://deno.land/x/mandarinets/mod.ts";

@Controller('/api')
export class BooksController {

}

In the above example, we have created our controller which will have a base route which is /api, this means, all the endpoints that belong to this controller will be part of the URL /api.

Step 8: Injecting our repository in our controller

Now that we have created our controller, it's time to inject our repository by using Mandarine's Dependency Injection.

import { Controller } from "https://deno.land/x/mandarinets/mod.ts";
import { BooksRepository } from "../database/repositories/booksRepository.ts";

@Controller('/api')
export class BooksController {

    constructor(private readonly booksRepository: BooksRepository){}

}

While it is true that we could use the @Inject decorator, the best practice is to do injections in the constructor of our component.

Step 9: Creating endpoints

In this step, we will be adding the endpoints we will request to interact with our database.

Our booksController.ts will look like this:

import { Controller, POST, RequestBody, GET, RouteParam, QueryParam } from "https://deno.land/x/mandarinets/mod.ts";
import { BooksRepository } from "../database/repositories/booksRepository.ts";
import { IBook, BooksModel } from "../database/models/booksModel.ts";

@Controller('/api')
export class BooksController {

    constructor(private readonly booksRepository: BooksRepository){}

    @POST('/add-book')
    public async addBook(@RequestBody() bookToAdd: IBook) {

        let newBook: BooksModel = new BooksModel();
        newBook.author = bookToAdd.author;
        newBook.name = bookToAdd.name;
        await this.booksRepository.save(newBook);

        return { msg: "New book have been added" };
    }

    @GET('/get-book/:id')
    public async getBookById(@RouteParam() id: number) {
        return await this.booksRepository.findById(id);
    }

    @GET('/:author/books')
    public async getBooksByAuthor(@RouteParam() author: string) {
        return await this.booksRepository.findByAuthor(author);
    }

    @GET('/search')
    public async getBooksByIdAndName(@QueryParam() id: number, @QueryParam() name: string) {
        return await this.booksRepository.findByIdAndName(id, name);
    }

    @GET('/all-books')
    public async getAllBooks() {
        return await this.booksRepository.findAll();
    }

}

I know, it can seem like a lot of code so let me explain some of it.

  • addBook: Add a book to our database using repository.save, the method save in our repository is a mandarine reserved keyword for repositories, and it will resolve the insertion of a new row.
  • getAllBooks: List all the books that we have in our database by using repository.findAll. findAll is another Mandarine reserved keyword for repositories, it is responsible for bringing all the rows of that specific table.

Step 10: Creating our single-entry point file.

The single entry-point file is a file where we will import all our components in order for Mandarine & Deno to compile them properly. You can read more about this in Mandarine's official Documentation (Click here)

We will create this file in the root of our project and we'll call it entry-point.ts

app.ts

import { BooksController } from "./controllers/booksController.ts";
import { BooksRepository } from "./database/repositories/booksRepository.ts";
import { MandarineCore } from "https://deno.land/x/mandarinets/mod.ts";

const controllers = [BooksController];
const repositories = [BooksRepository];

new MandarineCore().MVC().run();

Step 10: Running our app.

Now to run our app, we will run the following command:

deno run --config tsconfig.json --allow-net --allow-read entry-point.ts

Step 11: Interacting.

As we coded before, we have an endpoint for adding books which is a POST-type endpoint. Now we will send some JSON data to it.
I personally use postman, but you can use any REST client you'd like.

POST /api/add-book HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
    "name": "The Diary Of a Young Girl",
    "author": "Anne Frank"
}

And let's add one more book

POST /api/add-book HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
    "name": "Sapiens: A Brief History of Humankind",
    "author": "Yuval Noah Harari"
}

Getting a book by its ID

/get-book/:id

Now, let's try our endpoint /get-book/:id.

GET /api/get-book/1 HTTP/1.1
Host: localhost:8080

and to that request, we are getting:

[
    {
        "id": 1,
        "name": "The Diary Of a Young Girl",
        "author": "Anne Frank"
    }
]

Getting books by its author

/:author/books

Now, let's try our endpoint /:author/books.

GET /api/Yuval Noah Harari/books HTTP/1.1
Host: localhost:8080

which will make us get

[
    {
        "id": 2,
        "name": "Sapiens: A Brief History of Humankind",
        "author": "Yuval Noah Harari"
    }
]

Searching a book by name & id

/search

Our first book (1) is The Diary Of a Young Girl
Now let's try to find it by using this endpoint

GET /api/search?id=1&name=The Diary Of a Young Girl HTTP/1.1
Host: localhost:8080

which will return

[
    {
        "id": 1,
        "name": "The Diary Of a Young Girl",
        "author": "Anne Frank"
    }
]

Getting all books in our table.

/all-books

Finally, let's test our endpoint which is responsible for returning all the books we have added.

GET /api/all-books HTTP/1.1
Host: localhost:8080

which will return the following array

[
    {
        "id": 1,
        "name": "The Diary Of a Young Girl",
        "author": "Anne Frank"
    },
    {
        "id": 2,
        "name": "Sapiens: A Brief History of Humankind",
        "author": "Yuval Noah Harari"
    }
]

There is more

  • Mandarine's ORM has reserved methods such as findAll or save that will save you a lot of code already. But there is more to it. Mandarine has countAll (returns the amount of rows in the table), deleteAll (deletes all rows) and the possibility to write existsBy... which would return a boolean value after evaluating if a row exists => existsById would be a good example of this.
  • If you would like to learn more about Mandarine.TS framework & specially Mandarine.TS ORM, don't hesitate to visit its official documentation and follow me.

Summary

What we did:

  • We created an API with Deno, Mandarine & Postgres
  • We used Mandarine.TS framework ORM
  • We added & requested data from our database without having to write any SQL whatsoever.
  • We created models, repositories & controllers ## Why Mandarine.TS?
  • As we saw in this article and past articles I have written, Mandarine.TS offers a lot of built-in solutions that will save us a lot of code and will allow us to work in a more enterprise way as well as making our code simple & readable.
  • Mandarine.TS is decorator driven which gives us a lot of flexibility on how to write our code.

Note

The intention of this article is to show how to build an API with Deno. The examples shown are simple examples that are not meant to be used in production environments.

Disclaimer

I am the creator of Mandarine.TS Framework.

The end

Get the full source code here

Do you have any question or something to say? If so, please leave a comment. If you like this article, please tweet it!

Posted on by:

andreespirela profile

andreespirela

@andreespirela

Creator of Mandarine.TS Framework. I am addicted to refactoring code. Self-taught since I was 9 years old. I love rockets.

Discussion

markdown guide