Projects

• Projects are a file tree containing the source code of your project.
• The top-level directory is called the project root.
• Below is an example of a project.

[project]
version = "0.1.0"
title = "my_project"

[database]
db = "postgres" # will be "sqlite" if you're using sqlite

create table users (
    pk integer primary key auto increment,
    username text unique not null,
    password text not null
);

[[migrations]]
script = "schema.sql"
from = "=0.0.0"
to = "0.1.0"

-- [ my_project 0.1.0 ]

-- [ users.sql ]

create table users (
    pk integer primary key auto increment,
    username text unique not null,
    password text not null
);

Scripts

• Scripts are SQL files which contain the code to our project.
• Scripts must use the .sql extension.
• When we build our project, we will concatenate all of our scripts together into a single script.

Modules

• Modules are directories which contain scripts.
• They are located in the src/ directory.
  • The src/ directory itself is a module.
• Modules may contain submodules, which are subdirectories containing their own scripts.
• This allows us to organize our code by topic, making our project more maintainable.
• Modules may contain a manifest.
  • The module manifest is a file called sqigl.toml which contains configuration information.

Artifacts

• Artifacts are special modules containing migration scripts.
• They are automatically created by sqigl as needed. You won't need to create or manage them manually.
• Migrations move the database from one version to another.
• Unlike other modules, artifacts do not contain submodules.
• They are located in the artifacts/ directory.
• They are named after the version they concern.

Manifests

• Manifests are configuration files.
• They are always named sqigl.toml.
• Their function is determined by the directory they are in.

Project manifest

• The project manifest specifies the project name, version, and database parameters.
• It's located in the project root.
• The project manifest is required.

[project]
version = "0.1.0"
title = "my_project"

[database]
db = "postgres"

Module manifests

• Module manifests specify dependencies.
• They are optional. You won't need to use them most of the time.

[module]
dependencies = ["resources/"]

[[scripts]]
script = "posts.sql"
dependencies = ["users.sql"]

Artifact manifests

• Artifacts are special modules containing scripts called migrations.
• Migrations move a database from one version to another.
• The module manifest specifies what versions are compatible with a migration, and what version they update the database to.
• Artifact manifests are required.

[[migrations]]
script = "schema.sql"
from = "=0.0.0" # The versions this migration is compatible with.
to = "0.1.0" # The version the migration moves the database to.

Dependencies

• Sometimes SQL requires statements to appear in a certain order.
• The most common example is create table statements with a foreign-key relationship.

  -- Correct ✅
  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  
  -- Incorrect ❌
  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  

• When these statements are in different scripts, it creates a dependency relationship between them.
• If sqigl doesn't know about these dependencies, it won't be able to build our code correctly.

Implicit dependencies

• Modules depend on their parent modules implicitly.
• Organizing our project so that our tables with references are at the leaves of our project and tables that are reffered to are at the root naturally expresses our dependency relationships.
• Consider the following project:

  File

  Content

  [project]
  version = "0.1.0"
  title = "my_project"
  
  [database]
  db = "postgres"
  

  Directory

  Directory

  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  

  Directory

  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  

• Because users.sql is in the parent module of posts.sql, it will preceed posts.sql in the build.

  > sqigl project build
  -- [ my_project 0.1.0 ]
  
  -- [ users.sql ]
  
  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  
  -- [ resources/posts.sql ]
  
  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  

• When possible, dependencies should be expressed implicitly.
• Otherwise, you will need to express the dependency explicitly in one of the following ways.

Module-level dependencies

• We can add an entry to a module's manifest indicating that it depends on another module.
• This is useful when a module depends on a module which isn't an ancestor (a sibling for instance).
• Consider the following project:

  File

  Content

  [project]
  version = "0.1.0"
  title = "my_project"
  
  [database]
  db = "postgres"
  

  Directory

  Directory

  Directory

  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  

  Directory

  [module]
  dependencies = ["/users"] # "../users" would also work
                            # This is a list, so we can specify multiple dependencies
                            # (eg ["/foo", "/bar/baz"])
  

  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  

• Because the posts/ module's manifest specifies the users/ module as a dependency, users.sql will preceed posts.sql in the build.

  > sqigl project build
  -- [ my_project 0.1.0 ]
  
  -- [ users/users.sql ]
  
  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  
  -- [ posts/posts.sql ]
  
  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  

Script-level dependencies

• If we have dependencies between two scripts in the same module, we can specify this in the module's manifest.
• Consider the following project:

  File

  Content

  [project]
  version = "0.1.0"
  title = "my_project"
  
  [database]
  db = "postgres"
  

  Directory

  Directory

  [[scripts]]
  script = "posts.sql"
  dependencies = ["users.sql"] # This is a list, so we can specify multiple dependencies
                               # (eg ["foo.sql", "bar.sql"])
  

  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  

  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  

• Because we've identified users.sql as a dependency of posts.sql, it will preceed posts.sql in the build.

  > sqigl project build
  -- [ my_project 0.1.0 ]
  
  -- [ users.sql ]
  
  create table users (
      pk integer primary key auto increment,
      username text unique not null,
      password text not null
  );
  
  -- [ posts.sql ]
  
  create table posts (
      pk integer primary key auto increment,
      user integer not null primary key users(pk)
  );
  

Starting a new project

• To create a new, empty project, use the command sqigl project create <project_name> <database>.
• Database will be postgres or sqlite, depending on the database you're using.
• This will create a new directory named <project_name>, which will be more project root.

[project]
version = "0.1.0"
title = "my_project"

[database]
db = "postgres" # will be "sqlite" if you're using sqlite