SQL table reference

Using SQL tables to denormalize data

Though not required, it's a common practice to:

1. Generate the raw data you need in normalized tables

2. Mark those tables as hidden.

3. Create views to to model the denormalized tables.

For example, an ecommerce database contains orders and order_items tables. The orders table includes the total order amount, which is based on the sum of the prices and quantities of the order items.

In this case, you can:

1. Create an orders_base table with the order data.

2. Create a SQL table orders that joins the orders_base table with the order_items table, and then calculates the total amount for each order:

SELECT *, sum(order_items.price * order_items.quantity) as total_amount FROM orders_base JOIN order_items ON orders_base.id = order_items.order_id GROUP BY orders_base.id

Using variables in SQL tables

To get access to a variable, use the $ prefix. For example, to use the user_id variable to create a SQL table that selects a subset of records from an events table:

SELECT * FROM events WHERE user_id = $user_id

Functions for working with JSON

SQLite provides some helpful functions to compose JSON objects and arrays.

For more information about SQLite's support for JSON, go to the SQLite JSON Documentation.

json_object

To create a JSON object that pulls the user's first and last names from the users_base table:

SELECT json_object( 'first', first_name, 'last', last_name ) as name FROM users_base

json_group_array

The json_group_array function creates a JSON array from a set of values.

For example, to create an array of all roles for each user in the users_base table:

SELECT
 users_base.*,
 json_group_array(roles.name) as roles 
FROM users_base 
JOIN users_roles ON users_base.id = users_roles.user_id 
JOIN roles ON users_roles.role_id = roles.id 
GROUP BY users_base.id