database-structure.md

Database Structure

Every entity requires an id column of type uuid. You can use an integer, but it will be treated as a string.

Naming

Postgres does not support uppercase letters in table and column names. Use a _ in a column or table name to make the next letter upper case in your models

CREATE TABLE book_price_group (
	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

	group_name TEXT
)

This will create a context which can be used like this

const group = new BookPriceGroup();
group.groupName = "Kids Books";

References

Let's demonstrate a simple reference between an author and a book

CREATE TABLE person (
	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
	name TEXT
);

CREATE TABLE book (
	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
	title TEXT
	
	author_id UUID CONSTRAINT author__books REFERENCES person (id)
);

This will create a context which with the references

const book = db.book.find(1);
const author = await book.author.fetch();

author.books.toArray();

You can use _ in a references name to make it uppercase in the context

Hide Reference

You can use empty constraint names to hide references from models

CREATE TABLE localized_text (
	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

	en TEXT,
	de TEXT,
	ru TEXT
);

CREATE TABLE category (
	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

	localized_title_id UUID CONSTRAINT localized_title__ REFERENCES localized_text (id)
);

This will create a localizedTitle reference from category to LocalizedText without adding a reference from LocalizedText to Category

Deactivate instead of delete

vlquery supports deactivating rows instead of deleting them. References will be checked by the framework. Let's demonstrate this feature with a column named _active.

First, add the following configuration to vlconfig.json:

	"context": {
		"active": "_active"
		...
	}

This requires a column _active of type boolean in every entity in the database

CREATE TABLE book (
	id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
	_active BOOLEAN DEFAULT true,

	...
);