Dear @kristijanhusak

I was wondering if it could be at all possible to be able to have lsp completion in files other than sql. Would be really nice to be able to have a toggle to include the database lsp information while inside another file type.

Thanks!

I wonder how I can troubleshoot this issue.

Another question: does the engine suggest columns of the queried table if I were to start writing something like this

select 
from db.schema.table

and then started to type columns between the select and the from like:

select 
col1, col2
from db.schema.table

I am using LunarVim (which again uses nvim-cmp), with Neovim 0.9.1. I am installing the plugin using Lazy.

Just by having this plugin installed I lose all my code autocompletion. Things looks right when I do LspInfo (all the language servers are connected).

There is nothing different in the LspLog when having the plugin installed or not.

My config is basically:

lvim.plugins = {
  {
    "kristijanhusak/vim-dadbod-ui",
    dependencies = { "tpope/vim-dadbod", "kristijanhusak/vim-dadbod-completion"}
  }
}

vim.api.nvim_create_autocmd(
  "FileType",
  { pattern = { "dbui" }, command = ":nmap <buffer> l <Plug>(DBUI_SelectLineVsplit)" }
)

vim.api.nvim_create_autocmd("FileType", {
  pattern = { "sql", "mysql", "plsql" },
  command = ":lua require('cmp').setup.buffer({ sources = {{ name = 'vim-dadbod-completion' }} })",
})

Thank you for the great plugin!

Unfortunately i'm experiencing some lag when using a combination of nvim-compe and vim-dadbod-completion. It happens when i'm in an sql file and type a dot '.' character. I ran a profile and some functions of vim-dadbod-completion are being triggered 10212 times. That would explain the huge size of the intellisense list after pressing that dot. Is there some way to limit the amount of things being loaded in the intellisense list? Or is this caused by nvim-compe? I'm unsure what is causing this.

NVIM v0.5.0-dev+1294-gad8114444
Build type: Debug

vim-dadbod-completion and nvim-compe both up to date.

Attaching the profile.log and the nvim-compe.vim configuration to the ticket.
compe.txt
profile.log

This is pretty niche and is probably more to do with PostgreSQL's information_schema not working in the most predictable manner, but here's what happens.

I have a materialized view in this project and when attempting to list its columns, eg

select os.<something> from open_schools os;

The following message flickers in the command line bar in vim.

[dadbod completion] Fetching columns for table open_schools... Done

Looking at pg_stat_activity it's running this repeatedly.

select table_name,column_name from information_schema.columns where table_name='open_schools'

This always returns nothing because materialized view columns won't show up in information_schema.columns. Before this I would totally have expected they would.

I think not offering completion when it's a materialized view is an acceptable solution really. Looking at the generated queries to produce the output from \d+ open_schools (via psql -E to show hidden queries) it's pretty hairy and very PostgreSQL-specific.

Again, more than happy to assist if I can. I'll have a look today to see if there's a simpler way of querying columns of a materialized view via the information schema.

I created
~/.config/nvim/ftplugin/sql.vim file
and added

" hrsh7th/nvim-cmp
autocmd FileType sql,mysql,plsql lua require('cmp').setup.buffer({ sources = {{ name = 'vim-dadbod-completion' }} })

But it doesn't seem to do anything. Wondering what's the recommended way to add this?

There seems to be an issue with the way that plugin refreshes database tables and columns information.

I was only able to give a cursory look at what is happening here, but it seems that the cache is not being updated after the plugin loads, and I have no idea how to reload the plugin.

For example, if I open a new buffer with filetype=sql, completion works with all of the existing tables and columns.

However, if I now create a new table, :enew with filetype=sql appears to reconnect to the database, but completion doesn't consider the new table at all.

These lines of code seems to be preventing the plugin from reloading the tables and columns into the cache:

function! s:save_to_cache(bufnr, db, table, dbui) abort
...
  if has_key(s:cache, a:db)
    return
  endif
