Database

The DB namespace provides basic database operations in Kooboo. Currently, it supports seven types of databases. To use SqlServer, MySql, or Mongo, you need to configure the connection string in the system settings. Although the usage may vary slightly between different databases, we aim to provide a unified operation standard.

Here are some example operations:

k.DB.indexedDb.tablename.all(); 
k.DB.mongo.tablename.all(); 
k.DB.mysql.tablename.all(); 
k.DB.sqlServer.tablename.all(); 
k.DB.sqlite.tablename.all(); 
k.DB.sequenceDb.openDb("path", "filename").range(0,1000);
k.DB.keyValue.get("key");

IndexedDb: Kooboo's self-implemented indexed database. It is known for its fast performance but does not fully implement the ACID concept.

MongoDB: MongoDB is a non-relational database.

MySql: MySQL is a popular relational database.

SqlServer: Microsoft SQL Server is a relational database.

Sqlite: Sqlite is an embedded database.

SequenceDb: SequenceDb is a highly efficient storage system developed by Kooboo. It only supports sequential insertion and returns an INT ID value.

KeyValue: KeyValue is a key-value storage system developed by Kooboo.

INSERT

All Database support dynamic insertion, without the need to create the table or fields beforehand. If the fields do not exist, they will be automatically created. You can also manually create tables in the database.

The following is an example code for inserting into a user table:

var user ={};
user.FirstName = "Joe"; 
user.LastName = "Smith";
k.DB.sqlite.User.add(user);

QUERY

Kooboo provides several query methods, mainly using the get, find, findAll, and query API methods for querying.

JSON Query

The available syntax includes: EQ, AND, GT, GTE, LT, LTE, OR, CONTAINS.

You can nest and combine conditions, where AND is the default operator, but you can also specify OR. Here is an example:

var MatchLastName = k.DB.sqlite.User.find({LastName:"Smith"}); 

var MatchOR = k.DB.sqlite.User.find({LastName:"smith",FirstName:"one"}); 
 
const { EQ, AND, GT, GTE, LT, LTE, OR, CONTAINS } = k.DB.indexedDb.operators();   
var MatchNested = k.DB.sqlite.User.find({[OR]:{LastName:{[EQ]:"Smith"}, FirstName:{[EQ]:"Joe"}}});

k.response.write(MatchLastName);

Non-SQL QUERY

The system also provides a syntax structure similar to MongoDB Non-SQL query methods.

// available operators: ==, >=, >, <, <=, contains, startwith

var table = k.DB.indexedDb.tablename;

var items = table.find("name == 'matchedvalue'");

var items = table.find("number>=123");

var items = table.find("number >=123&&name=='matchedvalue'");

var items = table.find("name contains 'matchedvalue'");

var items = table.find("name startwith 'matchedvalue'");

SQL Syntax

If you are using an SQL database, Kooboo supports direct SQL queries and execution of SQL statements.

k.DB.sqlite.query("SELECT * FROM Customer"); 

k.DB.sqlite.execute("DELETE FROM Customer");

Match Query

Both the find and findAll methods support match queries, where you can search for records that have specific field values. Here is an example:

var item = k.DB.indexedDb.tablename.find("fieldname", "matchvalue");