NosDB SQL & LINQ Support

NosDB is a 100% native .NET Open Source NoSQL Database released under the Apache 2.0 License. NosDB helps you accelerate development through its flexible JSON schema. You can store JSON documents in the database and later search them with SQL.

In addition to using standard NosDB API for accessing database, NosDB provides powerful SQL support for all database operations including data manipulation operations like SELECT, INSERT, UPDATE, and DELETE. You can also do data definition by creating databases, collections, and more.

SQL SELECT Statement

You can use standard SELECT statements to find data in the database. Or, you can use advanced SQL statements with JSON attributes to search Embedded Documents. Standard SQL statements allow your relational database application to easily migrate to a NoSQL database like NosDB. Whereas, the advanced SQL statement allows you to search all kinds of JSON documents in NosDB.

Below is an example of an advanced SQL statement:

SELECT Category.Name, SUM(UnitsInStock)
FROM Products
GROUP BY Category 
ORDER BY Category DESC;

When you run the above statement, you get results like these in JSON format:

{ "Name": "Seafood", "SUM(UnitsInStock)": 701 }
{ "Name": "Produce", "SUM(UnitsInStock)": 100 }
{ "Name": "Meat/Poultry", "SUM(UnitsInStock)": 165 }

NosDB API for Executing SQL Queries

Here is how you would run SQL queries from your .NET code:

Database db = 
NosDB.InitializeDatabase("Data Source=20.200.20.44; Port=9950; 
		Database=myDatabase; Integrated Security=false;");

string query = "SELECT * FROM ProductDetails WHERE ProductID < 10";
ICollectionReader reader = db.ExecuteReader(query);

while (reader.ReadNext())
{
    //return result in the form a document
    IJSONDocument data = reader.GetDocument();
    // Perform operations here
}

LINQ Queries

Here is how you would run the same query as a LINQ query from within your .NET application:

IQueryable<Product> productQuery= from product in ProductDetails
where product.ProductID > 10
select product;

foreach (Product p in productQuery)
{
    // Do something with the product here
}

SQL Statements for INSERT UPDATE and DELETE

You can also use standard INSERT statements from SQL to add JSON documents into NosDB database. Below is an example:

INSERT INTO ProductDetails 
(ProductName, Category, UnitsAvailable, UnitsOrdered) 
VALUES ('Eggs', 'Dairy', 23, 50);

Below is an example of an UPDATE statement:

UPDATE ProductDetails
SET (Category = 'Dairy', Price = 99, 
ProductionDate = DateTime('2000-03-03')) 
WHERE Discontinued = false;

And below is an example of the DELETE statement:

DELETE FROM ProductDetails WHERE ProductID IN (10, 20, 30);

Data Definition with SQL

You can also create databases, collections, indexes, and more by using data definition statements of SQL. For example, below is an example of how you would create a database:

CREATE DATABASE "myDatabase"
  {"CLUSTER": "myCluster", 
   "SingleField": false, 
   "CacheSize": 1024, 
   "MaxCollections": 10};

And, you can also create collections inside your databases like this:

CREATE COLLECTION "myCollection" 
  {"Database": "myDatabase", 
   "CollectionType": "NormalCollection", 
   "Distribution": {"Strategy": "HashBasedDistributionStrategy"},
   "PartitionKey": [{"KeyName": "Category", "KeyType": "String"}]};

What to Do Next?