Sql Fns

Warning: this project is a work-in-progess, not fully tested, and the API could change

For developers who want to write SQL. Currently only supports postgres using the pg package.

There are lots of existing libraries for developers who want to intereact with their database, but don't want to write SQL.

https://github.com/prisma/prisma

https://github.com/sequelize/sequelize/

https://github.com/knex/knex

https://github.com/typeorm/typeorm

https://github.com/PostgREST/postgrest

The intention of this library is to be able to write SQL queries directly.

Getting started

npm i sql-fns --save

Define table models and table fields

Table models defines the schema your data models. Table fields map the model property names to the database table column names.

/*  Table Models */
class User {
    id: string;
    name: string;
    avatarUrl: string;
}

class Post {
    id: string;
    authorId: string;
    text: string;
    dateCreated: Date;
}

class PostComment {
    postId: string;
    authorId: string;
    comment: string;
    dateCreated: Date;
}

/*  Table Fields */
const user: TableFor<User> = {
    id: 'id',
    name: 'name',
    avatarUrl: 'avatar_url',
}

const post: TableFor<Post> = {
    authorId: 'author_id',
    text,
    dateCreated: 'date_created',
} 

const postComment: TableFor<PostComment> = {
    authorId: 'author_id',
    comment,
    dateCreated: 'date_created',
}

Define database table names

const tables = {
    user,
    post,
    postComment: 'post_comment',
}

rowsForQuery

rowsForQuery<QueryReturnType>(ctx, queryString, args)

• QueryReturnType is the Typescript type of the data returned by your query
• ctx is either a DfContext of DfTrContext -- which is either a context with a database Pool object or a PoolClient object (for a transaction)

const args = [];

/*
    Use the f function to generate the sql for your table fields.
    
    Always use dynamic values for fields and table names to make future refactoring a bit easier
*/
const users = await rowsForQuery<User[]>(ctx,
    sql`SELECT ${f(user)} FROM ${tables.user}`,
    args
);

oneRowForQuery

const args = [];
const whereArgs = [];
let count = 0;

whereArgs.push(sql`${user.id} = $${++count}`);
args.push(1);

const user = await oneRowForQuery<User>(ctx,
    sql`SELECT ${f(user)} FROM ${tables.user} ${WHERE(whereArgs)}`,
    args
);

transaction

transaction<TransactionReturnType>(baseCtx)(async ctx => {

• TransactionReturnType is the Typescript type of the data returned by the async callback function
• baseCtx is an object with the DfContext interface. This will have a property 'db' which will reference a database Pool object. The async callback will receive a context with a database PoolClient which will be set up as a transaction.

Examples

const getUsers = () => {
    return transaction<User[]>(ctx)(async ctx => {

        const args = [];

        const users = await rowsForQuery<User[]>(ctx,
            sql`SELECT ${f(user)} FROM ${tables.user}`,
            args
        );
    });
}

const getUser = (userId: string) => {
    return transaction<User>(ctx)(async ctx => {

        const args = [];
        const whereArgs = [];
        let count = 0;

        whereArgs.push(sql`${user.id} = $${++count}`);
        args.push(userId);

        const user = await oneRowForQuery<User>(ctx,
            sql`SELECT ${f(user)} FROM ${tables.user} ${WHERE(whereArgs)}`,
            args
        );
    });
}

const createUser = (userInfo: { name: string, avatarUrl: string, firstPostText: string }) => {

    const { name, avatarUrl, firstPostText } = userInfo;

    return transaction<User>(ctx)(async ctx => {

        /*
            Set up the args and fields for the query
        */
        const userArgs = [name, avatarUrl];
        const userFields = [user.name, user.avatarUrl];

        /*
            Define an ad-hoc return type for the query
            Use the PostgreSQL RETURNING keyword to return the created users's id
        */
        const userId = await oneRowForQuery<{ id: string}>(ctx,
            sql`
                INSERT INTO ${tables.user} (${userFields.join(',')})
                ${VALUES(1, userFields.length)}
                RETURNING
                    ${user.id}
            `,
            args
        );

        const postArgs = [userId, firstPostText, 'NOW()'];
        const postFields = [post.authorId, post.text, post.dateCreated];
        const postId = await oneRowForQuery<{ id: string}>(ctx,
            sql`
                INSERT INTO ${tables.post} (${postFields.join(',')})
                ${VALUES(1, postFields.length)}
                RETURNING
                    ${post.id}
            `,
            args
        );

        return userId;
    });
}

IN

const userIds = [1,2,3,4];
const whereArgs = [];
const args = [];

whereArgs.push(sql`${user.id} ${IN(userIds.length)}`);
args.push(...userIds);

const user = await rowsForQuery<User[]>(ctx,
    sql`SELECT ${f(user)} FROM ${tables.user} ${WHERE(whereArgs)}`,
    args
);

VALUES

const userArgs = [name, avatarUrl];
const userFields = [user.name, user.avatarUrl];

/*
    Define an ad-hoc return type for the query
    Use the PostgreSQL RETURNING keyword to return the created users's id
*/
const userId = await oneRowForQuery<{ id: string}>(ctx,
    sql`
        INSERT INTO ${tables.user} (${userFields.join(',')})
        ${VALUES(1, userFields.length)}
        RETURNING
            ${user.id}
    `,
    args
);

SET

const userId = 1;
const newName = 'New Name';
const newAvatarUrl = '/newavatar.png';

const whereArgs = [];
const updates = [];
const args = [];
let count = 0;

updates.push(`${user.name} = $${++count}`);
args.push(newName);

updates.push(`${user.avatarUrl} = $${++count}`);
args.push(newAvatarUrl);

whereArgs.push(sql`${user.id} = $${++count}`);
args.push(userId);

await oneRowForQuery<void>(ctx.db,
    sql`
        UPDATE user
        ${SET(updates)}
        ${WHERE(whereArgs)}
    `,
    args
);