Deno API + Postgres: without SQL
Tags: typescript, deno, mvc, postgres
Published: 2020-06-09 22:49:13

# 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](https://en.wikipedia.org/wiki/Single-responsibility_principle). 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](https://deno.land)
> 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_
```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](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-core/properties#setting-your-own-properties).
For this, we will create a file called `properties.json` under `/src/main/resources` (**your-project**/src/main/resources/properties.json)

_properties.json_
```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_
```typescript
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.
```typescript
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](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/orm/models).

**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_)](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/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_

```typescript
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](https://doc.deno.land/https/raw.githubusercontent.com/mandarineorg/mandarinets/master/orm-core/repository/mandarineRepository.ts#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..


```typescript
@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](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-data/mandarine-query-language).

### 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_
```typescript
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. 

```typescript
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](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-core/dependency-injection#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:

```typescript
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)](https://mandarineframework.gitbook.io/mandarine-ts/mandarine-core/core-initialization#single-entry-point-file)

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

_app.ts_
```typescript
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:
```bash
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.
```http
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
```http
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`.
```http
GET /api/get-book/1 HTTP/1.1
Host: localhost:8080
```
and to that request, we are getting:
```json
[
    {
        "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
```json
[
    {
        "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. 
```http
GET /api/all-books HTTP/1.1
Host: localhost:8080
```
which will return the following array
```json
[
    {
        "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](https://mandarineframework.gitbook.io/mandarine-ts/) 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](https://github.com/andreespirela/deno-tutorials/tree/master/database-books-api)

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