@databases/[email protected]

New Features

• feat: allow using @databases/pg without a connection string (#222)

  This lets you use $PGHOST, $PGUSER etc. to configure your connection, which can work better in environments that expect you to connect using a unix socket and/or if you want to store only your database password in a secret manager.

@databases/[email protected]

Performance Improvements

• Skip quote escaping for identifiers that do not contain quotes (#223)

  The vast majority of identifiers in most applications do not include any quote characters. Since pg-typed & mysql-typed have to pass every table/column name to escapeDatabaseIdentifier, this could have been causing a significant performance penalty for some applications.

  On my local machine, escaping 10,000,000 Postgres identifiers goes from approximately 2,400ms to approximately 600ms.

@databases/[email protected]

New Features

• Add utility for re-exporting a value or type declared in another file. (#211)

• Add writeFile method to PrintContext to enable writing non-TypeScript files (e.g. .json files) (#212)

@databases/[email protected]

New Features

• Added .orderByAscDistinct and .orderByDescDistinct to SelectQuery (#216)

  This lets you select only the first record with a given id.

• You can combine conditions on a field with allOf (#217)

  For example, you can do:

  import {allOf, greaterThan, lessThan} from '@databases/pg-typed';
  
  /**
   * Get posts where:
   *
   *   timestamp >= start AND timestamp < end
   */
  async function getPostsBetween(start: Date, end: Date) {
    return await posts(db)
      .find({
        timestamp: allOf([anyOf([greaterThan(start), start]), lessThan(end)]),
      })
      .all();
  }

• You can ignore case when comparing string using caseInsensitive (#217)

  For example, you can do:

  import {caseInsensitive} from '@databases/pg-typed';
  
  /**
   * Return true if there is a user with this username, ignoring
   * the case of the user, so ForbesLindesay would be equivalent
   * to forbeslindesay
   */
  async function userExists(username: string) {
    return (
      0 !==
      (await users(db).count({
        username: caseInsensitive(username),
      }))
    );
  }

• You can test fields in JSON and JSONB columns using jsonPath (#217)

  For example, you can do:

  import {jsonPath} from '@databases/pg-typed';
  
  /**
   * return events where:
   *
   *   event_data.type = 'FEEDBACK'
   */
  async function getFeedbackEvents() {
    return await events(db)
      .find({
        event_data: jsonPath(['type'], 'feedback'),
      })
      .all();
  }

• You can combine entire queries using or and and (#217)

  For example, you can do:

  import {or, and, greaterThan} from '@databases/pg-typed';
  
  /**
   * return posts where:
   *
   *   user_id=${authorId}
   *   AND (
   *     (is_public IS TRUE AND view_count > 1000)
   *     OR (is_public IS FALSE AND view_count > 100)
   *   )
   */
  async function getPopularPostsByAuthor(authorId: User['id']) {
    return await posts(db)
      .find(
        and(
          {user_id: authorId},
          or(
            {
              is_public: true,
              view_count: greaterThan(1_000),
            },
            {
              is_public: false,
              view_count: greaterThan(100),
            },
          ),
        ),
      )
      .all();
  }

  This could be written without using and as:

  import {or, greaterThan} from '@databases/pg-typed';
  
  /**
   * return posts where:
   *
   *   (user_id=${authorId} AND is_public IS TRUE AND view_count > 1000)
   *   OR (user_id=${authorId} AND is_public IS FALSE AND view_count > 100)
   */
  async function getPopularPostsByAuthor(authorId: User['id']) {
    return await posts(db)
      .find(
        or(
            {
              user_id: authorId,
              is_public: true,
              view_count: greaterThan(1_000),
            },
            {
              user_id: authorId,
              is_public: false,
              view_count: greaterThan(100),
            },
          ),
      )
      .all();
  }

• Added error codes and isNoResultFoundError and isMultipleResultsFoundError utilities to allow testing for common errors that come from pg-typed (#211)

• Added the table name to the "More than one row matched..." error (#211)

• Added a new databaseSchema option (#211)

  If you pass the databaseSchema generated by pg-schema-cli into pg-typed, it enables the new "bulk" operations:

  • bulkFind
  • bulkInsert
  • bulkUpdate
  • bulkDelete

  Check the pg-typed documentation for more details.

• Added .findOneRequired to the table API (#211)

  This behaves exactly like .findOne except that it throws an error rather than returning null if the record is not found.

• Added .one and .oneRequired to SelectQuery (#211)

  This can be helpful if you want to select only a subset of fields. For example:

  function getPostWithAuthorName(id: DbPost['id']) {
    const post = await posts(database).findOne({id});
    if (!post) return null;
    const author = await users(database)
      .find({id: post.author_id})
      .select(`name`)
      .oneRequired();
    return {post, author: author.name};
  }

• Added table.key utility for following foreign keys. (#211)

  This is like a type safe version of the inQueryResults field query:

  function getPostAuthor(postId: DbPost['id']) {
    return await users(database).findOne({
      // find users where the id matches the
      // author_id field in posts where the post's
      // id is postId
      id: posts.key(`author_id`, {id: postId}),
    });
  }

  These can even be nested:

  async function getPostAuthorOrg(postId: DbPost['id']) {
    return await orgs(database).findOne({
      id: users.key(
        `org_id`,
        {id: posts.key(`author_id`, {id: postId})}
      ),
    });
  }

Performance Improvements

• Skip running queries that are guarnted to return no results (#217)

  If you had a query like:

  import {anyOf} from '@databases/pg-typed'
  
  async function getUsers(ids: number[]) {
    return users(db).find({id: anyOf(ids)}).all()
  }

  and you called getUsers([]) we used to run the query:

  SELECT * FROM users WHERE FALSE

  We now detect that the WHERE condition always evaluates to FALSE and skip sending the query to the database.

@databases/[email protected]

New Features

• Re-export table and insert record types from index.ts by default. (#211)

  You can set types.tableReExportFileName and types.tableInsertParametersReExportFileName to null to disable this behaviour if you don't want this.

• Generate a schema.json file containing an array of table names and some basic metadata about the columns. (#211)

  This structure can be passed to pg-typed instead of serializeValue to not only enable proper JSON serialisation, but also enable the new bulkOperation methods.

  If you don't want to generate this file, you can set schemaJsonFileName to null to disable it.

@databases/[email protected]

New Features

• Added types.tableReExportFileName (#211)

  This re-exports the table record type from index.ts by default as a convenience. You can explicitly set it to null to disable this behaviour.

• Added types.tableInsertParametersReExportFileName (#211)

  This re-exports the table insert type from index.ts by default as a convenience. You can explicitly set it to null to disable this behaviour.

• Added types.schemaJsonFileName, with a default of "schema.json" (#211)

  These help to support pg-typed's usage of pg-bulk. You should use this generated object instead of serializeValue in most places. If you don't want to generate this file, you can set schemaJsonFileName to null to disable it.

@databases/[email protected]

Refactors

• Simplify usage of UNNEST (#218)

  You can use one UNNEST call with multiple arrays. This is the recommended way to produce records like this.

@databases/[email protected]

New Features

• Initial release (#211)

@databases/[email protected]

New Features

• Add testConnection option as alternative to just waiting for the container to listen on a port (#209)

• Add enableDebugInstructions to allow providing instructions for enabling debug logs (#209)

Bug Fixes

• Do not print incorrect instructions for enabling debug logs (#209)

@databases/[email protected]

Bug Fixes

• Print correct instructions for enabling debug logs (#209)

• Wait for the database and user to be created before resolving (#209)