Getting Started

A database is used by almost every modern online application. Using raw SQL, a fluent query builder, and the Eloquent ORM, OstroJS makes communicating with databases across a number of supported databases exceedingly straightforward. OstroJS currently offers first-party support for four databases:

• MySQL 5.7+
• PostgreSQL 9.6+
• SQLite 3.8.8+
• SQL Server 2017+

The config/database.js configuration file in your application contains the configuration for OstroJS's database services. You may define all of your database connections as well as which connection should be utilised by default in this file. The values of your application's environment variables determine the majority of the configuration choices in this file. This file contains examples for the majority of OstroJS's supported database systems.

OstroJS's example environment setup is pre-configured for creating OstroJS apps on your local PC. However, for your local database, you are free to change the database settings as needed.

SQLite databases are stored in a single file on your computer's hard drive. Using the touch command in your terminal, you may create a new SQLite database: touch database/database.sqlite. After the database has been built, you can easily point your environment variables to it by using the DB_DATABASE environment variable to save the absolute path to the database:

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Set the DB_FOREIGN_KEYS environment variable to true to enable foreign key restrictions for SQLite connections:

DB_FOREIGN_KEYS=true

You may use the DB façade to perform queries once you've setup your database connection. Each type of query has its own method in the DB facade: select, update, insert, delete, and statement.

You can use the select method on the DB facade to conduct a basic SELECT query:

const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller{
	async index({view})  {
        let users = await DB.table('users').where('active',1).get();
        view('user.index', {users});
    }
}
module.exports = UserController

The SQL query is supplied as the first argument to the select method, followed by any parameter bindings that need to be tied to the query. These are usually the where clause restrictions' values. The use of parameter binding protects against SQL injection.

The choose method returns an array of results in all cases. Each result in the array will be a NodeJS stdClass object that represents a database record:

const DB = require('@ostro/support/facades/database');
let users = await DB.table('users').get();

for (let user of users) {
    console.log(user.name);
}

Instead of using? to express parameter bindings, you may use named bindings to run a query:

const DB = require('@ostro/support/facades/database');

let results = await DB.table('users').where('id',1).get();

You may use the insert method on the DB facade to run an insert statement. This method, like select, takes the SQL query as the first parameter and the bindings as the second:

await DB.table('users').insert({id:1, name:'Marc'});

To edit existing records in the database, use the update method. The method returns the number of rows impacted by the statement:

let affected = await DB.table('users).where('name','Anita').update({votes: 100 });

To remove records from the database, use the delete method. The method will return the number of rows affected, much like update:

let deleted = await DB.table('users').delete();

You may access each connection using the connection method given by the DB facade if your application declares several connections in its config/database.js configuration file. One of the connections provided in your config/database.js configuration file or configured at runtime using the config helper should be supplied to the connection method:

let users = await DB.connection('sqlite').table('users').get();

1. If you want to connect to the CLI of your database, use the db Assistant command:

node assistant db:query "select * from table"