...
endfunction

If I use :DBUI, refreshing and creating a new query works to include the newly created table for completion, but doesn't seem to be aware of its columns, because they do not appear when I try to complete them.

Is there something that I am missing here?

Connecting to a pgsql instance, using nvim-cmp as the completion back-end, and AFAICT the completion list appears to add non-existent columns for selection when using an alias. Please see screen grab below for reference.

As you can see the completion list includes six additional columns that should not be in the list id, name, territoryid, stateprovinceid, countryregioncode, stateprovincecode, isonlystateprovienceflag.

As shown in the query results at the bottom of the screen grab, the only nine columns that should be available from the sales.salesperson schema/table combination using the alias sp are, in the order of the completion list bonus, rowguid, salesytd, salesquota, territoryid, modifieddate, commissionpct, saleslastyear, businessentityid

However, if I avoid using an alias and use the schema.table identifier the completion list only includes the correct columns as shown below:

I'm trying to use this as a coc extension. I have set let g:db=... , I also have DATABASE_URL set.

call plug#begin()
Plug 'neoclide/coc.nvim', {'branch': 'release'}
" ...
Plug 'tpope/vim-dadbod'
" ...
call plug#end()

let g:db = $DATABASE_URL

The :DB command works fine (e.g. vim-dadbod is working). I am still not getting any autocompletion from this plugin and In :CocOpenLog I'm seeing those errors:

2020-04-25T11:17:03.332 ERROR (pid:56192) [completion-complete] - Complete error: db Error: request error nvim_call_function - Vim(let):E716: Key not present in Dictionary: 1
    at /Users/lucas/.config/nvim/plugged/coc.nvim/build/index.js:14243:32
    at NvimTransport.parseMessage (/Users/lucas/.config/nvim/plugged/coc.nvim/build/index.js:10847:17)
    at DecodeStream.<anonymous> (/Users/lucas/.config/nvim/plugged/coc.nvim/build/index.js:10817:18)
    at DecodeStream.emit (events.js:321:20)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:273:9)
    at DecodeStream.Readable.push (_stream_readable.js:214:10)
    at DecodeStream.Transform.push (_stream_transform.js:152:32)
    at DecodeBuffer.DecodeStream.decoder.push (/Users/lucas/.config/nvim/plugged/coc.nvim/build/index.js:13767:12)
    at DecodeBuffer.flush (/Users/lucas/.config/nvim/plugged/coc.nvim/build/index.js:12757:12)
2020-04-25T11:17:03.333 INFO (pid:56192) [completion-complete] - Results from: around

Just installed this and the diagnostics seem to be working fine. However the autocomplete is not displaying.

Running vim 8.2
Node v12.16.0

Hi, thank you for such cool plugin.

I have a problem, I use nvim-cmp with the config:

vim.api.nvim_create_autocmd("FileType", {
    pattern = { "sql", "mysql", "plsql", "redis" },
    callback = function()
        cmp.setup.buffer({ sources = { { name = "vim-dadbod-completion" } } })
    end,
})

And currently when I pick the completion suggested by this plugin only it wraps it in ""

Using nvim-cmp as completion engine and am trying to customize the display of the vim-dadbod-completion source.

I've seen your issue opened with nvim-cmp , and from what I understand of the exchange it should be possible as long as I explicitly set this up using formatting table within cmp?

My relevant setup is as follows:

~/.config/nvim/lua/plugins.lua
...
  use({
    "kristijanhusak/vim-dadbod-ui",
    config = [[require("dadbodui")]],
    requires = {
      { "tpope/vim-dadbod" },
      {
        "kristijanhusak/vim-dadbod-completion",
        config = function()
          vim.cmd(
            [[autocmd FileType sql,mysql,plsql lua require("cmp").setup.buffer({sources = {{ name = "vim-dadbod-completion" }, { name = "buffer" },} })]]
          )
        end,
      },
    },
  })
