autoscriptorm

Autoscript ORM

$3

Autoscript is a lite TypeScript ORM for PostgreSQL.

NPM Link: https://www.npmjs.com/package/autoscriptorm

$3

``
npm i autoscriptorm
`

$3

- Every table must have the following fields:
- id [uuid, generated, not null]
- is_deleted [boolean, default = false, not null]
- create_date [timestamp, default = now(), not null]
- update_date [timestamp]
- The orm does not keep track of table relationships(Foraign Key).
- The orm does not hard delete any row it sets is_deleted = false for the deleted row.

$3

- CREATE
- UPDATE
- DELETE
- READ
- WHERE = != <> < > <= >= like in not in is null is not null
- ORDER BY ASC DESC
- OFFSET
- LIMIT
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- VIRTUAL COLUMN

$3

#### For raw sql query you can use query() function

`typescript
import { query, configureDB } from 'autoscript'

// 1. Set credentials once at startup
configureDB({
user: 'my_user',
host: 'localhost',
database: 'my_db',
password: 'password123',
port: 1234
})

async function main() {
console.log(await query("SELECT * FROM user"))
}

main()
`

#### A DTO class represents a table and its columns. Every TableDTO should extend DTO class and implement functions
- setChildFromObject(obj: any): void : setter for column data from a object
- setChildFromDBObject(obj: any): void : setter for column data from a db response object
- getChildAsObject(): Omit : getter for column data of the class

#### Also every TableDTO should implement object
- childVarToCol:{[key in keyof Omit]:string} : mapping of db column to class column variables

#### For virtual columns computedVarToCol can be used to map column and expression

#### Here is an example of how to create a DTO:

`typescript
export type DummyDTOType = DTOType & {
firstName: string, // Actual Column
calculatedCol: string //Virtual Column
}

export class DummyDTO extends DTO {

public firstName: string = "" // Variable for actual Column
public calculatedCol: string = "" // Variable for virtual Column

public childVarToCol = {
firstName: "first_name", // Actual column name in the database
calculatedCol: "calculated_col", // Virtual column name
}

public override computedVarToCol = {
// Virtual column expression
calculatedCol: "CONCAT(first_name, 'potato')"
}

constructor() {
// Must pass database table name
super("dummy")
}

// Since calculatedCol is a virtual column we won't set it in the setter
// You can use the helper function (assignIfTypeMatches) to enforce typecheck
protected setChildFromObject(obj: any): void {
obj ??= {}
this.firstName = DTO.assignIfTypeMatches(this.firstName, obj["firstName"], "string") as string
}

// We will set both properties because this is used for db serialization
protected setChildFromDBObject(obj: any): void {
obj ??= {}
this.firstName = obj[this.varToCol["firstName"]]
this.calculatedCol = obj[this.varToCol["calculatedCol"]]
}

// Getter for table data
protected getChildAsObject() {
return {
firstName: this.firstName,
calculatedCol: this.calculatedCol
}
}

}
`

#### TableDAO class is responsible for crud operation. So, after creating the DTO class it is recommanded to create a DAO class for that table.

#### Here is an example of how to create a DAO:

`typescript
import { DummyDTO, type DummyDTOType } from "./Dummy1DTO.ts"
import { DAO } from "autoscript"

export class DummyDAO extends DAO {
public constructor() {
super(Dummy1DTO)
}
}
`

#### Here is an example of how to use the DAO class:

`typescript
import { configureDB } from 'autoscript'
import DummyDAO from 'DummyDAO.ts'

// 1. Set credentials once at startup
configureDB({
user: 'my_user',
host: 'localhost',
database: 'my_db',
password: 'password123',
port: 1234
})

