eftify-drizzle-pg v0.0.19

eftify-drizzle-pg

EF Core-like queries similar to LINQ using Drizzle ORM

Install

npm install eftify-drizzle-pg

About the library

Small library attempting to bring other relational syntax to Drizzle ORM similar to LINQ in composition. Might help anyone transitioning from EF Core who does not like the drizzle query API. As for now supports only Postgres database with limited functionality available. No guarantee given whatsover, use at your own risk.

Library can run alongside standard drizzle. All it does is create new "eftify" property on the root drizzle object with new API available.

Mainly developed for our team's needs. There is no plan to develop further than the scope of our needs, in case you're missing a feature, feel free to create a PR.

Usage

import { drizzleEftify } from 'eftify-drizzle-pg';
import { and, lt, ne } from 'drizzle-orm';
import * as schema from '../schema/schema';

const queryConnection = postgres(getDbUrl());
const drizzleEftified = drizzleEftify.create(queryConnection, {
	logger: appConfig.database.logQuery,
	schema: schema
});

(async () => {
    const dbContext = drizzleEftified.eftify;

    //All users without projection
    const allUsers = await dbContext.users.toList();

    //All users without projection, sorted
    const sortedUsers = await dbContext.users.orderBy(p => [p.name, p.id]).toList();

    //Queries list
    const result = await dbContext.users.where(p => lt(p.id, 3)).select(p => ({
        id: p.id,
        street: p.userAddress.address,    //Navigation properties in similar manner like in EF
        posts: p.posts.select(p => ({     //Basic One-to-many collection support
            id: p.id,
            text: p.content
        })).toList('posts')               //Due to limitations requires name specification
    })).toList();

    //Obtaining author name from post (possibly fixed in 0.0.5)
    const otherWay = dbContext.posts.where(p => eq(p.authorId, 3)).select(p => ({
        id: p.id,
        authorId: p.author.name
    })).firstOrDefault();

    //Single result
    const singleResult = await dbContext.users.where(p => lt(p.id, 3)).select(p => ({
        id: p.id,
        street: p.userAddress.address,    //Navigation properties in similar manner like in EF
        posts: p.posts.select(p => ({     //Basic One-to-many collection support
            id: p.id,
            text: p.content
        })).toList('posty')               //Due to limitations requires name specification
    })).firstOrDefault();

    //Querying from entity joined by array column
    const allGroups = await dbContext.userGroups.select(p => ({
        id: p.id,
        name: p.name,
        users: p.users.select(u => ({
            id: u.id,
            name: u.name
        })).toList('users')
    })).toList();

    //Simple sum
    const summary = await dbContext.users.where(p => and(
        lt(p.id, 2),
        ne(p.id, 0)
    )).sum(p => p.id);

    //Count query
    const userCount = await dbContext.users.where(p => and(
        lt(p.id, 2),
        ne(p.id, 0)
    )).count();

    //Grouping example
    const groupedResult = await dbContext.users.select(p => ({
        id: p.id,
        street: p.userAddress.address,  
        name: p.name
    })).groupBy(p => ({
        street: p.street
    })).select(p => ({
        idCount: p.count(),
        idSum: p.sum(p => p.id),
        street: p.key.street     //Key property holds the grouping key similar to EF Core
    })).toList();

    //Insert example + transaction
    const userRow = await dbContext.transaction(async trx => {
        try {
            const userRow = await trx.users.insert({
                name: 'new user'
            }).returning({
                id: trx.users.getUnderlyingEntity().id
            });

            const userAddressRow = await trx.userAddress.insert({
                userId: userRow[0].id,
                address: 'some address'
            });

            return { id: userRow[0].id };
        } catch (error) {
            await trx.rollback();
            return null;
        }
    });

    //Update example
    const affectedCount = await dbContext.users.where(p => eq(p.id, 1)).update({
        name: 'changed name'
    });

})();

Sample schema

import { relations } from 'drizzle-orm';
import { integer, pgTable, text } from 'drizzle-orm/pg-core';
import { eftifyRelations } from 'eftify-drizzle-pg';

// ==================== USERS ====================
export const users = pgTable('users', {
	id: integer('id').primaryKey().generatedAlwaysAsIdentity({name: 'users_id_seq'}),
	name: text('name'),
});

export const usersRelations = relations(users, ({ one, many }) => ({
	userAddress: one(userAddress, {
		fields: [users.id],
		references: [userAddress.userId],
	}),
	posts: many(posts),
}));

// ==================== USER ADDRESS ====================
export const userAddress = pgTable('user_address', {
	id: integer('id').primaryKey().generatedAlwaysAsIdentity({name: 'user_address_id_seq'}),
	userId: integer('sender_user_id').references(() => users.id),
	address: text('address'),
});

export const userAddressRelations = relations(userAddress, ({ one }) => ({
	user: one(users),
}));

// ==================== POST ====================
export const posts = pgTable('posts', {
	id: integer('id').primaryKey().generatedAlwaysAsIdentity({name: 'posts_id_seq'}),
	content: text('content'),
	authorId: integer('author_id'),
});
export const postsRelations = relations(posts, ({ one }) => ({
	author: one(users, {
		fields: [posts.authorId],
		references: [users.id],
	}),
}));

// ==================== USER GROUPS ====================
export const userGroups = pgTable('user_groups', {
    id: integer('id').primaryKey().generatedAlwaysAsIdentity({ name: 'user_groups_id_seq' }),
    name: text('name'),
    usersIds: integer('user_ids').array(),
});
export const userGroupsRelations = eftifyRelations(userGroups, ({ manyFromKeyArray }) => ({
    users: manyFromKeyArray(users, {
        fields: [userGroups.usersIds],
        references: [users.id]
    })
}));

// ============ UNRELATED TABLE =================
export const unrelatedTable = pgTable('unrelated_table', {
	id: integer('id').primaryKey().generatedAlwaysAsIdentity({name: 'unrelated_table_id_seq'}),
	sometext: text('sometext'),
});