loopback-connector-sqlite3x

The advanced but unofficial SQLite3 Connector Module for the LoopBack Framework.

Unlike the official, this module provides full support for e.g auto-migrate, auto-upgrade and model discovery. (please see Features)

Features

• auto-migrate and auto-update for tables, indexes, foreign keys
• model discovery
• full control over the names for tables, fields, indexes and foreign key constraints in the mapped database schema
• connection pool
• all other features provided by sqlite3orm

Installation

for loopback 3 it is recommended to use:

npm install loopback-connector-sqlite3x@<2.0 --save

for loopback 4 please use:

npm install loopback-connector-sqlite3x --save

lb4 datasource test
? Select the connector for test: other
? Enter the connectors package name: loopback-connector-sqlite3x
   create src/datasources/test.datasource.json
   create src/datasources/test.datasource.ts
   update src/datasources/index.ts

Datasource Test was created in src/datasources/

adjust src/datasources/test.datasource.json:

{
  "name": "test",
  "connector": "loopback-connector-sqlite3x",
  "file": "test.db",
  "poolMin": 2
}

Connector settings

export interface Sqlite3AllSettings {
  /**
   * [file=shared memory] - The database file to open
   */
  file: string;
  /**
   * [mode=SQL_OPEN_DEFAULT] - The mode for opening the database file
   * A bit flag combination of:
   *   SQL_OPEN_CREATE,
   *   SQL_OPEN_READONLY,
   *   SQL_OPEN_READWRITE
   * SQL_OPEN_DEFAULT = SQL_OPEN_CREATE | SQL_OPEN_READWRITE
   */
  mode: number;
  /**
   * [min=1] - Minimum connections which should be opened by the connection pool
   */
  poolMin: number;
  /*
   * [max=0] - Maximum connections which can be opened by this connection pool
   */
  poolMax: number;
  /*
   * [debug=false] - enable debug
   */
  debug: boolean;
  /*
   * [lazyConnect=false] - enable lazy connect
   */
  lazyConnect: boolean;
  /*
   * [schemaName='main'] - the default schema
   */
  schemaName: string;
  /*
   * [dbSettings]
   */
  dbSettings: SqlDatabaseSettings;

  /*
   * [propertyValueForNULL=undefined] - the property value if column value is NULL
   */
  propertyValueForNULL: any;

  /*
   * [implicitAutoincrementByDefault=false] - use autogenerated ROWIDs instead of using AUTOINCREMENT keyword explicitly
   */
  implicitAutoincrementByDefault: boolean;
}

/*
 * additional database settings
 *
 *  for a description of the pragma setting see: https://www.sqlite.org/pragma.html
 *  for a description of the execution mode see: https://github.com/TryGhost/node-sqlite3/wiki/Control-Flow
 *
 * defaults:
 *   journalMode 'WAL'
 *   busyTimout = 3000
 *   readUncommitted = 'FALSE
 *   executionMode = 'PARALLELIZE'
 */
export interface SqlDatabaseSettings {
  /*
   * PRAGMA schema.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
   *  for multiple schemas use e.g [ 'temp.OFF', 'main.WAL' ]
   */
  journalMode?: string|string[];
  /*
   * PRAGMA busy_timeout = milliseconds
   */
  busyTimeout?: number;
  /*
   * PRAGMA schema.synchronous = OFF | NORMAL | FULL | EXTRA;
   *  for multiple schemas use e.g [ 'temp.OFF', 'main.FULL' ]
   */
  synchronous?: string|string[];
  /*
   * PRAGMA case_sensitive_like = TRUE | FALSE
   */
  caseSensitiveLike?: string;

  /*
   * PRAGMA foreign_keys = TRUE | FALSE
   */
  foreignKeys?: string;

  /*
   * PRAGMA ignore_check_constraints = TRUE | FALSE
   */
  ignoreCheckConstraints?: string;

  /*
   * PRAGMA query_only = TRUE | FALSE
   */
  queryOnly?: string;

  /*
   * PRAGMA read_uncommitted = TRUE | FALSE
   */
  readUncommitted?: string;

  /*
   * PRAGMA recursive_triggers = TRUE | FALSE
   */
  recursiveTriggers?: string;

  /*
   * PRAGMA schema.secure_delete = TRUE | FALSE | FAST
   *  for multiple schemas use e.g [ 'temp.OFF', 'main.FAST' ]
   */
  secureDelete?: string|string[];

  /*
   *  SERIALIZE | PARALLELIZE
   */
  executionMode?: string;

  /*
   *  PRAGMA cipher_compatibility = 1 | 2 | 3 | 4
   *    see: https://www.zetetic.net/sqlcipher/sqlcipher-api/#cipher_compatibility
   *    only available if node-sqlite3 has been compiled with sqlcipher support
   *      see: https://github.com/gms1/node-sqlite3-orm/blob/master/docs/sqlcipher.md
   */
  cipherCompatibility?: number;

  /*
   * PRAGMA key = 'passphrase';
   *    see: https://www.zetetic.net/sqlcipher/sqlcipher-api/#PRAGMA_key
   *    only available if node-sqlite3 has been compiled with sqlcipher support
   *      see: https://github.com/gms1/node-sqlite3-orm/blob/master/docs/sqlcipher.md
   */
  key?: string;
}

Model definition

You can use the 'sqlite3x' property to specify additional database-specific options for a LoopBack model (see Sqlite3ModelOptions).

@model({
  settings: {
    sqlite3x: {
      tableName: 'MyTableName',
      withoutRowId: true // default: false
    },
  },
})

properties

You can use the 'sqlite3x' property to specify additional database-specific options for a LoopBack property (see Sqlite3PropertyOptions).

  @property({
    type: 'date',
    required: true,
    sqlite3x: {
      columnName: 'MyColumnName',
      dbtype: 'INTEGER NOT NULL',
      dateInMilliSeconds: false // default: true
    }
  })
  myPropertyName: Date;

default type mapping

LoopBack type	Database type
Number	INTEGER if primary key, REAL otherwise
Boolean	INTEGER 1 or 0
Date	INTEGER milliseconds since Jan 01 1970
String	TEXT
JSON / Complex types	TEXT in JSON format

indexes

you can define indexes using the loopback 'indexes' property in the standard or shortened form, as well as using the MySql form

@model({
  settings: {
    indexes: {
      myIndex1: {  // MySql form
        columns: 'col1,col2',
        kind: 'unique'
      },
      myIndex2: { // standard form
        keys: {
          col1: 1, // ascending
          col2: -1, //descending
        },
        options: {
          unique: true,
        }
      },
      myIndex3: { // shortened form
        col1: 1, // ascending
        col2: -1, //descending
      }
    }
  },
})

NOTE: specifying indexes at the model property level is not supported

foreign key constraints

It seems there is no standard way to define database-specific foreign key constraints using loopback, therefore a new way has been introduced: You can define foreign keys using a 'foreignKeys' property

"foreignKeys": {
    "<constraint identifier>": {
      "properties": "<property key>[,<property key>]...",
      "refTable": "<table identifier>",
      "refColumns": "<column identifier>[,<column identifier>]...",
    }
  }

License

loopback-connector-sqlite3x is licensed under the Artistic License 2.0: LICENSE

Release Notes

CHANGELOG