A library to simplify handling of postgres queries with node. It tries to be in the sweet spot between manual queries and ORM.
Posigrade goal:
- queries are written in SQL: Posigrade is not an ORM;
- usage of variables must be simple and secure: Posigrade prevents SQL Injection by default;
- ensure that once in a transaction, all queries are run in this transaction
- typesafe: validate that queries returns what their type say they return
Install posigrade
npm i posigrade OR yarn add posigrade
Create a connection pool in your code as you would do with the node postgres driver
import { Pool } from 'pg';
const pool = new Pool(); // cf node-postgres doc for more informationWith this pool you can create an SqlExecutor, that will simplify transaction handling
const sqlExecutor = SqlExecutor(pool);Now you can define you deserializers, that will ensure the type safety of what you're doing
type User = { id: number; name: string };
const userDeserializer = SqlDeserializer.fromRecord<User>({
id: deser.toInteger,
name: deser.toString,
});You can now use posigrade to query the database as you want:
// in a direct run
await sqlExecutor.run(
Sql`CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT NOT NULL)`.update()
);
// or in a transaction
const users: User[] = await sqlExecutor.transact(async (client) => {
const names = ['georges', 'peter' ,'steven']
const insertionQuery = (name:string) => Sql`INSERT INTO users(name) VALUES ${name}`.update();
await Promise.all(names.map(name => insertionQuery(name).run(client));
const users = Sql`SELECT id, name FROM users`.list(userDeserializer).run(client);
return users;
});
// users contains `[{id:1, name: 'georges'},{id:2, name: 'peter'},{id:3, name: 'steven'}]`Posigrade is wrapper around a chain of function calls leading to the result of an SQL query.
Sql string template --> SqlQuery ---+
|
SqlDeserializer<T> -----------------+-> ExecutableQuery<T> ---+
|
SqlExecutor --------------> QueryableClient ------------------+----> Promise<T>
So you'll need 3 objects for everything to work:
- an
SqlExecutorwhich represent a connection to a database - some
SqlDeserializer<T>that will ensure the typesafe transformation of the rows to something useful - an
SqlQuerywhich is essentially an... SQL query.
It's a wrapper around the connection pool of the node postgres driver; Please look at the driver to understand how to configure the connection pool. Getting the executor from the pool can be done with:
import { Pool } from 'pg';
const pool = new Pool(); // cf node-postgres doc for more information
const sqlExecutor = SqlExecutor(pool);The SqlExecutor let you run your queries in 2 "modes":
- directly
- in a transaction
Call the run method. The input parameter is a callback taking the postgres client in parameter.
// Returns Promise<number[]>
sqlExecutor.run((client) => Sql`SELECT id FROM my_table`.list(deser.toInteger.forColumn('id')).run(client));To simplify things, it can also take an ExecutableQuery<T>
// Returns Promise<number[]>
sqlExecutor.run(Sql`SELECT id FROM my_table`.list(deser.toInteger.forColumn('id')));Works like the run method, but everything executed on the client will be done on a transaction, meaning that
if any Error is thrown in the callback, all changes done will be rollbacked.
// Returns Promise<number[]>
sqlExecutor.transact((client) => {
Sql`INSERT INTO my_table(id) VALUES ${1}`.update().run(client);
Sql`INSERT INTO my_table(id) VALUES ${2}`.update().run(client);
});It transforms a row (which is essentially an object with unknown properties/values) to a known and well-typed object.
Simple deserializers can be used:
const idDeserializer: SqlDeserializer<number> = deser.toInteger.forColumn('id');
Sql`SELECT id FROM my_table`.list(idDeserializer); // deserialize to number[]If something may be nullable, it should be explicit in the SqlDeserializer
const nameDeserializer: SqlDeserializer<string | null> = deser.toString.orNull().forColumn('name');
Sql`SELECT name FROM my_table`.list(nameDeserializer); // deserialize to (string | null)[]If you want to get a richer type, you can transform the result type of the serializer with map
const nameDeserializer: SqlDeserializer<string | null> = deser.toString.map((name) => ({ name })).forColumn('name');
Sql`SELECT name FROM my_table`.list(nameDeserializer); // deserialize to ({name:string})[]Finally, to get a more complex type, we can combine serializers
const userDeser = SqlDeserializer.fromRecord({
id: deser.toInteger, // if you don't specify a column name, it will take the object key for column name
name: deser.toString,
creationDate: deser.toDate.forColumn('creation_date'),
});
Sql`SELECT id, name, creation_date FROM users`.list(userDeser); // deserializer to ({id:number, name:string, creationDate: Date})[]You can explicitly set the type expected from a deserializer (and TS will check that you deserializer returns correctly the expected type)
type User = { id: number; name: string };
const userDeser = SqlDeserializer.fromRecord<User>({
id: deser.toInteger,
name: deser.toString,
});Or you can define your type from your deserializer
const userDeserializer = SqlDeserializer.fromRecord({
id: deser.toInteger,
name: deser.toString,
});
// will hold {id:number, name:string}
type User = InferDeserializerType<typeof userDeserializer>;A SqlQuery is built from an SQL template string. It will automatically pass the variables as parameters to postgres
to prevent any attempt of SQL Injection
function findNameById(id: string): ExecutableQuery<string> {
return Sql`SELECT name FROM users WHERE id = ${id}`.unique(deser.toString.forColumn('name'));
}You can build queries with dynamic part by wrapping the dynamic parts in Sql:
function findAllName(sortByName: boolean): ExecutableQuery<string[]> {
const orderBy = sortByName ? Sql`ORDER BY name` : Sql``;
return Sql`SELECT name FROM users ${orderBy}`.list(deser.toString.forColumn('name'));
}If you need a variable that is not a query parameter, but that cannot be known at compile time, you can use SqlConst.
/!\ This is dangerous, as it can be susceptible to SQL Injections. Refrain to use this feature if you can, and never use
it with a non sanitized user input.
function findAllName(fieldToSelect: 'firstName' | 'lastName'): ExecutableQuery<string[]> {
return Sql`SELECT ${SqlConst(fieldToSelect)} FROM users`.list(deser.toString.forColumn('name'));
}Short answer: use ANY
function findNamesByIds(ids: number[]): ExecutableQuery<string[]> {
return Sql`SELECT name FROM users WHERE id = ANY(${ids})`.list(deser.toString.forColumn('name'));
}If mapping and combinators are not enough for your need, you can build a deserializer by yourself:
Use DeserDefintion:
const stringStartingWithA: DeserDefinition<string> = {
guard: (value): value is string => typeof value === 'string' && value.startsWith('a'),
errorMessage: (value) => `'${value}' is not a string starting with 'a'`,
};
const myCustomDeser = toNamedDeserializer(stringStartingWithA);If you want things to be safe, return a Failure when the Deserializer should not work.
const myCustomDeser: NamedDeserializer<{ name: string }> = new NamedDeserializer(
(col: string) =>
new SqlDeserializer((row: RowObject) => {
if (!Object.prototype.hasOwnProperty.call(row, col)) {
throw new Error(`No column named '${col}' exists in the list of cols '${Object.keys(row).join(', ')}'`);
}
const value = row[col];
if (typeof value === 'string') {
return Success.of({ name: value });
} else {
return Failure.raise(`The value in col '${col}' is not a string`);
}
})
);Software architecture is not a question with an objective answer, and the followings are only suggestion, that may or may not apply to your case.
Our model will be the same in the 2 cases
// user.model.ts
type User = {
id: number;
name: string;
};Here we don't care that posigrade leaks in our types/classes. It's a commitment to posigrade, as it means it would make it hard to use another library instead. That being said, it's also a very simple architecture, both to understand AND to test.
First we define our model
Then we define our repository, responsible for creating the queries
const userDeser = SqlDeserializer.fromRecord<User>({
id: deser.toInteger,
name: deser.toString,
});
class UserRepo {
createUser({ name }: { name: string }): ExecutableQuery<User> {
return Sql`INSERT INTO users(name) VALUES ${name} RETURNING *`.unique(userDeser);
}
findUserById(id: number): ExecutableQuery<User | null> {
return Sql`SELECT * FROM users WHERE id = ${id}`.option(userDeser);
}
findAll(): ExecutableQuery<User[]> {
return Sql`SELECT * FROM users`.list(userDeser);
}
}Finally, we define the service, responsible to executing the queries
class UserService {
constructor(private sqlExecutor: SqlExecutor, private userRepo: UserRepo) {}
createUser(payload: { name: string }): Promise<User> {
return sqlExecutor.run(this.userRepo.createUser(payload));
}
findUserById(id: number): Promise<User | null> {
return sqlExecutor.run(this.userRepo.findUserById(id));
}
findAll(): Promise<User[]> {
return sqlExecutor.run(this.userRepo.findAll());
}
}The repository is not really unit testable: the core of the functions are strings, the queries, that can contain syntax errors, not return what you expect, etc. So I would suggest to test the repository against a real database, with testcontainers for example
The service can be easily tested by faking the Executor and the repo:
const fakeClient: QueryableClient = {
query: (): any =>
Promise.resolve({
rows: [],
}),
};
const fakeExecutor: SqlExecutor = {
async run<T>(fn: ExecutableQuery<T> | ((client: QueryableClient) => Promise<T>)): Promise<T> {
if (fn instanceof ExecutableQuery) {
return fn.run(fakeClient);
} else {
return fn(fakeClient);
}
},
async transact<T>(fn: ExecutableQuery<T> | ((client: QueryableClient) => Promise<T>)): Promise<T> {
if (fn instanceof ExecutableQuery) {
return fn.run(fakeClient);
} else {
return fn(fakeClient);
}
},
async close() {},
};
const fakeRepo = {
createUser({ name }: { name: string }): ExecutableQuery<User> {
return ExecutableQuery.of({ id: 1, name: 'georges' });
},
findUserById(id: number): ExecutableQuery<User | null> {
return ExecutableQuery.of(null);
},
findAll(): ExecutableQuery<User[]> {
return ExecutableQuery.of([{ id: 1, name: 'georges' }]);
},
};
const userServiceUnderTest = new UserService(fakeExecutor, fakeRepo);Here the goal is for posigrade to not be "visible" from the interface point of view, leading to easier revertability. But the code is also less straightforward for it
Then we define our repository, responsible for creating the queries, but here we wants the method to return Promise directly
const userDeser = SqlDeserializer.fromRecord<User>({
id: deser.toInteger,
name: deser.toString,
});
class UserRepo {
constructor(private client: QueryableClient) {}
createUser({ name }: { name: string }): Promise<User> {
return Sql`INSERT INTO users(name) VALUES ${name} RETURNING *`.unique(userDeser).run(this.client);
}
findUserById(id: number): ExecutableQuery<User | null> {
return Sql`SELECT * FROM users WHERE id = ${id}`.option(userDeser).run(this.client);
}
findAll(): ExecutableQuery<User[]> {
return Sql`SELECT * FROM users`.list(userDeser).run(this.client);
}
}Then the service is responsible to call the repo
class UserService {
// Notice that the userRepoFactory is function that returns the UserRepo once we pass a client
constructor(private sqlExecutor: SqlExecutor, private userRepoFactory: (client: QueryableClient) => UserRepo) {}
createUser({ name }: { name: string }): Promise<User> {
return sqlExecutor.run((client) => this.userRepo(client).createUser(payload));
}
findUserById(id: number): Promise<User | null> {
return sqlExecutor.run((client) => this.userRepo(client).findUserById(id));
}
findAll(): Promise<User[]> {
return sqlExecutor.run((client) => this.userRepo(client).findAll());
}
}For the tests, the idea is same than above
const fakeClient: QueryableClient = {
query: (): any =>
Promise.resolve({
rows: [],
}),
};
const fakeExecutor: SqlExecutor = {
async run<T>(fn: ExecutableQuery<T> | ((client: QueryableClient) => Promise<T>)): Promise<T> {
if (fn instanceof ExecutableQuery) {
return fn.run(fakeClient);
} else {
return fn(fakeClient);
}
},
async transact<T>(fn: ExecutableQuery<T> | ((client: QueryableClient) => Promise<T>)): Promise<T> {
if (fn instanceof ExecutableQuery) {
return fn.run(fakeClient);
} else {
return fn(fakeClient);
}
},
async close() {},
};
const fakeRepo = {
createUser({ name }: { name: string }): ExecutableQuery<User> {
return ExecutableQuery.of({ id: 1, name: 'georges' });
},
findUserById(id: number): ExecutableQuery<User | null> {
return ExecutableQuery.of(null);
},
findAll(): ExecutableQuery<User[]> {
return ExecutableQuery.of([{ id: 1, name: 'georges' }]);
},
};
const userServiceUnderTest = new UserService(fakeExecutor, () => fakeRepo);Here the interfaces of the different classes are clean of any trace of posigrade.
But it leads to a code that feels more cumbersome because we need to pass client explicitly everywhere,
and we deprive ourselves of all the combinators that we can use on ExecutableQuery
git checkout master
git reset --hard HEAD
npm run release
npm publish