Databases, schemas, and tables

To manage data in a database efficiently, you need to follow a structured process that involves creating databases, tables, and schemas. The following detailed guide can help you understand and implement each step.

Setup

The examples run on any YugabyteDB universe.

• Local
• YugabyteDB Aeon
• YugabyteDB Anywhere

./bin/yugabyted start \
                --advertise_address=127.0.0.1 \
                --base_dir=${HOME}/var/node1 \
                --cloud_location=aws.us-east-2.us-east-2a

./bin/yugabyted configure data_placement --base_dir=${HOME}/var/node1 --fault_tolerance=zone

./bin/yugabyted status --base_dir=${HOME}/var/node1

Databases

A database is the highest level of data organization and serves as a container for all objects such as tables, views, indexes, functions, and schemas. A YugabyteDB cluster can manage multiple databases and each database is isolated from the others, ensuring data integrity and security.

Create a database

By default, a database named yugabyte is already created. To create a new database, testdb, run the following statement:

CREATE DATABASE testdb;

This creates an empty database where you can create tables.

Switch to the new database

To switch or connect to the new database, use the \c meta-command as follows:

\c testdb

You should see the following output:

You are now connected to database "testdb" as user "yugabyte".
testdb=#

List databases

To list all databases, use the \l or \list meta-commands.

testdb=# \l

                                   List of databases
      Name       |  Owner   | Encoding | Collate |    Ctype    |   Access privileges
-----------------+----------+----------+---------+-------------+-----------------------
 postgres        | postgres | UTF8     | C       | en_US.UTF-8 |
 system_platform | postgres | UTF8     | C       | en_US.UTF-8 |
 template0       | postgres | UTF8     | C       | en_US.UTF-8 | =c/postgres          +
                 |          |          |         |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | C       | en_US.UTF-8 | =c/postgres          +
                 |          |          |         |             | postgres=CTc/postgres
 testdb          | yugabyte | UTF8     | C       | en_US.UTF-8 |
 yugabyte        | postgres | UTF8     | C       | en_US.UTF-8 |

Drop database

To drop or delete the database, connect to another database and then use the DROP command.

Connect to another database as follows:

testdb=# \c yugabyte

You are now connected to database "yugabyte" as user "yugabyte".

Use the DROP command as follows:

yugabyte=# DROP DATABASE testdb;

DROP DATABASE

Tables

A table is the fundamental database object that stores the actual data in a structured format, consisting of rows and columns. Tables are created in a specific schema (by default the public schema) and contain the data that applications and users interact with. Each table has a defined structure, with columns representing the different attributes or fields of the data, and rows representing individual records or entries.

Create a table

Create a table using the CREATE TABLE statement.

CREATE TABLE users (
  id serial,
  username CHAR(25) NOT NULL,
  email TEXT DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE

To list all tables, use the \dt meta-command.

yugabyte=# \dt

                List of relations
 Schema |        Name         | Type  |  Owner
--------+---------------------+-------+----------
 public | users               | table | yugabyte

To list more information about the tables you created, use the \d+ meta-command.

yugabyte=# \d+

                          List of relations
 Schema |     Name     |   Type   |  Owner   |  Size   | Description
--------+--------------+----------+----------+---------+-------------
 public | users        | table    | yugabyte | 3072 kB |
 public | users_id_seq | sequence | yugabyte | 0 bytes |

The users_id_seq sequence is the result of the serial datatype that has been used in the definition of the id column.

Insert data

After the tables are set up, you can add data to them. To add a record to the table, you can use the INSERT command.

INSERT INTO users VALUES(1, 'Yoda');

As the statement does not have an explicit value for the column email, the default value of NULL is set for that column.

Query data

You can retrieve data from tables using the SELECT statement. For example:

SELECT * FROM users;

To retrieve only certain columns, you can specify the column name as follows:

SELECT username FROM users;

Alter a table

After a table is created, you might need to alter it by adding, removing, or modifying columns. You can use the ALTER TABLE command to perform these actions.

Add a column

To add a new column address, run the following command:

ALTER TABLE users ADD COLUMN address TEXT;

Drop a column

To drop an existing column, say enabled, you can run the following command:

ALTER TABLE users DROP COLUMN enabled ;

Modify a column name

To modify the name of a column, say to change the name of the id column to user_id, do the following:

ALTER TABLE users RENAME COLUMN id to user_id;

Schemas

A schema is a logical container in a database that holds database objects such as tables, views, functions, and indexes. Schemas provide a way to organize objects into logical groups, making it easier to manage large databases with many objects, and avoiding name conflicts. By default, YugabyteDB creates a schema named public in each database.

Create a schema

To create the schema with name myschema, run the following command:

testdb=# CREATE SCHEMA myschema;

List schemas

List the schemas as follows:

yugabyte=# \dn

   Name   |  Owner
----------+----------
 myschema | yugabyte
 public   | postgres
(2 rows)

Current schema

To see which schema is currently the default, run the following:

yugabyte=# SHOW search_path;

   search_path
-----------------
 "$user", public
(1 row)

Create tables in a schema

To create a table in a specific schema, prefix the table name with the schema name. For example, create the table users in the schema myschema:

yugabyte=# CREATE TABLE myschema.users(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

At this point, the public schema is still the selected schema, and running the \d meta-command would not list the table you just created.

Switch schemas

To set myschema as the current schema in this session, do the following:

SET search_path=myschema;

yugabyte=# SHOW search_path;

 search_path
-------------
 myschema

List the table you created.

yugabyte=# \d

           List of relations
  Schema  | Name  | Type  |  Owner
----------+---------+-------+----------
 myschema | users | table | yugabyte

Drop schemas

To drop the schema myschema and all the objects inside it, first change the current schema.

yugabyte=# SET search_path=public;

Next, run the DROP statement as follows:

yugabyte=# DROP SCHEMA myschema CASCADE;

You should see the following output.

NOTICE:  drop cascades to table myschema.users
DROP SCHEMA

Users

Managing users (also called roles) involves creating, altering, and deleting users, and managing their permissions.

By default, YugabyteDB has two admin users already created: yugabyte (the recommended user) and postgres (mainly for backward compatibility with PostgreSQL).

Current user

You can display the current user information as follows:

yugabyte=# \conninfo

This should output the following:

You are connected to database "yugabyte" as user "yugabyte" on host "127.0.0.1" at port "5433".

List users

To check all the users provisioned, run the following meta-command:

yugabyte=# \du

                                     List of roles
  Role name   |                         Attributes                         | Member of
--------------+------------------------------------------------------------+-----------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 yb_db_admin  | No inheritance, Cannot login                               | {}
 yb_extension | Cannot login                                               | {}
 yb_fdw       | Cannot login                                               | {}
 yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Create a user

You can create a user with the CREATE USER command. For example, to create a user yoda with password feeltheforce as follows.

CREATE USER yoda WITH PASSWORD 'feeltheforce';

Change a user password

You can change passwords of existing users using the ALTER USER command. For example:

ALTER USER yoda WITH PASSWORD 'thereisnotry';

User privileges

Users can be granted privileges to perform certain operations on databases and tables. Privileges include actions like SELECT, INSERT, UPDATE, DELETE, and more. You can provide privileges to users using the GRANT command:

GRANT SELECT, INSERT ON TABLE users TO yoda;

Superuser privileges

To give a user superuser privileges (full administrative rights), you can use the ALTER USER command. For example:

ALTER USER yoda WITH SUPERUSER;

To revoke superuser privileges, do the following:

ALTER USER yoda WITH NOSUPERUSER;

Drop a user

To delete a user, use the DROP USER command:

DROP USER yoda;