async function main() {

let dummyDAO = new DummyDAO()

// Gets all rows
let dtos = await dummyDAO.execute()
if(dtos != null) {
console.log(dtos.map(e => e.getAsObject()))
}

// [
// {
// id: 04e45af8-bd52-495a-93e5-3cd09c29ea63,
// isDeleted: false,
// createDate: '2025-02-28T06:05:20.380Z',
// updateDate: '',
// firstName: 'Jhon',
// calculatedCol: 'Jhonpotato'
// },
// {
// id: 0e9e0d4f-f390-40dc-b675-91a704e58658,
// isDeleted: false,
// createDate: '2025-02-28T06:05:20.380Z',
// updateDate: '',
// firstName: 'Doe',
// calculatedCol: 'Doepotato'
// }
// ]

// Gets rows that satisfies where clause
dtos = await dummyDAO.where("firstName", "=", "Jhon").execute()
if(dtos != null) {
console.log(dtos.map(e => e.getAsObject()))
}
// [
// {
// id: 04e45af8-bd52-495a-93e5-3cd09c29ea63,
// isDeleted: false,
// createDate: '2025-02-28T06:05:20.380Z',
// updateDate: '',
// firstName: 'Jhon',
// calculatedCol: 'Jhonpotato'
// }
// ]

// Gets rows that satisfies chained where clause
dtos = await dummyDAO
.where("firstName", "=", "Jhon")
.or()
.where("firstName", "=", "Doe")
.execute()
if(dtos != null) {
console.log(dtos.map(e => e.getAsObject()))
}

// [
// {
// id: 04e45af8-bd52-495a-93e5-3cd09c29ea63,
// isDeleted: false,
// createDate: '2025-02-28T06:05:20.380Z',
// updateDate: '',
// firstName: 'Jhon',
// calculatedCol: 'Jhonpotato'
// },
// {
// id: 0e9e0d4f-f390-40dc-b675-91a704e58658,
// isDeleted: false,
// createDate: '2025-02-28T06:05:20.380Z',
// updateDate: '',
// firstName: 'Doe',
// calculatedCol: 'Doepotato'
// }
// ]

}

main()
`

#### For join operation JoinDAO class needs to be used. This class can be used to perform innerJoin, leftJoin, rightJoin and where operations. Also virtual columns can be added based on multiple tables. Here is an example:

`typescript
import { configureDB } from 'autoscript'

let result = await new JoinDAO(new Dummy1DAO())
.innerJoin(new Dummy2DAO(), "table1.name1", "table2.name2")
.leftJoin(new Dummy3DAO(), "table2.name2", "table3.name3")
.rightJoin(new Dummy4DAO(), "table4.name4", "table3.name3")
.addComputedColumn("col1", "CONCAT(table1.name1, table2.name2)")
.where("table1.name1", "=", "potato")
.and()
.where("table2.name2", "=", "tomato")
.execute()

if(result!=null) {
result.map(e => {
console.log(
e[0].getAsObject(), //Dummy1DTO
e[1].getAsObject(), //Dummy2DTO
e[2].getAsObject(), //Dummy3DTO
e[3].getAsObject(), //Dummy4DTO
e[4] //Record<"col1", any>
)
})
}
`

#### If in any case execute cannot be called instantly, AnyJoinDAO type will be needed to chain methods later. But this cause typescript from tracking types. Here is an example:

`typescript
import { type AnyJoinDAO, configureDB } from 'autoscript'

let joinDAO: AnyJoinDAO = new JoinDAO(new Dummy1DAO())
.innerJoin(new Dummy2DAO(), "table1.name1", "table2.name2")

if(/SomeLogic/) {
joinDAO = joinDAO
.leftJoin(new Dummy3DAO(), "table2.name2", "table3.name3")
.rightJoin(new Dummy4DAO(), "table4.name4", "table3.name3")
}

result = await joinDAO.execute()

if(result!=null) {
result.map(e => {
console.log(
(e[0] as Dummy1DTO).getAsObject(),
(e[1] as Dummy2DTO).getAsObject(),
(e[2] as Dummy3DTO).getAsObject(),
(e[3] as Dummy4DTO).getAsObject()
)
})
}
``