...

~/.config/nvim/lua/mycmp.lua
...
  formatting = {
    format = function(entry, vim_item)
      vim_item.menu = ({
        nvim_lsp = "[LSP]",
        buffer = "[BFR]",
        path = "[PTH]",
        nvim_lua = "[LUA]",
        vim_dadbod_completion = "[DB]",
      })[entry.source.name]
      return vim_item
    end,
  },
})
...

But I still only have TEXT listed as the completion source.

I guess my question is what is the correct way of manually assigning this? I know you use - in the name, but the vim_item does not accept those so I changed to _. I'm sure I'm missing something obvious, can you provide any insight please?

Ok so, I'm a totaly noob in sql but why there is no sugestions for just normal sql syntax like if readme gif?
I don'y have select, from etc. syntax, just a column name sugestions. What soould I do to enable it?

Im using vim-dadbod, instaled from coc, macOS.

I don't know why I keep getting this error:

Error executing Lua callback: .../share/nvim/lazy/lazy.nvim/lua/lazy/core/handler/cmd.lua:45: function db_ui#find_buffer[25]..59[19]..FileType Autocomm ands for "mysql"..function vim_dadbod_completion#fetch[16]..<SNR>80_get_buffer_db_info[8]..db#connect, line 29: DB exec error: /usr/bin/mysql: Deprecat ed program name. It will be removed in a future release, use '/usr/bin/mariadb' instead stack traceback: [C]: in function 'cmd'
  .../share/nvim/lazy/lazy.nvim/lua/lazy/core/handler/cmd.lua:45: in function <.../share/nvim/lazy/lazy.nvim/lua/lazy/core/handler/cmd.lua:16>

I'm using mariadb instead of MySQL. Here's my configuration:

...
  {
    "tpope/vim-dadbod",
    config = function()
      local function db_completion()
        require("cmp").setup.buffer { sources = { { name = "vim-dadbod-completion" } } }
      end

      vim.g.db_ui_save_location = vim.fn.stdpath "config" .. require("plenary.path").path.sep .. "db_ui"

      vim.api.nvim_create_autocmd("FileType", {
        pattern = {
          "sql",
        },
        command = [[setlocal omnifunc=vim_dadbod_completion#omni]],
      })

      vim.api.nvim_create_autocmd("FileType", {
        pattern = {
          "sql",
          "mysql",
          "plsql",
        },
        callback = function()
          vim.schedule(db_completion)
        end,
      })
    end,
    cmd = { "DBUIToggle", "DBUI", "DBUIAddConnection", "DBUIFindBuffer", "DBUIRenameBuffer", "DBUILastQueryInfo" },
    dependencies = {
      "kristijanhusak/vim-dadbod-ui",
      "kristijanhusak/vim-dadbod-completion",
    },
  },
  {
    "nanotee/sqls.nvim",
    cmd = {
      "SqlsExecuteQuery",
      "SqlsExecuteQueryVertical",
      "SqlsShowDatabases",
      "SqlsShowSchemas",
      "SqlsShowConnections",
      "SqlsSwitchDatabase",
      "SqlsSwitchConnection",
    },
  },
...

when assigning name of source to vim-dadbod-completion in nvim-cmp it causes errors.
In lua having dashed key in tables causes problems.
I have fixed it for myself by changing source name at vim-dadbod-completion.lua to vim_dadbod_completion

After the latest commits, whenever I open nvim I'm getting:

Error detected while processing /home/pedro/git-repos/private/dotfiles/vim/bundle/.cache/init.vim/.dein/after/plugin/vim_dadbod_completion.vim:
line    5:
E117: Unknown function: compe#source#vim_bridge#register

Hi, is there a feature to format sql statements?

or perhaps I am not understanding correctly.

Here's what I have done:

In init.nvim

Plug 'tpope/vim-dadbod'
Plug 'kristijanhusak/vim-dadbod-ui'
Plug 'kristijanhusak/vim-dadbod-completion'

autocmd FileType sql setlocal omnifunc=vim_dadbod_completion#omni

Then I open vim vim and :DBUI, then try to autocomplete with omnifunc <C-x><C-o.

I get a warning: SQLComplete:The dbext plugin must be loaded for dynamic SQL completion

What have I missed?

Hi, I installed / updated this plugin to latest with support for cmp but whenever I try to use mapped keys like <CR>, <Tab>, <C-p> I get

Error executing lua [string "v:lua"]:1: attempt to index field 'utils' (a nil value)

this apply to all buffers (yes this is full error message 😐 ). If I uninstall vim-dadbod-completion or git reset git reset --hard a3f8ea8e666f4bebe12739d8854a4bd31544dfbb before cmp support everything works as expected.

This happens even when the { name = "vim-dadbod-completion" } is not used in sources for nvim-cmp.

For , I use default mappings

["<C-p>"] = cmp.mapping.select_prev_item()
["<CR>"] = cmp.mapping.confirm({
	behavior = cmp.ConfirmBehavior.Replace,
	select = true,
}),

so I don't think it is my custom config.

nvim --version
NVIM v0.6.0-dev+189-g274a3504a

If you need more info just let me know.

This might already exist but I get a huge list of SQL keywords and sometimes the DB specific values (table names, column names, etc..) get buried in them. Is there an effective way to prefer column names and table names to sort higher?

Or probably more difficult prefer columns and functions in SELECT and WHERE scope, tables and table functions and buffer CTEs in FROM scopes.

Also, is there a way to restrict keyword selections (FROM, SELECT) to only show when all caps are used?
Like "Se" would no longer suggest SELECT?

Hello,

I'm encountering an issue with the vim-dadbod-completion plugin when working with large MSSQL Server databases. Specifically, the autocompletion feature for database columns is not functioning as expected.

Issue Description:

When attempting to write SQL queries in Neovim, the column autocompletion feature does not trigger after typing a period following a table alias. This issue seems to be particularly prevalent in larger databases. For example, I've noticed this behavior in a database with over 12,000 columns and 1,150 tables. The autocompletion fails to fetch column names, hindering the workflow.

Steps to Reproduce:

1. Connect to a large MSSQL Server database using vim-dadbod-completion.
2. Write a query such as SELECT TOP 10 * FROM some_table st WHERE st..
3. After typing the period following the table alias (st.), expect column names to be suggested, but no suggestions appear.

Expected Behavior:

The plugin should provide a list of column names for autocompletion after typing the period following a table alias.
This issue does not seem to occur with smaller databases.

I would appreciate any guidance or suggestions on resolving this issue. Is there a configuration setting that I might be missing, or is this a known limitation with larger databases?

This is my current setting:

return {
	'kristijanhusak/vim-dadbod-ui',
  dependencies = {
    'tpope/vim-dadbod',
    'tpope/vim-dotenv',
    { "kristijanhusak/vim-dadbod-completion", ft = { "sql", "mysql", "plsql"}, lazy = true },
  },
  cmd = {
      "DBUI",
      "DBUIToggle",
      "DBUIAddConnection",
      "DBUIFindBuffer",
  },

  init = function()
    vim.g.db_ui_use_nerd_fonts = 1
    vim.g.db_ui_show_database_icon = 1
    -- vim.g.db_ui_force_echo_notifications = 1
    vim.g.db_ui_win_position = 'left'
    vim.g.db_ui_winwidth = 30


    -- opening it in a new tab
    vim.keymap.set('n', '<leader><leader>db', ':tab DBUI<cr>', {})

    -- just close the tab, but context related of the keybinding
    vim.keymap.set('n', '<leader><leader>tq', ':tabclose<cr>')

    vim.api.nvim_create_autocmd("FileType", {
      pattern = "sql",
      callback = function()
          vim.bo.omnifunc = "vim_dadbod_completion#omni"
      end,
    })
  end,
}

Thank you for your assistance and for the great work on this plugin.

I get

DEBUG (pid:75108) [extension-coc-db] - COC DB ERROR Error: request error nvim_call_function - DB: no adapter for postgres

when trying out the extension. I'm using vim-dadbod-ui and have $DBUI_URL correctly set, dadbod-ui works and I can browse db and make queries, just coc-db completion isn't working for some reason.

While editing a SQL file, when attempting to autocomplete fields (for instance, I type TableName. then any letter) I get the following error:

Error detected while processing TextChangedI Autocommands for "*":
Error executing lua callback: Vim(call):E730: using List as a String
stack traceback:
        [C]: in function 'vim_dadbod_completion#omni'
        ...tart/vim-dadbod-completion/lua/vim_dadbod_completion.lua:39: in function 'complete'
        ...re/nvim/site/pack/paqs/start/nvim-cmp/lua/cmp/source.lua:314: in function 'complete'
        ...hare/nvim/site/pack/paqs/start/nvim-cmp/lua/cmp/core.lua:289: in function 'complete'
        ...hare/nvim/site/pack/paqs/start/nvim-cmp/lua/cmp/core.lua:169: in function 'callback'
        ...hare/nvim/site/pack/paqs/start/nvim-cmp/lua/cmp/core.lua:219: in function 'autoindent'
        ...hare/nvim/site/pack/paqs/start/nvim-cmp/lua/cmp/core.lua:161: in function 'on_change'
        ...hare/nvim/site/pack/paqs/start/nvim-cmp/lua/cmp/init.lua:312: in function 'callback'
        .../site/pack/paqs/start/nvim-cmp/lua/cmp/utils/autocmd.lua:49: in function 'emit'
        .../site/pack/paqs/start/nvim-cmp/lua/cmp/utils/autocmd.lua:23: in function <.../site/pack/paqs/start/nvim-cmp/lua/cmp/utils/autocmd.lua:22>

Here is how I have configured autocompletion for SQL:

cmp.setup.filetype("sql", {
	view = {
		entries = "native",
	},
	snippet = {
		expand = function(args)
		    require("luasnip").lsp_expand(args.body)
		end,
	},
	mapping = cmp.mapping.preset.insert({
		["<C-d>"] = cmp.mapping.scroll_docs(-4),
		["<C-f>"] = cmp.mapping.scroll_docs(4),
		["<C-Space>"] = cmp.mapping.complete(),
		["<C-e>"] = cmp.mapping.close(),
		["<CR>"] = cmp.mapping.confirm({ select = true }),
	}),
	sources = cmp.config.sources(
		{ { name = "vim-dadbod-completion" }, { name = "luasnip" } },
		{ { name = "buffer" } }
	),
})

I am likely doing something wrong, but this used to work until quite recently. Other completion sources appear to be working fine, as far as I can tell.

I am using Neovim 0.8.0, and nvim-cmp commit aee40113c2ba3ab158955f233ca083ca9958d6f8

I have two databases. For one database column names are being suggested as expected. For the other they are not.

I found this issue, where I tried this query.
The query finishes in SQL Server in 00:00:00s (rounded I guess)

I also checked the troubleshoot here
which results in the following

Since everything works with other databases, but the one in question, even when I remove all other databases from the configuration in the vimrc, I think the config to the database is fine.

I attached the logs (gotten via profile func *).
Can I try something else?

dbui_bug.log
dbui_working.log

Firstly, thanks for this tool, it worked perfectly the first time I tried it. Now, however, I'm encountering this problem. In PostgreSQL, every time I try to complete, I get this error:

Error detected while processing function coc#_complete:
line    3:
E731: using Dictionary as a String
Press ENTER or type command to continue

This happens both when Coc is enabled and disabled using using omnicompletion (having set :setlocal omnifunc=vim_dadbod_completion#omni).

I'm using PostgreSQL 12.2 with this database and neovim 0.4.3. All the plugins are up to date and loaded via Pathogen.

One thing to note, the completion does still appear to work but input is interrupted by the error message flashing up after every keystroke.

Please let me know if you need any more info.

line 73:
E15: Invalid expression: ]}
E15: Invalid expression: { 'sql': [ {'complete_items': ['vim-dadbod-complet
ion']}, ], ]}
Press ENTER or type command to continue

I saw in readme on the screen that there are pluses used as vertical boundaries, but see nothing about such customizations in docs :(

hey, thanks for the plugin!
I saw your config https://github.com/kristijanhusak/neovim-config/blob/bleeding-edge/nvim/lua/partials/completion.lua#L18 and tried to do the same:

return function()
    require "compe".setup {
        enabled = true,
        autocomplete = true,
        debug = false,
        min_length = 1,
        preselect = "enable",
        throttle_time = 80,
        source_timeout = 200,
        incomplete_delay = 400,
        max_abbr_width = 100,
        max_kind_width = 100,
        max_menu_width = 100,
        documentation = true,
        allow_prefix_unmatch = true,
        source = {
            path = {menu = " "},
            buffer = {menu = " "},
            vsnip = {menu = " "},
            nvim_lsp = {menu = "  "},
            nvim_lua = {menu = " "},
            spell = true,
            calc = true,
            tags = true,
            -- omni = true,
            vim_dadbod_completion = true,
            treesitter = true
        }
    }
    map("i", "<cr>", 'compe#confirm("<cr>")', {silent = true, expr = true})
    -- map("i", "<s-cr>", "compe#complete()", {silent = true, expr = true})
end

but with that when typing in query buffer i see nothing from dadbod completion :(

Just installed this and the diagnostics seem to be working fine. However the autocomplete is not displaying.

Hi. I'm using vim-dadbod and this plugin (through coc-db), and I'm not able to get completion candidates through the plugin. Dadbod works fine for executing queries. How can I debug this?
I'm setting the variable b:db after the buffer loads using dadbod, not through an autocmd. Could that be the reason?
Thanks in advance.

I am unsure if this is a feature request, or a need for better understanding on my part... Thus far, I only get completion of table names and columns from the database specified in the connection. But when I SELECT FROM OtherDatabase.dbo.OtherTable I do not see completion suggestions.

Is this by design, or am I doing something wrong?

I am using a sqlserver connection.

If this is by design, might you consider adding this feature? Or perhaps this would be a feature added to vim-dadbod?

Please add support for completion with coq.nvim.

continuation from #31 (comment)

Here is the config

g:db setting https://github.com/zer09/nvim-config/blob/7b584b177697e6981f8949a143460a6c26267e57/init.lua#L12

cmp autocmd https://github.com/zer09/nvim-config/blob/7b584b177697e6981f8949a143460a6c26267e57/lua/cmds.lua#L63-L68

package installation https://github.com/zer09/nvim-config/blob/7b584b177697e6981f8949a143460a6c26267e57/lua/plugins/init.lua#L82-L88

lsp server setup https://github.com/zer09/nvim-config/blob/929012eab2acb07f46033576c98c961487207c28/lua/plugins/config/lsp_conf.lua#L13

Thanks

I'm getting an timeout error message from CoC source db timeout after 5000ms on a database with a large number of tables but its fine on all of the other databases. The database has 43K tables and its nor surprising that it times out. It works great on all of the other databases which are < 1000 tables.

It might be a useful feature to be able to cache the tables. I'm using vim-dadbod-ui which has already loaded the tables. Would it be possible to have an option for coc-db to use the table list already loaded in vim-dadbod-ui?

In neovim with cmdheight=0 this forces a hit-enter-prompt.

I'd like the option to disable (or redirect) messages rather than echoing them.