"al-sql" is a Abstraction_Layer for sql databases to perform simple sql querys.
You create or use a sql dialect interface and a sql connection interface for your sql database. With this you can create a SqlClient instance which provides full controll over a database and its table structure.
There is already a working postgres abstraction implementation that you can use for a postgres databases or as base to create a own abstraction implementation (see here).
- al-sql
- Getting started (postgres)
- Debugging help
- Layer Implementation
- NPM Scripts
- Contributing
- License
- Disclaimer
npm i al-sqlDon't worry, much of it is or can be copied and pasted or distributed across multiple files.
Implement the base client connection:
import { SqlClient } from "al-sql"
import { PostgresConnection } from "al-sql/dist/pg"
export const client = new SqlClient(
new PostgresConnection(
env.POSTGRES_HOST,
env.POSTGRES_PORT,
env.POSTGRES_USER,
env.POSTGRES_PASSWORD,
env.POSTGRES_DB,
)
)Define your tables in the database, this tables can be created via al-sql:
// user table example:
export const accountTable = client.getTable(
"account",
[{ // column example:
name: "id",
type: "SERIAL",
primaryKey: true,
nullable: false,
},{
name: "name",
type: "VARCHAR",
unique: true,
nullable: false,
size: 32,
},{
name: "email",
type: "VARCHAR",
unique: true,
nullable: false,
size: 128,
},]
)
// friendship example:
export const friendshipTable = client.getTable(
"friendship",
[{ // column example:
name: "id",
type: "SERIAL",
primaryKey: true,
nullable: false,
},{
name: "sender_id",
type: "INT",
nullable: false,
},{
name: "receiver_id",
type: "INT",
nullable: false,
},{
name: "accepted",
type: "BOOL",
nullable: false,
default: false,
},],
[{ // foreign keys example:
columnName: "sender_id",
foreignColumnName: "id",
foreignTableName: "account",
},{
columnName: "receiver_id",
foreignColumnName: "id",
foreignTableName: "account",
},]
)This way database entities feel like local objects with control functions. This is just a example, there are better implementations depends on the codebase and coding style:
export async function getAccountByName(
name: string
): Promise<number> {
const result = await accountTable.selectOne(
["id"], // SELECT "id" FROM "account" LIMIT 1
{ // WHERE name = $1 ("name" is a prepared statement)
name: name,
}
)
if (!result || typeof result.id != "number") {
throw new Error("User with name '" + name + "' not exists!")
}
return result.id
}
export async function getAccountByEmail(
email: string
): Promise<number> {
const result = await accountTable.selectOne(
["id"], // SELECT "id" from "account" LIMIT 1
{ // WHERE email = $1 ("email" is a prepared statement)
email: email,
}
)
if (!result || typeof result.id != "number") {
throw new Error("User with email '" + email + "' not exists!")
}
return result.id
}
export async function createAccount(
name: string,
email: string
): Promise<number> {
const result = await accountTable.insert(
{ // INSERT INTO "account" (name, email) VALUES ($1, $2)
name: name,
email: email,
},
["id"] // RETURNING "ID"
)
if (!result || typeof result.id != "number") {
throw new Error("User with email '" + email + "' not exists!")
}
return result.id
}
export async function requestFriendship(
senderId: number,
receiverId: number
): Promise<void> {
await removeFriendship(senderId, receiverId)
// INSERT INTO "friendship" (sender_id, receiver_id) VALUES ($1, $2)
await friendshipTable.insert({
sender_id: senderId,
receiver_id: receiverId,
})
}
export async function acceptFriendship(
senderId: number,
receiverId: number
): Promise<void> {
await friendshipTable.update(
{ // UPDATE SET accepted = $1
accepted: true,
},{ // WHERE sender_id = $1 AND receiver_id = $2
sender_id: senderId,
receiver_id: receiverId,
},
)
}
export async function getFriends(
user: number
): Promise<number[]> {
const result = await Promise.all([
friendshipTable.select(
[ // SELECT "friendship".sender_id from "friendship"
["friendship", "sender_id"],
],
{ // WHERE receiver_id = $1
receiver_id: user,
},
),
friendshipTable.select(
[ // SELECT "friendship".receiver_id from "friendship"
["friendship", "receiver_id"],
],
{ // WHERE sender_id = $1
sender_id: user,
}
)
])
// merge results together
const friends: number[] = []
result[0].forEach((f) => friends.push(f.sender_id as number))
result[1].forEach((f) => friends.push(f.receiver_id as number))
return friends
}
export async function removeFriendship(
user1: number,
user2: number
): Promise<void> {
await Promise.all([
friendshipTable.delete(
{ // DELETE FROM "friendship" WHERE sender_id = $1 AND receiver_id = $2
sender_id: user1,
receiver_id: user2
}
),
friendshipTable.delete(
{ // DELETE FROM "friendship" WHERE sender_id = $1 AND receiver_id = $2
sender_id: user2,
receiver_id: user1
}
)
])
}After defining the tables in code use "createTable()" on the client to create the tables if not exist:
await client.createTables()You can use the "dropAllTables()" function to drop all (defined) tables. This is handy for debug and tests:
// drops all tables (cascaded) in reversed order
await client.dropAllTables()
// creates all tables in normal order
await client.createAllTables()From here on your can use the tables or control function to manipulate the database data.
Example:
showResult(object, ...options) / showTable(table, ...options)
If you want to create a own abstraction layer implementation you need to implement this two interfaces:
- AbstractSqlDialect
- AbstractSqlConnection
First you implement the sql querys for your sql dialect. You can checkout the postgres implementation for help:
export interface AbstractSqlDialect {
getDialectName(): string
getTablesQuery(
client: SqlClient
): ExecutableSqlQuery
createTableQuery(
table: SqlTable
): ExecutableSqlQuery
dropTableQuery(
table: SqlTable
): ExecutableSqlQuery
insertQuery(
table: SqlTable,
set: SqlSetValueMap,
returning?: SqlResultColumnSelector | undefined,
): ExecutableSqlQuery
updateQuery(
table: SqlTable,
set: SqlSetValueMap,
where?: SqlWhereSelector,
returning?: SqlResultColumnSelector | undefined,
): ExecutableSqlQuery
selectQuery(
table: SqlTable,
select?: SqlResultColumnSelector,
where?: SqlJoinWhereSelector,
join?: number | undefined,
...joins: SqlJoin[]
): ExecutableSqlQuery
deleteQuery(
table: SqlTable,
where?: SqlWhereSelector,
returning?: SqlResultColumnSelector | undefined,
): ExecutableSqlQuery
}Now you can implement the needed sql connection based on the sql driver/library. If two sql databases share the same sql dialect but need a other connection driver/library you can reuse the AbstractSqlDialect and just implement a other AbstractSqlConnection for that driver/library.
export interface AbstractSqlConnection {
getDialect(): AbstractSqlDialect // HERE YOU RETURN YOUR SQL DIALECT IMPLEMENTATION
execute(query: ExecutableSqlQuery): Promise<SqlQueryExecuteResult>
isConnected(): Promise<boolean>
connect(): Promise<void>
close(): Promise<void>
}The postgres connection implementation looks like this:
export class PostgresConnection implements AbstractSqlConnection {
public readonly client: Client
public readonly dialect: PostgresSqlDialect
public connected: boolean = false
constructor(
public readonly host: string,
public readonly port: number,
public readonly username: string,
public readonly password: string,
public readonly database: string
) {
this.client = new Client({ // <- "Client" is a export of the "pg"-package (postgres-client)
host: host,
port: port,
user: username,
password: password,
database: database
})
this.dialect = new PostgresSqlDialect()
}
getDialect(): AbstractSqlDialect {
return this.dialect
}
async execute(query: ExecutableSqlQuery): Promise<SqlQueryExecuteResult> {
try{
return this.client.query(
query[0],
query.slice(1)
)
}catch(err: Error | any){
await this.client.end().catch(() => {})
this.connected = false
throw err
}
}
async isConnected(): Promise<boolean> {
return this.connected
}
async connect(): Promise<void> {
await this.client.connect()
this.connected = true
}
async close(): Promise<void> {
await this.client.end()
this.connected = false
}
}The npm scripts are made for linux. But your welcome to test them on macos and windows and create feedback.
You can run npm scripts in the project folder like this:
npm run <scriptname>Here is an example:
npm run testYou can find all npm scripts in the package.json file.
This is a list of the most important npm scripts:
- test // test the app
- build // build the app
- exec // run the app
- start // build and run the app
Like this example you can run all npm scripts in watch mode:
npm run start:watchContributions to this project are welcome!
Interested users can refer to the guidelines provided in the CONTRIBUTING.md file to contribute to the project and help improve its functionality and features.
This project is licensed under the MIT license, providing users with flexibility and freedom to use and modify the software according to their needs.
This project is provided without warranties.
Users are advised to review the accompanying license for more information on the terms of use and limitations of liability.