Application will not compile when a foreign reference uses schema prefix

[Zlash92]

SQLDelight Version

1.5.3

Operating System

macOS Big Sur 11.6.1

Gradle Version

7.3.1

Kotlin Version

1.6.0

AGP Version

No response

Describe the Bug

My postgres database contains a schema called pld1 and within this schema there are two tables Person and Car. Car references Person through a foreign key. The name of the schema is used as a prefix for the tables and foreign key. Application doesn't compile because of the schema prefix in the REFERENCES statement, even though it is valid sql. Everything works fine if the public schema is used and no schema prefix is used, but this doesn't satisfy my needs.

Stacktrace

> Task :generateMainTestDatabaseInterface FAILED

line 6:65 - '(', ')', ',', DEFERRABLE, MATCH, NOT or ON expected, got '.'
1    CREATE TABLE pld1.Car
2    (
3        id SERIAL PRIMARY KEY,
4        make VARCHAR(255) NOT NULL,
5        person_id VARCHAR(255) NOT NULL,
6        CONSTRAINT fk_personId FOREIGN KEY(person_id) REFERENCES pld1.Person(id)
                                                                      ^
7    )

Gradle Build Script

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
    id("org.springframework.boot") version "2.6.1"
    id("io.spring.dependency-management") version "1.0.11.RELEASE"
    id("com.squareup.sqldelight") version "1.5.3"
    kotlin("jvm") version "1.6.0"
    kotlin("plugin.spring") version "1.6.0"

}

group = "com.example"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11

repositories {
    google()
    mavenCentral()
}

dependencies {
    implementation(kotlin("stdlib"))
    implementation("com.squareup.sqldelight:gradle-plugin:1.5.3")
    implementation("com.squareup.sqldelight:jdbc-driver:1.5.3")

//    implementation("org.springframework.boot:spring-boot-starter-data-rest")
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-data-jpa")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")

    implementation(fileTree(mapOf("dir" to "libs", "include" to listOf("*.jar"))))

    implementation("com.h2database:h2:1.3.148")
    implementation("org.postgresql:postgresql:42.1.4")

    testImplementation("org.springframework.boot:spring-boot-starter-test")

}

tasks.withType<KotlinCompile> {
    kotlinOptions {
        freeCompilerArgs = listOf("-Xjsr305=strict")
        jvmTarget = "11"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

sqldelight {
    database("TestDatabase") {
        packageName = "no.testpack"
        dialect = "postgresql"
        schemaOutputDirectory = file("build/dbs")
        sourceFolders = listOf("sqldelight")
        deriveSchemaFromMigrations = true
    }
    linkSqlite = false
}

[Zlash92]

@AlecStrong Tried using mysql instead of postgres, but seems like an issue there as well.

[Zlash92]

Ok, I was partially mistaken. Realized that in mysql syntax the schema/database of the foreign key relation does not have to be specified (e.g., REFERENCES pld1.Person(id) can be stated without the pld1 prefix). However, in postgres this is necessary - perhaps because the public schema may be default in postgres, while in mysql it defaults to the schema of the table. Also mysql doesn't differ between schema and database like postgres does, though I'm not sure if that's quite relevant for this problem.

In the .sq file, everything is in order by omitting the schema prefix, also using postgres. So the initial problem I've stated can be bypassed.

Two reasons why it's useful to fix in the future:

1. The CREATE TABLE query used directly in the database will differ from how the .sq/sqm file needs to look like.
2. Cross-schema foreign key references are not possible. Though in general this may not be recommended, it is still possible in sql.

[AlecKazakova]

yea we should definitely support this, I don't think it should be too complex to do since we can just treat the whole string pld1.Person as the table name

[AlecKazakova]

just to confirm, do you also create the Person table with the schema prefix?

like

CREATE TABLE pld1.Person (
  ...
);

[Zlash92]

Yep, that's correct!