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.

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)

Note

Database name rules

  • Max length is 128 characters

  • Must contain only alphanumeric characters and underscore

  • Must start with an alpha character

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;`)

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: