Installing duckDB

DuckDB Installation

DuckDB has been installed on the MEDS server. We also recommend to install it on your personal machine following those instructions.

MacOS

We recommend to use homebrew to install DuckDb on a Mac:

brew install duckdb

Windows

1. Download the latest version from https://duckdb.org/docs/installation/

2. Create a DuckDB folder inside C:\Users\YOUR_USERNAME\AppData\Local

3. Unzip the duckdb_xx_zip you just downloaded inside that folder (this should put duckdb.exe in this folder)

4. Add this folder to your path C:\Users\YOUR_USERNAME\AppData\Local

   1. Open System Properties by pressing the Windows key on your keyboard and searching for “environment variables”
   2. Access Environment Variables: In the System Properties window, go to the “Advanced” tab. Click on the “Environment Variables” button. Edit the PATH Variable: In the Environment Variables window, you will see two sections: User variables and System variables. Select the “Path” variable under the User section. Click the “Edit” button
   3. Add New Path: In the Edit Environment Variable window, click the “New” button. Type the full directory path of the program you want to add. Press “Enter” to confirm the path.

   

   4. Save Changes: Click “Ok” in the Edit Environment Variable window. Click “Ok” in the Environment Variables window. Click “Ok” in the System Properties window.
   5. Restart your machine

VS Code integration

You can use DuckDB directly from the terminal but if you also want to have the option to have both a SQL script and the terminal open, we recommend to use visual code. There is one setting to be done to link the script and the terminal:

Under the main VS menu, go to Settings -> Keyboard Shortcuts, search for “run selected”, the “run selected text in active terminal” will be one of the options. Set the shift-enter keypress by double-clicking. Right-click on the When column, select Change When Expression, and add editorLangId == 'sql' to restrict to just SQL files and not every kind of file.

Now you can hit Shift+Return at then end of a line in your SQL script and it should run the command directly in the terminal!

Test

Let’s test our new installation using Visual Studio Code:

1. open a terminal from the terminal menu -> new terminal

2. In the terminal type duckdb, this will start duckdb (your prompt in the terminal should now be a D)

3. Open a New text file: file menu -> new text file

4. Copy paste the following code:

-- Start the DB at the terminal: duckdb

CREATE TABLE ducks AS SELECT 3 As age, 'mandarin' AS breed;

SHOW TABLES;

FROM ducks SELECT *;

5. Use Shift+Return to run the SQL code line by line

You should have something that looks like this:

What if this work in your terminal but not in VS Code??

When running the Terminal from VS Code, your path is not always loaded correctly depending of your machine settings. You can check this by running both in an independant terminal and the VS Code terminal:

Mac

echo $PATH

Windows

In PowerShell:

echo $env:PATH

If you do not get the same answers, then you will have to edit the terminal settings in VS Code. For more information please read this page https://code.visualstudio.com/docs/supporting/troubleshoot-terminal-launch