HomeOfThings - NestJs Sqlite3: Sqlite3 module for NestJs based on 'sqlite3orm'
npm install @homeofthings/nestjs-sqlite3






This module allows you to map your model, written in JavaScript or TypeScript, to a database schema using SQLite Version 3.
nestjs-sqlite3 offers connection pool, automatic upgrades and online backups as well as typesafe database queries and refactoring, using a filter syntax designed to serialize safely without any SQL injection possibility
> NOTE: Your contribution is highly welcome! Feel free to pick-up a TODO-item or add yours.
This module is based on sqlite3orm
supporting SQLCipher
``bash`
npm install @homeofthings/nestjs-sqlite3
`Typescript`
@Module({
imports: [
Sqlite3Module.register(Sqlite3Module, { file: SQL_MEMORY_DB_SHARED }),
],
})
export class AppModule {}
`Typescript`
@Module({
imports: [
Sqlite3Module.registerAsync(Sqlite3Module, {
imports: [], // optional
useFactory: (): Promise
Promise.resolve({
// provide your options
}),
inject: [], // optional inject params for useFactory method
}),
],
})
export class AppModule {}
`Typescript`
@Injectable()
export class MyService {
constructor(connectionManager: ConnectionManager) {}
}
`Typescript`
const connection = await this.connectionManager.getConnection(connectionName);
This is using asynchronous context tracking to get the same database connection from the pool throughout the lifetime of a web request or any other asynchronous duration
You can create a new context by calling ConnectionManager.createConnectionContext() and close it by calling ConnectionManager.closeConnectionContext().Sqlite3Interceptor
For web requests this is automatically accomblished by the provided .
If you need a connection which works independend from the asynchronous connection context (e.g for backup, schema creation/upgrade, ...), you can get it from the pool:
`Typescript`
const connection = await this.connectionManager.getConnectionPool(connectionName).get();
> NOTE: all repositories as well as the entity-manager require an asynchronous connection context to be created
`Typescript`
@Injectable()
export class MyService {
constructor(@InjectConnectionPool() sqlConnectionPool: SqlConnectionPool) {}
}
optional you can provide connectionName as argument for @InjectConnectionPool
please see (https://github.com/gms1/node-sqlite3-orm#mapping-intruduction)
`Typescript`
@Injectable()
export class MyService {
constructor(@InjectEntityManager() entityManager: EntityManager) {}
}
optional you can provide connectionName as argument for @InjectEntityManager
register repository by providing entity class to the Sqlite3Module.forFeature method
`Typescript`
@Module({
imports: [
Sqlite3Module.forFeature([User]),
],
})
export class AppModule {}
thereafter you can inject the standard repository using:
`Typescript`
@Injectable()
export class MyService {
constructor(@InjectRepository(User) repository: Repository
}
optional you can provide connectionName as second argument for Sqlite3Module.forFeature
define a custom repository:
`Typescript`
export class UserRepository extends Repository
constructor(connectionManager: ConnectionManager, connectionName: string) {
super(User, connectionManager, connectionName);
}
}
register the custom Repository using the Sqlite3Module.forFeature method:
`Typescript`
@Module({
imports: [
Sqlite3Module.forFeature([UserRepository]),
],
})
export class AppModule {}
inject the custom repository using InjectCustomRepository decorator
`Typescript`
@Injectable()
export class MyService {
constructor(@InjectCustomRepository(UserRepository) repository: UserRepository) {}
}
for convenience, similar decorators are supported as by typeorm
`Typescript
@Entity({ name: 'USERS', autoIncrement: true })
class User {
@PrimaryKeyColumn({ name: 'user_id', dbtype: 'INTEGER NOT NULL' })
public id?: number;
@Column({ name: 'user_email', dbtype: 'TEXT NOT NULL' })
@Index('idx_users_email', true)
public email: string;
@Column({ name: 'user_firstname', dbtype: 'TEXT NOT NULL' })
public firstName: string;
@Column({ name: 'user_lastname', dbtype: 'TEXT NOT NULL' })
public lastName: string;
@Column()
public password: string;
}
`
relations can be defined by using the ForeignKey decorator
please see (https://github.com/gms1/node-sqlite3-orm#typesafe-query-syntax)
please see (https://github.com/gms1/node-sqlite3-orm#schema-creation)
`Typescript
const connection = await this.connectionManager.getConnection(connectionName);
const autoUpgrader = new AutoUpgrader(connection);
// run autoupgrade for all registered tables
autoUpgrader.upgradeAllTables();
// run autoupgrade for all tables referenced by a connection:
autoUpgrader.upgradeTables(ConnectionManager.getTables(connectionName));
`
> NOTE: if you are using a single database, you can call upgradeAllTables, otherwise you will need to specify the tables for the specific database that you want to upgrade.forFeature
> All tables referenced by a call for a specific database can be retrieved by calling ConnectionManager.getTables
to run the backup in one step you can call:
`Typescript`
const connection = await this.connectionManager.getConnection(connectionName);
const backup = await connection.backup('backup.db');
await backup.step(-1);
backup.finish();
sqlite3orm uses the debug` module, so you can turn on the logging by setting the 'DEBUG' environment to "sqlite3orm:\*"