Bookstore Application - Database

Overview

This section shows how to create the database layer for the Bookstore application. It contains a database table definition for the BOOKS table, CSV data, CSVIM import definition and TypeScript Repository class.

Steps

Table Definition

Create a project named babylon-project.
Right click on the babylon-project project and select New → Folder.
Enter data for the name of the folder.
Right click on the data folder and select New → Database Table.
Enter BABYLON_BOOKS.table for the name of the database table descriptor.
Right click on BABYLON_BOOKS.table and select Open With → Code Editor.

Replace the content with the following definition:

{
    "name": "BABYLON_BOOKS",
    "type": "TABLE",
    "columns": [
        {
            "name": "BOOK_ID",
            "type": "INTEGER",
            "primaryKey": true,
            "identity": "true",
            "unique": false,
            "nullable": false
        },
        {
            "name": "BOOK_ISBN",
            "type": "CHAR",
            "length": "17",
            "unique": true,
            "primaryKey": false,
            "nullable": false
        },
        {
            "name": "BOOK_TITLE",
            "type": "VARCHAR",
            "length": "120",
            "primaryKey": false,
            "unique": false,
            "nullable": false
        },
        {
            "name": "BOOK_PUBLISHER",
            "type": "VARCHAR",
            "length": "120",
            "primaryKey": false,
            "unique": false,
            "nullable": false
        },
        {
            "name": "BOOK_DATE",
            "type": "DATE",
            "nullable": true,
            "unique": false
        },
        {
            "name": "BOOK_PRICE",
            "type": "DOUBLE",
            "primaryKey": false,
            "unique": false,
            "nullable": false
        }
    ],
    "dependencies": []
}

Save the changes and close the Code Editor.
Double click on BABYLON_BOOKS.table to view the definition with the Table Editor.

Save & Publish

Saving the file will trigger a Publish action, which will create the database table in the target database schema. Usually this action should take several seconds to complete, after which the database table would be visible in the Database Perspective.

Note: Manual Publish can be performed by right clicking on the artifact and selecting Publish from the context menu. The Publish action can be performed also on project level.

CSV Data

Right click on the babylon-project/data folder and select New → File.
Enter books.csv for the name of the file.
Right click on books.csv and select Open With → Code Editor.

Paste the following CSV data:

BOOK_ID,BOOK_ISBN,BOOK_TITLE,BOOK_PUBLISHER,BOOK_DATE,BOOK_PRICE
10001,978-3-598-21500-1,Beartown,Simon & Schuster,2019-05-01,17.0
10002,978-3-598-21501-8,Beneath a Scarlet Sky,Lake Union Publishing,2017-05-01,9.74
10003,978-3-598-21529-2,Dead Certain,Free Press,2007-09-04,7.19
10004,978-3-598-21550-6,Everything We Keep,Lake Union Publishing,2016-08-01,14.65
10005,978-3-598-21550-9,Exit West,Hamish Hamilton,2017-02-27,11.45

Save the changes and close the Code Editor.
Double click on books.csv to view the data with the CSV Editor.

CSVIM

Right click on the babylon-project/data folder and select New → File.
Enter books.csvim for the name of the file.
Right click on books.csvim and select Open With → Code Editor.

Paste the following CSVIM definition:

{
  "files": [
    {
      "table": "BABYLON_BOOKS",
      "schema": "PUBLIC",
      "file": "/babylon-project/data/books.csv",
      "header": true,
      "useHeaderNames": true,
      "delimField": ",",
      "delimEnclosing": "\"",
      "distinguishEmptyFromNull": true,
      "version": ""
    }
  ]
}

Save the changes and close the Code Editor.
Double click on books.csvim to view the definition with the CSVIM Editor.

Database

Save & Publish

Once the file is saved a Publish action would be triggered, which will result into the data from the CSV file to be imported to the database table.

Note: Navigate to the Database Perspective to check that the BABYLON_BOOKS table is created and perform the following SQL query to check that the data from the CSV file is imported.

select * from BABYLON_BOOKS;

Repository

Right click on the babylon-project project and select New → File.
Enter tsconfig.json for the name of the File.

Replace the content with the following:

{
    "compilerOptions": {
        "module": "ESNext"
    }
}

Right click on the babylon-project project and select New → File.
Enter project.json for the name of the File.

Replace the content with the following:

{
    "guid": "babylon-project",
    "actions": [
        {
            "name": "Build TypeScript",
            "commands": [
                {
                    "os": "unix",
                    "command": "tsc"
                },
                {
                    "os": "windows",
                    "command": "cmd /c tsc"
                }
            ],
            "registry": "true"
        }
    ]
}

TypeScript Compilation

The tsconfig.json and project.json files are needed for the compilation of the TypeScript files. In order to run the compilation a Publish action should be performed on the Project level (right click on the project and select Publish).

Right click on the babylon-project/data folder and select New → TypeScript Service.
Enter BookRepository.ts for the name of the TypeScript Service.

Replace the content with the following code:

import { dao as daoApi } from "sdk/db"

export interface Book {
    readonly id?: number;
    readonly isbn: string;
    readonly title: string;
    readonly publisher: string;
    readonly date: Date;
    readonly price: number;
}

export class BookRepository {

    private repository;

    constructor(dataSourceName?: string, logCtxName?: string) {
        this.repository = daoApi.create({
            table: "BABYLON_BOOKS",
            properties: [
                {
                    name: "id",
                    column: "BOOK_ID",
                    type: "INTEGER",
                    id: true,
                    required: true
                }, {
                    name: "isbn",
                    column: "BOOK_ISBN",
                    type: "CHAR",
                    id: false,
                    required: false
                }, {
                    name: "title",
                    column: "BOOK_TITLE",
                    type: "VARCHAR",
                    id: false,
                    required: false
                }, {
                    name: "publisher",
                    column: "BOOK_PUBLISHER",
                    type: "VARCHAR",
                    id: false,
                    required: false
                }, {
                    name: "date",
                    column: "BOOK_DATE",
                    type: "DATE",
                    id: false,
                    required: true
                }, {
                    name: "price",
                    column: "BOOK_PRICE",
                    type: "DOUBLE",
                    id: false,
                    required: true
                }]
        }, logCtxName, dataSourceName);
    }

    public list = (settings?): Book[] => {
        return this.repository.list(settings);
    };

    public findById = (id: number): Book | null => {
        return this.repository.find(id);
    };

    public create = (entity: Book): Book => {
        return this.repository.insert(entity);
    };

    public update = (entity: Book): Book => {
        return this.repository.update(entity);
    };

    public deleteById = (id: number): void => {
        this.repository.remove(id);
    };

    public count = (): number => {
        return this.repository.count();
    }
}

Save & Publish

In order to run the compilation of TypeScript files a Publish action should be performed on the Project level (right click on the project and select Publish).

db/dao

Take a look at the db/dao documentation for more details about the API.

Next Steps

Section Completed

After completing the steps in this tutorial, you would have:

Database table named BABYLON_BOOKS.
Initial data imported into the database table.
TypeScript repository class to perform basic data operations.

Continue to the API section to build a REST API for the Book entity.

Note: The complete content of the Bookstore tutorial is available at: https://github.com/dirigiblelabs/tutorial-babylon-project