TypeScript clients for databases that prevent SQL Injection
MIT License
Bot releases are hidden (Show)
Published by ForbesLindesay over 3 years ago
Rewritten to share most of the implementation with Postgres (#148)
This means the types now match Postgres for Queryable/ConnectionPool/Connection/Transaction. It also introduces some new timeouts.
Upgrade the underlying driver (i.e. mysql2
) to support MySQL version 8 (#145)
This shouldn't cause any breaking changes for end users, but it is a substantial change, so I have marked it as a breaking change to be on the safe side.
Support nested transactions: (#148)
const db = createConnectionPool();
await db.tx(async db => {
// I'm in a transaction
await db.tx(async db => {
// I'm in a nested transaction, implemented using "savepoints"
});
});
New option: tinyIntMode
to allow you to control whether TINYINT
should be treated as a boolean
or a number
(#148)
MySQL doesn't have a true boolean type, so when you create a column of type BOOLEAN
or BOOL
you actually get a column of type TINYINT(1)
. It is possible to use this to store other numbers (in the range 0-255).
If you use 'boolean'
mode, all values other than "0" are interpreted as true
, and "0" is interpreted as false
. This matches the behaviour of MySQL queries that use value IS TRUE
or value IS NOT TRUE
.
Usage:
// to treat TINYINT as a number (this is currently the default):
const db = createConnectionPool({
tinyIntMode: 'number'
});
// to treat TINYINT as boolean:
const db = createConnectionPool({
tinyIntMode: 'boolean'
});
See https://www.mysqltutorial.org/mysql-boolean/ for details.
New option: bigIntMode
to allow you to control whether BIGINT
(aka LONGLONG
) should be parsed as a number
, string
or BigInt
(#148)
If you choose number
you may get inexact values for numbers greater than Number.MAX_SAFE_INTEGER. Currently defaults to 'number'
, but this may change in a future version
Usage:
// to treat BIGINT as a number (this is currently the default):
const db = createConnectionPool({
bigIntMode: 'number'
});
// to treat BIGINT as string:
const db = createConnectionPool({
bigIntMode: 'string'
});
// to treat BIGINT as BigInt (N.B. requires a recent version of node.js):
const db = createConnectionPool({
bigIntMode: 'bigint'
});
New option: dateMode
to allow you to control how DATE
fields should be returned (#148)
If you choose 'date-object' it will be a JavaScript Date
that is midnight in the client timeZone
.
Currently this defaults to 'date-object'
but the default will change to 'string'
in a future version.
Usage:
// to treat DATE as a Date object (this is currently the default):
const db = createConnectionPool({
dateMode: 'date-object'
});
// to treat DATE as string in the format "yyyy-mm-dd":
const db = createConnectionPool({
dateMode: 'string'
});
New option: dateTimeMode
allows you to control how DATETIME
fields should be returned from the database (#148)
Date
.// to treat DATETIME as a Date object (this is the default):
const db = createConnectionPool({
dateTimeMode: 'date-object'
});
// to treat DATETIME as string in the format "yyyy-mm-dd HH:MM:SS[.nnnnnn]":
const db = createConnectionPool({
dateTimeMode: 'string'
});
New option: timeStampMode
allows you to control how DATETIME
fields should be returned from the database (#148)
Date
.// to treat TIMESTAMP as a Date object (this is the default):
const db = createConnectionPool({
timeStampMode: 'date-object'
});
// to treat TIMESTAMP as string in the format "yyyy-mm-dd HH:MM:SS[.nnnnnn]":
const db = createConnectionPool({
timeStampMode: 'string'
});
New option: timeZone
allows you to control the time zone to use when serializing and parsing (#148)
There are two timestamps involved in MySQL:
NOW()
function in SQLThe "client" timezone defaults to 'local'
, i.e. the timezone of your server. This can be overridden using the the TZ
environment variable.
The "server" timezone defaults to undefined
, which means it will just be whatever your MySQL server is currently configured with.
Strange things can happen when these don't match. It's also not a good idea to store times in an arbitrary time zone. It's therefore a good idea to set both to utc
.
// default value, not recommended:
const db = createConnectionPool({
timeZone: {client: 'local', server: undefined},
});
// set both server and client to utc, recommended:
const db = createConnectionPool({
timeZone: 'utc'
});
New option: maxUses
allows you to limit how many times the same connection is re-used from the pool. This is unlikely to be needed. Defaults to Infinity
(#148)
New option: idleTimeoutMilliseconds
- number of milliseconds a connection can be idle in the pool before it is automatically closed and discarded. Defaults to 10,000 (i.e. 10 seconds) (#148)
New option: queueTimeoutMilliseconds
- number of milliseconds to wait for a connection from the connection pool. Defaults to 60,000 (i.e. 60 seconds) (#148)
New option: aquireLockTimeoutMilliseconds
- number of milliseconds to wait for a lock on a connection/transaction. This is helpful for catching cases where you have accidentally attempted to query a connection within a transaction that is on that connection, or attempted to query an outer transaction within a nested transaction. (#148)
Event handlers (#148)
These event handlers can be passed in when creating the connection pool:
// database.ts
import createConnectionPool, {sql} from '@databases/mysql';
export {sql};
const db = createConnectionPool({
onQueryStart: (_query, {text, values}) => {
console.log(
`${new Date().toISOString()} START QUERY ${text} - ${JSON.stringify(
values,
)}`,
);
},
onQueryResults: (_query, {text}, results) => {
console.log(
`${new Date().toISOString()} END QUERY ${text} - ${
results.length
} results`,
);
},
onQueryError: (_query, {text}, err) => {
console.log(
`${new Date().toISOString()} ERROR QUERY ${text} - ${err.message}`,
);
},
});
export default db;
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Published by ForbesLindesay over 3 years ago
Added events to enable logging each Postgres query (#144)
These event handlers can be passed in when creating the connection pool:
// database.ts
import createConnectionPool, {sql} from '@databases/pg';
export {sql};
const db = createConnectionPool({
onQueryStart: (_query, {text, values}) => {
console.log(
`${new Date().toISOString()} START QUERY ${text} - ${JSON.stringify(
values,
)}`,
);
},
onQueryResults: (_query, {text}, results) => {
console.log(
`${new Date().toISOString()} END QUERY ${text} - ${
results.length
} results`,
);
},
onQueryError: (_query, {text}, err) => {
console.log(
`${new Date().toISOString()} ERROR QUERY ${text} - ${err.message}`,
);
},
});
export default db;
See Postgres Logging & Debugging for details.
Published by ForbesLindesay over 3 years ago
Support async stack traces in node.js 14+ (#141)
Previously errors thrown by pg would have their stack traces cut off, making it difficult to track down where the call originated from. This update means that stack traces will normally include the full chain of functions that lead up to the query, even if they are async.
bigIntMode
warning message (#138)Published by ForbesLindesay almost 4 years ago
Added a serializeValue
option (#134)
This lets you apply a custom transformation to any column value before passing it to the database in insert/update statements, and where clauses. If you update to @databases/pg-schema-cli
version 4.0.0, it will automatically generate a serializeValue
function that handles calling JSON.stringify(...)
on any values being passed to JSON
or JSONB
columns. This fixes an issue that caused an error to be thrown if you attempted to store an array (or other non-object value) in a column of type JSON
or JSONB
.
N.B. If you were previously calling
JSON.stringify
manually (and suppressing the TypeScript error) and you enable this feature, you will need to remove those calls toJSON.stringify
, otherwise your values will be stringified twice. Which will result in them being incorrectly parsed when you query.
Published by ForbesLindesay almost 4 years ago
Generates a new serializeValue
function (#134)
By default, this is placed in index.ts
. You can pass this function to pg-typed
to automatically serialize all JSON & JSONB fields, even when they are not objects. This is useful if you want to store Arrays, booleans, strings etc. in JSON fields.
Published by ForbesLindesay almost 4 years ago
Generates a new serializeValue
function (#134)
By default, this is placed in index.ts
. You can pass this function to pg-typed
to automatically serialize all JSON & JSONB fields, even when they are not objects. This is useful if you want to store Arrays, booleans, strings etc. in JSON fields.
Published by ForbesLindesay almost 4 years ago
Add config for serializeValueTypeName
and serializeValueFileName
(#134)
These let you configure the name & file location of the new serializeValue
function that pg-schema-print-types
outputs
Published by ForbesLindesay almost 4 years ago
Do not pass file migrations as an array when calling db.query
(#132)
This would have resulted in the error:
The number of queries in the array did not match the number of result sets. You cannot pass a query with multiple statements as an entry in an array.
For any migration with multiple statements.
Published by ForbesLindesay almost 4 years ago
IsolationLevel
enum (#131)Published by ForbesLindesay almost 4 years ago
Do not verify ssl certificates when no certs were provided (#130)
This is a closer match for the default behaviour of most postgres clients. You can still perform ssl verification by providing sslcert
, sslkey
, sslrootcert
or by specifying the ssl options when constructing the client. If ssl is only enabled because the connection string or environment variables specifies an sslmode, we will not verify certificates.
Published by ForbesLindesay almost 4 years ago
Added support for more complex queries (#127)
This adds new conditions:
not(condition)
- check the field does not match the conditionanyOf([...conditions])
- check the field matches one or more of the conditionsgreaterThan(value)
- check the field is greater than value
lessThan(value)
- check the field is less than value
inQueryResult(query)
- check the field value is in the results of query
You can use any of these in place of field values for the where clause in .find
and .findOne
calls. It is still only possible to include results from a single table, but these helpers make queries significantly more flexible.
Renamed .selectOne(...)
to .findOne(...)
(#125)
The old method name is still supported, but it is deprecated and will be removed in a future major version.
The name .selectOne
was confusing for people familiar with SQL, because we are never selecting/filtering the list of columns that are retrieved, we are filtering the rows. Hopefully the verb "find" will be a more natural fit.
Renamed .select(...)
to .find(...)
(#125)
The old method name is still supported, but it is deprecated and will be removed in a future major version.
The name .select
was confusing for people familiar with SQL, because we are never selecting/filtering the list of columns that are retrieved, we are filtering the rows. Hopefully the verb "find" will be a more natural fit.
Generate IS NULL
instead of = ${null}
when null
is passed as a condition (#126)
This fixes an issue where pg-typed would never return any results if you passed null
as one of the fields.
Published by ForbesLindesay almost 4 years ago
Handle empty arrays passed to .query
(#123)
If you call db.query([])
it is a no-op and just immediately returns. If you call it on a ConnectionPool, it won't even allocate a connection.
Published by ForbesLindesay almost 4 years ago
Transaction
and Queryable
types (#121)