jsonstor-mssql

Home: http://jsonstor.liquicode.com

Version: 0.2.0

Documents are stored in a Microsoft SQL Server database.

Overview

jsonstor defines one interface for working with a document store and implements it for a variety of database products and file formats. The interface is the same whichever adapter is underneath, so the storage a project uses becomes a configuration decision rather than a structural one.

This package is one such adapter. Documents are stored in a Microsoft SQL Server database.

It is packaged separately so that it stays optional. A project which does not store documents this way never downloads mssql.

See @liquicode/jsonstor for the interface, and jsonstor.liquicode.com for the documentation.

Getting Started

npm install --save @liquicode/jsonstor-mssql

const jsonstor = require( '@liquicode/jsonstor' )();
jsonstor.LoadPlugin( require( '@liquicode/jsonstor-mssql' ) );

let storage = jsonstor.GetStorage( 'jsonstor-mssql', {
	Server: "localhost",
	Port: 1433,
	Database: '...',
	Schema: "dbo",
	Table: '...',
	PrimaryKey: "",
	PrimaryKeyMutable: false,
	UserName: '...',
	Password: '...',
	Encrypt: false,
	TrustServerCertificate: true,
	ModifySchema: false,
	PayloadColumn: "",
	PayloadSync: false,
	Columns: [],
} );

Versions

This package answers to more than one name. Pass any of these to GetStorage(); a name which is not listed is refused.

| Name | Dialect it uses | Measured against | |------|-----------------|------------------| | jsonstor-mssql-v14.0 | its own | 14.0 | | jsonstor-mssql | jsonstor-mssql-v14.0 | - | | jsonstor-mssql-v14 | jsonstor-mssql-v14.0 | - | | jsonstor-mssql-v15 | jsonstor-mssql-v14.0 | - | | jsonstor-mssql-v15.0 | jsonstor-mssql-v14.0 | 15.0 | | jsonstor-mssql-v16 | jsonstor-mssql-v14.0 | - | | jsonstor-mssql-v16.0 | jsonstor-mssql-v14.0 | 16.0 |

A name with its own dialect was measured against that server version and covers every later one up to the next such name. The rest resolve to one of those. The bare name follows the newest dialect this package carries, which is what most callers want.

A name whose dialect your server cannot serve is refused on the first operation, naming the version you asked for and the one the server needs.

Settings

| Setting | Required | Default | Description | |---|:---:|:---:|---| | Server | No | "localhost" | The name or address of the SQL Server instance. | | Port | No | 1433 | The service port of the SQL Server instance. | | Database | Yes | - | The name of the database to use. It must already exist; this adapter never creates a database. | | Schema | No | "dbo" | The schema holding the table. Every statement names it, so the connection's default schema does not decide which table is used. | | Table | Yes | - | The name of the table to use. | | PrimaryKey | No | "" | The column to treat as the document identifier. Empty discovers it from the table: a column named _id, then an auto-increment key. IdField is the former spelling and still works. | | PrimaryKeyMutable | No | false | Allow an update or replacement to change the identifier. When false, such an operation is refused. | | UserName | Yes | - | The user to connect as. | | Password | Yes | - | That user's password. Pass an empty string for none - the setting itself is required. | | Encrypt | No | false | Encrypt the connection. The driver defaults this to true, which a server presenting a self-signed certificate refuses; this adapter defaults it to false so a local server connects, and a real one should turn it on. | | TrustServerCertificate | No | true | Accept a certificate the machine does not trust. Turn this off wherever Encrypt is on and the certificate is a real one. | | ModifySchema | No | false | Allow the adapter to create the schema, the table, and the columns it is told to create. It never adds a column because a document had a field. | | PayloadColumn | No | "" | The column which stores the document as JSON text, as an NVARCHAR(MAX). Empty means none, and then every field must already be a column. Created when missing if ModifySchema is true. | | PayloadSync | No | false | Store the whole document in the payload, and copy fields into their columns for filtering. When false, the payload holds only fields without a column. | | Columns | No | [] | Columns to create, as { Name, Type, Key }. Used only when this adapter creates the table; afterwards the table itself is the authority. |

Peculiarities

• A criteria becomes a WHERE clause, and jsongin checks every returned row. Conditions SQL cannot express are left out of the clause, so more rows are read, but the result is the same as on any other adapter. $mod and the $bits* operators are always left out. See Translation Layer.
• Two settings choose how a document is stored:
  • No PayloadColumn. The document is the columns. A field with no column, or a value its column cannot hold, is refused. Use this for flat documents in an existing table.
  • PayloadColumn with PayloadSync: false. Fields with a column are stored there, and every other field in the payload as JSON. A value which does not fit its column is refused.
  • PayloadColumn with PayloadSync: true. The payload holds the whole document, and the columns are copies used for filtering. Only this configuration keeps every document exactly.
• A value must fit its column exactly. A fraction does not fit an INT, and a string longer than its NVARCHAR does not fit. With PayloadSync: true such a value is stored as NULL in the column and kept in the payload, so a search still finds it. A condition comparing a column with a value of another type is left out of the clause.
• A boolean is stored in a BIT column and filtered in the clause.
• Without a payload for a field, an absent field reads back as null. Use PayloadSync: true if that matters.
• ModifySchema: true lets the adapter change your database: it creates the schema, the table, the Columns you list, and the PayloadColumn. It never adds a column because a document has a new field.
• A table the adapter creates has an NVARCHAR(450) _id and a _seq identity column which records insertion order and is never part of a document. The payload column is NVARCHAR(MAX). A table you created has no _seq and is read in the server's order.
• The database must already exist. A missing database is reported by SQL Server as a failed login for the user.
• Encrypt is false by default, unlike the driver's own default, so a server with a self-signed certificate connects. Turn it on for a real server, and turn TrustServerCertificate off when its certificate is trusted.
• UserName and Password are both required, even when the password is empty.

Storage Interface

Every adapter implements the same functions, and they are documented once: Storage Interface.

The operator list is not repeated here. A criteria, a projection, and an update are the engine's, and the list changes whenever the engine gains an operator - a copy in this file could only ever be out of date. See the Operator Reference.

Dependencies

• mssql : The Microsoft SQL Server client for Node.
• @liquicode/jsonstor : The storage interface.
• @liquicode/jsongin : The query engine.

License

MIT