Queries

Base Options

{ file?: string, table?: string }

Any query can be run with the file parameter, which represents the relative path to a database file. The database is accessed dynamically during the query and will only remain open until its resolution. This is ideal for avoiding the clutter of synchronously accessing a database when only a single query needs to be run.

// No need to call db.open('./database.db') or db.close(). This query will handle that itself!
const data = await db.select({ file: './database.db', all: true, from: 'table' }); 

A query can also be run with the table parameter, which represents what table to query in the database. This parameter overrides and is functionally identical to the into parameter of insert and the from parameter of select and delete – it exists as a user preference. The code below executes identically to the example above:

// Tomayto, tomahto - table, from
const data = await db.select({ file: './database.db', table: 'table', all: true }); 

Insert

insert({ into: string, columns?: string[], values: string[] })
=> Promise<void>

The insert query adds new rows of data into a table in the database.

Options

• into – Which table to insert into.
• columns? – Which columns to insert data to. This option does not need to be specified if adding values for all columns.
• values – Which values to add to the table.

Examples

Insert with Variables

var id = '123456789';
var username = 'user99';

// INSERT INTO users (id, username) VALUES (123456789, 'user99');
await db.insert({
  into: 'users',
  columns: [ 'id', 'username' ],
  values: [ id, username ]
});

Insert without Columns

// INSERT INTO users VALUES (314159265, 'pi', 314);
await db.insert({
  into: 'users',
  values: [ '314159265', 'pi', 314 ]
});

Select

select({ first?: boolean, all?: boolean, columns?: string[], from: string, where?: (string | BooleanExpression)[] })
=> Promise<Object | Object[]>

The select query fetches data from a table in the database. This query returns the data as an Object or array of Objects.

Options

• first – When true, the query returns the first Object fetched from the database. This is useful for selections that will only return one row, e.g., when querying a unique value or running an aggregate function.
• all – When true, the query fetches data from all columns in the table. This option is mutually exclusive with columns.
• columns – Which columns to select data from.
• from – Which table to select from.
• where – Specify conditions for querying the table.

Examples

Select All

// SELECT * FROM users;
const data = await db.select({
  all: true,
  from: 'users'
});

console.log(data);

[
   { id: '123456789', username: 'user99', points: 20 },
   { id: '314159265', username: 'pi', points: 314 },
   { id: '987654321', username: 'nullptr', points: null }
]

Select with Conditions

// SELECT username FROM users WHERE points >= 20 OR username = 'user99';
const data = await db.select({
  columns: [ 'username' ],
  from: 'users',
  where: [ 
    db.expression.geq('points', 50), 
    db.logic.OR, 
    db.expression.eq('username', 'user99')
  ]
});

console.log(data);

[
   { username: 'user99' },
   { username: 'pi' }
]

Select First

// SELECT * FROM users WHERE username = 'user99';
const data = await db.select({
  first: true, 
  all: true,
  from: 'users',
  where: [ db.expression.eq('username', 'user99') ]
});

console.log(data);

{ id: '123456789', username: 'user99', points: 20 }

Update

Delete