npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@openfn/language-mssql

v5.0.7

Published

A Microsoft SQL language pack for OpenFn

Downloads

469

Readme

Language MSSQL

Language Pack for connecting to Azure SQL Server via OpenFn.

Documentation

Configuration

View all the required and optional properties for state.configuration in the official configuration-schema definition.

Sample expression

sql query

sql({
  query: `
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_CATALOG='my-demo'
  `,
});

sql({
  query: `SELECT * FROM Household`,
  options: {
    writeSql: true, // Keep to true to log query (otherwise make it false).
    execute: true, // keep to false to not alter DB
  },
});

Find a single value for a table

This helper function allows to build a specific query where sql would not be best suited. It returns a single value and not a promise. An example of usage would be in building a mapping object with a value from a lookup table.

fn(async state => {
  const user = {
    id: 1,
    name: 'Mamadou',
    user_id: await findValue({
      uuid: 'id',
      relation: 'users',
      where: { first_name: 'Mama%' },
      operator: { first_name: 'like' }, // operator is optional. "=" is used by default.
    })(state),
  };

  return upsert(...)(state);
});

Insert one single record

insert(
  'SomeDB.dbo.SupplierTest',
  {
    SupplierNumber: 1,
    Name: dataValue('name'),
    Address: 'Nunya Bihz-Nash',
  },
  {
    // The optional `options` argument allows for global string replacement with
    // NULL. This is useful if you want to map an undefined value (e.g., x.name)
    // to NULL. It can be a single string or an array of strings.
    // It DEFAULTS to "'undefined'", and can be turned off w/ `false`.
    setNull: "'undefined'",
    logValues: true,
  }
);

Insert or Update using a unique column as a key

This function insert or update depending on the existence of a record in the database.

upsert(
  'SomeDB.dbo.Supplier',
  'SupplierNumber',
  {
    SupplierNumber: 1,
    Name: dataValue('name'),
    Address: 'Now I can tell!',
  },
  // Do NOT replace any instances of 'undefined' in the final SQL statement.
  { setNull: false, logValues: true }
);

Insert or Update if a value exist in the record

This function will upsert a record only if the logical given is true. In this case we check if dataValue('name') exists.

upsertIf(
  dataValue('name'),
  'users',
  'user_id',
  {
    name: 'Elodie',
    id: 7,
  },
  // Replace any occurence of '' and 'undefined' to NULL
  {
    setNull: ["''", "'undefined'"],
    writeSql: true,
    execute: false,
    logValues: true,
  }
);

Insert Many records

This function allows the insert of a set of records inside a table all at once. Pass logQuery option to true to display the query.

// Note that insertMany takes a function which returns an array—this helps
// enforce that each item in the array has the same keys.
insertMany(
  'SomeDB.dbo.Supplier',
  state =>
    state.data.supplierArray.map(s => {
      return {
        SupplierNumber: s.id,
        Name: s.name,
        Address: s.address,
      };
    }),
  { writeSql: true, logValues: true }
);

Insert or Update Many records

This function inserts or updates many records all at once depending on their existence in the database.

// Note that insertMany takes a function which returns an array—this helps
// enforce that each item in the array has the same keys.
upsertMany(
  'SomeDB.dbo.Supplier',
  'SupplierNumber',
  state =>
    state.data.supplierArray.map(s => {
      return {
        SupplierNumber: s.id,
        Name: s.name,
        Address: s.address,
      };
    }),
  { writeSql: true, execute: false, logValues: true }
);

In case we need to check on multiple columns before upserting, we can have an array of uuids.

upsertMany(
  'SomeDB.dbo.Supplier',
  ['SupplierNumber', 'SupplierCode'],
  state =>
    state.data.supplierArray.map(s => {
      return {
        SupplierNumber: s.id,
        Name: s.name,
        Address: s.address,
        SupplierCode: s.code,
      };
    }),
  { writeSql: true, execute: false, logValues: true }
);

Describe a table from mssql

This function is used to fetch the list of columns of a given table in the database.

describeTable('users', { writeSql: false, execute: true });

Create a table in the database

This function allows to create a table in a database from a given array of columns. The key identity can be use for a column to auto-generate a value.

insertTable('users', state =>
  state.data.map(column => ({
    name: column.name,
    type: column.type,
    required: true, // optional
    unique: false, // optional - set to true for unique constraint
  }))
);

Alter a table in the database

This function allows to add new columns to a table. Beware of the fact that you cannot add new columns with names that already exist in the table.

modifyTable(
  'users',
  state =>
    state.data.map(newColumn => ({
      name: newColumn.name,
      type: newColumn.type,
      required: true, // optional
      unique: false, // optional - set to true for unique constraint
    })),
  { writeSql: false, execute: true }
);

Development

Clone the adaptors monorepo. Follow the Getting Started guide inside to get set up.

Run tests using pnpm run test or pnpm run test:watch

Build the project using pnpm build.

To just build the docs run pnpm build docs