Explore Nile in 5 minutes with SQL

Learn about Nile's tenant aware tables and how they provide tenant isolation through virtual tenant databases.

1. Create a database

1. Sign up for an invite to Nile if you don't have one already
2. You should see a welcome message. Click on "Lets get started"
3. Give your workspace and database names, or you can accept the default auto-generated names. In order to complete this quickstart in a browser, make sure you select to “Use Token in Browser”.

2. Create a table

Lets imagine that we are building a todo list app and create our first table:

create table todos (
    id uuid DEFAULT (gen_random_uuid()),
    tenant_id uuid,
    title varchar(256),
    complete boolean);

You will see the new table in the panel on the left side of the screen, and you can expand it to view the columns. If you are in psql, you can do \d todos in order to view the schema.

See the tenant_id column? By specifying this column, You are making the table tenant aware. The rows in it will belong to specific tenants. If you leave it out, the table is considered shared, more on this later.

3. Insert tenant

Nile ships with built-in tables, like tenants table that you used earlier. They are covered in more depth in our concepts documentation.

Meanwhile, lets insert a tenant into the built-in tenant table:

-- Creating my first tenant
insert into tenants (name) values ('my first customer');
select * from tenants;

Now, with the new tenant created, lets insert a record to the “todos” table - don’t forget to include the tenant_id:

-- adding a todo item for this tenant
insert into todos (tenant_id, title, complete) values ('<tenant_id>', 'feed my cat', false);

SELECT tenants.name, title, complete
FROM todos join tenants on tenants.id = todos.tenant_id;

If all went well, you’ll see the first todo of your first customer 🏆

name                 title          complete
---------------------------------------------
my first customer    feed my cat    f

4. Add another tenant

It is now time for our second customer and their todo item:

-- creating my second tenant
insert into tenants (name) values ('different customer');
select * from tenants;

-- a new todo item for our second tenant
insert into todos (tenant_id, title, complete) values ('<tenant_id_of_different_customer>',
'take out the trash', false);

SELECT tenants.name, title, complete
FROM todos join tenants on tenants.id = todos.tenant_id;

If all went well, you now see the todo items for both customers:

name                 title                 complete
----------------------------------------------------
my first customer    feed my cat           f
different customer   take out the trash    f

5. Tenant isolation

Nile goes a step further and provides tenant isolation. You can set the session to a specific tenant, and every query that follows will only return data that belongs to this specific tenant.

Think of it as querying a virtual database dedicated to this one specific tenant.

You can select a tenant either from the drop-down list next to the ▶️ button. Or by setting the session parameter in SQL.

select * from tenants;
-- set context to isolate query to a specific tenant DB
-- our example uses the second tenant here
set nile.tenant_id = '<tenant_id>';

SELECT tenants.name, title, complete
FROM todos join tenants on tenants.id = todos.tenant_id;

If all went well, you’ll see the todo task for “different customer” and not the first customer:

name                 title                 complete
---------------------------------------------------
different customer   take out the trash    f

6. Looking good! What's next?

🏆 Tada! You have learned the key Nile concepts. And it only took 5 minutes.

You can learn more about Nile's tenant virtualization features in the following tutorials:

• Tenant management
• Tenant isolation

Next, you will probably want to learn how to use Nile for building an app in your favorite language. Check out our Getting Started guides for more information.