npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

al-sql

v1.2.3

Published

Abstraction_Layer-Structured_Query_Language is a sql abstraction layer that can be used for every sql database to perform DML and simple DLL querys.

Downloads

3,578

Readme

al-sql

CI/CD MIT typescript npm

"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).


Getting started (postgres)

1. Install package

npm i al-sql

2. Client cnnnections

Don'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,
    )
)

3. Table definition

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",
    },]
)

4. Implement control functions

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
            }
        )
    ])
}

6. Use the table

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.

Debugging help

Example:
showResult(object, ...options) / showTable(table, ...options)
showTables output

Layer Implementation

If you want to create a own abstraction layer implementation you need to implement this two interfaces:

  • AbstractSqlDialect
  • AbstractSqlConnection

AbstractSqlDialect

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
}

AbstractSqlConnection

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>
}

Postgres connection via 'pg'

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
    }
}

NPM Scripts

The npm scripts are made for linux. But your welcome to test them on macos and windows and create feedback.

use

You can run npm scripts in the project folder like this:

npm run <scriptname>

Here is an example:

npm run test

base scripts

You 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

watch mode

Like this example you can run all npm scripts in watch mode:

npm run start:watch

Contributing

Contributions to HiveSsh 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.

License

HiveSsh is licensed under the MIT license, providing users with flexibility and freedom to use and modify the software according to their needs.

Disclaimer

HiveSsh is provided without warranties.
Users are advised to review the accompanying license for more information on the terms of use and limitations of liability.