Suggestion: IntelliJ automatic DB connection

[Hollerweger]

HI,

When running a test i can see the connection URL printed to the log and can manually connect in IntelliJ to this db.

i.z.t.d.l.EmbeddedDatabaseReporter: JDBC URL to connect to 'dataSource':
url='jdbc:sqlserver://localhost:49154;databaseName=cywlsplkqkij;user=sa;password=A_Str0ng_Required_Password', scope='ClientControllerTests#createClientsTest'

Is there a way that IntelliJ can automatically connect to this db?
IntelliJ supports also "Data Source from Path" so maybe this plugin could write the connection settings to a defined file path where IntellJ could read it.

This would be quite useful for debugging to avoid searching the connection URL in the logs and updating the config for every test run.

We are using Postgres and MSSQL Server.

[rkrisztian]

So far I've been doing this:

• Add to your test code:

  @Autowired
  DataSource dataSource;

• Add a breakpoint to the test code, and run the test in debug mode.
• Evaluate Expression → dataSource.unwrap(EmbeddedDatabase.class).getJdbcUrl(), and copy its value to the clipboard. (You can import classes there.)
• Press Shift twice, type in Database, and press Enter.
• Add Data Source from URL, paste the URL, and select the PostgreSQL driver (or whichever you use).
• Test the connection, download the driver if missing.

But to keep the same database between test runs, we need more configuration, which I haven't elaborated yet...

This is of course still a bit cumbersome, I wish there was something easier.

[tomix26]

@rkrisztian You don't have to evaluate expressions on the embedded data source to obtain the connection url. The intended and much easier way is to copy it from logs, as @Hollerweger wrote.

@Hollerweger I don't know of any better way at the moment. What is important to note is that the database name may differ for each run or each test execution. So the "Data Source from Path" option would probably not be useful in that case. However, even though I understand that it could be better if the whole solution was automated, I don't find the current process to be too complicated. You just need to copy the link from the log and use the "Data Source from URL" option in IntelliJ. All the necessary data is already included in the link.

[rkrisztian]

Thanks, @tomix26 , I didn't notice that in the documentation, my bad.

[tomix26]

As I already wrote, I have no idea how to make this process faster and more convenient. So for now I'm closing the issue. Feel free to reopen it if you get any specific ideas.

[tomix26]

Ok, I've finally found a solution to the problem. I've prepared a Groovy script to LivePlugin that watches the log and updates the database settings in IntelliJ IDEA according to it.

Here are the instructions:

1. Install the LivePlugin plugin
2. In LivePlugin, create a new Groovy plugin and copy the snippet below into the newly created plugin.groovy file
3. In IntelliJ IDEA, create a new data source with the corresponding name and driver (embedded-postgres, embedded-sqlserver, ... - names and types are configurable at the beginning of the script)
4. Run the plugin and from that moment the database settings will be synchronized with the log

import com.intellij.database.psi.DbPsiFacade
import com.intellij.database.util.DbImplUtil
import com.intellij.execution.filters.ConsoleInputFilterProvider
import com.intellij.execution.filters.InputFilter
import com.intellij.execution.ui.ConsoleViewContentType as ContentType
import com.intellij.openapi.extensions.Extensions
import com.intellij.openapi.progress.ProcessCanceledException
import com.intellij.openapi.project.Project
import com.intellij.openapi.util.Pair

import java.util.regex.Matcher
import java.util.regex.Pattern

import static com.intellij.execution.filters.ConsoleInputFilterProvider.INPUT_FILTER_PROVIDERS
import static com.intellij.openapi.util.text.StringUtil.newBombedCharSequence
import static liveplugin.PluginUtil.changeGlobalVar
import static liveplugin.PluginUtil.show

// depends-on-plugin com.intellij.database

if (isIdeStartup) return

// =====================================================================================
// Customizable mappings of application's data sources to IntelliJ IDEA's database names
// =====================================================================================
def dataSourceMapping = [
        [ds: "dataSource", db: 'embedded-postgres', driver: 'postgresql'],
        [ds: "dataSource", db: 'embedded-sqlserver', driver: 'sqlserver'],
        [ds: "dataSource", db: 'embedded-mariadb', driver: 'mariadb'],
        [ds: "dataSource", db: 'embedded-mysql', driver: 'mysql'],
        [ds: "dataSource", db: 'embedded-h2', driver: 'h2']
]

Pattern pattern = Pattern.compile(".*JDBC URL to connect to '([^']+)': url='(jdbc:([^:]+):[^']+)'.*")

static CharSequence limitAndCutNewline(String text, int maxLength, milliseconds) {
    int endIndex = text.length()
    if (text.endsWith("\n")) {
        --endIndex
    }
    if (maxLength >= 0) {
        endIndex = Math.min(endIndex, maxLength)
    }
    def substring = text.substring(0, endIndex)

    if (milliseconds > 0) {
        return newBombedCharSequence(substring, milliseconds)
    }
    return substring
}

static def updateDatabase(Project project, String dbName, String jdbcUrl) {
    DbPsiFacade.getInstance(project).getDataSources()
            .collect { DbImplUtil.getMaybeLocalDataSource(it) }
            .findAll { it != null }
            .findAll { it.name == dbName }
            .findAll { it.url != jdbcUrl }
            .forEach { ds ->
                ds.url = jdbcUrl
                show("Database '" + ds.name + "' has been updated to '" + jdbcUrl + "'")
            }
}

static def databaseUpdater(Project project, Pattern pattern, List<Map<String, String>> mappings) {
    new InputFilter() {
        List<Pair<String, ContentType>> applyFilter(String consoleText, ContentType contentType) {
            if (!consoleText.contains("EmbeddedDatabaseReporter")) {
                return null
            }
            try {
                CharSequence textForMatching = limitAndCutNewline(consoleText, 500, 1000)
                Matcher matcher = pattern.matcher(textForMatching)
                
                if (matcher.matches()) {
                    def dsName = matcher.group(1)
                    def jdbcUrl = matcher.group(2)
                    def driver = matcher.group(3)

                    def mapping = mappings.find { it.get("ds") == dsName && it.get("driver") == driver }
                    if (mapping) {
                        updateDatabase(project, mapping.get("db"), jdbcUrl)
                    }
                }
            } catch (ProcessCanceledException ex) {
                show("Processing took too long for: " + consoleText)
            }
            return null
        }
    }
}

def extensionPoint = Extensions.rootArea.getExtensionPoint(INPUT_FILTER_PROVIDERS)
def inputFilterProvider = changeGlobalVar("EmbeddedDatabaseUpdater") { prevInputFilterProvider ->
    if (prevInputFilterProvider != null && extensionPoint.hasExtension(prevInputFilterProvider)) {
        extensionPoint.unregisterExtension(prevInputFilterProvider)
    }
    new ConsoleInputFilterProvider() {
        InputFilter[] getDefaultFilters(Project project) {
            [databaseUpdater(project, pattern, dataSourceMapping)]
        }
    }
}
extensionPoint.registerExtension(inputFilterProvider)