How to Use Databases in KDB.AI

This page provides details on how to create, retrieve, and delete databases in KDB.AI.

In KDB.AI, the database is the fundamental structure for storing and organizing your data. Each database is a collection of tables, providing data storage, indexing, and API support.

Tip

For the best experience, start by reading about databases in KDB.AI.

Key principles of database management in KDB.AI

To simplify database design/management and prevent naming conflicts, follow the rules below:

  • Default database: You don't need to create a database to create tables. When you create a table without specifying a database, it is placed in a default, undeletable database.

  • Unique table names within a database: Tables within a database must have unique names, but different databases can contain tables with the same name. This is similar to the concept of namespaces.

  • Cascade deletion: When you delete a database, all child entities (tables) are also deleted.

Refer to the KDB.AI Naming Conventions and Reserved Words guidelines.

Setup

Before starting, you must have:

Import dependencies

Import the following dependencies:

Python

Copy 
import sys
import kdbai_client as kdbai
from pprint import pprint # for pretty printing
import pandas as pd
import numpy as np

Create database

Create a database using one of the following methods:

  • Use the create_database function (in Python).

  • Use the createDatabase function (in q).

  • Send a POST request to /api/v2/databases (in REST).

Each database must have a unique name.

Python

REST

q

Python

Copy 
session.create_database("myDatabase")

Shell

Copy 
curl -s localhost:8081/api/v2/databases -d '{"database" : "myDatabase"}'

q

Copy 
`gw set hopen 8082;
gw(`createDatabase;enlist[`database]!enlist `myDatabaseName)

Tip

Naming Reminder

When creating a table, column, or database in KDB.AI, make sure to follow our Naming Conventions and Reserved Words guidelines. A quick check now can save hours of troubleshooting later.

Get database

Retrieve the details of a database by name, including a list of its tables metadata, by using one of the following methods:

Python

REST

q

Python

Copy 
session.database("myDatabase")

Shell

Copy 
curl -s localhost:8081/api/v2/databases/default

q

Copy 
`gw set hopen 8082;
gw(`getDatabase;enlist[`database]!enlist `myDatabaseName)

Refresh database

If multiple users can manage your database, to ensure that the list of tables associated with your database is current, use the database.refresh function (only available in Python):

Python

REST

q

Python

Copy 
database.refresh()

N/A

N/A

List databases

Retrieve a list of database names in ascending order, including the default database, with one of the following methods:

Python

REST

q

Python

Copy 
session.databases()

Shell

Copy 
curl -s localhost:8081/api/v2/databases

q

Copy 
`gw set hopen 8082;
gw(`listDatabases;`)

Get database info

Get database info, including table info of each table in the database:

Python

Copy 
db = session.database("default")
db.info()

q

Copy 
// Open connection
`gw set hopen 8082;

// Get Database Info
gw(`getDatabaseInfo;args:enlist[`database]!enlist `default)

REST

Copy 
curl -H 'Content-Type: application/json' localhost:8082/api/v2/info/databases/default

Get databases info

Get all databases info, including table info of all tables in all databases:

Python

Copy 
session = kdbai.Session(endpoint='http://localhost:8082')
session.databases_info()

q

Copy 
// Open connection
`gw set hopen 8082;

// Get databases info
gw(`getAllDatabasesInfo;`)

REST

Copy 
curl -H 'Content-Type: application/json' localhost:8082/api/v2/info/databases

Delete database

Delete a database by name along with all associated tables using one of the following commands:

Python

REST

q

Python

Copy 
db=session.database("myDatabase")
db.drop()

Shell

Copy 
curl -s -X DELETE localhost:8081/api/v2/databases/myDatabase

q

Copy 
`gw set hopen 8082;
gw(`deleteDatabase;enlist[`database]!enlist `myDatabaseName)

Next steps

Now that you know how to work with databases, learn how to: