Creating Tables

The code in this topic assumes a SQLite database has been mounted on the drive named "mydb". For instructions on mounting such a drive, see
Creating Databases.

To create a table inside of a SQLite database, you use the new-item PowerShell cmdlet, specifying the mounted SQLite drive in the path argument and the table specification for the table as the value argument. For instance:

specifies the path to the SQLite database drive, and optionally the new table (Users in this example)

value

is a valid table specification, suitable for inclusion in a CREATE TABLE DDL statement

The SQLite provider allows you to express the table specification in several ways. You can use a DDL string, as shown above. You can also specify the table using a hashtable of column definitions; for example, this code is functionally equivalent to the previous
example:

In addition, the provider offers a simplified syntax that allows you to express the table specification as a sequence of parameters to new-item. The example below is functionally equivalent to the previous two examples:

The shortcut syntax uses a single dynamic parameter with the ValueFromRemainingArguments flag on the Parameter attribute. The order of the parameters is retained, so the provider can pass them through a small state machine to produce the DDL.

How does the shortcut syntax work? Looking at it as a PowerShell statement, it appears as if "INTEGER" would be mapped to a value to the "id" parameter, but that because you have an unquoted string with spaces, "PRIMARY" would be an unmapped value that would get assigned to the next ordered parameter according to existing PowerShell command-line parsing rules.